Storing and Retrieving Images from SQL Server using Microsoft .NET

• Download source - 19.6 Kb

Introduction

This article is about storing and retrieving images from database in Microsoft .NET using C#.

Tools Used

• SQL Server 2000
• Microsoft .NET Version 1.1
• C# (Windows Forms based application)

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

   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();
    }
}

Bibliography

• Retrieving Images from SQL Server in ASP.NET
• Images, Thumbnails, SQL Server, and ASP.NET - Level 200