Displaying An Image Stored In A SQL Server Database On An ASP.NET Page Using VB.NET

Recently asked on Yahoo! Answers:

How do I read and write binary image data from SQL server?
I’m making a website where visitors can upload their images, I’ve done the uploading code and it (probably) works fine because I can see the data is recorded on the database.

The problem is when I want to test it (display the image on the website) theoretically I have to read the data from the SQL server and write it to the web. I’ve done several ways and tests but all of them failed to display the image.

Anybody knows how to do this? I’m using visual web developer 2008 and VB.NET as programming language

Note: no “wizards” please, I want it to be implemented using fully coded like I did with the uploading code

There are other examples on the Web on how to do this, but most are either written in C#, or are very specific to a given set of circumstances, so I thought I would explore this subject for Visual Basic .NET, and in the process try to address a few different ways to display an image.

The executive summary: Displaying an image that’s stored in a database requires you to make a “helper page.” And that’s what we’re about to make.

An Aside On The Many Ways This Cat Can Be Skinned

In most object-oriented languages, there are a lot of different ways to solve a given problem, and that’s especially true of displaying images stored in a database.

In my opinion — and it is only that — the best way to display database-stored images is via an HTTP handler.

Basically, an HTTP handler allows you to send certain requests directly to some code, without incurring the overhead and other issues of a Web page. The code does what you want and responds with the content you indicate.

You’ve probably seen HTTP handlers on other Web sites, such as eBay: A page that ends in .dll, .do or the like is usually an HTTP handler.

The benefits of this approach are manifold: HTTP handlers tend to be resource-efficient; it’s easier to secure a handler from bad input / leeching / attacks; and in very large applications, it’s often easier to manage resource allocation with a handler.

Handlers require tinkering directly with the Web server’s settings, they are a bit complex to accomplish if you aren’t used to programming, and if you monkey them up, you can do real harm to your Web server.

So we’ll keep it simple and use ASP.NET pages only to handle this task. If you’d like to investigate using an HTTP handler, check out this excellent article on ASPAlliance.com.

The ASP.NET Helper Page: dbImage.aspx

We will call our image display page dbImage.aspx. It is going to act like a real image by basically lying to the Web server about what it is and what it contains.

Specifically, the page is going to:

  • connect to the database;
  • create a DataReader and populate it from a query that gets the relevant image;
  • read a byte stream from that query;
  • declare its content type to be that of an image; and
  • spit out the image as a byte stream, the same way a Web server sends an image.

Some notes about this page’s code and needs:

First, some assumptions about the database table holding the images. It will be named tbl_images. It will contain three columns:

Column Name Column Type Description
image_id int unique record identifier, primary key
image_type varchar content type of the image
image_data image / blob binary image data

This page will determine the proper image to display via a querystring variable named ID. That variable will key to the image_id field.

To ensure the query string variable contains a reasonable number, I am going to use a regular expression. So you will need to make sure you have imported the System.Text.RegularExpressions namespace on this page or in your web.config file.

I am going to use the SqlClient library to connect, and send commands, to my SQL Server database, so make sure you import the System.Data and System.Data.SqlClient libraries, too.

Finally, although you should use stored procedures and parameters any time you can, I will be using a SQL query to get the data in this example.

Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)

	'construct regular expression to establish
	'the range of the ID variable
	Dim objExpression As New RegEx("^[0-9]{1,10}$")

	'ensure querystring is present and in range
	If Not String.IsNullOrEmpty(Request.QueryString("ID")) And objExpression.IsMatch(Request.QueryString("ID")) Then

		'create SQL statement
		Dim strSQL As String = "SELECT * FROM tbl_images WHERE image_id = " & Request.QueryString("ID")

		'database variables
		Dim objConn As New SqlConnection("your connection string")
		Dim objCmd As New SqlCommand(strSQL, objConn)
		objCmd.CommandType = CommandType.Text


		Try
			'open connection, populate reader
			objConn.Open()
			Dim objReader As SqlDataReader = objCmd.ExecuteReader()

			'ensure reader has at least one result
			If objReader.HasRows() Then

				'get data
				objReader.Read()
				'put image into byte array
				Dim byteArray As Byte() = objReader("image_date")

				'send content type
				Response.ContentType = objReader("image_type")

				'if you have a fixed content type [e.g., image/jpeg]
				'comment out the Response.ContentType declaration above
				'uncomment the declaration below
				'and change the value to the appropriate type
				'Response.ContentType = "image/jpeg"


				'send image
				Response.BinaryWrite(byteArray)

			Else

				'report no results for query
				Throw New WebException("No matching image for ID provided")

			End If

			'clean up our DB objects
			objReader.Close()
			objConn.Close()
			objCmd.Dispose()
			objConn.Dispose()

		Catch Ex As Exception

			'if there's a DB error, report it
			Response.Write(Ex.Message)

		End Try

	Else

		'bad querystring value
		Throw New WebException("You have failed to provide an image ID value that is in range")

	End If

