Display Images from a Database in a Silverlight DataGrid Control

Posted by: Suprotim Agarwal , on 1/29/2009, in Category Silverlight 2, 3, 4 and 5
Views: 35807
Abstract: In one of our previous article, we saw how to Display Images From Database In Silverlight 2. In this article, we will see how to pull images from the database and display them in a Silverlight DataGrid.
Display Images from a Database in a Silverlight DataGrid Control
 
In one of our previous article, we saw how to Display Images From Database In Silverlight 2. In this article, we will see how to pull images from the database and display them in a Silverlight DataGrid.
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:
Images DataGrid
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.
If you liked the article,  Subscribe to the RSS Feed or Subscribe Via Email 
 

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigious Microsoft MVP award for Sixteen consecutive years. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that offers Digital Marketing and Branding services to businesses, both in a start-up and enterprise environment.

Get in touch with him on Twitter @suprotimagarwal or at LinkedIn



Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Pasaban on Tuesday, August 11, 2009 2:09 AM
Awesome post. Exactly what I was looking for. Just one concern. I am not very familier with HTTP Handlers, is it a good preactice to pass informaiton over query string? Is there any other way? Is it secured?
Comment posted by Suprotim Agarwal on Saturday, August 15, 2009 2:45 AM
Pasaban: No not always. Check this link - http://nayyeri.net/blog/how-to-encrypt-query-string-parameters-in-asp.net/
Comment posted by xser on Wednesday, April 28, 2010 7:43 AM
Thanks this code wonderful.
Comment posted by Jaymz on Thursday, July 7, 2011 1:44 PM
Nice code, and it does work.  I am using this approach because quite frankly I do not know an "optimized" approach for loading images in this fashion.  Do you have any articles or further advice on this?