Written by:
Jesse Liberty
Microsoft

ADO.NET DataEntities and WCF Feeding a Silverlight DataGrid

Feb 05, 2009

Summary

Key to virtually every business application is the ability to move data from a database to the User Interface. While there are many issues to discuss in great detail, this tutorial will take a “drop line” approach – the shortest possible path using the simplest technology to demonstrate one way of moving data from there to here. This is not the only way, far from it, but it is a very easy way and all the parts fit together very nicely.

Future tutorials will expand upon each area in more depth.

Goals

The proximate goal of this exercise is to extract some data from a SQLServer Database, limited by a where clause and serve that data via a web service using WCF-SOAP. The data will then be consumed by a Silverlight application that will display it in a datagrid control.

The more general goal is to show how these technologies work together to enable data retrieval relatively easily.

Step 1 – the Database

To start, you’ll need the AdventureWorks database, which is now available on Codeplex. Be careful to download the version for 2005 or 2008 depending on which version of SqlServer you are going to use. If you have only SqlExpress and Visual Studio, one way to make a data connection to the file is by opening the Server Explorer Window and right-clicking on Data Connection and choosing “Add Connection.” Then click on Data Source->Change and select SQL Server Database File.

Back at the Add Connection Dialog click on Browse and browse to the Adventureworks.mdf file (typically installed in: C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data). Test your connection and fuss¹ as needed

Step 2 – Create a new Project

Create a new Silverlight project, named EntitiesWebSvcGrid², and accept the default of creating an ASP.NET Web Application,

Figure 9-1. Web Project (Click to view full-size image)

Visual Studio will create one solution with two projects:

EntitiesSvcGrid (the Silverlight project)
EntitiesSvcGrid.Web (the Web project)

The second project will house both the ADO.NET Entity model and the web service, while the first will hold the Silverlight DataGrid.

Step 3 – Add the ADO.NET Entity Data Model

To create the Data Model, right click on the web project and select Add New… Choose Ado.Net Entity Data Model, name it EmployeeDataModel.edmx and click the Add button.

In the next dialog click on the Generate from Database button – this will, ultimately, cause the data model to be created for you, a wonderful thing saving you endless boring work. The next dialog will ask you to choose the data connection you’d like to use,

Figure 9-2. Choose Data Source (Click to view full-size image)

and once the connection is made and the data structure retrieved, you’ll be asked to select which tables, views and stored procedures you’d like included in your model. For this exercise, to keep things very simple, we’ll select just one table: Employee,

Figure 9-3. Choose Data Objects (Click to view full-size image)

When you click Finish, your entity data model is generated. Spend a couple minutes looking at the result, it is quite cool. Note there may be a window hiding at the bottom of Visual Studio named Mapping Details. Be sure to open this and give it about half the screen. This is worth examining as well. Take your time, I’ll wait here.

Figure 9-4. Mapping Details (Click to view full-size image)

Step 4 Creating the Web Service

With the Entity Data Model in place we’re ready to create a web service to make the data available to the Silverlight application that will be running on the client.

We have a number of choices on how to do this, and in future tutorials I’ll explore a few (certainly a Restful alternative) but for now, taking the most direct approach, we’ll use the WCF/SOAP model by way of the Silverlight-aware template.

Once again right-click on the web project and chose Add… New Item, this time selecting Silverlight-Enabled WCF Service. Name the new service EmployeeWebService and click OK.

Open EmployeeWebService.svc.vb where you will find a stub method, DoWork which you will modify to fetch the list of employees.

<ServiceContract(Namespace:="")> _
<AspNetCompatibilityRequirements( _
  RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)> _
Public Class EmployeeWebService
 
   <OperationContract()> _
   Public Function GetEmployees() As List(Of Employee)
      Dim de As New AdventureWorks_DataEntities()
      Return de.Employee.Where(Function(emp) emp.SalariedFlag = True).ToList()
   End Function
 
End Class

This code warrants a bit of examination. We begin with attributes that set this up for use as a WCF service contract and the function (GetEmployees) as an Operation Contract.

GetEmployees is defined as taking no parameters and returning a list of Employeee objects.

The logic is to obtain an instance of the DataEntities class we created earlier, and to use that instance to find all the employees whose Salaried Flag is set to true.

The constraint of finding only Salaried employees is enforced by calling the Where() method on the DataEntity and passing in a lambda expression (discussed in the next section).

What you get back is a result set, which we cast to a List of Employee objects which is what will be needed by the DataGrid (which needs a collection that implements IEnumerable).

Using A Lambda Expression in a Where Clause

The statement we use to restrict which employees are returned is the lambda expression

emp => emp.SalariedFlag == true

There are a number of ways to interpret this statement, depending on how precise you choose to be and how deeply you wish to dive into the corner cases (for an example of pushing on these issues, see this blog entry).

