Introduction:
Today you will learn to write images to SQL Server and then read and display them. In order to follow this tutorial, you will need to have SQL Server, Visual Studio .NET and .NET Framework installed. Ensure that the SQL Server instance is running.
Getting to work:
First, we will need to create a new C# Windows application ( File -> New -> Project -> Visual C# Projects -> Windows Application ). Name the project readImage.
We are now in front of the empty form. Add a button from the Toolbox (if the Toolbox is not visible, select View -> Toolbox or simply press Ctrl + Alt + X). Set the Name property of the newly created Button to btn_add and set its Text to Add picture. We will need also another Button to view the image. So drag from the Toolbox another Button and set its name to btn_load. Set its Text property to Display Selected Picture. Now we must add a ListBox object to keep track of the inserted images. So add it into the form. We don’t need to modify anything for this object so let’s move on. The last thing we will need on our form is a PictureBox control to display the picture extracted from the database. Add the control and set its SizeMode property to StretchImage, to display the full image stretched to fit the control. We are now finished with the design.
After these steps, my application looks like this:
If yours looks different, don’t worry! The issue here is not design, so you can arrange the control in any way you like. Well, now it’s time to get to coding.
You need to declare a string with the data to connect to SQL Server. This differs from server to server. On my machine, the string is:
string c_string = @"server=raulcelrausqlserver;uid=sa;pwd=sa;database=Northwind;";
You will need to change the server property to your SQL Server’s name, and your sa password to your password. The database property should remain unchanged. You will use the above string very often, so declare in your class, below the declaration of the controls you’ve added in the design part.
The next thing we will need to do is create a table where we want to put the image. We’ll add this code in the Form1_Load method. So go to Design view (if you are not already there), and double-click the form’s surface. Visual Studio .NET will create the Form1_Load method for you. Here, we add the following code:
string cmd = “create table Picture” +
“(” +
“PictureID int IDENTITY(0,1) NOT NULL PRIMARY KEY, ” +
“PictureName varchar(40) NOT NULL, ” +
“Picture Image NOT NULL” +
“);”;
SqlConnection c = new SqlConnection(c_string);
SqlCommand cm = new SqlCommand(cmd,c);
try
{
c.Open();
cm.ExecuteNonQuery();
}
catch (SqlException ee)
{
MessageBox.Show(ee.Message);
}
finally
{
c.Close();
}
The string cmd is the SQL code to create our table. Also you need to create a SqlConnection based on the above connection string and a SqlCommand based on the connection and the command string. We try opening the connection and executing the SQL. If something goes wrong, we display a MessageBox with the text of the error. No matter what happens before (if we get an error on don’t), we close the connection. You should now have a table in the Northwind database named Picture.
The next step is to code the insertion of the picture in the database. We want that when we press the Add Picture button, to have an OpenFileDialog the helps us to select the picture to add in the database. From the Design view, double-click the Add Picture button. In the btn_add_Click method created, we will add code to insert the picture in the database.
OpenFileDialog dlg = new OpenFileDialog(); dlg.Filter = "All Pictures|*.bmp;*.gif;*.jpg|Bitmaps|*.bmp|GIFs|*.gif|JPEGs|*.jpg";
We instantiate an OpenFileDialog object, and we allow only picture files to be selected(the Filter method lets you specify what type of files to be selected). If you want to have a choice to view all files, you add “All files|*.*” to the string.
if ( dlg.ShowDialog() ==DialogResult.OK )
{
pictureBox1.Image = new Bitmap(dlg.FileName);
string name=dlg.FileName.Substring(dlg.FileName.LastIndexOf(@"")+1
,dlg.FileName.Length-dlg.FileName.LastIndexOf(@"")-1);
}
If the user presses cancel instead of ok, we want to skip the insert part. So if the user presses the OK button, we set the pictureBox1’s Image property to the selected image ( after declaring a Bitmap object based on the selected image path ). In the name variable we keep only the name of the file, and not its path.
MemoryStream mstr = new MemoryStream(); pictureBox1.Image.Save(mstr, pictureBox1.Image.RawFormat); byte[] arrImage = mstr.GetBuffer(); string cmd = "insert into Picture (PictureName, Picture) values (@PName, @Pic)";
A MemoryStream object is created, on witch the selected image in saved. We are now able to get the bytes from the stream on witch the image is saved on. These bytes are going to be written to the table that we created earlier. The next step is the creation of the SQL witch will insert the image and the image’s name into the database. You notice the @PName and @Pic. They are parameters that we will assign next.
SqlConnection c = new SqlConnection(c_string);
SqlCommand comm = new SqlCommand(cmd,c);
comm.Parameters.Add( new SqlParameter("@PName", SqlDbType.VarChar,40)).Value = name;
comm.Parameters.Add( new SqlParameter("@Pic",SqlDbType.Image)).Value = arrImage;
try
{
c.Open();
comm.ExecuteNonQuery();
}
catch(SqlException err)
{
MessageBox.Show(err.Message);
}
finally
{
c.Close();
}
listBox1.Items.Add(name);
We instantiate a SqlConnection and a SqlCommand and we add the parameters for the connection and also set their values. And we insert them into the database. One last thing we do is also add the name of the pictures in the ListBox control, so that we can display them when we’ll press the btn_load button.
That’s the code to insert in picture in the database, let’s see now how do we get it out. Go to the Design view and double-click the Display Selected Picture button. You will notice that Visual Studio .NET has created for you a method called btn_display_Click. We will need to add code to get the image out of the database and into the PictureBox control. We should display the pictures based on the index of the selected picture name that we add when we insert a picture into the ListBox. So we should first check if we have no item selected (if we do, the index will be negative)
if (listBox1.SelectedIndex<0)
{
MessageBox.Show("Please select a picture from the ListBox");
}
else
{
string cmd = "select picture from Picture where PictureID="+ listBox1.SelectedIndex +";";
SqlConnection cc = new SqlConnection(c_string);
SqlCommand com = new SqlCommand(cmd,cc);
Let’s see what we’ve done. We checked if the selected index is positive, and if it is, we create a SQL string that selects the image from the database that has the same index as the selected index from the Listbox ( perhaps you noticed, that when we created the table, we added a column called PictureID, that was the primary key, and also had an attribute called IDENTITY(0,1). This attribute starts the counting from 0 and increments it each time by 1 – the same way our ListBox control does ). Next, we create a connection and a command.
try
{
cc.Open();
byte[] b = (byte [])com.ExecuteScalar();
MemoryStream mem = new MemoryStream(b);
pictureBox1.Image = Image.FromStream(mem);
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
finally
{
cc.Close();
}
We try to open the connection, end we execute the command using its ExecuteScalar method, witch returns the first row resulted from the select query. We take this result and convert it into a byte array ( remember that when we inserted it, we inserted also as a byte array ). From this byte array we instantiate a MemoryStream, from witch we reconstruct the image. If anything goes wrong, we get a messagebox with the error. Finally, we close the connection.
Try your program…. I did and here it is:
Close the application and run it again. You will get an error that the table already exists. Let’s fix that by dropping the table when you close the application. Go to the Dispose method, and add:
string cmd = "drop table Picture;";
SqlConnection c = new SqlConnection(c_string);
SqlCommand cm = new SqlCommand(cmd,c);
try
{
c.Open();
cm.ExecuteNonQuery();
}
catch (SqlException ee)
{
MessageBox.Show(ee.Message);
}
finally
{
c.Close();
}
This code opens a connection to SQL Server and executes the drop table command. This is it. Hope you had as much fun as I did doing this.
Until next time I wish you good luck and happy programming.
Raul POPESCU
Attachments:
Project Files: ImagesToDatabase.zip