Image insertion in table of SQL Server database

Herein i will demonstrate a way to insert image into a table of SQL Server database. Normally we come around a scenario where in we want to store images into database tables. As we know image cannot be directly stored/inserted into a database table. First the image should be converted into byte array and byte array can be inserted into database. I will be showing a detailed example to achieve the same objective.

I created a sample web page with the following controls:-

1) asp:FileUpload control for the browse functionality.

2) asp:Button, onclick of which the image will be saved into database table.

Now we need a table which can be used to store the selected images.

SQL Server supports a datatype named as image for storing the image. A brief description about image datatype is as follows:-

image : Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

The following query can be used to create a table:

CREATE TABLE [dbo].[tblImage] (

        [ID] [int] IDENTITY ,

        [Image_Name] [varchar] (500) NULL ,

        [Image] [image] NULL

)

In the create table statement, ID is declared as IDENTITY column.

On the OnClick event of Submit button, following code is written to retrieve the details of the image selected and then to save it in SQL Server table:

// Gets the length of the file.

//imgUploader is the ID of asp:FileUpload control

int len = imgUploader.PostedFile.ContentLength;

byte[] byteArray = new byte[len];

//reads a sequence of bytes from the current stream into the byteArray variable

imgUploader.PostedFile.InputStream.Read(byteArray, 0, len);

//retrieving the fileName which is selected using the FileUpload control

string strFileName = imgUploader.FileName;

//defining the connection string to connect to local database

SqlConnection connection = new SqlConnection(@"server=MyPC;database=master;uid=sa;pwd=password1");

try

{

   //opening the connection

   connection.Open();

  SqlCommand cmd = new SqlCommand();

  

  //insert statement creation as a commandText

  cmd.CommandText = "INSERT INTO tblImage(Image_Name,Image) values(@Image_Name,@Image)";

  cmd.Connection = connection;

  cmd.Parameters.Add("@Image_Name", System.Data.SqlDbType.VarChar, 50);

  cmd.Parameters.Add("@Image", System.Data.SqlDbType.Image);

  

  cmd.Parameters["@Image_Name"].Value = strFileName;

  cmd.Parameters["@Image"].Value = byteArray;

  //executing the query

  int result = cmd.ExecuteNonQuery();

  if (result > 0)

       Response.Write("Save Successful");

}

finally

{
  //Closing the database connection

   connection.Close();

}

In this example  exceptions are not handled as this code is written for sample application but actual application should contain exception blocks.

I hope the given example can help people understand the way to store images in SQL Server table. There are other ways also in .Net which i will discuss later.

Happy Coding....