25.15. Using BYTEA Data Type in a .NET Application

So far all our examples have shown the usage of non-binary data types. However, for storing certain data like images or documents one needs to make use of a binary datatype. The binary datatype available in EnterpriseDB is called BYTEA, and the following samples highlight the insertion and retrieval of binary data from .NET.

First we will use the following script file for the creation creating the of "EMP_DETAIL", table in EnterpriseDB, which is needed to executefor running the BYTEA examples with .NET connectivity which we will make use in the examples in this section.

create or replace function BYTE_FUNC(x bytea) return bytea IS
BEGIN
 return x;
END;
CREATE TABLE EMP_DETAIL(EMPNO INT4 PRIMARY KEY,PIC BYTEA);
CREATE OR REPLACE PROCEDURE ADD_PIC(e IN int4,x IN bytea) AS
BEGIN
 INSERT INTO EMP_DETAIL VALUES(e,x);
END;
CREATE OR REPLACE FUNCTION GET_PIC(e IN int4) RETURN BYTEA IS
DECLARE
 x BYTEA;
BEGIN
 SELECT PIC INTO x from EMP_DETAIL WHERE EMPNO=e;
 RETURN x;
END;

As seen above a 'BYTE_FUNC' is created in which the return type is BYTEA. Hence the image being stored is of type BYTEA, in correspondence to a given 'EMPNO' i.e. employee id.

Stored Procedures used in the .NET code.

For displaying the usage of BYTEA data type via .NET the following two procedures will be created:

1. Add_Pic - A stored procedure taking two IN parameters which are inserted into the "emp_detail" table. The first parameter "empID" is an integer value corresponding to an employee's id, whereas the second parameter "pic" is a bytea datatype which is storing an image for an employee corresponding to the first parameter of "empID".

2. Get_Pic - A stored procedure taking one IN parameter and returning one OUT parameter. We pass the "empID" as in IN parameter against which the "picture" column from the "emp_detail" table is returned as a bytea OUT parameter.

Following is the SQL script for the above mentioned stored procedures:

'Add_Pic' Procedure:

CREATE OR REPLACE PROCEDURE ADD_PIC(empID IN int4,pic IN bytea) AS
BEGIN
INSERT INTO EMP_DETAIL VALUES(empID,pic);
END;


'Get_Pic' Procedure:

CREATE OR REPLACE PROCEDURE get_pic(empID IN int4,picture OUT bytea) IS
BEGIN
SELECT PIC INTO picture from EMP_DETAIL WHERE EMPNO=empID; 
END;

25.15.1. Retrieving the BYTEA Column Using .NET Code

Now that we are done with creating the stored procedures used for inserting and getting an image let's make use of those stored procedures from some .NET code. The following example retrieves a picture against a given employee (making use of the "get_pic" stored proc) and stores it onto our hard disk.

<% @ Page Language="C#" %> 
<% @Import Namespace="EnterpriseDB.EDBClient" %> 
<% @Import Namespace="System.Data" %> 
<% @Import Namespace="System.IO" %> 

<script language="C#" runat="server"> 

private void Page_Load(object sender, System.EventArgs e) 
{ 
	string strConnectionString = @"Server=localhost;Port=5444;
	User Id=enterprisedb;Password=enterprisedb;Database=edb"; 
	EDBConnection conn = new EDBConnection(strConnectionString);	 
			 
	try 

		{ 
			conn.Open(); 
				 
			string SQL = "select pic from emp_detail where empno = :empID"; 
			EDBCommand cmd = new EDBCommand(SQL); 
			cmd.Connection = conn; 
				 
			cmd.Parameters.Add(new EDBParameter
			(":empID",EDBTypes.EDBDbType.Integer)); 
			cmd.Parameters[0].Value = 101;				 

			EDBDataReader reader =  cmd.ExecuteReader(); 
			reader.Read(); 
			if (reader.HasRows) 
			{ 
				Byte[] image = new Byte[Convert.ToInt32
				((reader.GetBytes(0, 0,null, 0, Int32.MaxValue)))]; 
				reader.GetBytes(0, 0, image, 0, image.Length);	 
							 
				FileStream fs = new FileStream
				(@"c:\tips.gif", FileMode.Create, FileAccess.ReadWrite); 

				for(int i=0;i<image.Length;i++) 
						fs.WriteByte(image[i]);				 

					fs.Close();	
				} 
			 
				conn.Close(); 

				Response.Write("File Saved to Disk"); 
			} 
			catch(EDBException ex) 
			{ 
				Response.Write(ex.ToString()); 
			} 
	finally { 
		conn.Close(); 
	} 
} 
		 
</script>

First a connection with the database is established by specifying the server name, port number, user ID, and password. If due to some reason a connection is unable to establish an error message will be displayed.

Then the user is asked to give the employee id against which the image will be retrieved and saved onto the hard disk. The image is retrieved depending on the 'empID' parameter that is passed in against which the image is retrieved into a byte array. To get the image as a whole we loop through the raw bytes and write out the content via a FileStream into a file called "tips.gif". The following code shows how we would do this:

     
	  EDBDataReader reader =  cmd.ExecuteReader(); 
			reader.Read(); 
			if (reader.HasRows) 
			{ 
			Byte[] image = new Byte[Convert.ToInt32
			((reader.GetBytes(0, 0,null, 0, Int32.MaxValue)))]; 
			reader.GetBytes(0, 0, image, 0, image.Length);	 
			FileStream fs = new FileStream(@"c:\tips.gif", 
			FileMode.Create, FileAccess.ReadWrite); 

				for(int i=0;i<image.Length;i++) 
				fs.WriteByte(image[i]);				 

					fs.Close();
                                       	 }

Once we are done with writing out our content we can close the FileStream object and subsequently the EDBConnection object. If everything goes well you should see the following message:

25.15.2. Insertion of the BYTEA Column via .NET Code

The given example describes how a user can store an image to EnterpriseDB using a bytea data type and correspondingly assign it to an employee with reference to their id. This is done by using the "add_pic" procedure.

<% @ Page Language="C#" %> 
<% @Import Namespace="EnterpriseDB.EDBClient" %> 
<% @Import Namespace="System.Data" %> 
<% @Import Namespace="System.IO" %> 

<script language="C#" runat="server"> 

private void Page_Load(object sender, System.EventArgs e) 
{ 
	string strConnectionString = @"Server=localhost;Port=5444;
	User Id=enterprisedb;Password=enterprisedb;Database=edb"; 
	EDBConnection conn = new EDBConnection(strConnectionString);	 
			 
	try 

			{ 
			FileStream fs = null; 
fs = new FileStream(@"C:\WINDOWS\Web\tips.gif", FileMode.Open, FileAccess.Read); 
			Byte[] data = new Byte[fs.Length]; 
			fs.Read(data, 0, data.Length); 
			fs.Close(); 

			conn.Open(); 
				 
			string SQL = "insert into emp_detail values(:empID,:Picture)"; 
			EDBCommand cmd = new EDBCommand(SQL); 
			cmd.Connection = conn; 
				 
			cmd.Parameters.Add(new EDBParameter
			(":empID",EDBTypes.EDBDbType.Integer)); 
			cmd.Parameters[0].Value = 101; 

			cmd.Parameters.Add(new EDBParameter
			(":Picture",EDBTypes.EDBDbType.Bytea)); 
			cmd.Parameters[1].Value = data; 

			cmd.ExecuteNonQuery(); 
			conn.Close(); 

			Response.Write("Image Inserted into DataBase"); 
			} 
			catch(EDBException ex) 
			{ 
				Response.Write(ex.ToString()); 
			} 
	finally { 
		conn.Close(); 
	} 
} 
		 
</script>

Similar to the previous example first a connection is established with the database server. Once a connection is established the user is asked for the location on the hard disk from where the picture is to be retrieved. Also the user is aksed for the 'empID', against which the picture is be stored in the 'emp_detail' table.

After retrieving the data it is stored as a byte array; whose length will start from zero to the max length of the file stream given as 'fs'.

Once the file has been retrieved as a FileStream, it will be converted to a byte array and stored in the 'emp_detail' table. The index of the array will start range from 0 to the total number of bytes in the FileStream object being used to retrieve the image. This functionality is highlighted by the following code:

   
FileStream fs = null; 
fs = new FileStream(@"C:\WINDOWS\Web\tips.gif", FileMode.Open, FileAccess.Read); 
				Byte[] data = new Byte[fs.Length]; 
				fs.Read(data, 0, data.Length);

Once the file is saved in the database the following message will be seen by the user.

Note: When we create a file from Asp.Net we need to assign proper authorization to ASPNET (MachineName/ASPNET) account; as any application that is made in asp.net needs to run under the ASPNET user account.
Also note that the path specified must be valid and if a file with the same name already exists at the specified location then the system will overwrite the existing image.