End Sub

Some notes about the code above:

  • This ASP.NET page need only contain this code. However, you can place it on a codebehind page with a blank ASP.NET page.
  • If you use this code on a page that isn’t blank, keep in mind that the page won’t render; it will simply output the database image or an error message.
  • The first step in our subroutine is to create a regular expression: ^[0-9]{1,10}$. What that regular expression basically says is, “make sure the ID variable contains only integers; it must contain at least one integer, and it can be as long as 10 integers.” This should be fine for just about any database that has a numeric primary key.
  • The code above assumes your database has a column that contains the content type of the image — that is, whether the image is a JPEG, GIF or PNG.
    • If you are going to store content types for your images in the database table, the image_type column must contain the proper text value for the given image’s content type. In other words, if image_data contains a JPG, image_type must be image/jpeg. If the image is a GIF, image_type must be image/gif. For a PNG, image_type would be image/png.
    • For a comprehensive list of content type values, check out w3schools.com.
    • If all the images in your database are the same content type — say, all JPEGs or all GIFs — just change the content type to the fixed value, as described in the commented code.
  • Although this code throws exceptions whenever there’s an error, you may not see those exceptions on the pages that use this script to create images. In other words, this page will show the errors if you call it directly in your Web browser, but the pages that call it may not display the error messages.

Using dbImage.aspx

There are a number of ways to use the code above to render the image. I’ll discuss three:

  • as the src attribute for an img tag;
  • as the ImageUrl attribute for an ASP.NET Image control; and
  • inside a GridView control.

In each case, we need to pass the ID value from the page that will show the image to dbImage.aspx.

Example 1: As An img Tag’s src Attribute

If you have some static HTML, or a page that always calls the same image from the database, the easiest thing to do is simply create a standard img tag and make dbImage.aspx the src attribute.

For example, if we are always going to show the image with the image_id value of 43, we could do this:

<img src="dbImage.aspx?ID=43" alt="" />

Of course, this pretty much defeats the purpose of saving images to a database; chances are they’re in a database because we want to render a given image under a given case.

Example 2: Using An ASP.NET Image Control

Let’s suppose you want to call an image based on some code.

For example, maybe you have 10 images in your database, with image_id values from 1 to 10, and you want to randomly display one of them on your page.

The easiest way to handle that is to first place an ASP.NET Image control where you want the image to show up:

<asp:Image runat="server" ID="imgRandom" />

Then, we create a subroutine that generates a random number, assigns that random number as the value of the querystring ID value for dbImage.aspx, and sets the ImageUrl property of the Image control to be dbImage.aspx with its querystring value.

We’ll call that subroutine GetRandomImage(). It will be invoked by calling it in the Page_Load subroutine of the page where the Image control is located.

Sub GetRandomImage()

	'create random number
	Dim rndNumber As New Random()

	'create querystring value
	Dim strID As String = rndNumber.Next(1, 10).ToString()
	strID = "dbImage.aspx?ID=" & strID

	'assign ImageUrl for image control
	'to be URL of dbImage.aspx with ID generated above
	imgRandom.ImageUrl = strID

End Sub

Note that the subroutine above assumes dbImage.aspx is located in the same directory as the page containing the Image control.

Example 3: Inside A GridView Control

Even more useful is the ability to dynamically display an image within a GridView or similar data control. For example, maybe your database table actually contains a number of catalog products, and you want to show the images along with the name of the product and its cost.

For simplicity’s sake, I am going to assume the product name, cost and image data are all in the same table. (If there is extensive interest in how one goes about making this work for multiple keyed tables, I’ll consider a follow-up entry.)

The names of the two new columns will be product_name (a varchar) and product_cost (a single). The other columns previously assumed will be the same, as well as the previous table name.

Showing the image is as simple as adding an ImageField to the GridView.

First, I’m going to tie my GridView to a SqlDataSource:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
SelectCommand="SELECT [image_id], [product_name], [product_cost] FROM [tbl_images]"
ConnectionString="<%$ ConnectionStrings:YourConnectionString %>" />

On to the GridView and its ImageField control.

Thanks to the DataImageUrlField and DataImageUrlFormatString properties of the ImageField control, we can easily specify the ID value for dbImage.aspx to be image_id, and we can easily add dbImage.aspx’s complete URL as the source for our image.

<asp:GridView runat="server" ID="GridView1" DataSourceID="SqlDataSource1" AutoGenerateColumns="False">
	<columns>
		<asp:BoundField HeaderText="ID" DataField="image_id" />
		<asp:BoundField HeaderText="Product Name" DataField="product_name" />
		<asp:BoundField HeaderText="Unit Price" DataField="product_cost" />
		<asp:ImageField HeaderText="Image" DataImageUrlField="image_id" DataImageUrlFormatString="dbImage.aspx?ID={0}" DataAlternateTextField="product_name" />
	</columns>
</asp:GridView>

Notice that as a final tweak, I set the name of the product to be the alt value for the image.

And that’s it for now. I don’t have a working demo, but you can simply copy the code above and apply it to your own pages.

I distribute code under the GNU GPL. See Copyright & Attribution for details.

2 thoughts on “Displaying An Image Stored In A SQL Server Database On An ASP.NET Page Using VB.NET

  1. human_magician

    Hi, I asked that question you answered and I have no idea you’d make a blog out of it:D

    well, I’ve tried your code but it didn’t work I don’t know why. But of course I didn’t use your code directly, I changed it a bit so it would fit with the database.

    my database primary key is not int,it’s varchar called Judul (Judul means title in my language).I make it so people only need to write the title of the image to show it,but it didn’t show up. :(

    I tried the first and second method to display the image (img tag and the image control).

    here’s the GetRandomImage() procedure that I’ve changed (it’s only a little though)

    Sub GetRandomImage()

    Dim strJudul As String = “IPhone” ‘ of course IPhone is just an example, I’ll
    ‘ modify it in the future so people only
    ‘ need to input their title

    strJudul = “dbImage.aspx?judul=” & strJudul

    ‘assign ImageUrl for image control
    ‘to be URL of dbImage.aspx with ID generated above
    Image1.ImageUrl = strJudul

    End Sub

    the dbImage.aspx.vb code is just like what you wrote up there,just a little changes to fit the database.

    I don’t know why this fails,but for the time being I’ll code the other parts of the website first before taking care of the image retriever again.

    anyway,thanks for helping and making a blog out of my question :D

  2. Doug Vanderweide

    I can’t speak to problems you are having unless you better describe your problem than it “fails.” I also cannot further support you for free, sorry.

    I can advise this:

    • You should not use a varchar column as a primary key, especially if the key is likely to be repeated. This is highly likely if you’re going to let users generate values that will serve as keys, and almost certain if you are using the names of uploaded files as key values. Just add an int column to your table and assign it as the identity column for the table, then use its numbers as your primary keys.
    • Your GetRandomImage() subroutine will work if you:
      1. Invoke it in the Page_Load() subroutine; and
      2. Have an image properly stored in an image / blob column, and that image has IPhone as its key. Keep in mind that IPhone will be case-sensitive; if your key column’s value is actually Iphone or iPhone or iphone, the dbImage script will throw an exception.
    • If the previous proves not to be the problem, almost certainly you have wrongly altered the dbImage.aspx page. Simply call that page with a key value you know to be good. If you see a picture, then the problem is how you are calling it. If you see an exception, then you have incorrectly modified the dbImage.aspx page.

Leave a Reply