Insert /Retrieve an Image Into/ From a Blob Field in Oracle Database using C#.Net

General:

The following code will help you to create a table called "emp" in oracle database with three fields namely id, name, photo. The field photo is a blob field for which you are going to insert an image as the value using the code. The image size may be anything even in GB as the blob field in oracle can store upto 4GB of size.

Code to create a table in oracle database:

public void oracleconnection()

{

try

{

//use your own userid,password and datasorce

connstr="User Id="userid ";Password="password" ;Data Source="datasource ";";

conn=new OracleConnection(connstr);

conn.Open();

OracleCommand cmnd=conn.CreateCommand();

cmnd.CommandText="CREATE TABLE emp(id INTEGER,name VARCHAR2(50),photo BLOB)";

cmnd.ExecuteNonQuery();

}

catch(Exception e)

{

MessageBox.Show(e.Message);

}

}

Working of the code:

Using the fileopendialog() you can give the path of the .jpg file in your system. This file is then converted as a bitmap image and stored in a picture box as its image. To insert an image into the blob field, you have to use a filestream object. Using filestream you have to read() the imagefile and store it as byte[] array as in C#.Net. Then you have to insert the byte array to the blob field as an Oracle parameter which is of parameter type Blob and its value as the byte[] array specified above, using normal sql query used for insertion.

The same you have to do while retrieving the image. You have to specify the name to store the retrieved image as "image.jpg" (which is stored in the bin folder of your project). For this you have to select all the rows in the table and select the column of teh image ie. The blob field with the datarow and the datacolumn properties where the image is stored as blob in the database. Using the same filestream object write() the entire file upto its length and retrieve the same image in the picture box control.

USING THE CODE:

(1) For inserting the data into the blob field of the database

Note: This should be done after creating the table in oracle database as specified above in this article.

Open a project in C# and name it as oracleblob. Now click add reference and in .Net category select "System.data.Oracleclient.dll". Then in your project add before the namespace using system.data.oracleclient.

Now in the form1 add two textboxes, two labels, three buttons and a picturebox and name them as follows:

• textbox:txtid,txtname
• label:lblid,lblempname
• buttons:btnbrowse,btnsave,btnclose
• picturebox:pctimg

Declare these variables as global:

string connstr;

When the user gives the id and name in the textboxes and update the picturebox's image by specifying the path of image using Browse button, the picture is loaded in the picturebox. When he clicks the save button then these details are inserted as a row in the table. After inserting he can close the application using close button.(see picture of the form form1.jpg).

The code for the browse button is as follows:

This will open a dialogbox wherein you can select the image file's path.

private void btnbrowse_Click(object sender, System.EventArgs e)

{

try

{

FileDialog fldlg=new OpenFileDialog();

//specify your own initial directory

fldlg.InitialDirectory=@":D\";

//this will allow onlt those file extensions to be added

fldlg.Filter="Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif";

if(fldlg.ShowDialog()==DialogResult.OK)

{

imagename=fldlg.FileName;

Bitmap newimg=new Bitmap(imagename);

pctimg.SizeMode=PictureBoxSizeMode.StretchImage;

pctimg.Image=(Image)newimg;

}

fldlg=null;

}

catch(System.ArgumentException ae)

{

imagename=" ";

MessageBox.Show(ae.Message.ToString());

}

catch(Exception ex)

{

MessageBox.Show(ex.Message.ToString());

}

}

The code for save button is as follows:

This code will add the details specified in textboxes namely the id, name and the image in picturebox to a row in the table created inoracle database. This code uses the function Updatedata() which is explained as follows:

private void updatedata()

{

//use filestream object to read the image.

//read to the full length of image to a byte array.

//add this byte as an oracle parameter and insert it into database.

try

{

//proceed only when the image has a valid path

if(imagename!="")

{

FileStream fls;

fls=new FileStream(@imagename,FileMode.Open,FileAccess.Read);

//a byte array to read the image

byte[] blob=new byte[fls.Length];

fls.Read(blob,0,System.Convert.ToInt32(fls.Length));

fls.Close();

//open the database using odp.net and insert the data

connstr="User Id="userid";Password="password";Data Source="datasource";";

conn=new OracleConnection(connstr);