Use SQL Server Images in ASP.NET Pages
Level: Intermediate
Technology Toolbox: C#, ASP.NET
Displaying images in ASP.NET pages is straightforward—just use the <img> tag. However, you must provide a filename such as JPEG, GIF, or BMP in order to use the <img> tag. What if your images are stored in a SQL Server table's blob field?
When the Web server processes an ASP or ASP.NET page, it processes any image file the page references as a separate download. In other words, the browser gets the image bits from a distinct HTTP stream marked with a different content-type. When the <img> points at a graphic file, the Web server sets the content-type automatically and sends the bits of the file. You aren't involved in any way with this process. However, if you make the <img> src property point to an ASP or ASPX page, then you're responsible for the output being sent to the browser fitting in the image placeholder.
Consider this ASPX fragment:
<img
src=<%# "image.aspx?id=" +
DataBinder.Eval
(Container.DataItem,
"employeeid") %>
/>
The image.aspx file renders the image by creating and outputting a graphic stream. The image.aspx page can use any external information you pass through URL parameters to acquire the image bits physically. Typically, the URL image.aspx?id=1 would execute a query against a database using the id value as a primary key.
In .NET, you can retrieve a blob field's content as a scalar value and store it in an array of bytes temporarily:
byte [] img = (byte[])
command.ExecuteScalar();
Once you get the bits, you can send them to the page's output stream as a graphic object. First off, set the page's content type to the value that matches the actual image format. For example, do this for GIF images:
Response.ContentType = "image/gif";
To output the image, create a Bitmap object from the bytes you hold. You can't just pass an array of bytes to any constructor of the Bitmap class. You can work around this by defining an intermediate MemoryStream object:
MemoryStream ms = new MemoryStream();
ms.Write(img, 0, img.Length);
Bitmap bmp = null;
bmp = new Bitmap(ms);
At this point, you're almost done. You need to convert the format-agnostic Bitmap object into the expected image format and serialize the resulting object to the page's output stream:
bmp.Save(Response.OutputStream,
ImageFormat.Gif);
This code shows the full source for the image.aspx file that retrieves the picture of the Northwind employee whose employee ID matches the URL argument:
<%@ Page Language="C#" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace=
"System.Data.SqlClient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace=
"System.Drawing.Imaging" %>
<%@ Import Namespace="System.Drawing" %>
<html>
<script runat="server">
private void Page_Load(object sender,
System.EventArgs e)
{
SqlConnection cn;
cn = new SqlConnection
("DATABASE=northwind;SERVER
=localhost;UID=sa;");
String cmdText = "SELECT photo FROM
Employees WHERE employeeid=" +
Request["id"].ToString();
SqlCommand cmd = new SqlCommand(cmdText, cn);
MemoryStream ms = new
MemoryStream();
// 78 is the size of the OLE header
// for Northwind images.
// There's no header in PUBS as PUBS
// just contains the raw image bits.
int offset = 78;
cn.Open();
byte [] img = (byte[])
cmd.ExecuteScalar();
ms.Write(img, offset,
img.Length-offset);
cn.Close();
Bitmap bmp = null;
bmp = new Bitmap(ms);
Response.ContentType = "image/gif";
bmp.Save(Response.OutputStream,
ImageFormat.Gif);
ms.Close();
}
</script>
</html>
Notice a subtlety in this code that is particular of the Northwind database but has no relevance in general. The original Access database was converted into SQL Server's Northwind database, so the image field called Photo doesn't contain a true GIF file; instead it contains the OLE object that Access builds to wrap any image. As a result, the stream of bytes you read from the field is prefixed with a header you must strip off to get the bits of the image. Such a header is variable-length and also depends on the length of the originally imported file's name. For Northwind, the length of this offset is 78 bytes.
If you want to adapt the image.aspx to work with other databases (such as Pubs), set the offset variable to 0.
About the Author
Dino Esposito is a trainer and consultant for Wintellect, where he manages the ADO.NET class. Dino is a columnist for MSDN Magazine and MSDN Voices. The author of Building Web Solutions with ASP.NET and ADO.NET (Microsoft Press), Dino is also the cofounder of www.vb2themax.com. Reach him at .
Back to top
|