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:
OracleConnection conn;
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);
conn.Open();
OracleCommand cmnd;
string query;
query="insert into emp(id,name,photo) values(" + txtid.Text + "," + "'" + txtname.Text + "'," + " :BlobParameter )";
//insert the byte as oracle parameter of type blob
OracleParameter blobParameter = new OracleParameter();
blobParameter.OracleType = OracleType.Blob;
blobParameter.ParameterName = "BlobParameter";
blobParameter.Value = blob;
cmnd=new OracleCommand(query,conn);
cmnd.Parameters.Add(blobParameter);
cmnd.ExecuteNonQuery();
MessageBox.Show("added to blob field");
cmnd.Dispose();
conn.Close();
conn.Dispose();
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Code in the save button:
private void btnsave_Click(object sender, System.EventArgs e)
{
this.updatedata();
}
Code in the close button:
private void btnclose_Click(object sender, System.EventArgs e)
{
this.Close();
}
Note: Add a few rows in the table using the above mentioned procedure so that it will be helpful in retreiving the blob field.
(2) For retreiving the image from the blob field of the oracle database
Open a form to the same project and name it as frmread.cs. Then in your project add using system.data.oracleclient.Now in the form1 add one combobox, one label, two buttons and a picturebox and Name them as follows:
- combobox:cmbempname (*make its dropdown property as dropdownlist)
- label:lblname
- buttons:btnshow,btnclose
- picturebox:pctimg
When the form loads the name is added in the combobox, the user has to select a name, and when he clicks the show button, the corresponding image in the blob field is retreived and added to the picturebox. See the form in the picture frmread.jpg.
Declare these variables as global in the form:
OracleConnection conn;
string connstr;
OracleDataAdapter empadap;
DataSet dset;
Code for the Show button:
There is a function named connection() which performs the work of adding all the names ito combobox after getting them from the database using oracledataadapter(). This should be used in the form's Load event,so that it helps user to select one of the names which is in the table added using the previous form.
private void Frmread_Load(object sender, System.EventArgs e)
{
Connection();
}
private void Connection()
{
//connect to the database and table
//selecting all the columns
//adding the name column alone to the combobox
try
{
connstr="User Id=scott;Password=tiger;Data Source=star;";
conn=new OracleConnection(connstr);
conn.Open();
empadap=new OracleDataAdapter();
empadap.SelectCommand=new OracleCommand("SELECT * FROM emp",conn);
dset=new DataSet("dset");
empadap.Fill(dset);
DataTable dtable;
dtable=dset.Tables[0];
cmbempname.Items.Clear();
foreach(DataRow drow in dtable.Rows)
{
cmbempname.Items.Add(drow[1].ToString());
cmbempname.SelectedIndex=0;
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Now the code inside the show button is as follows:
private void btnshow_Click(object sender, System.EventArgs e)
{
DataTable dataTable = dset.Tables[0];
//if there is an already an image in picturebox, then delete it
if(pctimg.Image != null)
{
pctimg.Image.Dispose();
}
//using filestream object write the column as bytes and store it as an image
FileStream FS = new FileStream("image.jpg", FileMode.Create);
foreach(DataRow dataRow in dataTable.Rows)
{
if(dataRow[1].ToString() == cmbempname.SelectedItem.ToString())
{
byte[] blob = (byte[])dataRow[2];
FS.Write(blob,0,blob.Length);
FS.Close();
FS = null;
pctimg.Image = Image.FromFile("image.jpg");
pctimg.SizeMode = PictureBoxSizeMode.StretchImage;
pctimg.Refresh();
}
}
}
Code in the close button:
private void btnclose_Click(object sender, System.EventArgs e)
{
conn.Close();
this.Close();
}
Note: The image is retreived from the blob field and added to the picturebox control and also is stored as "image.jpg" in the bin folder of your application.
Summary:
I hope this article will clear your doubts regarding how to insert an image in a blob field of an oracle database and then how to retreive the image stored in a blob field.