Adding and retrieving Images from a SQL Server Table

This article describes the process to add and retrieve images from a SQL Server table using ADO.NET. You can possibly have an entry form that will allow the user to choose what operation he wants to do: add or view images. Depending on the option chosen you can display the relevant form.

 

To add images to the form, the following procedure can be used. A textbox can be displayed on the form to accept the desired image filename from the user. This file must be existing on the local machine and can be chosen using an OpenFileDialog instance.

OpenFileDialog oFileDialog1 = new OpenFileDialog();
oFileDialog1.InitialDirectory = "c:\\";
oFileDialog1.Filter = "Image Files(*.BMP;*.JPG;*.GIF)|*.BMP;*.JPG;*.GIF|All files (*.*)|*.*";
oFileDialog1.FilterIndex = 1;
oFileDialog1.RestoreDirectory = true;
if (oFileDialog1.ShowDialog() == DialogResult.OK) {
      if (oFileDialog1.FileName != null) {
            txtPic.Text = oFileDialog1.FileName;
      }
}

Once the image file name has been given, it can be passed to another method to convert the underlying image to stream format.

filename = txtPic.Text;
byte [] content = ImageToStream(fileName);
StoreImage(content);

The user-defined method ImageToStream will create a MemoryStream instance based on the given file and then convert it into a byte array.

private byte[] ImageToStream(string fileName) {
      Bitmap image = new Bitmap(fileName);
      MemoryStream stream = new MemoryStream();
      image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp);
      return stream.ToArray();
}

The method StoreImage will create parameters and populate them with values so that they can be used with an insert command to add the image and its details to the table. It is assumed that the table has 3 fields - image, filename and image number. Given the byte array as input, this method constructs an Insert command and creates 3 parameters for the 3 field values. The image number is an incremental value which is calculated by getting the last value and adding 1 to it. If the table is empty, it begins with 1.

private void StoreImage(byte[] content) {
      if (MainForm.conn.State.Equals(ConnectionState.Closed))
            MainForm.conn.Open();
      try {
            SqlCommand insert = new SqlCommand("Insert into Images values (@image, @picid,@name)", MainForm.conn);
            int id;
            try {
                  SqlCommand selectcmd = new SqlCommand("Select max(picid) from images", MainForm.conn);
                  id = (int) selectcmd.ExecuteScalar() + 1;
            } catch (Exception ex) {
                  Console.WriteLine(ex.StackTrace);
                  id = 1;
            }
            SqlParameter picParameter = insert.Parameters.Add("@picid", SqlDbType.Int);
            picParameter.Value = id;
            picParameter.Size = 4;
            SqlParameter imageParameter = insert.Parameters.Add("@image", SqlDbType.Binary);
            imageParameter.Value = content;
            imageParameter.Size = content.Length;
            SqlParameter nameParameter = insert.Parameters.Add("@name", SqlDbType.Char);
            nameParameter.Value = fileName;
            nameParameter.Size = fileName.Length;
            insert.ExecuteNonQuery();
            MessageBox.Show("Image has been added successfully");
            success = true;
      } catch (Exception ex) {
            MessageBox.Show(ex.Message.ToString());
            MessageBox.Show(ex.StackTrace.ToString());
      } finally {
            MainForm.conn.Close();
      }
}

 

If you try to look at the contents of the image field in the Sql Server table, you will find that it is stored in binary format.

 

On loading the View form, a listbox can be populated with the existing image file names from the table. The user can then select a particular image file name from the list to display it.

MainForm.conn.Open();
SqlCommand cmd = new SqlCommand("Select picname from images", MainForm.conn);
SqlDataReader content = cmd.ExecuteReader();
while (content.Read()) {
      lstImages.Items.Add(content.GetString(0));
}
content.Close();

The user selection from the listbox can be captured using SelectIndexChanged event.

private void lstImages_SelectedIndexChanged(object sender, System.EventArgs e) {
      image = lstImages.SelectedItem.ToString();
}

To view images, the following code can be used:

if (MainForm.conn.State.Equals(ConnectionState.Closed))
      MainForm.conn.Open();
SqlCommand cmd = new SqlCommand("Select picture from images where picname like '" + image + "';", MainForm.conn);
byte[] content = (byte[]) cmd.ExecuteScalar();
try {
      MemoryStream stream = new MemoryStream(content);
      pic1.Image = Image.FromStream(stream);
} catch (Exception ex) {
      MessageBox.Show(ex.Message.ToString());
      MessageBox.Show(ex.StackTrace.ToString());
}

 

As you can see, the procedure is quite simple.