Silverlight 3 is by far, the coolest technology for web programming!  I’m so excited about Silverlight 3!  I hope I can share some things with you here that will help you get up and running with Silverlight 3 and SQL Server Databases.

This article walks you through displaying data from an SQL Server Database in a Silverlight 3 application.

There are three technologies that will be used in this example, which are:  SQL Server, Windows Communication Foundation (WCF), and Silverlight 3, which runs in Visual Studio 2008.
 
 

Preview…

This article is going to retrieve the top 1000 rows from the Person.Contact table in the AdventureWorks SQL Server database, and display them in a Silverlight DataGrid.

 

Here’s a preview of what things are going to look like when we finish this article:

 
 

Setting Things Up…

Before we get started, let’s make sure we get the following things setup:
 

SQL Server
First, you have to have SQL Server or SQL Express setup on your computer.  Since you’ve already started reading this article, I’m guessing you do.  However, if you do not, then follow this link for instructions on downloading and installing SQL Express:

http://www.microsoft.com/Sqlserver/2005/en/us/express.aspx
 

AdventureWorks Database
Next, you have to download the AdventureWorks database and get it setup with SQL Server.  For instructions on how to do that, follow this link:

http://msdn.microsoft.com/en-us/library/aa992075(VS.80).aspx


Silverlight 3
Additionally, you have to have Silverlight 3 installed.  Silverlight 3 runs inside of Visual Studio 2008.  To install Silverlight 3, please visit the following link:

http://silverlight.net/getstarted/silverlight3/default.aspx

Creating the Project…

To begin, fire up Visual Studio 2008 and create a new Project (File menu | New | Project…)

In the Project Types treeview on the left, under the Visual Basic node, click the Silverlight node, then select the Silverlight Application template on the right.  Name the project “AdventureWorks”.  Make sure the .NET Framework 3.5 is selected.  Click OK when you’re finished.

Here’s what mine looks like:

 

After you click the OK button, the New Silverlight Application window should open.  Here we will be prompted to select whether we want to create a new ASP.NET Web Site project, or a ASP.NET Web Application Project, which is used to host the Silverlight Application within.  We will leave the ASP.NET Web Application Project selected, but change the New Web Project Name from “AdventureWorks.Web” to “AdventureWorks_WebServer”, to make it a little more clear.  Click the OK button when you’re done.

Here’s what mine looks like:




 

After we click the OK button, Visual Studio will create a Solution for us containing both the “AdventureWorks_WebServer” ASP.NET web server application in it, which will serve as a test environment for the “AdventureWorks” Silverlight client application, which was also created.

A look at Solution Explorer will reveal the following:

 

In the AdventureWorks Silverlight client application, there are 2 .xaml files: an App.xaml file, which is used to declare resources; and the MainPage.xaml file, which is the default user control that loads when the application starts.  The MainPage.xaml file is the file we will use later to design our interface.

In the AdventureWorks_WebServer application, any of the following 4 files can be used to host the Silverlight application:

     -  AdventureWorksTestPage.aspx
     -  AdventureWorksTestPage.html

     -  Default.aspx

     -  Silverlight.js

The AdventureWorksTestPage.aspx file is the default startup page that will load when the application runs, and will host the MainPage.xaml user control.

The AdventureWorksTestPage.html file, the Default.aspx file, and the Silverlight.js file can all be deleted from this example, because they will not be used.

The Web.config file is used to store configuration information for the web server.  We will learn more about this in a bit.
 
 

Creating the AdventureWorks Connection…

If you already have SQL Server installed, and the AdventureWorks database attached, then you are ready to move forward with creating the connection.

In Visual Studio, click on the Tools menu | Connect to Database…  The Add Connection window will open, and be ready for configuration.  Make sure the Data Source is set to Microsoft SQL Server (SqlClient).  Set the Server name to either “localhost”, if you attached the database to SQL Server, or “localhost\SQLExpress”, if you attached the database to SQL Express (If you changed the instance name from something other than the default, than be sure you specify that, instead of “SQLExpress”).  Finally, select the “AdventureWorks” database.  Click the OK button when you’re finished.

