Retrieving Multiple Records from SQL Server using WCF and Silverlight

Consider the following very specific scenario. You have a SQL Server 2008 database and want to retrieve multiple data records from the database using WCF 4 hosted in IIS and display the results in a Silverlight 4 application hosted in a Web page. And you are using Visual Studio 2010 with C# and .NET Framework 4. Let me walk through one way to do this. If you look at the image below you can see the goal where I request employee ID, last name, and age of employees who are older than 25:

First I use SQL Server 2008 to create a dummy database named dbEmployees with a single table named tblEmployees and add four records:

use master

if exists(select * from sys.sysdatabases where name=’dbEmployees’)
drop database dbEmployees

create database dbEmployees

use dbEmployees

create table tblEmployees
empid char(3) primary key,
lastName varchar(12) not null,
age int

insert into tblEmployees values (‘111’, ‘Adams’, 19)
insert into tblEmployees values (‘222’, ‘Baker’, 29)
insert into tblEmployees values (‘333’, ‘Chung’, 39)
insert into tblEmployees values (‘444’, ‘Dunne’, 49)

Next I create a WCF service to expose the data. I launch an instance of Visual Studio 2010 as an administrator. I go File New Web Site. I select the WCF Service template, location type HTTP, and location http://localhost/WCFServicesRoot/DbEmployeesService. In file Service.cs I add “using System.Data.SqlClient;” and define a method that will retrieve the data for all employees whose age is greater than some age:

public string GetEmployees(int age) // info of all emps whose age >= age
    string connString = “Server=vte;Database=dbEmployees;Trusted_Connection=True”;
    SqlConnection sc = new SqlConnection(connString);
    SqlCommand cmd = new SqlCommand(“SELECT empid, lastName, age FROM tblEmployees
      WHERE age > ” + age.ToString(), sc);
    SqlDataReader sdr = null;
    sdr = cmd.ExecuteReader();
    string s = “”;
    while (sdr.Read() == true)
      s += sdr[0] + “:” + sdr[1] + “:” + sdr[2] + “&”;
    return s;
  catch (Exception ex)
    return ex.Message;

In file IService.cs, inside the [ServiceContract] block I define the retrieval method’s interface:

string GetEmployees(int age);

Now I can build the WCF service and it will be hosted inside IIS. Now I have to give WCF access permissions to the dbEmployees database. In SQL Server Management Studio, I go to the Security Logins area and double-click on the NT AUTHORITY\NETWORK SERVICE object. In the resulting Login Properties dialog I select the User Mapping tab then check dbEmployees to select it, and then at the bottom I check db_datareader and db_datawriter.

Next I hit the F5 key in Visual Studio. I get a Web page with information about the WCF service including its location:

The next step is to create a Silverlight application that accesses the SQL data by using WCF. I launch a new instance of Visual Studio 2010 as an administrator. I go File New Project. Then I select a C# Silverlight application from the template list, give it Name UseDbEmpoyeesService at some convenient Location like C:\SilverlightProjects. I accept the defaults on the New Application dialog: Host the Silverlight application in a new Web site (yes) and Enable WCF RIA Services (no).

In file MainPage.xaml I add a Label control, TextBox control, Button control, and a ListBox control. In the XAML code I change the Grid background color to Wheat so I’ll be able to see where the Silverlight control is later on the host Web page.

Now I tell the Silverlight application about the WCF service. In Solution Explorer I right-click on the UseDbEmployeesService entry and select Add Service Reference from the context menu. In the dialog box I copy paste the location of the WCF service and click on Go. The dialog will list the Service. I like to rename the Namespace: from the default ServiceRefrence1 to DbEmployeesServiceRef. Adding the Service Reference adds all the WCF plumbing behind the scenes.

I double-click on the Button control to generate its event handler and the I add code:

private void button1_Click(object sender, RoutedEventArgs e)
  DbEmployeesServiceRef.ServiceClient sc =
    new DbEmployeesServiceRef.ServiceClient();
  sc.GetEmployeesCompleted += new EventHandler<
    DbEmployeesServiceRef.GetEmployeesCompletedEventArgs >(AddTheData);

private void AddTheData(object sender,
  DbEmployeesServiceRef.GetEmployeesCompletedEventArgs ea)
  string result = ea.Result;
  string[] tokens = result.Split(‘&’);
  for (int i = 0; i < tokens.Length; ++i)

Now I am good to go. I hit F5 to start the Silverlight application hosted inside a test Web page, enter an age in the TextBox, click the Button, and wait for the Silverlight app to send a request to the WCF service to get the data for employees who are older than the supplied age, and place that data into the ListBox.

There are a milion details I haven’t covered. Because my SQL Server, WCF Server, and Silverlight app are all on the same machine I don’t have to worry about cross domain security files ClientAccessPolicy.xml and CrossDomain.xml. If my result set from SQL is huge I might want to consider binary encoding with the GZipStream classto reduce the size.

This entry was posted in Software Test Automation. Bookmark the permalink.