Save and retrieve image from SQL 2005 in ASP.Net

In this article you will learn how to save and retrieve image from SQL 2005 in ASP.Net

How to save image in database

Data base design:

1. Create SQL table with the name of "ImageExample" and the create column with image datatype as Image.
   
   
   

Store Image in Grid:

To store image in database first we have to open and read the file and convert the file into byte array. For saving to database

The C# coding to save image:

    SqlConnection con = new SqlConnection("Connection string");
                                con.Open();
            
    SqlCommand oc=new SqlCommand("insert into TableName values(@Image)",con);
    string path=@"Path\imagename.JPG";
                                FileStream fs=new FileStream(path,FileMode.Open,FileAccess.Read);
                                BinaryReader br=new BinaryReader(fs);
                                 FileInfo fi=new FileInfo(path);
                                byte[] imagedata = br.ReadBytes((int)fi.Length);
                                oc.Parameters.Add("@Image", imagedata);
                                oc.ExecuteNonQuery();
                                con.Close();

Retrieve image from database using handler.

1. Right click on solution explorer and select add new item.In that select hanler.ashx and rename as ImageHandler.ashx

Code used in Imagehandler.aspx


    public class Handler1 : IHttpHandler
    { 
        public void ProcessRequest(HttpContext context)
        {
            string imageid = context.Request.QueryString["Image"];
            SqlConnection con = new SqlConnection("Connectionstring");
            con.Open();
            SqlCommand command = new SqlCommand("select Image from ImageExample", con);
            SqlDataReader dr = command.ExecuteReader();
            dr.Read();
            context.Response.BinaryWrite((Byte[])dr[0]);
            con.Close();
            context.Response.End();
        }
 
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }

Code used in aspx page

    <asp:GridView ID="GridView1" runat="server" Width="500px" AutoGenerateColumns="false">
    <Columns>      
           <asp:TemplateField HeaderText="Image">
           <ItemTemplate>
           <asp:Image ID="Image" runat="server" ImageUrl='<%#"ImageHandler.ashx"%>'/>
           </ItemTemplate>
           </asp:TemplateField>
    </Columns>
    </asp:GridView>

Code used in Retrieve image using C#

   SqlConnection con = new SqlConnection("Connnection string");
   con.Open();
   SqlCommand oc = new SqlCommand("Select * from ImageExample", con);
   SqlDataAdapter da = new SqlDataAdapter(oc);
   DataTable dt = new DataTable();
   da.Fill(dt);          
   GridView1.DataSource = dt;
   GridView1.DataBind();