How to Insert and Read image To/From database in ASP.NET and C# ?

How to Insert and Read image To/From database in ASP.NET and C# ?

04:53
In this article we are discussing about how to insert and read image from database (SQL Sserver) in our applications.

 At a times you might need to insert or read am image stored in a SQL Server in your project. So in this article we are discussing about how to insert and read image from database (SQL Sserver) in our applications.

 To insert an image we first need to create a table in the SQL Server database; the code is as below:



CREATE TABLE [dbo].[Student](
 [Id] [int] IDENTITY(1,1) ,
 [StudentName] [nvarchar](200) ,
 [Address] [nvarchar](500) ,
 [Country] [nvarchar](50) ,
 [Photo] [image] 
)
GO

We have Student table with Id as identity column and StudentName, Address, Country and Photo as the remaining columns.

We need to create a procedure to insert data in SQLServer Student Table; the following is the code for it.




CREATE TABLE [dbo].[StudentRegistration]
 (
  @StudentName nvarchar(50),
  @Address nvarchar(100),
  @Country nvarchar(20),
  @Photo image
 )
as
 begin
 Insert into Student values(@StudentName,@Address,@Country,@photo)
 end
GO

With few student details this procedure from the user and click on a button to add the data to the database.

 Now we are design Student Registration for using below code:



<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" 
AutoEventWireup="true" CodeFile="Registration.aspx.cs" 
Inherits="Registration" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="Server">
    <style type="text/css">
        /*.style1
        {
            width: 100%;
        } */
        fieldset
        {
            margin: 1em 20px;
            border-radius: 5px;
            padding: 1em;
            border: 7px solid #3a4f63;
        }
        legend
        {
            font-size: medium;
            border-radius: 5px;
            font-weight: bold;
            color: Black;
            background-color: #F5F5F5;
        }
        table
        {
            width: 100%;
            background-color: #F5F5F5;
            border-radius: 5px;
            margin: 10px 0px 20px 0px;
    padding: 20px 0px 20px 50px;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="Server">
    <fieldset >
        <legend >Student Registration</legend>
        <table>
            <tr>
                <td colspan="2">
                </td>
            </tr>
            <tr>
                <td>
                    Student Name
                </td>
                <td>
                    <asp:TextBox ID="txtStdName" Width="200px" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Address
                </td>
                <td>
                    <asp:TextBox ID="txtAddress" Width="200px" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Country
                </td>
                <td>
                    <asp:TextBox ID="txtCountry" Width="200px" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Photo
                </td>
                <td>
                    <asp:FileUpload ID="fileUpload" Width="250px" runat="server" />
                    <asp:Label ID="lblStatus" ForeColor="Red" runat="server"></asp:Label>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;
                </td>
                <td>
                    <asp:Button ID="BtnSave" runat="server" Style="text-align: center" Text="Save Details"
                        OnClick="BtnSave_Click" />
                </td>
            </tr>
        </table>
    </fieldset>
</asp:Content>


Page design:



The code behind the "Save Details" button click event as follows:



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

public partial class Registration : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
    SqlDataAdapter da;
    DataSet ds;
    SqlCommand cmd;

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void BtnSave_Click(object sender, EventArgs e)
    {
        try
        {
            if (fileUpload.FileName != "")
            {
                //Check file extension and allow only JPG, gif and png file to upload
                string fileExtension = Path.GetExtension(fileUpload.PostedFile.FileName);
                //string path = Path.GetFullPath(fileUpload.FileName);
                string path = Server.MapPath(fileUpload.FileName);
                if (fileExtension == ".jpg" || fileExtension == ".png" || fileExtension == ".gif")
                {                    
                    Stream fs = fileUpload.PostedFile.InputStream;
                    BinaryReader br = new BinaryReader(fs);
                    Byte[] photo = br.ReadBytes((Int32)fs.Length);

                    fs.Read(photo, 0, Convert.ToInt32(fs.Length));

                    cmd = new SqlCommand("StudentRegistration", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@StudentName", txtStdName.Text);
                    cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
                    cmd.Parameters.AddWithValue("@Country", txtCountry.Text);
                    cmd.Parameters.AddWithValue("@photo", photo);
                    con.Open();
                    int rows = cmd.ExecuteNonQuery();
                    if (rows > 0)
                    {
                        string script = "<script>alert('Data Added Successfully')</script>";
                        Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "Added", script);
                        txtStdName.Text = "";
                        txtAddress.Text = "";
                        txtCountry.Text = "";
                    }
                    else
                    {
                        string script = "<script>alert('Error Adding Data')</script>";
                        Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "Error", script);
                    }
                }
                else
                {
                    lblStatus.Text = "Only Jpg,gif or Png files format are permitted";
                }
            }
            else
            {
                lblStatus.Text = "Please Select a File.....";
            }
            con.Close();
        }
        catch (Exception ex)
        {

        }
    }
}

