How To Store And Retrieve Images In SQL Server Through Visual

I am using MS SQL Server 2005 and Visual Studio 2005. However this procedure should work on the latest versions too.

1. Create a Database : The first thing you will need to do  is to create a database. Name it anything you like. I named it Images.


database

Now, for the newly created database Images, create a new table with two columns namely image_id of datatype numeric(18,0) and picture of datatype image as shown in the figure below and name it image_table.

table

2. Create a new Visual Studio Project : Next step is to create a new project in Visual Studio, select Windows Application and the language as Visual C# and name this project as ImageProject. By default a windows form named Form1 will appear. Change this Form’s Text to “Store Images In Database” from the properties section just to make it more presentable and the also change the form’s name to FormStore as this form will be used to store images in database. Now using the toolbox, drag and drop a textbox, two labels and four buttons.  From the properties section change the text property of the buttons and enter the text of the buttons as Browse, Save, Retrieve Image and Exit just for simplicity. Now change the names of the buttons Browse, Save , Retrieve Image and Exit as btnbrowse, btnsave, btnretrieve and btnexit respectively so that you don’t confuse yourself with button names in coding. Also change the text of the labels as show in the figure below. Adjust the size of the form and the buttons accordingly. Now the FormStore should look like this :

FormStore

Also create another form to retrieve images. Name it as FormRetrieve and change its text property to “Retrieve Images From Database“. In this form, add a textbox, two labels and two buttons. Change the text property of the buttons to Retrieve Image and Back and change their names to btnret and btnback respectively. Change the text of the labels as shown in the figure below and now your FormRetrieve should look like this:

FromRetrieve

3. Add a Data Source : Next step is to add a new data source. Go to    Data -> Add New Data Source . Choose Database as the Database Source Type and Click Next.

datasource

Now, click on the New Connection button to establish a new connection with the database.  Enter the Server name. This name is the name with which you connect to the SQL Server. After you have entered the Server Name,all the databases will become available in the combo box below. Select the database Images from the combo box and click OK.

addconnection

Then click  Next -> Next . Then the wizard will ask you to choose your database objects. In this case, as we only need the tables so check the Tables checkbox and click Finish. Now the connection with the dabase is established using the dataset.

You will see in Data Sources, ImageDataSet will be added with the table image_table and its two columns. In the combobox for the column picture, select pictuebox as shown in the figure below.

2

Now click on this column picture and drag it on to the FormStore(Design View) and FormRetrieve(Design View). Delete the labels which are dragged with it because we already have the labels. Adjust the pictureboxes to suitable sizes and infront of the labels which were already present there.

4. Add Queries : Double click the ImageDataSet in the solution explorer to add queries for adding and retrieving images from the database. Right click on the image_TableAdapter -> Add -> Query  . The wizard will ask to choose the query type. For adding images in database, choose the INSERT option and click Next. Click Next again. Name it as InsertImage and click finish.

Again Follow the same procedure to add a new query but this time for retrieving images. So choose the query type as “Select which returns rows“. Click Next and this time we have to edit this query a little bit. Remove all the code which is written by default and paste this code.

SELECT image_id, picture FROM dbo.image_table
where (image_id = @id)

Click Next -> Finish.

5. Coding : Next step is the coding ofcourse.  Open the FormStore and double click the Browse button to open the code view and first of all declare a global variable imagename of string datatype.

string imagename;

Also add the following directory at the top.

using System.IO;

Now, paste the following code in the function for browse button click event.

try

{

FileDialog fldlg = new OpenFileDialog();

fldlg.InitialDirectory = @":D\";

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

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

{

imagename = fldlg.FileName;

Bitmap newimg = new Bitmap(imagename);

picturePictureBox.SizeMode = PictureBoxSizeMode.StretchImage;

picturePictureBox.Image = (Image)newimg;

}

fldlg = null;

}

catch (System.ArgumentException ae)

{

imagename = ” “;

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

}

catch (Exception ex)

{

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

}


Now, in FormStore double click the Save button and paste the following code in the function for the save button click event.

try
{

if (imagename != "")
{

FileStream fs;

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

byte[] picbyte = new byte[fs.Length];

fs.Read(picbyte, 0, System.Convert.ToInt32(fs.Length));

fs.Close();

this.image_tableTableAdapter.InsertImage(textBox1.Text, picbyte);

MessageBox.Show(“Picture has been successfully saved into the database”);

}

}

catch (Exception ex)
{

MessageBox.Show(“Please enter a valid ID…\n\n\n” + ex);

}


Now, in FormStore double click the Retrieve button and paste the following code in the function for the retrieve button click event.

this.Hide();

FormRetrieve f = new FormRetrieve();

f.Show();

Now, in FormStore double click the Exit button and paste the following code in the function for the exit button click event.

Application.Exit();


Now, in FormRetrieve double click the Retrieve Image button and paste the following code in the function for the retrieve image button click event.

try

{

picturePictureBox.SizeMode = PictureBoxSizeMode.StretchImage;

int a = this.image_tableTableAdapter.FillBy(this.imagesDataSet.image_table, textBox1.Text);

if (a == 0)

{

MessageBox.Show("Sorry...Image Not Found");

}

}

catch (Exception ex)

{

MessageBox.Show(“Please enter a valid ID…\n\n\n” + ex);

}

Now, in FormRetrieve double click the Back button and paste the following code in the function for the back button click event.

this.Hide();

FormStore frm = new FormStore();

frm.Show();

In the FormStore.cs  there will be a function like this :

private void FormStore_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'imagesDataSet.image_table' table.

//this.image_tableTableAdapter.Fill(this.imagesDataSet.image_table);

}

Make sure that all the code is in comments in this function.

Similarly in FormRetrieve.cs  there will be a function like :

private void FormRetrieve_Load(object sender, EventArgs e)

{

// TODO: This line of code loads data into the 'imagesDataSet.image_table' table.

//this.image_tableTableAdapter.Fill(this.imagesDataSet.image_table);

}

Also comment all the code in this function.

And finally you’re done. Run the project and you will be able to store and retrieve images from the database.