Storing Images in database with C#

Storing Images

1. Create a table in a SQL Server 2000 database which has at least one field of type IMAGE.

   Here is the script I used:

   CREATE TABLE [dbo].[tblImgData] (
   
          [ID] [int] NOT NULL ,
   
          [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   
          [Picture] [image] NULL
   
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

2. Actually IMAGE field is just holding the reference to the page containing the binary data so we have to convert our image into bytes.
   1. I used a file open dialog box to locate the file.

      this.openFileDialog1.ShowDialog(this);
      string strFn=this.openFileDialog1.FileName;

   2. By using FileInfo class, I retrieved the file size:

      FileInfo fiImage=new FileInfo(strFn);

   3. Declare an array of that size.

      this.m_lImageFileLength=fiImage.Length;
      m_barrImg=new byte[Convert.ToInt32(this.m_lImageFileLength)];

   4. By using FileStream object, I filled the byte array.

      FileStream fs=new FileStream(strFn,FileMode.Open,
                       FileAccess.Read,FileShare.Read);
      int iBytesRead=fs.Read(m_barrImg,0,
                    Convert.ToInt32(this.m_lImageFileLength));
      fs.Close();

   Complete Load Image Code

   protected void LoadImage()
   {
      try
      {
          this.openFileDialog1.ShowDialog(this);
          string strFn=this.openFileDialog1.FileName;
          this.pictureBox1.Image=Image.FromFile(strFn);
          FileInfo fiImage=new FileInfo(strFn);
          this.m_lImageFileLength=fiImage.Length;
          FileStream fs=new FileStream(strFn,FileMode.Open,
                            FileAccess.Read,FileShare.Read);
          m_barrImg=new byte[Convert.ToInt32(this.m_lImageFileLength)];
          int iBytesRead = fs.Read(m_barrImg,0,
                           Convert.ToInt32(this.m_lImageFileLength));
          fs.Close();
      }
      catch(Exception ex)
      {
          MessageBox.Show(ex.Message);
      }
   }

3. Saving byte array data to database.
   1. Create command text to insert record.

      this.sqlCommand1.CommandText=
       "INSERT INTO tblImgData(ID,Name,Picture)" +
       " values(@ID,@Name,@Picture)";

   2. Create parameters.

      this.sqlCommand1.Parameters.Add("@ID",
                System.Data.SqlDbType.Int, 4);
      this.sqlCommand1.Parameters.Add("@Name",
                System.Data.SqlDbType.VarChar, 50);
      
      this.sqlCommand1.Parameters.Add("@Picture",
                System.Data.SqlDbType.Image);

      Notice “@Picture” has “SqlDbType.Image” because it is of IMAGE type Field.

   3. Provide the value to the parameters.

      this.sqlCommand1.Parameters["@ID"].Value=this.editID.Text;
      this.sqlCommand1.Parameters["@Name"].Value=this.editName.Text;
      
      this.sqlCommand1.Parameters["@Picture"].Value=this.m_barrImg;

      “this.m_barrImg” is a byte array which we filled in the previous step.

   4. Now execute non-query for saving the record to the database.

      int iresult=this.sqlCommand1.ExecuteNonQuery();

   Complete Save Image Code

   Collapse

   private void btnSave_Click(object sender, System.EventArgs e)
   {
      try
      {
          this.sqlConnection1.Open();
          if (sqlCommand1.Parameters.Count ==0 )
          {
              this.sqlCommand1.CommandText="INSERT INTO tblImgData(ID," +
                             " Name,Picture) values(@ID,@Name,@Picture)";
              this.sqlCommand1.Parameters.Add("@ID",
                               System.Data.SqlDbType.Int,4);
              this.sqlCommand1.Parameters.Add("@Name",
                               System.Data.SqlDbType.VarChar,50);
              this.sqlCommand1.Parameters.Add("@Picture",
                               System.Data.SqlDbType.Image);
          }
   
          this.sqlCommand1.Parameters["@ID"].Value=this.editID.Text;
          this.sqlCommand1.Parameters["@Name"].Value=this.editName.Text;
          this.sqlCommand1.Parameters["@Picture"].Value=this.m_barrImg;
   
          int iresult=this.sqlCommand1.ExecuteNonQuery();
          MessageBox.Show(Convert.ToString(iresult));
      }
      catch(Exception ex)
      {
          MessageBox.Show(ex.Message);
      }
      finally
      {
          this.sqlConnection1.Close();
      }
   }

Retrieving Image

Retrieving images from the database is the exact reverse process of saving images to the database.

1. First create command text to retrieve record.

   SqlCommand cmdSelect = new SqlCommand("select Picture" +
                         " from tblImgData where ID=@ID",
                         this.sqlConnection1);

2. Create parameter for the query.

   cmdSelect.Parameters.Add("@ID",SqlDbType.Int,4);

3. Provide value to the parameter.

   cmdSelect.Parameters["@ID"].Value=this.editID.Text;

4. Open database connection and execute “ExecuteScalar” because we want only “IMAGE” column data back.

   byte[] barrImg=(byte[])cmdSelect.ExecuteScalar();

   As the execute scalar returns data of “Object” data type, we cast it to byte array.

5. Save this data to a temporary file.

   string strfn=Convert.ToString(DateTime.Now.ToFileTime());
   FileStream fs=new FileStream(strfn,FileMode.CreateNew,FileAccess.Write);
   fs.Write(barrImg,0,barrImg.Length);
   fs.Flush();
   fs.Close();

6. And display the image anywhere you want to display.

   pictureBox1.Image=Image.FromFile(strfn);

Complete Image Retrieving Code

private void btnLoad_Click(object sender, System.EventArgs e)
{
   try
   {
       SqlCommand cmdSelect=new SqlCommand("select Picture" +
             " from tblImgData where ID=@ID",this.sqlConnection1);
       cmdSelect.Parameters.Add("@ID",SqlDbType.Int,4);
       cmdSelect.Parameters["@ID"].Value=this.editID.Text;

       this.sqlConnection1.Open();
       byte[] barrImg=(byte[])cmdSelect.ExecuteScalar();
       string strfn=Convert.ToString(DateTime.Now.ToFileTime());
       FileStream fs=new FileStream(strfn,
                         FileMode.CreateNew, FileAccess.Write);
       fs.Write(barrImg,0,barrImg.Length);
       fs.Flush();
       fs.Close();
       pictureBox1.Image=Image.FromFile(strfn);
   }
   catch(Exception ex)
   {
       MessageBox.Show(ex.Message);
   }
   finally
   {
       this.sqlConnection1.Close();
   }
}