Displaying Images from SQL Server database in ASP.NET DataGrid

Introduction

ASP.NET DataGrid web control can be bound quickly with any database table like SQL server. For most of the data types all you need to do is to add a bound column and set its datafield property to the column name from the table. However, displaying image data type i.e. binary data is not that easy. It requires some kind of extra coding from developer's side to make that work. In this article we will see how to do just that. SQL Server tables

The articles make use of a SQL server database table called images. The structure of which can be as follows:

Help with Code Tags

(Toggle Plain Text)

CREATE TABLE [dbo].[IMAGES] (
	[imgid] [int] IDENTITY (1, 1) NOT NULL ,
	[imgdata] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[IMAGES] ( [imgid] [int] IDENTITY (1, 1) NOT NULL , [imgdata] [image] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Creating a web form with DataGrid

In order to proceed with the example create a new ASP.NET web form say webform1.aspx and add a DataGrid web control to it. Add two columns to it :

• one bound column with datafield set to imgid and
• another templated column with an image web control.

In the Page_Load event of the web form bind the datagrid with the database.

Help with Code Tags

(Toggle Plain Text)

  If Not Page.IsPostBack Then
	BindGrid()
End If
  Public Sub BindGrid()
	Dim connstr As String =
	"Integrated Security=SSPI;
	Initial Catalog=Northwind;
	Data Source=SERVER\netsdk"
	Dim cnn As New SqlConnection(connstr)
	Dim da As New SqlDataAdapter("select * from images", cnn)
	Dim ds As New DataSet()
	da.Fill(ds, "images")
	DataGrid1.DataSource = ds
	DataGrid1.DataBind()
End Sub

If Not Page.IsPostBack Then BindGrid() End If Public Sub BindGrid() Dim connstr As String = "Integrated Security=SSPI; Initial Catalog=Northwind; Data Source=SERVER\netsdk" Dim cnn As New SqlConnection(connstr) Dim da As New SqlDataAdapter("select * from images", cnn) Dim ds As New DataSet() da.Fill(ds, "images") DataGrid1.DataSource = ds DataGrid1.DataBind() End Sub

Now write the ItemDataBound event for the datagrid as follows:

Help with Code Tags

(Toggle Plain Text)

  Private Sub DataGrid1_ItemDataBound
(ByVal sender As Object,
ByVal e As DataGridItemEventArgs)
Handles DataGrid1.ItemDataBound

	Dim img As System.Web.UI.WebControls.Image
	If e.Item.ItemType = ListItemType.AlternatingItem Or
	   e.Item.ItemType = ListItemType.Item Then
		img = CType(e.Item.Cells(1).Controls(1),
			  System.Web.UI.WebControls.Image)
		img.ImageUrl = 
		"webform2.aspx?id=" & e.Item.Cells(0).Text
	End If
End Sub

Private Sub DataGrid1_ItemDataBound (ByVal sender As Object, ByVal e As DataGridItemEventArgs) Handles DataGrid1.ItemDataBound Dim img As System.Web.UI.WebControls.Image If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then img = CType(e.Item.Cells(1).Controls(1), System.Web.UI.WebControls.Image) img.ImageUrl = "webform2.aspx?id=" & e.Item.Cells(0).Text End If End Sub

This event gets fired for all the rows from the DataGrid just before data binding action. All the code from the event handler should be familiar to you except the line where we are setting ImageUrl property of the image web control. Generally this property is set to a valid image file (gif/jpg etc.) but here, we are setting it to another web form. What does that means? This means that the web form (webform2.aspx) is returning the image data based on some processing logic. That is why we are also passing image id to the web form via query string. Now, let us examine the code from the webform2.aspx. Creating web form that returns image data

The webform2.aspx simply contains following code in the Page_Load event handler.

Help with Code Tags

(Toggle Plain Text)

Dim connstr As String = "Integrated Security=SSPI;
Initial Catalog=Northwind;Data Source=SERVER\netsdk"
Dim cnn As New SqlConnection(connstr)
Dim cmd As New SqlCommand
("select * from images where id=" 
& Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
dr.Read()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)

Dim connstr As String = "Integrated Security=SSPI; Initial Catalog=Northwind;Data Source=SERVER\netsdk" Dim cnn As New SqlConnection(connstr) Dim cmd As New SqlCommand ("select * from images where id=" & Request.QueryString("id"), cnn) cnn.Open() Dim dr As SqlDataReader = cmd.ExecuteReader() dr.Read() Dim bindata() As Byte = dr.GetValue(1) Response.BinaryWrite(bindata)

Here, we are retrieving the image based on the query string passed. Note how we have used Response.BinaryWrite to emit the binary image data. Now, run the application and you should get all the images displayed in columns of the DataGrid. Summary

In this article we saw how to retrieve image data type from SQL server database and display the image in DataGrid web control. We saw here that ImageUrl of the image web control can be set to another web form that emits binary image data via Response.BinaryWrite method.