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

1. Save and Retrieve Files from SQL Server Database using ASP.Net

2. Save Files to SQL Server Database using FileUpload Control

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 Object, ByVal 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 the DataImageUrlFormatString 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 Object, ByVal 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.

ImagesFromDBinGridView.zip (1.80 mb)