Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:

Storing image in sql-server 2005 database is quiet simple task. All you need to deal with binary datatype. One more benefit you will get by storing image in database is that you are free from burden of managing lots of image folder and its path consistency.

Herewith, I am describing one simple web application in ASP.NET which will store the image in database and will retrieve it right from there.

Let us start our journey to this interesting topic.

First we will create one table in AdventureWorks database or in any other DB you have.

use adventureworks

GO

CREATE TABLE [dbo].[ImageStore] (

[ID] [int] NOT NULL ,

[ImageContent] [image] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Note: You can create couple more field in above table in order to get file name and its extension. I will be using “.JPG” extension in my code while retrieving the image but you can make it more dynamic.

Now, we have done with SQL-Server and let us move to our Visual Studio 2008. We will be creating one new web application of C#.

As soon as you will get your web form, draw one button on that with text property to “Upload Image”.

After setting text property of button, double click on that so it will open code behind of C# with click event button. Before we go further, uses following namespaces in your code behind as those are mandatory.

using System.Drawing;

using System.Data.SqlClient;

using System.IO;

Now, let us move to uploading the file.

Note: I will be going to upload one file from my “C” drive, you can make it more dynamic by using open file dialog box and select file from users computer.

Once you set the reference of above given name spaces, you have to write the following code in button1’s click event.

protected void Button1_Click(object sender, EventArgs e)

{

string strImageName= @”C:\Conversion.jpg”;

Bitmap bNewImage = new Bitmap(strImageName);

FileStream fs = new FileStream(strImageName, FileMode.Open, FileAccess.Read);

//creating byte array to read image

byte[] bImage = new byte[fs.Length];

//this will store conversion.jp in bImage byte array

fs.Read(bImage, 0, Convert.ToInt32(fs.Length));

fs.Close();

fs = null;

//open the database using odp.net and insert the data

string connstr = @”Data Source=.;Initial Catalog=AdventureWorks;

Persist Security Info=True;User ID=sa;Password=sa”;

SqlConnection conn = new SqlConnection(connstr);

conn.Open();

string strQuery;

strQuery = “insert into [dbo].[ImageStore](id,[ImageContent]) values(“ + “1,” + ” @pic)”;

SqlParameter ImageParameter= new SqlParameter();

ImageParameter.SqlDbType = SqlDbType.Image;

ImageParameter.ParameterName = “pic”;

ImageParameter.Value = bImage;

SqlCommand cmd = new SqlCommand(strQuery, conn);

cmd.Parameters.Add(ImageParameter);

cmd.ExecuteNonQuery();

Response.Write(“Image has been added to database successfully”);

cmd.Dispose();

conn.Close();

conn.Dispose();

}

So far, you have finished half of the journey. You have select the image from your hard drive, convert it in byte and insert it in SQL-Server table, now we are going to do reverse procedure to get image back.

Now, create another button on the same web form and set the text property with “Retrieve Image” value and also put one image box or grid or whatever tool in which you want to retrieve the image or else, you can simply save it at your hard drive. I am saving it in my hard drive with .JPG extension, you can make more dynamic as I specified in starting of this article.

Finally, write down the image retrieval code in button2’s click event as follows.

protected void Button2_Click(object sender, EventArgs e)

{

string connstr = @”Data Source=.;Initial Catalog=AdventureWorks;

Persist Security Info=True;User ID=sa;Password=sa”;

SqlConnection conn = new SqlConnection(connstr);

conn.Open();

//selecting image from sqldataadapter to dataset.

SqlDataAdapter sdImageSource = new SqlDataAdapter();

sdImageSource.SelectCommand = new SqlCommand(“SELECT * FROM [dbo].[ImageStore]”, conn);

DataSet dsImage = new DataSet();

sdImageSource.Fill(dsImage);

string strfn = Convert.ToString(DateTime.Now.ToFileTime());

strfn = @”D:\StoreIMG\” + strfn + “.jpg”;

FileStream fs = new FileStream(strfn, FileMode.CreateNew, FileAccess.Write);

//retrieving binary data of image from dataset to byte array

byte[] blob = (byte[])dsImage.Tables[0].Rows[0][“imageContent”];

//saving back our image to D:\StoreIMG folder

fs.Write(blob, 0, blob.Length);

fs.Close();

fs = null;

}

Note: You have to have write permission in D:\StoreIMG for ASP.Net user or else it will show you an error of permission.

Hope you have enjoyed it!!!!

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah