JTBarton.com

Skip Navigation Links
Home
Barcode overview
SQL playground
Web Cameras
Personal information
Developer links
About Web Host
Contact Me
Sign In

How to get image data out of a SQL database.

Edit

Prelude, Uploading and saving the data to SQL database

There are many articles on the net about this subject, none of them seemed simple, and some are just plain wrong.  What is simple and explained very well by many of these articles is how to put the image into the database, basically this consists of creating a table with columns for:
  1. An ID - used to lookup image
  2. The image type - very important as <img> control needs to use this to show
  3. The image
For an example of how I have created this data, see the schema for DisplayImage within the DisplayDetails database via the Listing Databases, Tables and Column Schema's in SQL server page.

The best article I found on the web was SQL Junkies - Creating and Saving Images to a Database in ASP.NET, not only did this show how to upload the image, it also gives a detailed description of how to generate thumbnail images.  This code is in VB, but was simple enough to convert to C#, see my code.

An alternative article in C# can be found at:
OdeToCode - Saving Images in a SQL database using ASP.Net

Displaying the Image, specifically multiple image on a single page

Preview Many articles that I ran across whilst attempting to achieve this goal required the image to be copied from the SQL database, then saved to disk before being displayed which is the way in which I first implemented a solution.  Well this is possible, but it has many drawback, namely:
  1. How do you destroy the image afterwards?
  2. If you create your own image numbering systems, what happens when multiple visitors all generate what to view different images, how do you maintain a unique numbering system?
To resolve these problems I asked myself the question "how are the pure ASP folks doing this?", well after lots of web searching the best examples I looked at were provided by:
It took me a while to get to grips with the answer even after reading these examples, but the solution is quite simple:
You have a page where the images are created dynamically pointing to another page which for all intense and purpose seems to be empty but does have some code behind. The trick that I did initially realize is then the code behind uses the Response.ContentType & Response.OutputStream.Write, but instead of writing to the actually page, it writes back to the image control on the initial display page.

Let me explain further by providing a coding example.

To show all thumbnail images in my SQL database, the code is divided into two pages, the display page and the dummy page to return the image.
All code is listed below, the example connection string can be found in my article
Login to ASP.NET 2.0 using the new tools provided and "form authentication"   Please note that this is demonstration code, therefore there is no error handling.  In the production code of this site; error handling is fully implemented, also all the ADO management has been created in a business object using stored procedures.  To run the code shown below click here.

ImageManagementExample_DisplayPage.aspx

<%@ page autoeventwireup="true" codefile="ImageManagementExample_DisplayPage.aspx.cs"
    inherits="ImageManagementExample" language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Image Management Example, show all thumbnails</title>
</head>
<body>
    </FORM><form id="form1" runat="server">
        <div>
            <strong>Demonstrate how to display images from a SQL database<br />
            </strong>
            <hr />
        </div>
    </form>
       </FORM><form id="form1" runat="server">
        <div>
            <strong>Demonstrate how to display images from a SQL database<br />
            </strong>
            <hr />
        </div>
    </form>
    <form 
   </body>
   </html>
Code behind
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.Data.SqlClient;
using System.Web.Configuration;

public partial class ImageManagementExample : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //Generating page for first time
        if (!this.IsCallback)
        { 
            //Show all Images
            ShowThumbNailImages();
        }
    }

    protected void ShowThumbNailImages()
    {
        // Create and open connection
        SqlConnection connection = new 
          SqlConnection(WebConfigurationManager.ConnectionStrings["AspUserMangement"].ConnectionString); DataTable dtImage = new DataTable(); SqlDataAdapter daImage = new SqlDataAdapter(); daImage.SelectCommand = new SqlCommand("SELECT Img_ID FROM DisplayImage", connection); // Get the Image ID's connection.Open(); daImage.Fill(dtImage); connection.Close(); foreach (DataRow drImage in dtImage.Rows) { System.Web.UI.WebControls.Image ImageToAdd = new System.Web.UI.WebControls.Image(); // Create a link to the dummy page which will return the image back into the image control ImageToAdd.ImageUrl =
              "ImageManagementExample_GetThumbNailPage.aspx?ImageId=" + drImage["Img_ID"].ToString(); ImageToAdd.AlternateText = "Image Number: " + drImage["Img_ID"].ToString(); // Add the image to the page this.Controls.Add(ImageToAdd); // Add a few spaces to seperate the images this.Controls.Add(new LiteralControl("  ")); } } }

ImageManagementExample_GetThumbNailPage.aspx

<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="ImageManagementExample_GetThumbNailPage.aspx.cs"
Inherits="ImageManagementExample_GetThumbNailPage" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>John T Barton</title> </head> <body> <form id="form1" runat="server"> <div> </div> </form> </body> </html>
Code behind
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.Web.Configuration;
using System.Data.SqlClient;

public partial class ImageManagementExample_GetThumbNailPage : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {   
        // Get the Image details using a seperate method, 
        // based on the ID passed as a parameter to the web form DataRow dr = GetThumbnailImageData(int.Parse(Request.QueryString["ImageId"])); Response.ContentType = (string)dr["Img_Type"]; // Set the image type Response.OutputStream.Write((byte[])dr["Img_SmallImage"], 0,
          ((byte[])dr["Img_SmallImage"]).Length); Response.End(); // When this method closes,
        // it will return the image from the database into the IMG control } private DataRow GetThumbnailImageData(int imageID) { // Create and open connection SqlConnection connection = new
          SqlConnection(WebConfigurationManager.ConnectionStrings["AspUserMangement"].ConnectionString); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); da.SelectCommand = new
          SqlCommand("SELECT Img_ID, Img_Type, img_SmallImage FROM DisplayImage Where Img_id = @Img_ID",
          connection); SqlParameter Param0 = new SqlParameter("@Img_ID", SqlDbType.Int); Param0.Value = imageID; da.SelectCommand.Parameters.Add(Param0); connection.Open(); // Get the image details, including the image type da.Fill(dt); return dt.Rows[0]; } }