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

March 7, 2009 — riteshshah

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.

This article is move to following link:

http://blog.extreme-advice.com/2009/03/07/image-store-in-sql-server-2005-database-and-retrieve-it-in-asp-net-application-with-c/

Posted in ASP.NET, C#, SQL-Server. Tags: , , , , , , , . 38 Comments »

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

  1. Rashmi Says:
    March 11, 2009 at 11:41 am

    thank You very much

    Reply
  2. Leah Says:
    March 13, 2009 at 12:54 am

    What is :@”C:\Conversion.jpg”; referring to and why does bitmap give me an error saying its not declared?

    Reply
    • riteshshah Says:
      March 13, 2009 at 2:46 am

      while uploading the file, I am reading JPG file from C:\Conversion.Jpg then convert it to byte and insert it in database. You have to give read permission on the drive or folder to aspnet user so that it can read and write it back to that folder. Of course you can use any of the JPG file you have in your system, I had Conversion.JPG so I used it.

      Reply
  3. saravanan Says:
    March 13, 2009 at 2:38 pm

    Thank you..

    It works fine for image store,But problem in image retrive,
    Image comes from database to local folder as 124234232500.jpg..
    when i open the image there was no preview
    i cant see the image
    please help me

    Reply
    • riteshshah Says:
      March 13, 2009 at 2:47 pm

      Hi Saravanan,

      You need to look at conversion from binary code to image. there must be some problem in that. Look at it and still problem persist, let me have a look at your code.

      Reply
  4. sushant Says:
    June 29, 2009 at 6:52 am

    hi ritesh,
    thanx for ur imformation about image in sql database.
    plz let me know how to use ajax in c#. is it possible?

    Reply
  5. Rahul Satasia Says:
    August 13, 2009 at 5:59 am

    Hi.! Ritesh,

    Thx for sharing information.

    Will u plz help me, to upload any image(.gif, .png, .jpg) from users in perticular folder on the server, with dynamic Name(like primery key[int value] as a name) in SQL Server 2008.

    and how to retrive it from that folder…?

    this is supposed to be on the web not on computer.

    ThnX.

    Reply
  6. varun Says:
    August 25, 2009 at 12:20 pm

    Hi.! Ritesh,

    Thx for sharing information.But i cant do that i don know wat is d problem plz help me out……
    Also

    Will u plz help me, to upload any image(.gif, .png, .jpg) from users in perticular folder on the server, with dynamic Name(like primery key[int value] as a name) in SQL Server 2008.

    and how to retrive it from that folder…?

    this is supposed to be on the web not on computer.

    ThnX.

    Reply
  7. varun Says:
    August 25, 2009 at 12:21 pm

    using System;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Drawing;

    using System.Data.SqlClient;

    using System.IO;

    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= FileUpload1.FileName;

    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=ABC\\SQLEXPRESS;Initial Catalog=img;

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

    }

    protected void Button2_Click(object sender, EventArgs e)
    {string connstr = @”Data Source=ABC\\SQLEXPRESS;Initial Catalog=img;

    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 [dob].[ImageStore]“, conn);

    DataSet dsImage = new DataSet();

    sdImageSource.Fill(dsImage);

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

    ImageButton1= strfn ;

    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;

    }
    }

    i have written this code i can figure out the error plz helpme if u can……..

    Reply
  8. varun Says:
    August 25, 2009 at 12:28 pm

    sorry ..i cant figure out d error….

    Reply
  9. purna Says:
    September 4, 2009 at 8:23 am

    does it works only for jpg files?can i know the code for all types of images.

    Reply
  10. jeet Says:
    March 19, 2010 at 8:37 am

    thank you

    Reply
  11. jeet Says:
    March 19, 2010 at 8:40 am

    hii ritesh

    i want store photo on sql server 2005 using C# code and display the photo on web application.

    Reply
  12. Neetu Says:
    July 21, 2010 at 10:43 am

    hi ritesh! i wanna know about storing images though form in sql2005.give me ur information as soon as possible

    Reply
  13. Chintan Says:
    August 17, 2010 at 7:52 am

    Hello sir.
    How do I use ‘AJAX Slide Show Extender’ in the Master page ?

    Reply
  14. Kittiphong Says:
    September 23, 2010 at 4:40 am

    Thank you, this is a very good article 🙂

    Reply
  15. Rasika Says:
    January 22, 2011 at 5:56 pm

    Thanks a lot…this article really helpful….congtratzz 4 ur future works

    Reply
  16. gajraj singh Says:
    February 7, 2011 at 11:00 am

    HI

    Reply
  17. Banajyotsna Says:
    April 7, 2011 at 7:12 am

    Sir i am getting error in your coding is :Parameter is not valid .
    how to solve this problem?

    string strImageName = FileUpload1.FileName;
    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=.\sqlexpress;Initial Catalog=adventureworks;Integrated Security=True”;

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

    Reply
  18. Banajyotsna Says:
    April 7, 2011 at 7:14 am

    Sir i am getting error in your coding is: Parameter is not valid .
    how to solve this problem?

    string strImageName = FileUpload1.FileName;
    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=.\sqlexpress;Initial Catalog=adventureworks;Integrated Security=True”;

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

    Reply
  19. amant Says:
    June 8, 2011 at 2:20 pm

    how ca i use image control to retrive image??

    Reply
  20. kumar Says:
    October 12, 2011 at 1:19 pm

    sir,
    am working in visual studio 2005 and same time am using sql server 2005.how can i insert data to database.help me sir…

    Reply
  21. raja Says:
    October 14, 2011 at 12:18 pm

    hi….any one please help me?
    I wanna to update my image and retrieve in at image control using asp.net 3.5 c#.
    I have using update command to update the image but when we wanna to show then it con’t show in the image control.
    we are using handler for show image in image control like this-

    ” image1.imageurl=”handler.ashx?id”+id;”
    Update command———

    public void exq(string q)
    {
    SqlCommand cm = new SqlCommand(q,con);
    con.Open();
    cm.ExecuteNonQuery();
    con.Close();
    }

    if (FileUpload1.HasFile)
    {

    byte[] im = FileUpload1.FileBytes;
    string iid = TextBox1.Text;
    string ss = “delete image from patientdetail where id='”+iid+”‘ “;
    string s = “update patientdetail set image='” + im + “‘ where id='” + iid + “‘”;
    exq(s);
    }
    we does’t retrieve image from database please help me for this
    Thanks,
    krishraja88@gmail.com

    Reply
  22. Shakeel Says:
    December 1, 2011 at 10:38 pm

    I have one question guys before that

    1) storing image into databse
    2) from database to DataGridview
    3) from DataGridView to exporting into Excel Report.

    when i am exporting into Excel Report Image

    having in Bytes

    string imagString = “E:\\ImagesB\\kajal1_102131.jpg”; //Here i don’t have this address i having bytes
    Microsoft.Office.Interop.Excel.Range oRange = ( Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i + 1, j + 1];
    float Left = (float)((double)oRange.Left);
    float Top = (float)((double)oRange.Top);
    const float ImageSize = 32;

    xlWorkSheet.Shapes.AddPicture(imagString, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize);
    //xlWorkSheet.Shapes.AddPicture(imagString, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, (float)Left, (float)Top, (float)ImageSize, (float)ImageSize );

    //xlWorkSheet.Shapes.AddPicture(imagString, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 10, 10, 700, 350);
    oRange.RowHeight = ImageSize + 2;

    How can i do this by bytes for this code

    xlWorkSheet.Shapes.AddPicture(imagString, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize);

    Please help

    Reply
  23. sujeet Says:
    February 10, 2012 at 9:30 am

    using System;
    using System.Web;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.IO;
    using System.Data;

    public class Handler : IHttpHandler
    {

    public void ProcessRequest(HttpContext context)
    {
    Int32 empno;
    if (context.Request.QueryString[“id”] != null)
    empno = Convert.ToInt32(context.Request.QueryString[“id”]);
    else
    throw new ArgumentException(“No parameter specified”);

    context.Response.ContentType = “image/jpeg”;
    Stream strm = ShowEmpImage(empno);
    byte[] buffer = new byte[4096];
    int byteSeq = strm.Read(buffer, 0, 4096);

    while (byteSeq > 0)
    {
    context.Response.OutputStream.Write(buffer, 0, byteSeq);
    byteSeq = strm.Read(buffer, 0, 4096);
    }
    //context.Response.ContentType = “text/plain”;
    //context.Response.Write(“Hello World”);
    }

    public bool IsReusable
    {
    get
    {
    return false;
    }
    }
    public Stream ShowEmpImage(int empno)
    {
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“aa”].ConnectionString);
    string val = “Select Image from image where ImageId=@ID”;
    SqlCommand cmd = new SqlCommand(val, con);
    cmd.CommandType = new CommandType();
    cmd.Parameters.AddWithValue(“@ID”,empno);
    con.Open();
    object img = cmd.ExecuteScalar();
    try
    {
    return new MemoryStream((byte[])img);
    }
    catch
    {
    return null;
    }
    finally
    {
    con.Close();
    }

    }
    }

    Binary Image

    Image Name

    Image Upload

     

     

     

    using System;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Data.SqlClient;
    using System.IO;
    using System.ComponentModel;

    public partial class _Default : System.Web.UI.Page
    {

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
    Image1.ImageUrl = “~/Handler.ashx?ImageId=” + Image1;
    try
    {
    FileUpload img = (FileUpload)FileUpload1;
    Byte[] imgbyte = null;
    if (img.HasFile && img.PostedFile != null)
    {
    HttpPostedFile file = FileUpload1.PostedFile;
    imgbyte = new Byte[file.ContentLength];
    file.InputStream.Read(imgbyte, 0, file.ContentLength);
    }
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“aa”].ConnectionString);
    con.Open();
    string val = “insert into image(ImageName,Image)values(@name,@ima)SELECTED @@IDENTITY”;
    SqlCommand cmd = new SqlCommand(val, con);
    cmd.Parameters.AddWithValue(“@name”, TextBox1.Text.Trim());
    cmd.Parameters.AddWithValue(“@img”, imgbyte);
    int id = Convert.ToInt32(cmd.ExecuteScalar());
    Label1.Text = string.Format(“Employee id is {0}”, id);

    }
    catch
    {
    Label1.Text = “Error an image”;
    }
    finally
    {

    }
    }
    }

    Reply
  24. Baqar Hassan Says:
    March 15, 2012 at 12:13 pm

    ***// This code made by Syed baqar hassan.
    // E_Mail Add : baqar.hassan110@yahoo.com
    //Arrange the Picture Of Path.***

    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
    pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);

    string[] PicPathArray;
    string ArrangePathOfPic;

    PicPathArray = openFileDialog1.FileName.Split(‘\\’);

    ArrangePathOfPic = PicPathArray[0] + “\\\\” + PicPathArray[1];
    for (int a = 2; a < PicPathArray.Length; a++)
    {
    ArrangePathOfPic = ArrangePathOfPic + "\\\\" + PicPathArray[a];
    }
    }
    ***![// Save the path Of Pic in database][1]***
    // Save the path Of Pic in database

    SqlConnection con = new SqlConnection("Data Source=baqar-pc\\baqar;Initial Catalog=Prac;Integrated Security=True");
    con.Open();

    SqlCommand cmd = new SqlCommand("insert into PictureTable (Pic_Path) values (@Pic_Path)", con);
    cmd.Parameters.Add("@Pic_Path", SqlDbType.VarChar).Value = ArrangePathOfPic;

    cmd.ExecuteNonQuery();

    ***// Get the Picture Path in Database.***

    SqlConnection con = new SqlConnection("Data Source=baqar-pc\\baqar;Initial Catalog=Prac;Integrated Security=True");
    con.Open();

    SqlCommand cmd = new SqlCommand("Select * from Pic_Path where ID = @ID", con);

    SqlDataAdapter adp = new SqlDataAdapter();
    cmd.Parameters.Add("@ID",SqlDbType.VarChar).Value = "1";
    adp.SelectCommand = cmd;

    DataTable DT = new DataTable();

    adp.Fill(DT);

    DataRow DR = DT.Rows[0];
    pictureBox1.Image = Image.FromFile(DR["Pic_Path"].ToString());

    *****// This code made by Syed baqar hassan.
    // E_Mail for Feedback : baqar.hassan110@yahoo.com*****

    [1]: http://i.stack.imgur.com/LL2qD.png

    Reply
  25. click Says:
    June 5, 2012 at 8:36 pm

    This is a wonderful web site, would you be interested in working on an interview about just how you produced it? If so e-mail me!

    Reply
  26. Soumya Ranjan Samantaray Says:
    January 5, 2013 at 4:38 am

    Hi!! It’s Soumya. Plz reply how to store images in microsoft access database using C#.

    Reply
    • riteshshah Says:
      January 5, 2013 at 4:43 am

      Soumya,

      It is better to store images in hard drive somewhere and give path in access database. it is not at all advisable to use access as a binary storage.

      Reply
  27. Soumya Ranjan Samantaray Says:
    March 1, 2013 at 7:04 pm

    Hi! It’s Soumya. Sir I have uploaded some images in sql server database and i want to retrieve those images in asp .net web control.But it gets error “The filename, directory name, or volume label syntax is incorrect.” The code I”m using to save image file is as of follows:
    protected void Button1_Click(object sender, EventArgs e)
    {
    string image1 = FileUpload1.FileName;//i’m uploading image from “C:\Program Files\Common Files\microsoft shared\DevServer\10.0”
    string image2 = FileUpload2.FileName;//i’m uploading image from “C:\Program Files\Common Files\microsoft shared\DevServer\10.0”
    FileStream fs1 = new FileStream(image1, FileMode.Open, FileAccess.Read);
    FileStream fs2 = new FileStream(image2, FileMode.Open, FileAccess.Read);
    byte[] bimage1 = new byte[fs1.Length];
    byte[] bimage2 = new byte[fs2.Length];
    fs1.Read(bimage1, 0, Convert.ToInt32(fs1.Length));
    fs2.Read(bimage2, 0, Convert.ToInt32(fs2.Length));
    fs1.Close();
    fs2.Close();
    cn.Open();
    SqlParameter sp = new SqlParameter();
    sp.SqlDbType = SqlDbType.Image;
    sp.ParameterName = “@passport_photo”;
    sp.ParameterName = “@sign_photo”;
    sp.Value = bimage1;
    sp.Value = bimage2;
    SqlCommand cm = new SqlCommand(“INSERT INTO ImageCollection values(@img_id,” + “@passport_photo,”+”@sign_photo)”, cn);
    cm.Parameters.AddWithValue(“@img_id”,TextBox1.Text);
    cm.Parameters.AddWithValue(“@passport_photo”,sp.Value=bimage1);
    cm.Parameters.AddWithValue(“@sign_photo”,sp.Value=bimage2);
    cm.ExecuteNonQuery();
    cm.Dispose();
    cn.Dispose();
    cn.Close();
    }

    The code By which I tried to retrieve image from sql server database is as of follows and I”m getting error on this part. Please rectify my mistake in this part:

    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.ImageUrl =”~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/”+bimage1;
    Image2.ImageUrl = “~/E:\\Visual Studio 2010\\WebSites\\WebSite7\\images/”+bimage2;
    }
    dr.Close();
    cn.Close();
    }
    catch (Exception ex)
    {
    throw ex;
    }

    Reply

Leave a reply to Shakeel Cancel reply

«

»