Image storing and Retrieving from Sql server with C# Asp.Net

   Hi in this post i am going to explain how to Store ans Retrieve Image from Sql server using C# and Asp .Net.Lets split our Objective into two three steps.

Steps :
            1.Create a Table in Sql Server
            2.Storing Image to Table from c# web form.
            3.Retrieving Image from Table to c# web form


Step 1: Lets Create a table with following fields as show in the image

Sql Query:

CREATE TABLE [dbo].[ImageTable](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [Title] [nvarchar](50) NOT NULL,

      [Data] [varbinary](max) NOT NULL,

 CONSTRAINT [PK_ImageTable] PRIMARY KEY CLUSTERED

(

      [ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

Step 2:

Now Launch your visual studio and create a new Empty Web Application Project (File->New->Project->Asp.Net Empty Web Application)

Now the Project is created now click on the solution explorer and right click on your project and add a new webform(Add->New Item->webform)

Now add the following code to your webform

 <asp:Label ID="lblImage" runat="server" Text="Image:" />
            <asp:FileUpload ID="fload" runat="server" ToolTip="Select the image"        BackColor="#CC99FF" />
            <asp:Button ID="btnstore" runat="server" Text="Submit" OnClick="btnstore_Click" /><br />
            <asp:Label ID="lblMsg" runat="server" /><br />
            <asp:Label ID="lblId" Text="Enter Id:" runat="server" />
            <asp:TextBox ID="txtId" runat="server" />
            <asp:Button ID="btnReterive" runat="server" Text="Get Image" OnClick="btnReterive_Click" />
            <asp:Image ID="image" runat="server" />

Then open the web.config file and add the connection string:

<connectionStrings>
    <add name="DBCS" connectionString="Data Source=(local);Database=Test;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
 </connectionStrings>

Now move to the code behind of the webform1.aspx and the add the following code inside the btnstore_Click event handle to store the image to the database

protected void btnstore_Click(object sender, EventArgs e)
        {
            string constr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            if (fload.HasFile)
            {
                fload.SaveAs(Server.MapPath("~/Uploads/" + fload.FileName));
                string path = Server.MapPath("~/Uploads/" + fload.FileName);
                //Covert the image into stream to store the image file in database.
                FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
                BinaryReader br = new BinaryReader(fs);
                Byte[] by = br.ReadBytes((Int32)fs.Length);
                using (SqlConnection conn = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = "Insert into ImageTable (Title,Data) values(@Name,@Data)";
                        cmd.Parameters.AddWithValue("@Name", fload.FileName);
                        cmd.Parameters.AddWithValue("@Data", by);
                        cmd.Connection = conn;
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                        lblMsg.Text = "Image Saved Sucessfully..";
                    }
                }
            }
        }

Upto this we complete the storing of images into database process.In order to view the image in webform from database we need to create a handler.

Step 3:

To add a handler Right Click on the Project Name in the solution explorer and then Click Add->New Item->Generic Handler .The Generic Handler have extension of .ashx.

Add the following code inside the handler

 public void ProcessRequest(HttpContext context)
        {
             if(context.Request.QueryString["ID"] !=null)
             {
                 string constr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                 using (SqlConnection conn = new SqlConnection(constr))
                 {
                     using (SqlCommand cmd = new SqlCommand())
                     {
                         cmd.CommandText = "Select Title,Data from ImageTable where ID=@ID";
                         cmd.Parameters.AddWithValue("@ID",context.Request.QueryString["ID"].ToString());
                         cmd.Connection = conn;
                         conn.Open();
                         SqlDataReader rdr = cmd.ExecuteReader();
                         rdr.Read();
                         context.Response.BinaryWrite((byte[])rdr["Data"]);
                     }
                 }
             }
             else
             {
                 context.Response.Write("Enter correct id");
             }
        }

Now Come back to webform and the following code for the btnReterive_Click event handler

 protected void btnReterive_Click(object sender, EventArgs e)
        {
            image.ImageUrl = "~/ImageHandler.ashx?ID=" + txtId.Text;
            image.Width = 500;
            image.Height = 500;

        }

Sample Output Screen:

Download the full source code from here

Click here for my previous blog

Happy coding.