ASPdot.NET: Retreiving Images from a Database

» Home

Retrieving Images from a Database ( C# ) – Part II

by Dave of

Intro

In part I of this series, Uploading Images to a Database, we saw how to store images and other binary data in a database. In Part II, Retrieving Images from a Database, we are going to stream binary data out of a database and to a browser client. Again, if you want to skip this article, you can view the complete code here.

Refresher – Table Structure

Lets quickly refresh ourselves with the table structure we are using for storing images.

CREATE TABLE [dbo].[image] (
[img_pk] [int] IDENTITY (1, 1) NOT NULL ,
[img_name] [varchar] (50) NULL ,
[img_data] [image] NULL ,
[img_contenttype] [varchar] (50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[image] WITH NOCHECK ADD
CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED
(
[img_pk]
) ON [PRIMARY]
GO

We have 4 columns in our sql server:
img_pk – our Identity primary key.
img_name – the friendly name we are using for our image
img_data – the binary data field we are storing our image in.
img_contenttype – the Mime type of our image, for example: image/gif.

Getting the data

Now that we know the structure of our image table, lets begin by getting the data out of the database.

//get the image id from the url
string ImageId = Request.QueryString[“img”];

//build our query statement
string sqlText = “SELECT img_data, img_contenttype FROM Image WHERE img_pk = ” + ImageId;

SqlConnection connection = new SqlConnection( ConfigurationSettings.AppSettings[“DSN”].ToString() );
SqlCommand command = new SqlCommand( sqlText, connection);

//open the database and get a datareader
connection.Open();
SqlDataReader dr = command.ExecuteReader();

We start by finding the "ID" of the image we want. The "ID" is really the img_pk field, but we are hiding the real name from our users. Once we have the primary key of the image we want, we write our select statement, sqlText. An example URL would be http://localhost/viewimage.aspx?img=1. Once we have the image ID (or img_pk) we build our Sql Statement and store it in a local variable called sqlText. We define and open a connection to the database using a SqlConnection and use a SqlCommand to execute our sqlText. By executing our SqlCommand, we return a SqlDataReader. We chose a datareader, because of it’s speed and low overhead.

The Rest is a Piece of Cake

if ( dr.Read()) //yup we found our image
{
Response.ContentType = dr[“img_contenttype”].ToString();
Response.BinaryWrite( (byte[]) dr[“img_data”] );
}
connection.Close();

We check to see if any records were returned, by if (dr.Read() ). We tell the browser what content type it will be receiving from dr["img_contenttype"].ToString() and we output the binary image data by performing a Response.BinaryWrite, and casting our binary data to a byte array:

Response.BinaryWrite( (byte[]) dr["img_data"] );

Lets Test

So how do we link to our new image source. We put the path of our .aspx page as the “src” attribute of the image. Here is a sample html file that demonstrates this.

<html>
<head>
</head>
<title>View Image from the database</title>
<body bgcolor=#FFFFFF>

Here is the image, viewed from a database:
<BR>
<img src=”viewimage.aspx?img=1″ border=1>

</body>
</html>

And here is a screen shot of our results:

Conclusion

Retrieving binary data out of a database isn’t really that difficult. We connect to our database, retrieve the content-type, and output the binary data using Response.BinaryWrite().

Cheers!
dave
www.123aspx.com

All The code

Image Database Sql

TestImage.aspx

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.0 Transitional//EN” >
<html>
<head>
</head>
<title>View Image from the database</title>
<body bgcolor=#FFFFFF>

Here is the image, viewed from a database:
<BR>
<img src=”viewimage.aspx?img=1″ border=1>

</body>
</html>

ViewImage.aspx

<%@ Page language=”c#” Src=”ViewImage.aspx.cs” Inherits=”DBImages.ViewImage” %>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.0 Transitional//EN” >

<html>
<head>
</head>
<body MS_POSITIONING=”GridLayout”>

<form id=”ViewImage” method=”post” runat=”server”>

</form>

</body>
</html>

ViewImage.aspx.cs (CodeBehind file)

using System;
using System.Collections;
using System.Configuration;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace DBImages
{
/// <summary>
/// Summary description for ViewImage.
/// </summary>
public class ViewImage : System.Web.UI.Page
{
public ViewImage() { }

private void Page_Load(object sender, System.EventArgs e)
{
//get the image id from the url
string ImageId = Request.QueryString[“img”];

//build our query statement
string sqlText = “SELECT img_data, img_contenttype FROM Image WHERE img_pk = ” + ImageId;

SqlConnection connection = new SqlConnection( ConfigurationSettings.AppSettings[“DSN”].ToString() );
SqlCommand command = new SqlCommand( sqlText, connection);

//open the database and get a datareader
connection.Open();
SqlDataReader dr = command.ExecuteReader();
if ( dr.Read()) //yup we found our image
{
Response.ContentType = dr[“img_contenttype”].ToString();
Response.BinaryWrite( (byte[]) dr[“img_data”] );
}
connection.Close();

}
}
}