How to Retrieve Images from Database (In Layer Architecture)

 
image1.gif
 
Now I want to retrieve these images from the database and display in a Grid View.
 
These are the products I want to retrieve from the database.
 
image2.gif
 

Presentation Layer

 
Take a page SearchComputers.aspx
 
Under SearchComputers.aspx page:
  1. <asp:GridView ID="GVImages" runat="server" AutoGenerateColumns="false" HeaderStyle-BackColor="red" HeaderStyle-ForeColor="white" Height="278px" Width="476px">  
  2.      <Columns>  
  3.           <asp:BoundField DataField="ProductId" HeaderText="ID" Visible="false" />  
  4.           <asp:BoundField DataField="ProductType" HeaderText="ProductType" />  
  5.           <asp:TemplateField HeaderText="Image">  
  6.                <ItemTemplate>  
  7.                     <asp:ImageButton ID="ImageButton1" runat="server" ImageUrl='<%# "Handler.ashx?id="+ Eval("ProductId")%>' Width="200" Height="100" />  
  8.                </ItemTemplate>  
  9.           </asp:TemplateField>  
  10.      </Columns>  
  11.      <HeaderStyle BackColor="Red" ForeColor="White"></HeaderStyle>  
  12. </asp:GridView> 
Under SearchComputers.aspx page.cs :
  1. protected void Page_Load(object sender, EventArgs e) {  
  2.      // SelectProducts is class.Now we should create a object for that class.  
  3.   
  4.      SelectProducts b = new SelectProducts();  
  5.      DataSet ds = new DataSet();  
  6.      ds = b.FetchAllImagesInfo();  
  7.      GVImages.DataSource = ds;  
  8.      GVImages.DataBind();  
If you want to retrieve images from a database you should use a Generic Handler Class.
 
That class name is Handler.ashx
 
Under Handler.ashx Class
  1. <%@ WebHandler Language="C#" Class="Handler" %>  
  2. using System;  
  3. using System.Web;  
  4. using System.Data.SqlClient;  
  5. using System.Configuration;  
  6. using System.Data;  
  7. using System.IO;  
  8. public class Handler: IHttpHandler {  
  9.     public void ProcessRequest(HttpContext context) {  
  10.         string id = context.Request.QueryString["id"];  
  11.         SqlConnection con = new SqlConnection();  
  12.         con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;  
  13.         // Create SQL Command  
  14.         SqlCommand cmd = new SqlCommand();  
  15.         cmd.CommandText = "Select Image from Products where ProductId ='" + id + "'";  
  16.         cmd.CommandType = System.Data.CommandType.Text;  
  17.         cmd.Connection = con;  
  18.         SqlParameter ProductId = new SqlParameter("@ProductId", System.Data.SqlDbType.Int);  
  19.         ProductId.Value = context.Request.QueryString["id"];  
  20.         cmd.Parameters.Add(ProductId);  
  21.         con.Open();  
  22.         SqlDataReader dReader = cmd.ExecuteReader();  
  23.         dReader.Read();  
  24.         context.Response.BinaryWrite((byte[]) dReader["Image"]);  
  25.         dReader.Close();  
  26.         con.Close();  
  27.     }  
  28.     public bool IsReusable {  
  29.         get {  
  30.             return false;  
  31.         }  
  32.     }  

Business Access Layer

 
Take a Class i.e. SelectProducts.cs
 
Under SelectProducts.cs class
  1. public DataSet FetchAllImagesInfo() {  
  2.     return ds = SqlHelper.ExecuteDataset(clsConnection.Connection, CommandType.StoredProcedure, "sp_GetProductsWithImage");  
  3. }  
  4. // Here "sp_GetProductsWithImage" is the stored procedure Name. 

Data Access Layer

  1. public class clsConnection {  
  2.     public clsConnection() {  
  3.         //  
  4.         // TODO: Add constructor logic here  
  5.         //  
  6.     }  
  7.     public static string Connection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;  
Under Web.config:
  1. <connectionStrings>  
  2.      <add name="ConnectionString" connectionString="user Id=sa;Password=123;DataBase=RentalShopping;Data Source=server2" providerName="System.Data.SqlClient" />  
  3. </connectionStrings>   
  4. //Here write your database name,userid,password,datasource. 
    Under the DataBase:
     
    Create one database i.e RentalShopping.Under Database create one table.I have created one table i.e Products.
    1. CREATE TABLE [dbo].[Products]  
    2. (  
    3.       [ProductId] [int] IDENTITY(101,1) NOT NULL,  
    4.       [ProductType] [varchar](50) NULL,  
    5.       [Image] [image] NULL  
    This is the Stored Procedure
    1. USE[RentalShopping]  
    2. GO  
    3. SET ANSI_NULLS ON  
    4. GO  
    5. SET QUOTED_IDENTIFIER ON  
    6. GO  
    7. Create PROCEDURE[dbo]. [sp_GetProductsWithImage]  
    8. as  
    9. begin  
    10. select * from Products  
    11. end 
    If you want to store images into a database, see my article Click Here