A couple of weeks ago, i was working on a project in which i had to store images in SQL Server through C#. I searched the web to get some sort of help but couldn't find some really good tutorial about this topic. As it turned out it wasn't a very difficult task and i was finally able to do it. So i decided to write a small but comprehensive tutorial about this topic. Before i start, i would assume that you have the basic knowledge about SQL Server and C#. So here we go.
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.
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.
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 :
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:
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.
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.
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.
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. The following code will appear by default.
INSERT INTO [dbo].[image_table] ([image_id], [picture]) VALUES (@image_id, @picture)
Just Click Next. 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.
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());
}
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.
Hello,
Thank you very much for sharing your useful c# procedure. I wrote the code on VS2008 but I got a problem. I think at the 4th section the Insert Query is missing. Could you write the query completely, please?
Regards,
Mustafa Digdigi
@Mustafa
If you follow the procedure which i have described in the post, you will get the code for insert query by default. However for your convenience, i have also pasted the code in the post.
hi., i’ll try this. i hope it’ll help me.,. tnx
it helps me alot thanks boss god bless u
Hi,
Thanks for the example. I am having a poblem with the following statement…….
this.image_tableTableAdapter.InsertImage(textBox1.Text, picbyte);
and
int a = this.image_tableTableAdapter.FillBy(this.imagesDataSet.image_table, textBox1.Text);
I get the error
The best overloaded method match for ‘ImageProject.ImagesDataSetTableAdapters.image_tableTableAdapter.InsertImage(decimal?, byte[])’ has some invalid arguments C:\Users\Robert\Documents\Visual Studio 2008\Projects\ImageProject\ImageProject\Form1.cs 105 21 ImageProject
Well for this example to work you have to convert STRING TO INT, because the parameter expected is an INT you have to correct it to…
this.image_tableTableAdapter.InsertImage(Convert.ToInt32(textBox1.Text), picbyte);
is the same example for the other error.
Please let me know if it works.