Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to display images in GridView from SQL Server database in ASP.NET

Author: Rachel Nicole
Download Source Code : 1321_RetrieveImageFromDatabase.zip

The purpose of this article is to explain how we can retrieve saved images from SQL Server database and display in GridView in ASP.NET.

 

In a previous article on DevASP.NET, I have explained that how you can save images to SQL Server database. When you saved images into SQL Server database, you might need to retrieve them and display them. This article will explain how to retrieve and display already saved images in SQL Server database.

For this example, I am using GridView control and Image control to display images. A Generic Handler will be used to retrieve images from database. I have created a table in ‘master’ database with the name of ‘SaveImage’ and I have three images saved in it.

 

  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual Basic or Visual C#  > ASP.NET Empty Web Site
  4. Right click on web site > Add New Item > Web Form
  5. Right click on website > New Folder. Rename the folder as “Files”.
  6. Drag and drop A GridView control

     
    <asp:GridView ID="GridView1" runat="server">
     
    </asp:GridView>

     
  7. Include following name spaces in code behind file

    C#
     
    using System.Data;
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.Data
    Imports System.Data.SqlClient
     
  8. Write code below in Page Load event
     
    C#
     
    protected void Page_Load(object sender, EventArgs e) {
           
        string cmdText = "SELECT * FROM ImageSave";
        string myConnection = "Data Source=Local;Initial Catalog=master;Integrated Security=True";
        SqlConnection connection = new SqlConnection(myConnection);
        SqlCommand command = new SqlCommand(cmdText, connection);
        connection.Open();
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataTable dt = new DataTable();
        da.Fill(dt);
        connection.Close();
           
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
     
     
    VB.NET
     
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
     
        Dim cmdText As String = "SELECT * FROM ImageSave"
        Dim myConnection As String = "Data Source=Local;Initial Catalog=master;Integrated Security=True"
        Dim connection As New SqlConnection(myConnection)
        Dim command As New SqlCommand(cmdText, connection)
        connection.Open()
        Dim da As New SqlDataAdapter(command)
        Dim dt As New DataTable()
        da.Fill(dt)
        connection.Close()
     
        GridView1.DataSource = dt
        GridView1.DataBind()
    End Sub
     
    Here is the explanation of the code
     
    At the start of the Page Load Event, I have written a query to get all data from “ImageSave” table.  
    I have connected to my server to get data.
    Then I have created a SqlCommand object by passing command Text and SqlConnection parameters.
    Connection should be open to
    I have created a SqlDataAdapter object by passing SqlCommand object in constructor.
    Then I have created a new DataTable object and fill the table from SqlDataAdapter object.
    Don’t forget to close the connection.
    At the end of Page Load event, bind GridView to DataTable object.

     
  9. Add code below in GridView control in Default.aspx page

    <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server">
       <Columns>
         <asp:BoundField HeaderText="Image Name" DataField="ImageName" />
         <asp:TemplateField>
            <ItemTemplate>
               <asp:Image ID="Image1" Height="100px" Width="100px" ImageUrl='<%# "~/Handler.ashx?ImageID=" + Eval("ImageID") %>' runat="server" />
            </ItemTemplate>
         </asp:TemplateField>
       </Columns>
    </asp:GridView>
     
    I have displayed Image name in BoundField and Image TemplateField using Image control.
    In ImageUrl attribute, I have provided a URL of Generic Handler file passing ImageID as query string. We will create this Generic Handler file in next step.
     
  10. Right click on web site > Add New Item > Generic Handler
  11. Write code below in Handler.ashx file


    C#
     
    <%@ WebHandler Language="C#" Class="Handler" %>
     
    using System;
    using System.Web;
    using System.Data.SqlClient;
     
    public class Handler : IHttpHandler {
       
        public void ProcessRequest (HttpContext context) {
            context.Response.ContentType = "image/jpeg";
     
            int id = Convert.ToInt32(context.Request.QueryString["ImageID"]);
     
            string cmdText = "SELECT ImageData FROM ImageSave WHERE ImageID = " + id;
            string myConnection = "Data Source=Local;Initial Catalog=master;Integrated Security=True";
            SqlConnection connection = new SqlConnection(myConnection);
            SqlCommand command = new SqlCommand(cmdText, connection);
            connection.Open();
     
            SqlDataReader reader = command.ExecuteReader();
            reader.Read();
            byte[] image = (byte[])reader[0];
            context.Response.BinaryWrite(image);
            reader.Close();
            connection.Close();
           
        }
     
        public bool IsReusable {
            get {
                return false;
            }
        }
     
    }
     
    VB.NET
     
    <%@ WebHandler Language="VB" Class="Handler" %>
     
    Imports System
    Imports System.Web
    Imports System.Data.SqlClient
     
     
    Public Class Handler : Implements IHttpHandler
       
        Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
            context.Response.ContentType = "image/jpeg"
     
            Dim id As Integer = Convert.ToInt32(context.Request.QueryString("ImageID"))
     
            Dim cmdText As String = "SELECT ImageData FROM ImageSave WHERE ImageID = " & id
            Dim myConnection As String = "Data Source=Local;Initial Catalog=master;Integrated Security=True"
            Dim connection As New SqlConnection(myConnection)
            Dim command As New SqlCommand(cmdText, connection)
            connection.Open()
     
            Dim reader As SqlDataReader = command.ExecuteReader()
            reader.Read()
            Dim image As Byte() = DirectCast(reader(0), Byte())
            context.Response.BinaryWrite(image)
            reader.Close()
            connection.Close()
        End Sub
     
        Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
            Get
                Return False
            End Get
        End Property
     
    End Class
     
    Here is the explanation of the code
     
    First we have to include System.Data.SqlClient namespace.
    I have mentioned the ContentType as “image/jpeg”
    Then I have retrieved the query string value and passed this value in command Text to retrieve image.
    I have created SqlCommand object, open connection same like in Page Load event.
    Then I have created a SqlDataReader object and called the ExecuteReader() method of SqlCommand object.
    I have called the Read() method of SqlDataReader object to move forward SqlDataReader object.
    Then I have declared a byte array, read binary data and assigned this data to byte array.
    Then I have written the string of binary data to output stream.
    At the end, I have closed SqlDataReader object and SqlDataConnection object.     
    IsReuseable propery will be generated when you create Generic handler. You can return “true” to reuse it again.
     
  12. Right Click on website > View web site in browser.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to bind data to ListView control in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net