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

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

24 thoughts on “Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:”

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

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

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

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

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

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

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

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

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

  12. I feеl that iѕ onе of the ѕo much νital informаtion fοr me.
    And i’m happy studying your article. But should statement on some normal things, The website taste is ideal, the articles is truly great : D. Excellent job, cheers

  13. First off I wоuld lіκe to say
    great blog! I hаd a quiсk question іn ωhich I’d like to ask if you do not mind. I was interested to find out how you center yourself and clear your head prior to writing. I’ve hаd a difficult time clearing mу thoughts in getting
    my ideаs out. I dο take pleаsure in ωriting but it just ѕeеmѕ like
    the fіrst 10 to 15 mіnutes are waѕteԁ simρly juѕt tryіng to fіgurе out how tο begіn.
    Anу suggestіοnѕ or hіnts? Thanκ
    you!

  14. Hey there! I know this is kinda off topic but I was wondering if you
    knew where I could locate a captcha plugin for my comment form?
    I’m using the same blog platform as yours and I’m having
    trouble finding one? Thanks a lot!

  15. Hello Sir,I”m trying to retrieve image from sql database and want to show it on image control of asp.net. But it’s getting an error “The filename, directory name, or volume label syntax is incorrect.” Please Help me. Thanks…….

    cn.Open();
    SqlCommand cm = new SqlCommand(“select * from ImageCollection where img_id='” + DropDownList1.SelectedItem.ToString() + “‘”, cn);
    SqlDataAdapter da = new SqlDataAdapter(cm);
    DataSet ds = new DataSet();
    da.Fill(ds, “asdf”);
    SqlDataReader dr = cm.ExecuteReader();
    try
    {
    if (dr.Read())
    {

    string image1 = Convert.ToString(DateTime.Now.ToFileTime());
    image1 = “~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/” + image1 + “.jpeg”;
    string image2 = Convert.ToString(DateTime.Now.ToFileTime());
    image2 = “~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/” + image2 + “.jpeg”;
    FileStream fs1 = new FileStream(image1, FileMode.CreateNew, FileAccess.Write);
    FileStream fs2 = new FileStream(image2, FileMode.CreateNew, FileAccess.Write);
    byte[] bimage1 = (byte[])ds.Tables[0].Rows[0][“passport_photo”];
    byte[] bimage2 = (byte[])ds.Tables[0].Rows[0][“sign_photo”];
    fs1.Write(bimage1, 0, bimage1.Length);
    fs2.Write(bimage2, 0, bimage2.Length);
    fs1.Flush();
    fs2.Flush();
    //Image1.DataBind();
    //Image2.DataBind();
    Image1.ImageUrl = “~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/”;
    Image2.ImageUrl = “~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/”;
    }
    dr.Close();
    cn.Close();
    }
    catch (Exception ex)
    {
    throw ex;
    }

  16. Hello Sir,I want to retrieve image from sql database and want to show the image on image control of asp.net. But it getting an error “The filename, directory name, or volume label syntax is incorrect.” Please solve my problem. Thanks……..

    protected void DropDownList1_TextChanged(object sender, EventArgs e)
    {
    cn.Open();
    SqlCommand cm = new SqlCommand(“select * from ImageCollection where img_id='” + DropDownList1.SelectedItem.ToString() + “‘”, cn);
    SqlDataAdapter da = new SqlDataAdapter(cm);
    DataSet ds = new DataSet();
    da.Fill(ds, “asdf”);
    SqlDataReader dr = cm.ExecuteReader();
    try
    {
    if (dr.Read())
    {

    string image1 = Convert.ToString(DateTime.Now.ToFileTime());
    image1 = “~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/” + image1 + “.jpeg”;
    string image2 = Convert.ToString(DateTime.Now.ToFileTime());
    image2 = “~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/” + image2 + “.jpeg”;
    FileStream fs1 = new FileStream(image1, FileMode.CreateNew, FileAccess.Write);
    FileStream fs2 = new FileStream(image2, FileMode.CreateNew, FileAccess.Write);
    byte[] bimage1 = (byte[])ds.Tables[0].Rows[0][“passport_photo”];
    byte[] bimage2 = (byte[])ds.Tables[0].Rows[0][“sign_photo”];
    fs1.Write(bimage1, 0, bimage1.Length);
    fs2.Write(bimage2, 0, bimage2.Length);
    fs1.Flush();
    fs2.Flush();
    //Image1.DataBind();
    //Image2.DataBind();
    Image1.ImageUrl = “~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/”;
    Image2.ImageUrl = “~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/”;
    }
    dr.Close();
    cn.Close();
    }
    catch (Exception ex)
    {
    throw ex;
    }

Comments are closed.