Here’s what mine looks like:

 
Now that we have the connection created, we’ll create a LINQ to SQL Class, and configure it to use the AdventureWorks database.

 
 

Adding LINQ to SQL Classes…

Since we’ve just created the connection to the SQL Server AdventureWorks database in ServerExplorer, we’ll add a LINQ to SQL Class to our application that will be used to store the connection information, and retrieve the data from the Person.Contact table.

To perform this step, right-click on the AdventureWorks_WebServer project, then select Add | New Item…  When the Add New Item window opens, click on the Data node, then select the LINQ to SQL Classes Template from the right.  Leave the name as “DataClasses1.dmbl”, then click the Add button.

Here’s an example:



After you click the Add button, the “DataClasses1.dbml” file will be added to the AdventureWorks_WebServer project, and will be visible in Solution Explorer.  If it did not open up automatically for you, then double-click on it to open the designer.

Next, make sure the Server Explorer is open (View menu | Server Explorer).  Then, expand the AdventureWorks database node, and expand the Tables node.  Find the “Contact (Person)” table, left-click on it, then drag it from the Server Explorer, to the DataClasses1.dbml designer, as follows:


After successfully adding the Contact table to the DataClasses1.dbml, save the project (File menu | Save All).  Then, close the DataClasses1.dbml designer, because we are finished with it.
 
 

Adding the ContactRecord class…

Just by looking at the Contact table above, you can tell there is a lot of information!  There are a lot of columns, such as:  ContactID, NameStyle, Title, etc. Since we only want the FirstName, LastName and EmailAddress columns, we will make a custom ContactRecord class to represent each contact row from the table, instead of using the exposed Contact type.

As a note, one of the first things I wondered was, Why not just use ADO.NET?  DataSets and DataTables are much easier to work with!  While this is true, WCF was designed to follow the SOA principles, which means we need to program against contracts, not implementation. Since DataSets and DataTables are .NET specific types, we cannot use them.  Although they can be used within the WCF service, they cannot be passed to Silverlight.

To create the ContactRecord class, right-click on the AdventureWorks_WebServer project node in Solution Explorer, select Add | Class…  Name it “ContactRecord.vb”, then click the Add button.

After the class has been added, add 3 public members to it:  FirstName, LastName and Email.  Our class should now look like this:

 

Save the project (File menu | Save All).  Next, we’ll look at adding a WCF service.
 
 

Adding WCF…

Now that we have the LINQ to SQL Class set up, and our ContactRecord class created, we are ready to add a Windows Communication Foundation class to our AdventureWorks_WebServer project.  This class will act as a service, which will use the LINQ to SQL Class to retrieve the data from the AdventureWorks database, and return it to the Silverlight client application.

In Solution Explorer, right-click on the AdventureWorks_WebServer project node, and select Add | New Item…  Click on the Silverlight node, then select the Silverlight-enabled WCF Service template.  Leave it named “Service1.svc”, and click the Add button. 

Here’s what mine looks like:

After the WCF service has been added, you’ll find a “Service1.svc” file added to the AdventureWorks_WebServer project node in Solution Explorer.  If it did not automatically open, then double-click it to open the code window.

The code window should now look like this:
 

A couple of things we will do to this service:

     -  Add a Namespace to the ServiceContract.  This will make sure our
        custom types are unique.

        As a note, a Namespace should be a uri, but does not actually have to 
        point to a real location on the web.  It is only used as a unique identifier
        for the custom type.

     -  Delete all of the code and comments within the class.


Add the GetContacts Function
Now we are ready to add our own GetContacts() Function, which will be called from the Silverlight client application.  When adding this function, declare the return type to be List(Of ContactRecord), and be sure to add the <OperationContract()> attribute just above the function name.  This allows this function to be accessible to the Silverlight client.

 

After adding the namespace and the GetContactions() Function, the code window should look like this:
 

Add the Code to the GetContacts Function
Within the GetContacts() Function, we can use the “DataClasses1.dbml” LINQ to SQL Class that we added, and of course, LINQ, to access the to data in the Contact table of the AdventureWorks database.  Add the first 1000 rows to a list, and return it. 

Here’s what our Function looks like:
 

Next, Save the project (File menu | Save All).
 
  

Updating the Web.config file…

The WCF Service that we added uses the Web.config file to access configuration information.  The configuration information that it needs to retrieve is the address, binding and contract, which you’ll find in the <endpoint> tag.

Find the “Web.config” file in the Solution Explorer, and double-click on it to open it up.  Once you’ve opened it, scroll to the bottom, and look for the <services> section.  Here’s what the services section from my web.config file looks like:
 

Notice that there are two endpoint tags:   the top one, which has an address=””, and the bottom one, which has an address=”mex”.  We want to focus on the top one, with the address=””, as follows:
 

In order to successfully communicate with the Silverlight client application, we need to change the binding to “basicHttpBinding”, and remove the bindingConfiguration, as follows:
 

 
When you’ve made those changes, save the project (File menu | Save All), then close the web.config file.
 
Ok, so now we have the connection setup, the LINQ to SQL Class added, the ContactRecord class created, the WCF service programmed, and the web.config file updated…  So we are ready to start working with the Silverlight client interface.
 
 

Adding a Service Reference to the Silverlight Client…

Now for the exciting part!  We get to start working with Silverlight 3 itself! 

View the Solution Explorer, and go to the AdventureWorks silverlight client application, as shown here:

  
The first thing we’ll do is add a reference to the WCF Service that we created earlier.  This will enable our Silverlight client application to communicate with the WCF service we created earlier.  To do this, right-click on the AdventureWorks project node, then select “Add Service Reference…”, as follows:

Once you’ve clicked the menu item, the Add Service Reference window will open.  After it has opened, perform the following steps:

     -  Click the Discover button to find the Address of our service.
     -  In the Services treeview, you should see our service listed.
     -  Expand the “Service1.svc” node.
     -  Expand the “Service1” node.

     -  Click on the final “Service1” child node.

     -  You should be able to see the “GetContacts” method in the Operations
        listview on the right.

Here’s what the window looked like for me:


If after you have clicked the Discover button, you do not see the Service1.svc service listed, go back to the web.config file and make sure you configured it correctly, as demonstrated above.  Be sure to remove any unnecessary spaces between the address, binding and contract attributes and their values.

Next, click the Advanced… button to display the Service Reference Settings windows.  Here, we need to change the Collection type to System.Collections.Generic.List, as follows:


After you have changed the collection type, click the OK button to close the Service Reference Settings window, then OK again, to close the Add Service Reference window.
 

Now you should be able to see the new service reference that we added in Solution Explorer:

 
We will use the service reference later.
 
 

Programming the Interface…

Now we have everything in place!  The last thing we need to do is program the interface of our Silverlight client application, and then add the Visual Basic code necessary to retrieve the data, and display it on our interface.

In the Solution Explorer, find the MainPage.xaml file, and double-click on it.  This will open the file in the XAML editor.

Once the MainPage.xaml file is open in the XAML editor, you will notice that it is a UserControl.  This is obvious, just by looking at the starting and ending <UserControl> tags.  As mentioned earlier, the AdventureWorksTestPage.aspx is the start up web page that gets loaded when the application runs, but within it is the embedded MainPage.xaml user control.  The interface that we program in the MainPage.xaml user control is the actual interface that will appear on the web page when the program runs.


Configuring the main Grid
The first thing to do is find the <Grid tag and add a Margin attribute, setting it to a value of 10.  This will give us a margin of 10 all the way around all 4 sides of the UserControl.  Additionally, set ShowGridLines=”True”, so that the grid lines will be visible while we program the interface.  We will remove this later, but for now it will let us see the grid as we work.
 

The next thing we’ll do is break the interface up into 2 sections:  a header section at the top, and a data section filling the remainder of the page.  To do that, configure the Grid to have 1 column and 2 rows:
 

Notice that the asterisk (“*”) is used to indicate the remaining space available.  This will cause the width of the column to automatically adjust to the width of the Grid.

Now, run the application (F5).  Since this is the first time running the application, you should be prompted to enable debugging when the following window opens:


Leave the default selected, and click the OK button.

Once the application runs, and the web page loads, you’ll be able to see how the web page is divided into 2 separate sections:  a header at the top, and a data section that fills the remainder of the page.  Here’s what mine looks like:

 
Close Internet Explorer, and return to the MainPage.xaml editor.  Remove  ShowGridLines=”True” from the grid tag.


Configure the header Grid
In the header section of the main grid (row 0), we are going to add another grid that will be used to position the title of the page, and the button that will be used to retrieve and load the data.

Before we add the grid though, add a nice maroon border, with a thickness of “1”.  This border will encompass the grid.
 

Add the grid within the Border tags, and set the background color to light yellow.  Additionally, add extended attributes to the grid, instructing it to be positioned in the header section (first column (column 0); top row (row 0)) of the main grid.
 

Define 2 columns for the grid:  The first column will have a width of “*”, and will store the title.  The second column will have a width of “100”, and will store the button that will be used to load the data.
 

Finally, add a TextBlock to display the title of the page.  Set the Text to “AdventureWorks Contacts”.  Use the grid’s extended attributes to position the TextBlock in column 0, row 0.

Then, add a Button that will be used to display the data.  Set it’s Name to “btnLoad”, the Content to “Load”, and then use the grid’s extended attributes to position the Button in column 1, row 0.  Set the Click attribute to “btnLoad_Click”.
 

And that’s the end of the header!

Before moving on, find the Click attribute of the Button, and right-click on “btnLoad_Click”, then select “Navigate to Event Handler” to create the respective sub routine.  After the sub routine has been created, return to the xaml editor.


Configure the data grid
The next thing to do is add a Silverlight DataGrid to the interface.  To do this, display the ToolBox, find the DataGrid control, then drag it onto the xaml editor, and drop it just under the </Border> tag.

Set the Name to “dataGrid1”; AutoGenerateColumns to “False”, because we are going to add the columns manualy; Visibility to “Collapsed”, because we want the grid to be hidden when the page loads; and then use the main grid’s extended attributes to position the DataGrid in Column 0, Row 1 (which is the bottom part of the main grid).
 

The last thing we need to do to the DataGrid is add three text columns:  one for FirstName, one for LastName, and one for Email.

In particular, notice below how each column is bound to the DataSource, by setting the Binding attribute.  Additionally, notice how the value for the Binding attribute is a string expression, enclosed in curly braces {}, using the Binding keyword, and the name of the property in the DataSource to bind to. 
 

At this point, you can save the project (File menu | Save All).  Then, run the application (F5).  The end result should look like this:
 
 
Close Internet Explorer, and return to the xaml editor when you’re finished.


Adding the Visual Basic Code
The last thing we need to do is add the Visual Basic Code to tie it all together, and make things work!  So to start, right-click on the xaml editor and select “View Code” to go to the code window.

At the top of the code window, using the WithEvents keyword, add a declaration to our WCF service:
 

Then, go to the Class Name drop down list at the top of the code window and select “mService”; following, go to the Method Name drop down list at the top of the code window and select “GetContactsCompleted”.
 
 
Selecting “GetContactsCompleted” from the Method Name drop down list will create the mService_GetContactsCompleted event handler.  Within this event handler, just add 2 lines of code:  One to make the grid visible, and one to load the DataGrid with data:
 

The “GetContactsCompleted” event handler is executed after the WCF service has retrieved the data from the SQL Server, and is ready to pass the data to our Silverlight client.

The final thing to do is add the code that starts the asynchronous process of getting the Contact data from our WCF service.  Add this 1 line of code to the btnLoad_Click event handler:
 

So now, the complete code window should look like this:
 

And that’s all there is to it!

Save the project (File menu | Save All).  Then run the application (F5).  When the application opens, click the “Load” button to retrieve the data from the SQL Server AdventureWorks database, and load it into the Silverlight DataGrid.  If everything worked right, you should see the following:
 
 
 

Summary

This article demonstrated how to create a WCF service that used LINQ to SQL to retrieve data from the Person.Contact table of the AdventureWorks database, how to update the service endpoint configuration of the web.config file to enable the WCF service to return the results to the Silverlight Client application, plus how to reference a WCF service from a Silverlight Client application, and how to program the Silverlight Client interface to display the retrieved data in a Silverlight DataGrid.