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.

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/

Advertisements

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

  1. Rashmi Says:

    thank You very much

  2. Leah Says:

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

    • riteshshah Says:

      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.

  3. saravanan Says:

    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

    • riteshshah Says:

      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.

  4. sushant Says:

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

  5. Rahul Satasia Says:

    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.

  6. varun Says:

    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.

  7. varun Says:

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

  8. varun Says:

    sorry ..i cant figure out d error….

  9. purna Says:

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

  10. jeet Says:

    thank you

  11. jeet Says:

    hii ritesh

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

  12. Neetu Says:

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

  13. Chintan Says:

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

  14. Kittiphong Says:

    Thank you, this is a very good article 🙂

  15. Rasika Says:

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

  16. gajraj singh Says:

    HI

  17. Banajyotsna Says:

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

  18. Banajyotsna Says:

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

  19. amant Says:

    how ca i use image control to retrive image??

  20. kumar Says:

    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…

  21. raja Says:

    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

  22. Shakeel Says:

    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

  23. sujeet Says:

    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
    {

    }
    }
    }

  24. Baqar Hassan Says:

    ***// 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

  25. click Says:

    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!

  26. Soumya Ranjan Samantaray Says:

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

    • riteshshah Says:

      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.

  27. Soumya Ranjan Samantaray Says:

    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;
    }


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: