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

Saturday, March 7, 2009 |

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

17 comments:

Anonymous said...

hi,
while am executing the program its showing this error
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
can u help me?

Ritesh Shah said...

well you can go to below link at my friend's blog which will help you for this matter.

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

go to 4) Enable Remote Connection in above link.

Anonymous said...

hi,
while retriving image from database ,u r storing it in D drive.But i want to retrive image directly into webpage. can u help me in coding part.

Anonymous said...

hi,
while retriving image from database ,u r storing it in D drive.But i want to retrive image directly into webpage. can u help me in coding part.

Ritesh Shah said...

Well you can generate it in any temporary folder of your website, display in your website from that temporary folder and delete from it whenever you will finish work on it.

Ashish Kanoongo said...

Ritesh

How can I to open a pdf file from BLOB field ?

How can I open in a web page or open diretly with web page?

Ritesh Shah said...

Hi Ani,

As long as PDF concern, you can store it in VarBinary field and can give same treatment I saw you in article for Image.

About Web page, can you please be more specific? I don't understand your second question.

Ritesh

Anonymous said...

Hi, Ritesh I have Used ur code for retreiving the Image from the database. The file is creating well,
But there is no image into that. and further the code is not giving any error too..

Ritesh Shah said...

I am 100% sure you have made mistake either while converting image to byte or while converting byte to image. as this is the only possibility.

Dave said...

Excellent, just the example I was looking for. I converted the code to vb.net since that is what we use, but it worked perfectly. Thank you!

kino said...

Hello, I just used the first part of your code for storing images, but after execution of this code there's no change in my database tables, they remain null. Could you please check? Here's my code behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Data.SqlClient;
using System.IO;
using System.Data;


public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
string strImageName = Path.GetFileName(FileUploadControl.FileName);
FileUploadControl.SaveAs(Server.MapPath("~/") + strImageName);


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 images.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 = "Server=WINSP3UE\\SqlExpress;Database=MovieTestDB;Trusted_Connection=True;";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
string strQuery;

strQuery = "INSERT INTO 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();


}
}


Thank you so much!

kino said...

Hello, I just used the first part of your code for storing images, but after execution of this code there's no change in my database tables, they remain null. Could you please check? Here's my code behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Data.SqlClient;
using System.IO;
using System.Data;


public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
string strImageName = Path.GetFileName(FileUploadControl.FileName);
FileUploadControl.SaveAs(Server.MapPath("~/") + strImageName);


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 images.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 = "Server=WINSP3UE\\SqlExpress;Database=MovieTestDB;Trusted_Connection=True;";
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
string strQuery;

strQuery = "INSERT INTO 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();


}
}


Thank you so much!

thomas said...

Hello sir,
How can i upload and retrieve excel file in the same application..please help me....its urgent. In your code any image file working fine...but i want store and retrieve excel file....please help

Anonymous said...

Thanks a lot... it really helped me. It works using sql server 2008.

Unknown said...

hi i write one code for save doc file in sqlserver and retrieve.

save:
string name = @"C:\Documents and Settings\icoresoft\Desktop\murali.doc";

FileStream fs = new FileStream(name, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);

byte[] bname = new byte[fs.Length];
fs.Read(bname, 0, Convert.ToInt32(fs.Length));

tbal.my_doc = bname;
string i = tdal.createfile(tbal);
if (i == "1")
{
MessageBox.Show("saved");
}


retrive:

byte[] myByte = new byte[0];
tbal.id_ = 1;
SqlDataReader dr = tdal.getvalue(tbal);
dr.Read();
if (dr.HasRows)
{
myByte =(byte[])dr["mydoc"];
}

FileStream fs1 = new FileStream(@"C:\Documents and Settings\icoresoft\Desktop\murali1.doc", FileMode.OpenOrCreate, FileAccess.Write);
BinaryWriter br1 = new BinaryWriter(fs1);
br1.Write(myByte);


NOW I OPEN MY WORD FILE MEANS ERROR COMES.

ERROR:YOUR FILE CORRUPTED. SO HELP ME HOW CAN I VIEW MY TEXT FILE...PLEASE SEND YOUR ANSWER TO murali.bala.job@gmail.com

Anonymous said...

I tried this method for a txt files, and it worked great! Thanks Ritesh Shah! You made my day.

Chiklu.Soumya said...

Hello Sir, I"m trying to retrieve image from database and want to show it on Image control in asp.net. But it's getting an error ""

SqlCommand cm = new SqlCommand("select * from asdf where img_id='" + DropDownList1.SelectedItem.ToString() + "'", cn);//image id will be selected.
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds, "asdf");
SqlDataReader dr = cm.ExecuteReader();
if (dr.Read())
{

string image1 = Convert.ToString(DateTime.Now.ToFileTime());
image1 = "~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/" + image1 + ".jpeg";
FileStream fs1 = new FileStream(image1, FileMode.CreateNew, FileAccess.Write);
byte[] bimage1 = (byte[])ds.Tables[0].Rows[0]["passport_photo"];
fs1.Write(bimage1, 0, bimage1.Length);
Image1.ImageUrl = "~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/";