Sunday, October 9, 2011

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");

   //opening the connection

  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");
  //Closing the database connection

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....

No comments :

Post a Comment