Search This Blog

Friday, October 21, 2011

Inserting and Retrieving images from SQL Server Database using C#

Database
1.Create a database named TestImage
2.create a table called test_table holding two columns id_image(datatype: nvarchar[50]) and pic(datatype: image). 
Front End 


1.create a new project in visual studio
2.select windows application, language c# and name it as TestImage

3. drop two labels, a textbox, a combo box, three buttons and two picture boxes on your win form as shown below

4.
The first picture box will only displays the image you are going to save in your database    providing some id in   the textbox (I used this to just recall the image from database) once      an image is loaded on the clicking      event of Store button we are going to insert that         image with provided ID into the database and the combo box will load itself with all   available IDs in our database. By selecting an id we and clicking Retrieve the respective image will be shown in the second picture box. 
5.double click the button.It will create a button click event.
Here declare a global string
string imagename;
a data adapter
SqlDataAdapter empadap1;
and a dataset
DataSet dset;
you will also require to use System.IO and System.Data.SqlClient
using System.IO;
 
using System.Data.SqlClient;

now we need to create a method to insert our image into the database and second method to retrieve all images. I named the method inserting my image into the database as update() and to retrieve my images I named my method as connection(). Now update() uses filestream to convert the image into binary data since image datatype in SQL Server 2005 use to store binary data in its image datatype. While connection() simply convert that binary data to an image using identical technique for us. Code for both the methods is:
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 fs;
 
fs = new FileStream(@imagename, FileMode.Open, FileAccess.Read);
 
//a byte array to read the image
 
byte[] picbyte = new byte[fs.Length];
 
fs.Read(picbyte, 0, System.Convert.ToInt32(fs.Length));
 
fs.Close();
 
//open the database using odp.net and insert the data
 
string connstr = @"Data Source=.;Initial Catalog=TestImage;
                Persist Security Info=True;User ID=sa";
 
SqlConnection conn = new SqlConnection(connstr);
 
conn.Open();
 
string query;
 
query = "insert into test_table(id_image,pic) values(" + 
textBox1.Text + "," + " @pic)";
 
SqlParameter picparameter = new SqlParameter();
 
picparameter.SqlDbType = SqlDbType.Image;
 
picparameter.ParameterName = "pic";
 
picparameter.Value = picbyte;
 
SqlCommand cmd = new SqlCommand(query, conn);
 
cmd.Parameters.Add(picparameter);
 
cmd.ExecuteNonQuery();
 
MessageBox.Show("Image Added");
 
cmd.Dispose();
 
conn.Close();
 
conn.Dispose();
 
Connection();
 
}
 
}
 
catch (Exception ex)
{
 
MessageBox.Show(ex.Message);
 
}
 
}
 
//----------------------------------------
 
private void Connection()
{
 
//connect to the database and table
 
//selecting all the columns
 
//adding the name column alone to the combobox
 
try
{
 
string connstr = @"Data Source=.;Initial Catalog=TestImage;

            Persist Security Info=True;User ID=sa";
 
SqlConnection conn = new SqlConnection(connstr);
 
conn.Open();
 
empadap1 = new SqlDataAdapter();
 
empadap1.SelectCommand = new SqlCommand("SELECT * FROM test_table"

            , conn);
 
dset = new DataSet("dset");
 
empadap1.Fill(dset);
 
DataTable dtable;
 
dtable = dset.Tables[0];
 
comboBox1.Items.Clear();
 
foreach (DataRow drow in dtable.Rows)
{
 
comboBox1.Items.Add(drow[0].ToString());
 
comboBox1.SelectedIndex = 0;
 
}
 
}
 
catch (Exception ex)
{
 
MessageBox.Show(ex.Message);
 
}
 
}
now double click the Load button and write the code lines:

try
{
 
FileDialog fldlg = new OpenFileDialog();
 
//specify your own initial directory
 
fldlg.InitialDirectory = @":D\";
 
//this will allow only 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);
 
pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;
 
pictureBox1.Image = (Image)newimg;
 
}
 
fldlg = null;
 
}
 
catch (System.ArgumentException ae)
{
 
imagename = " ";
 
MessageBox.Show(ae.Message.ToString());
 
}
 
catch (Exception ex)
{
 
MessageBox.Show(ex.Message.ToString());
 
}
Now double click Store button and call the update () method to insert selected image into the database.
updatedata();
the update method itself calls connection() therefore combo box will be filled with IDs of images exits in database, one can use the connection method in anyway the person likes.
Finally double click Retrieve button and write
DataTable dataTable = dset.Tables[0];
 
//if there is an already an image in picturebox, then delete it
 
if (pictureBox2.Image != null)
{
 
pictureBox2.Image.Dispose();
 
}
 
//using filestream object write the column as bytes and store 
        it as an image
 
FileStream FS1 = new FileStream("image.jpg", FileMode.Create);
 
foreach (DataRow dataRow in dataTable.Rows)
{
 
if (dataRow[0].ToString() == comboBox1.SelectedItem.ToString())
{
 
byte[] blob = (byte[])dataRow[1];
 
FS1.Write(blob, 0, blob.Length);
 
FS1.Close();
 
FS1 = null;
 
pictureBox2.Image = Image.FromFile("image.jpg");
 
pictureBox2.SizeMode = PictureBoxSizeMode.StretchImage;
 
pictureBox2.Refresh();
 
}
 
}