One straightforward way to read this is “let emp be all the employee objects whose SalariedFlag property evaluates to true.”

Binding to the DataGrid

To keep the UI as simple as possible, we’ll just drag a DataGrid from the toolbox onto Page.xaml as the entire user interface for this application.

Dragging, rather than adding it by hand, has the advantage that Visual Studio will add the necessary namespace.

xmlns:data="clr-namespace:System.Windows.Controls;assembly = System.Windows.Controls.Data"

Add a name for your grid (x:Name=”dg”) and remove the height and width of the surrounding UserControl. Here is the complete source for Page.xaml

<UserControl 
    xmlns:data="clr-namespace:System.Windows.Controls;assembly = System.Windows.Controls.Data"  
    x:Class="EntitiesSvcGrid.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">   
        <data:DataGrid x:Name="dg" />
   </Grid>
</UserControl>

Binding the data to the DataGrid

The final step is in Page.xaml.vb where you need to bind the data from the web service to the data grid itself.

The first step is to add a reference to the web service to this project. To do so, right click on ServiceReferences and in the Add Service Reference dialog click on Discover. The Web Service you created earlier (EmployeeWebService) will appear in the list. Click on it and then rename the namespace to EmployeeWebService.

Figure 9-5. Add Service Reference (Click to view full-size image)

Click OK to add the service reference, and open Page.xaml.vb

I tend to set up event handlers in the Loaded event rather than in the constructor, though this may be a vestigial habit,

Imports Microsoft.VisualBasic
Imports System
Imports System.Windows
Imports System.Windows.Controls
 
Namespace EntitiesSvcGrid
   Partial Public Class Page
       Inherits UserControl
      Public Sub New()
           InitializeComponent()
           AddHandler Loaded, AddressOf Page_Loaded
      End Sub

Our first job in the Loaded event handler is to create an instance of the web service client and then use that client to call the GetEmployees method on the web service.

Instantiating the client is straight-forward,

Private Sub Page_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
 
  Dim ws As EmployeeWebService.EmployeeWebServiceClient = _
    New EmployeeWebService.EmployeeWebServiceClient()

Because Silverlight applications run in the browser, however, we cannot make direct method calls (you must not block the browser) but rather must make asynchronous calls. In fact, Intellisense not only offers an async alternative, it does not even offer the synchronous method you created!

Asynchronous Calls

Figure 9-6. Asynchronous Calls

Creating the Callback

When the call to GetEmployeeAsync completes, the event GetEmployeesCompleted will be raised, providing us the opportunity to retrieve the Employee list we requested from the web service.

Thus, your entire Page.xaml.vb file looks like this,

Imports Microsoft.VisualBasic
Imports System
Imports System.Windows
Imports System.Windows.Controls
 
Namespace EntitiesSvcGrid
   Partial Public Class Page
       Inherits UserControl
      Public Sub New()
           InitializeComponent()
           AddHandler Loaded, AddressOf Page_Loaded
      End Sub
 
      Private Sub Page_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
 
         Dim ws As EmployeeWebService.EmployeeWebServiceClient = _
           New EmployeeWebService.EmployeeWebServiceClient()
         AddHandler ws.GetEmployeesCompleted, AddressOf ws_GetEmployeesCompleted
         ws.GetEmployeesAsync()
      End Sub
 
      Private Sub ws_GetEmployeesCompleted(ByVal sender As Object, _
                 ByVal e As EmployeeWebService.GetEmployeesCompletedEventArgs)
         dg.ItemsSource = e.Result
      End Sub
   End Class
End Namespace

The work of the event handler is to bind the Result (obtained through the GetEmployeeCompletedEventArgs parameter) which is of type observableCollection, to the ItemSource property of the data grid.

Figure 9-7. Item Source (Click to view full-size image)

From Tinker to Evers To Chance…³

When you compile and run this solution data is extracted from the database, constrained by the lambda expression; made available through the web service and bound to the Silverlight DataGrid.

Figure 9-8. DataGrid (Click to view full-size image)

While the appearance is a bit Spartan (we’ve taken no steps to limit or name the columns or to improve the UI with styles, much less templates) all the core functionality is intact, including sorting, as we’ve done with the HireDate column.

[1] Fuss, verb: to hack, debug or twiddle code.

[2] You are of course free to name your projects and objects anything you like, but choosing the names used here makes it easier to follow along.

[3] This expression was used in my home, when I was growing up, to describe a series of predictable but discrete events: not unlike the progression of data from the database, to the web service to the Silverlight datagrid. It refers to the famous turn of the century Chicago Cubs double play combination of Joe Tinker at Short, Johnny Evers at second and Frank Chance at first base which was memorialized in a poem by Franklin Pierce Adams, who once said "I find that a great part of the information I have was acquired by looking up something and finding something else on the way." The poem was first published in the New York Evening Mail, 45 years (to the day) prior to my birth and can be found here.