Now run your project and on browser fill the details with the photo of the student. After clicking of "Save Details" button you'll get message box of "Data Added Successfully".



Now, To retrieve the image from the database and display it in the image control we need
to create a handler (ashx) file. 

 Rightclick on your solution explorer -> add new item -> Select Generic Handler from it. And add the following is the code for the same.


<%@ WebHandler Language="C#" Class="Handler" %>

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;

public class Handler : IHttpHandler
{

    public void ProcessRequest(HttpContext context)
    {
        if (context.Request.QueryString["id"] != null)
        {
            // context.Response.Write(context.Request.QueryString["id"]);
            string dbcon = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
            SqlConnection con = new SqlConnection(dbcon);
            con.Open();
            SqlCommand cmd = new SqlCommand("Select Photo from Student where ID=@ID", con);
            cmd.Parameters.AddWithValue("@ID", context.Request.QueryString["ID"].ToString());
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            context.Response.BinaryWrite((byte[])dr["Photo"]);
            dr.Close();
            con.Close();
        }
        else
        {
            context.Response.Write("No Image Found");
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Now add a new page to show details and add like as below code to your .aspx page.


<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeFile="StudentDetails.aspx.cs" Inherits="StudentDetails" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="Server">
    <style type="text/css">
        fieldset
        {
            margin: 1em 20px;
            border-radius: 5px;
            padding: 1em;
            border: 7px solid #3a4f63;
        }
        legend
        {
            font-size: medium;
            border-radius: 5px;
            font-weight: bold;
            color: Black;
            background-color: #F5F5F5;
        }
        table
        {
            width: 100%;
            background-color: #F5F5F5;
            border-radius: 5px;
            margin: 10px 0px 20px 0px;
            padding: 20px 0px 20px 50px;
        }
        .style1
        {
            text-align: left;
        }
        .style2
        {
            width: 253px;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="Server">
    <fieldset>
        <legend>Show Student Details</legend>
        <table>
            <tr>
                <td rowspan="3" class="style2">
                    Enter a Student ID To be Searched
                </td>
                <td class="style1">
                    <asp:DropDownList ID="ddlStudentID" runat="server" Width="200px" AutoPostBack="True"
                        DataSourceID="SqlDataSource1" DataTextField="Id" DataValueField="Id">
                    </asp:DropDownList>
                </td>
            </tr>
            <tr>
                <td class="style1">
                    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:connString %>"
                        SelectCommand="SELECT Id, StudentName, Address,Country, Photo FROM Student">
                    </asp:SqlDataSource>
                </td>
            </tr>
            <tr>
                <td class="style1">
                    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search" />
                </td>
            </tr>
            <tr>
                <td class="style2">
                    Image :
                </td>
                <td valign="top" class="style1">
                    <asp:Image ID="Image1" runat="server" ImageUrl='<%# "~/Handler.ashx?ID=" + Eval("Id")%>' />
                </td>
            </tr>
            <tr>
                <td class="style2">
                    Student Name :
                </td>
                <td class="style1">
                    &nbsp;<asp:Label ID="Label1" runat="server"></asp:Label>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    Address :
                </td>
                <td class="style1">
                    &nbsp;<asp:Label ID="Label2" runat="server"></asp:Label>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    Country :
                </td>
                <td class="style1">
                    &nbsp;<asp:Label ID="Label3" runat="server"></asp:Label>
                </td>
            </tr>
        </table>
    </fieldset>
</asp:Content>

And now add code behind Search button click as below code:



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class StudentDetails : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
    SqlCommand cmd;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        con.Open();

        cmd = new SqlCommand("ShowStudentDetails", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Id", ddlStudentID.Text);

        SqlDataReader dr = cmd.ExecuteReader();
        dr.Read();
        if (dr.HasRows)
        {
            Label1.Text = dr[1].ToString();
            Label2.Text = dr[2].ToString();
            Label3.Text = dr[3].ToString();
            Image1.ImageUrl = "~/Handler.ashx?id=" + ddlStudentID.Text;
        }
        con.Close();
    }
}

Now run the application and you can see like as below on your browser:

Now select the StudentID from the dropdown and click on Search button.
Here you see as follows details of student on the browser screen:




I hope you liked the above application and it might will help you in your project. Kindly post your queries/reply.

About

Beginner School is optimized for learning. This is useful for getting basic knowledge with easiest way. Tutorials, examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. With the help of other reference book and website, the motive to provide you easiest way to understand the coding, technology and other things.....

Subscribe to: Post Comments ( Atom )