Storing and Retrieving Image in SQL Server

           Hi, I saw many developers are asking this questions in different forums. Recently I saw this question on MSDN forum and I thought let me write a blog on this. I know many of us found this too easy however for new bees its bit hard.

            In this article, I had used SQL Server 2005 as back end and C# as front end. SQL Server has "Image" data type to store the image. In Oracle and some other database you can use a data type which is used to store binary value (may be BLOB). I have created a simple aspx which has File upload control and a button. When user selects a file to upload, I am checking it for valid image type and converting it to array of Bytes. Then I will store that byte array into database. Below is the code,

 

if (objFileUpload.PostedFile !=null)
{
          if (objFileUpload.PostedFile.ContentLength > 0)
         {
                  // Get Posted File.
                  HttpPostedFile objHttpPostedFile = objFileUpload.PostedFile;

                  // Check valid Image type. Create this function according to your need
                  if (CheckValidFileType(objHttpPostedFile.FileName))
                 {
                        // Find its length and convert it to byte array
                        int intContentlength = objHttpPostedFile.ContentLength;

                       // Create Byte Array 
                        Byte[] bytImage =new Byte[intContentlength];
                           
                        // Read Uploaded file in Byte Array
                        objHttpPostedFile.InputStream.Read(bytImage, 0, 
                                                                                    intContentlength);
                                      
                   }

              }

        }

Fig -  (1)  Read Uploaded file (here Image) in Byte Array

 

       Pass this Byte array to you DAL and use it for storing image in database. I am using Enterprise Library as DAL so my code will look like,

 

Database db =DatabaseFactory.CreateDatabase();

string sqlCommand ="StoredProcedureName";

DbCommand dbCommandWrapper = db.GetStoredProcCommand(sqlCommand);

db.AddInParameter(dbCommandWrapper,"@Image",DbType.Binary,
bytImage );

try
{
             db.ExecuteNonQuery(dbCommandWrapper);
}
catch {throw; }

Fig -  (2)  Insert Image in to database

 

      This is how you can store the Image in database. Retrieving the image is the same process. Write a SP which will return your image. Store this value in a Byte Array. Once you get the image in Byte array, you just have to write it on form as shown below,

 

Byte[] bytImage =Byte array retrieved from database.
if (bytImage !=null)
{
          Response.ContentType ="image/jpeg";
          Response.Expires = 0; Response.Buffer =true;
          Response.Clear();
          Response.BinaryWrite(bytImage); 
          Response.End();
}

Fig -  (3)  Code to display Byte array as Image on form.

     

        To use this at multiple places in your application, you create a page to which you can pass ID of Image and it will retrieve image from database and create image. To do this copy paste above code in aspx.cs file. Now on every page you require to show this image  take <asp:Image> on that page and set its ImageURL property to the path of newly created user control. See the code below,

<asp:Image ID="ViewImage" runat="server" />

Fig -  (4) Image control on any aspx page (Lets say Sample.aspx).

string strURL ="~/ViewImage.aspx?ID= 1 " ;
ViewImage.ImageUrl = strURL;

Fig -  (5)  Set Image URL for image control on code behind (Sample.aspx.cs)

 

       You can see the image will be displayed in your page where you had put Image tag.

Happy Programing.