Display Images from a Database in a Silverlight DataGrid Control
We will make use of the Employees table in the Northwind database to display images. Since the focus of this article is to display images from the database in a Silverlight DataGrid, I will be binding the DataGrid with a custom List<> object and will be pulling only the images from the database using an Image Handler. If you want to learn how to pull data from a database in Silverlight, check this article of mine Creating and Consuming a WCF Service in Silverlight. Similarly you can also use ADO.NET Data Services.
Let us get started.
Step 1: Open Visual Studio 2008 > File > New Project > Select the language (C# or VB) > Select ‘Silverlight’ in the Project Types > from the templates, select ‘Silverlight Application’. Type a name ‘DisplayImageInGrid’ and location for the project and click ok. In the 'Add Silverlight Application' box, select 'Add a new ASP.NET Web project to the solution to host Silverlight' > Select ‘ASP.NET Web Application Project' as the Project Type and the Name as ' DisplayImageInGrid.Web' for the application. Press OK.
Note: If you are unable to view the templates, you do not have Microsoft Silverlight Tools for Visual Studio 2008. Check out this link to see how to obtain it.
Step 2: Drag and drop a DataGrid from the toolbox. After setting up a few properties, the markup will look similar to the following:
<UserControl xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" x:Class="DisplayImageInGrid.Page"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
>
<Grid x:Name="LayoutRoot" Background="White" Width="400" Height="600">
<data:DataGrid x:Name="dGrid" AutoGenerateColumns="False"
IsReadOnly="True" CanUserSortColumns="True" Margin="3">
</data:DataGrid>
</Grid>
</UserControl>
Note: I am using a previous version of the DataGrid. As of this writing, the December Release of the Silverlight DataGrid is the latest one and can be downloaded from here.
In the Page.xaml.cs or Page.xaml.vb, add an Employee class, create a List<Employee> object and bind it to the Grid as shown below:
C#
public partial class Page : UserControl
{
public Page()
{
InitializeComponent();
PopulateGrid();
}
private void PopulateGrid()
{
List<Employee> empList = new List<Employee>();
empList.Add(new Employee() { ID = 1, FName = "Mary", MName = "", LName = "Shields", DOB = DateTime.Parse("12/11/1971"), Sex = 'F' });
empList.Add(new Employee() { ID = 2, FName = "John", MName = "Matthew", LName = "Jacobs", DOB = DateTime.Parse("01/17/1961"), Sex = 'M' });
empList.Add(new Employee() { ID = 3, FName = "Amber", MName = "Carl", LName = "Agar", DOB = DateTime.Parse("12/23/1971"), Sex = 'F' });
empList.Add(new Employee() { ID = 4, FName = "Kathy", MName = "", LName = "Berry", DOB = DateTime.Parse("11/15/1976"), Sex = 'F' });
empList.Add(new Employee() { ID = 5, FName = "Lenaldo", MName = "Ashco", LName = "Bilton", DOB = DateTime.Parse("05/11/1978"), Sex = 'M' });
empList.Add(new Employee() { ID = 6, FName = "Karl", MName = "", LName = "Buck", DOB = DateTime.Parse("03/7/1965"), Sex = 'M' });
empList.Add(new Employee() { ID = 7, FName = "Jim", MName = "", LName = "Brown", DOB = DateTime.Parse("09/11/1972"), Sex = 'M' });
empList.Add(new Employee() { ID = 8, FName = "Jane", MName = "G", LName = "Hooks", DOB = DateTime.Parse("12/11/1972"), Sex = 'F' });
empList.Add(new Employee() { ID = 9, FName = "Cindy", MName = "", LName = "", DOB= DateTime.Parse("06/28/1964"), Sex = 'F' });
empList.Add(new Employee() { ID = 10, FName = "Robert", MName = "Preston", LName = "Fox", DOB = DateTime.Parse("01/11/1978"), Sex = 'M' });
dGrid.ItemsSource = empList;
}
}
public class Employee
{
public int ID { get; set; }
public string FName { get; set; }
public string MName { get; set; }
public string LName { get; set; }
public DateTime DOB { get; set; }
public char Sex { get; set; }
}
VB.NET
Partial Public Class Page
Inherits UserControl
Public Sub New()
InitializeComponent()
PopulateGrid()
End Sub
Private Sub PopulateGrid()
Dim empList As New List(Of Employee)()
empList.Add(New Employee() With {.ID = 1, .FName = "Mary", .MName = "", .LName = "Shields", .DOB = DateTime.Parse("12/11/1971"), .Sex = "F"c})
empList.Add(New Employee() With {.ID = 2, .FName = "John", .MName = "Matthew", .LName = "Jacobs", .DOB = DateTime.Parse("01/17/1961"), .Sex = "M"c})
empList.Add(New Employee() With {.ID = 3, .FName = "Amber", .MName = "Carl", .LName = "Agar", .DOB = DateTime.Parse("12/23/1971"), .Sex = "F"c})
empList.Add(New Employee() With {.ID = 4, .FName = "Kathy", .MName = "", .LName = "Berry", .DOB = DateTime.Parse("11/15/1976"), .Sex = "F"c})
empList.Add(New Employee() With {.ID = 5, .FName = "Lenaldo", .MName = "Ashco", .LName = "Bilton", .DOB = DateTime.Parse("05/11/1978"), .Sex = "M"c})
empList.Add(New Employee() With {.ID = 6, .FName = "Karl", .MName = "", .LName = "Buck", .DOB = DateTime.Parse("03/7/1965"), .Sex = "M"c})
empList.Add(New Employee() With {.ID = 7, .FName = "Jim", .MName = "", .LName = "Brown", .DOB = DateTime.Parse("09/11/1972"), .Sex = "M"c})
empList.Add(New Employee() With {.ID = 8, .FName = "Jane", .MName = "G", .LName = "Hooks", .DOB = DateTime.Parse("12/11/1972"), .Sex = "F"c})
empList.Add(New Employee() With {.ID = 9, .FName = "Cindy", .MName = "", .LName = "", .DOB = DateTime.Parse("06/28/1964"), .Sex = "F"c})
empList.Add(New Employee() With {.ID = 10, .FName = "Robert", .MName = "Preston", .LName = "Fox", .DOB = DateTime.Parse("01/11/1978"), .Sex = "M"c})
dGrid.ItemsSource = empList
End Sub
End Class
Public Class Employee
Private privateID As Integer
Public Property ID() As Integer
Get
Return privateID
End Get
Set(ByVal value As Integer)
privateID = value
End Set
End Property
Private privateFName As String
Public Property FName() As String
Get
Return privateFName
End Get
Set(ByVal value As String)
privateFName = value
End Set
End Property
Private privateMName As String
Public Property MName() As String
Get
Return privateMName
End Get
Set(ByVal value As String)
privateMName = value
End Set
End Property
Private privateLName As String
Public Property LName() As String
Get
Return privateLName
End Get
Set(ByVal value As String)
privateLName = value
End Set
End Property
Private privateDOB As DateTime
Public Property DOB() As DateTime
Get
Return privateDOB
End Get
Set(ByVal value As DateTime)
privateDOB = value
End Set
End Property
Private privateSex As Char
Public Property Sex() As Char
Get
Return privateSex
End Get
Set(ByVal value As Char)
privateSex = value
End Set
End Property
End Class
Step 3: Since we are retrieving the images from the database, we would need a connection string to the db. Add the following connection string to your web.config
<connectionStrings>
<add name="NorthwindConnectionString" connectionString="Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
Step 4: We will be using an HttpHandler to pull images from the database. Handlers provide a lot of flexibility while accessing server-side resources. To create an Http handler, right click project DisplayImageInGrid.Web > Add New Item > Generic Handler > DisplayImageHandler.ashx. Add the following code to the handler.
C#
using System;
using System.Web;
using System.Data;
using System.Web.Services;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Imaging;
using System.ComponentModel;
using System.Configuration;
namespace DisplayImageInGrid.Web
{
///<summary>
/// Summary description for $codebehindclassname$
///</summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class DisplayImageHandler : IHttpHandler
{
byte[] empPic = null;
long seq = 0;
public void ProcessRequest(HttpContext context)
{
Int32 empno;
if (context.Request.QueryString["id"] != null)
empno = Convert.ToInt32(context.Request.QueryString["id"]);
else
throw new ArgumentException("No parameter specified");
// Convert Byte[] to Bitmap
Bitmap newBmp = ConvertToBitmap(ShowEmpImage(empno));
if (newBmp != null)
{
newBmp.Save(context.Response.OutputStream, ImageFormat.Jpeg);
newBmp.Dispose();
}
}
// Convert byte array to Bitmap (byte[] to Bitmap)
protected Bitmap ConvertToBitmap(byte[] bmp)
{
if (bmp != null)
{
TypeConverter tc = TypeDescriptor.GetConverter(typeof(Bitmap));
Bitmap b = (Bitmap)tc.ConvertFrom(bmp);
return b;
}
return null;
}
public byte[] ShowEmpImage(int empno)
{
string conn = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
SqlConnection connection = new SqlConnection(conn);
string sql = "SELECT photo FROM Employees WHERE EmployeeID = @ID";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ID", empno);
connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
seq = dr.GetBytes(0, 0, null, 0, int.MaxValue) - 1;
empPic = new byte[seq + 1];
dr.GetBytes(0, 0, empPic, 0, Convert.ToInt32(seq));
connection.Close();
}
return empPic;
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
VB.NET
Imports System
Imports System.Web
Imports System.Data
Imports System.Web.Services
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.ComponentModel
Imports System.Configuration
Namespace DisplayImageInGrid.Web
''' <summary>
''' Summary description for $codebehindclassname$
''' </summary>
<WebService(Namespace := "http://tempuri.org/"), WebServiceBinding(ConformsTo := WsiProfiles.BasicProfile1_1)> _
Public Class DisplayImageHandler
Implements IHttpHandler
Private empPic() As Byte = Nothing
Private seq As Long = 0
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
Dim empno As Int32
If context.Request.QueryString("id") IsNot Nothing Then
empno = Convert.ToInt32(context.Request.QueryString("id"))
Else
Throw New ArgumentException("No parameter specified")
End If
' Convert Byte[] to Bitmap
Dim newBmp As Bitmap = ConvertToBitmap(ShowEmpImage(empno))
If newBmp IsNot Nothing Then
newBmp.Save(context.Response.OutputStream, ImageFormat.Jpeg)
newBmp.Dispose()
End If
End Sub
' Convert byte array to Bitmap (byte[] to Bitmap)
Protected Function ConvertToBitmap(ByVal bmp() As Byte) As Bitmap
If bmp IsNot Nothing Then
Dim tc As TypeConverter = TypeDescriptor.GetConverter(GetType(Bitmap))
Dim b As Bitmap = CType(tc.ConvertFrom(bmp), Bitmap)
Return b
End If
Return Nothing
End Function
Public Function ShowEmpImage(ByVal empno As Integer) As Byte()
Dim conn As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString
Dim connection As New SqlConnection(conn)
Dim sql As String = "SELECT photo FROM Employees WHERE EmployeeID = @ID"
Dim cmd As New SqlCommand(sql, connection)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@ID", empno)
connection.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.Read() Then
seq = dr.GetBytes(0, 0, Nothing, 0, Integer.MaxValue) - 1
empPic = New Byte(seq){}
dr.GetBytes(0, 0, empPic, 0, Convert.ToInt32(seq))
connection.Close()
End If
Return empPic
End Function
Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
End Class
End Namespace
The steps for retrieving images from database, using the handler are as follows:
1. The EmployeeID whose image is to be retrieved, is passed to the handler via query string. We use the Request.QueryString[“id”] to retrieve the EmployeeID(emp_id) from the handler url. The ID is then passed to the ‘ShowEmpImage()’ method where the image is fetched from the database using SqlDataReader and returned in a byte[] object.
2. We pass this byte[] to the ConvertToBitmap() function where we use the TypeConverter class to convert a byte array to bitmap.
3. The last step is to save the image to the page's output stream and indicate the image format as shown here convBmp.Save(context.Response.OutputStream, ImageFormat.Jpeg)
Note: Silverlight does not support .bmp images. For this reason, we set the ImageFormat to Jpeg while saving the image to the OutputStream.
Step 5: With the handler set to retrieve images, let us now see how to display this image in a Silverlight DataGrid. In order to do so, we will use a DataGridTemplateColumn to display the image. We will be using the ColumnCollection to gain control over the DataGrid's column-order, look & feel and data representation. Add the following markup in the Page.xaml
<data:DataGrid LoadingRow="dGrid_LoadingRow" x:Name="dGrid" AutoGenerateColumns="False" IsReadOnly="True" CanUserSortColumns="True" Margin="3">
<data:DataGrid.Columns>
<data:DataGridTextColumn Header="First Name"
Binding="{Binding FName}" />
<data:DataGridTextColumn Header="Middle Name"
Binding="{Binding MName}" />
<data:DataGridTextColumn Header="Last Name"
Binding="{Binding LName}" />
<data:DataGridTextColumn Header="Sex" Width="30"
Binding="{Binding Sex}" />
<data:DataGridTemplateColumn>
<data:DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<Image x:Name="img1" ImageFailed="img1_ImageFailed" Width="100" Height="100" Visibility="Visible"/>
</DataTemplate>
</data:DataGridTemplateColumn.CellTemplate>
</data:DataGridTemplateColumn>
</data:DataGrid.Columns>
</data:DataGrid>
As you might have guessed, we will be displaying the image in the img1 control. We also use the ‘ImageFailed’ event to handle errors while displaying images.
What is left now is to pass the EmployeeID dynamically for each row and display the image for that respective EmployeeID. To achieve this requirement, I have used the DataGrid.LoadingRow event which enables you to make any necessary changes to a new row before it can be used.
In the Page.xaml.cs or vb, add the following code
C#
private void dGrid_LoadingRow(object sender, DataGridRowEventArgs e)
{
Employee emp = e.Row.DataContext as Employee;
FrameworkElement ele = dGrid.Columns[4].GetCellContent(e.Row);
string url = "http://localhost:7642/DisplayImageHandler.ashx?id=" + emp.ID;
Uri imageUri = new Uri(url, UriKind.RelativeOrAbsolute);
(ele as Image).Source = new BitmapImage(imageUri);
}
private void img1_ImageFailed(object sender, ExceptionRoutedEventArgs e)
{
Image img = sender as Image;
// Display an image for error here
}
VB.NET
Private Sub dGrid_LoadingRow(ByVal sender As Object, ByVal e As DataGridRowEventArgs)
Dim emp As Employee = TryCast(e.Row.DataContext, Employee)
Dim ele As FrameworkElement = dGrid.Columns(4).GetCellContent(e.Row)
Dim url As String = "http://localhost:7642/DisplayImageHandler.ashx?id=" & emp.ID
Dim imageUri As New Uri(url, UriKind.RelativeOrAbsolute)
TryCast(ele, Image).Source = New BitmapImage(imageUri)
End Sub
Private Sub img1_ImageFailed(ByVal sender As Object, ByVal e As ExceptionRoutedEventArgs)
Dim img As Image = TryCast(sender, Image)
' Display an image for error here
End Sub
And we are all set!
Note: For simplicity sake, I have not hosted the service on IIS. I am using the server that comes built-in with Visual Studio 2008. The server automatically allocates a port number. If you would like to keep the port number fixed, Right click the project > select the ‘Web’ tab > Select the radiobutton ‘Specific port’ > I am using the port 7642. Build the Solution.
If the protocol, path or the port number of the service differs from that of the Silverlight application, it is a Cross Domain call in Silverlight. You would need a clientaccesspolicy.xml kept in the root of the server where the service is hosted in order to make Cross Domain calls. Read more about it over here
On running the application, we get the image displayed for each EmployeeID passed to the handler. A screenshot of the application with the images displayed in the DataGrid is as shown below:
Remember that this is not an optimized solution when you are loading several rows with images, at a single point of time. So use this approach carefully!
I hope you liked the article and I thank you for viewing it. The source code in C# can be downloaded over here.