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
Advertisements

28 Responses to “Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:”

  1. Anonymous Says:

    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?

  2. Anonymous Says:

    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?

  3. Anonymous Says:

    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.

  4. Anonymous Says:

    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.

  5. Anonymous Says:

    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.

  6. Anonymous Says:

    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.

  7. Ritesh Shah Says:

    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.

  8. Ritesh Shah Says:

    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.

  9. Ani Says:

    RiteshHow can I to open a pdf file from BLOB field ?How can I open in a web page or open diretly with web page?

  10. Ani Says:

    RiteshHow can I to open a pdf file from BLOB field ?How can I open in a web page or open diretly with web page?

  11. Ritesh Shah Says:

    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

  12. Ritesh Shah Says:

    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

  13. Anonymous Says:

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

  14. Anonymous Says:

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

  15. Ritesh Shah Says:

    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.

  16. Ritesh Shah Says:

    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.

  17. Dave Says:

    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!

  18. Dave Says:

    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!

  19. kino Says:

    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!

  20. kino Says:

    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!

  21. kino Says:

    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!

  22. kino Says:

    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!

  23. thomas Says:

    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

  24. thomas Says:

    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

  25. Anonymous Says:

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

  26. Anonymous Says:

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: