Monday 1 April 2013

Display Images from Database using ASP.Net.

Display Images from Database using ASP.Net.

In my previous article, I explained Display Images from Database using ASP.Net.
In this article, I am explaining how to display images and pictures from database in the asp.net GridView Control. To save images to SQL Server Database refer my articles
Below is the design of the database table which has stored the images in binary format.


Database Design

I have already stored three images in the database table refer the figure below which displays how the images are stored.


Images stored in binary format in database

Next I’ll explain how create an Image Handler that will get the images from the database and write them to the Response Stream. To create Image Handler I am using aspx page which has the following code in its page load event.
1. ImageCSharp.aspx (Image Handler for C#)
2. ImageVB.aspx (Image Handler for VB)
As you will notice in the code snippet below, the handler receives the Image ID as QueryString parameter and based on that it retrieves the binary data of the image from the database and writes the same to Response stream.
                        


ImageCSharp.aspx

protected void Page_Load(object sender, EventArgs e)
{
    if (Request.QueryString["ImageID"] != null)
    {
        string strQuery = "select Name, ContentType, Data from" +
            " tblFiles where id=@id";
        String strConnString = System.Configuration.ConfigurationManager
            .ConnectionStrings["conString"].ConnectionString;
        SqlCommand cmd = new SqlCommand(strQuery);
        cmd.Parameters.Add("@id"SqlDbType.Int).Value
            Convert.ToInt32(Request.QueryString["ImageID"]);
        SqlConnection con = new SqlConnection(strConnString);
        SqlDataAdapter sda = new SqlDataAdapter();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        DataTable dt = new DataTable();
        try
        {
            con.Open();
            sda.SelectCommand = cmd;
            sda.Fill(dt);
        }
        catch
        {
            dt = null;
        }
        finally
        {
            con.Close();
            sda.Dispose();
            con.Dispose();
        }
        if (dt != null)
        {
            Byte[] bytes = (Byte[])dt.Rows[0]["Data"];
            Response.Buffer = true;
            Response.Charset = "";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = dt.Rows[0]["ContentType"].ToString();
            Response.AddHeader("content-disposition""attachment;filename="
                + dt.Rows[0]["Name"].ToString());
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();
        }
    }
}

 

ImageVB.aspx
Protected Sub Page_Load(ByVal sender As ObjectByVal e As System.EventArgs) Handles Me.Load
        If Request.QueryString("ImageID"IsNot Nothing Then
            Dim strConnString As String = System.Configuration. _
                ConfigurationManager.ConnectionStrings("conString") _
                .ConnectionString
            Dim strQuery As String = "select Name, ContentType," _
               " Data from tblFiles where id=@id"
            Dim cmd As SqlCommand = New SqlCommand(strQuery)
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = _
                Convert.ToInt32(Request.QueryString("ImageID"))
            Dim con As New SqlConnection(strConnString)
            Dim sda As New SqlDataAdapter
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            Dim dt As New DataTable
            Try
                con.Open()
                sda.SelectCommand = cmd
                sda.Fill(dt)
            Catch ex As Exception
                dt = Nothing
            Finally
                con.Close()
                sda.Dispose()
                con.Dispose()
            End Try
            If dt IsNot Nothing Then
                Dim bytes() As Byte = CType(dt.Rows(0)("Data"), Byte())
                Response.Buffer = True
                Response.Charset = ""
                Response.Cache.SetCacheability(HttpCacheability.NoCache)
                Response.ContentType = dt.Rows(0)("ContentType").ToString()
                Response.AddHeader("content-disposition", _
                    "attachment;filename=" _
                  & dt.Rows(0)("Name").ToString())
                Response.BinaryWrite(bytes)
                Response.Flush()
                Response.End()
            End If
        End If
    End Sub  


Once this is done we can proceed towards the GridView. In this article I will explain two ways to call the handler
1. Using Image Field of GridView
2. Using Image Control in Template Field on GridView

Using Image Field of GridView
As you can see below I have used an Image Field in which I have specified the respective handler page in theDataImageUrlFormatString and specified the ID column in the DataImageUrlField
C#
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns = "false"
Font-Names = "Arial" Caption = "Using ImageField">
<Columns>
    <asp:BoundField DataField = "ID" HeaderText = "ID" />
    <asp:BoundField DataField = "Name" HeaderText = "Image Name" />
    <asp:ImageField DataImageUrlField = "ID"
        DataImageUrlFormatString = "ImageCSharp.aspx?ImageID={0}"
     ControlStyle-Width = "100" ControlStyle-Height = "100"
     HeaderText = "Preview Image"/>
Columns>
asp:GridView>

VB.Net
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns = "false"
Font-Names = "Arial" Caption = "Using ImageField">
<Columns>
    <asp:BoundField DataField = "ID" HeaderText = "ID" />
    <asp:BoundField DataField = "Name" HeaderText = "Image Name" />
    <asp:ImageField DataImageUrlField = "ID"
        DataImageUrlFormatString = "ImageVB.aspx?ImageID={0}"
     ControlStyle-Width = "100" ControlStyle-Height = "100"
     HeaderText = "Preview Image"/>
Columns>
asp:GridView>

Using Image Control in Template Field on GridView
As you can see below I have used an Image Control in which I have specified the respective handler page and the ID from database using the Eval Statement in the ImageUrl property of Image Control
   
      
C#
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns = "false"
Font-Names = "Arial" Caption = "Using ImageControl" >
<Columns>
    <asp:BoundField DataField = "ID" HeaderText = "ID" />
    <asp:BoundField DataField = "Name" HeaderText = "Image Name" />
    <asp:TemplateField>
        <ItemTemplate>
          <asp:Image ID="Image1" Height = "100" Width = "100" runat="server"
             ImageUrl = '' />
        ItemTemplate>
    asp:TemplateField>
Columns>
asp:GridView>

VB.Net
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns = "false"
Font-Names = "Arial" Caption = "Using ImageControl" >
<Columns>
    <asp:BoundField DataField = "ID" HeaderText = "ID" />
    <asp:BoundField DataField = "Name" HeaderText = "Image Name" />
    <asp:TemplateField>
        <ItemTemplate>
          <asp:Image ID="Image1" Height = "100" Width = "100" runat="server"
             ImageUrl = '' />
        ItemTemplate>
    asp:TemplateField>
Columns>
asp:GridView>

In the code behind getting records from the database and binding the same to the GridView in the page load event of the page
   
     
C#
protected void Page_Load(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    String strConnString = System.Configuration.ConfigurationManager.
        ConnectionStrings["conString"].ConnectionString;
    string strQuery = "select ID, Name from tblFiles order by ID";
    SqlCommand cmd = new SqlCommand(strQuery);
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    try
    {
        con.Open();
        sda.SelectCommand = cmd;
        sda.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
        GridView2.DataSource = dt;
        GridView2.DataBind();

    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
        con.Close();
        sda.Dispose();
        con.Dispose();
        dt.Dispose();
    }
}

  VB.Net
        
Protected Sub Page_Load(ByVal sender As ObjectByVal e As EventArgs) Handles Me.Load
   Dim dt As New DataTable()
   Dim strConnString As String = System.Configuration.ConfigurationManager. _
        ConnectionStrings("conString").ConnectionString()
   Dim strQuery As String = "select ID, Name from tblFiles order by ID"
   Dim cmd As New SqlCommand(strQuery)
   Dim con As New SqlConnection(strConnString)
   Dim sda As New SqlDataAdapter()
   cmd.CommandType = CommandType.Text
   cmd.Connection = con
   Try
     con.Open()
     sda.SelectCommand = cmd
     sda.Fill(dt)
     GridView1.DataSource = dt
     GridView1.DataBind()
     GridView2.DataSource = dt
     GridView2.DataBind()
   Catch ex As Exception
     Response.Write(ex.Message)
   Finally
     con.Close()
     sda.Dispose()
     con.Dispose()
     dt.Dispose()
   End Try
End Sub

The figure below displays the GridView with the images from database


GridView : Displaying images from DataBase

This completes this article. Using the article one can biuld an image gallery in ASP.Net. You can download the source from the link below.





No comments:

Post a Comment