Introduction
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 a 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.
Background
I honestly wondered if I should post this article, because I know there are quite a few other great articles out there on this topic, such as:
I actually worked through some of the articles that I found, but I ran into some difficulties with some areas that were not completely clear. I decided to add this article to the repertoire,
because I felt I could provide another view, and add a little more detail to the topic that may make it more helpful for the beginning Silverlight developer. And I hope it does!
I have tried to capture all of the details and provide a step-by-step example for you.
Also, just a shameless plug, I posted this article at my blog as well: Silverlight 3: Displaying
SQL Server Data
Settings 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 that .NET Framework 3.5 is selected. Click OK when you are 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 an 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 are done.
Here’s what mine looks like:
After we click the OK button, Visual Studio will create a Solution for us containing 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 two .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 four 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, then be sure you specify that, instead of “SQLExpress”).
Finally, select the “AdventureWorks” database. Click the OK button when you are finished.
Here is 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 Server Explorer, 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 Server Explorer to the DataClasses1.dbml designer, as follows:
After successfully adding the Contact table to 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 three public members to it: FirstName, LastName, and Email. Our class should now look like this:
Public Class ContactRecord
Public FirstName As String
Public LastName As String
Public Email As String
End Class
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:
Imports System.ServiceModel
Imports System.ServiceModel.Activation
<ServiceContract(Namespace:="")> _
<AspNetCompatibilityRequirements( _
RequirementsMode:= _
AspNetCompatibilityRequirementsMode.Allowed)> _
Public Class Service1
<OperationContract()> _
Public Sub DoWork()
End Sub
End Class
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:
Imports System.ServiceModel
Imports System.ServiceModel.Activation
<ServiceContract(Namespace:="http://adventureworks.com/")> _
<AspNetCompatibilityRequirements( _
RequirementsMode:= _
AspNetCompatibilityRequirementsMode.Allowed)> _
Public Class Service1
<OperationContract()> _
Public Function GetContacts() _
As List(Of ContactRecord)
End Function
End Class
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 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:
<OperationContract()> _
Public Function GetContacts() As List(Of ContactRecord)
Dim db As New DataClasses1DataContext()
Dim contacts = _
(From contact _
In db.Contacts _
Order By contact.FirstName, _
contact.LastName _
Select contact).Take(1000)
Dim list As New List(Of ContactRecord)
For Each c In contacts
list.Add( _
New ContactRecord With _
{.FirstName = c.FirstName, _
.LastName = c.LastName, _
.Email = c.EmailAddress})
Next
Return list
End Function
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 includes the address,
binding, and contract, which you’ll find in the <endpoint> tag.
Find the “Web.config” file in 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:
<service behaviorConfiguration=
"AdventureWorks_WebServer.Service1Behavior"
name="AdventureWorks_WebServer.Service1">
<endpoint address="" binding="customBinding"
bindingConfiguration="customBinding0"
contract="AdventureWorks_WebServer.Service1" />
<endpoint address="mex"
binding="mexHttpBinding"
contract="IMetadataExchange" />
</service>
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:
<endpoint address="" binding="customBinding"
bindingConfiguration="customBinding0"
contract="AdventureWorks_WebServer.Service1" />
In order to successfully communicate with the Silverlight client application, we need to change the binding to “basicHttpBinding”,
and remove bindingConfiguration, as follows:
<endpoint address="" binding="basicHttpBinding"
contract="AdventureWorks_WebServer.Service1" />
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 window. 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 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, 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 four 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.
<Grid x:Name="LayoutRoot"
Margin="10" ShowGridLines="True" >
</Grid>
The next thing we’ll do is break the interface up into two 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 one column and two rows:
<Grid x:Name="LayoutRoot"
Margin="10" ShowGridLines="True" >
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*" />
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition Height="50" />
<RowDefinition Height="*" />
</Grid.RowDefinitions>
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 two 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.
<Border BorderBrush="Maroon" BorderThickness="1" >
</Border>
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.
<Border BorderBrush="Maroon" BorderThickness="1" >
<Grid Background="LightYellow" Grid.Column="0" Grid.Row="0" >
</Grid>
</Border>
Define two 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.
<Border BorderBrush="Maroon" BorderThickness="1" >
<Grid Background="LightYellow" Grid.Column="0" Grid.Row="0" >
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*" />
<ColumnDefinition Width="100" />
</Grid.ColumnDefinitions>
</Grid>
</Border>
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 its 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”.
<Border BorderBrush="Maroon" BorderThickness="1" >
<Grid Background="LightYellow" Grid.Column="0" Grid.Row="0" >
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*" />
<ColumnDefinition Width="100" />
</Grid.ColumnDefinitions>
<TextBlock Padding="10,0,0,0"
VerticalAlignment="Center"
Text="AdventureWorks Contacts"
FontSize="28"
Foreground="Maroon"
Grid.Row="0" Grid.Column="0" />
<Button x:Name="btnLoad" Content="Load"
VerticalAlignment="Center"
Margin="10"
Grid.Row="0" Grid.Column="1"
Click="btnLoad_Click"/>
</Grid>
</Border>
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 DataGrid
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
manually; 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).
<data:DataGrid x:Name="dataGrid1"
AutoGenerateColumns="False"
Visibility="Collapsed"
Grid.Row="1" Grid.Column="0" >
</data:DataGrid>
The last thing we need to do to the DataGrid is add three text columns: one for First Name, one for Last Name, 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.
<data:DataGrid x:Name="dataGrid1"
AutoGenerateColumns="False"
Visibility="Collapsed"
Grid.Row="1" Grid.Column="0" >
<data:DataGrid.Columns>
<data:DataGridTextColumn
Binding="{Binding FirstName}"
Header="First Name" />
<data:DataGridTextColumn
Binding="{Binding LastName}"
Header="Last Name" />
<data:DataGridTextColumn
Binding="{Binding Email}"
Header="Email Address" />
</data:DataGrid.Columns>
</data:DataGrid>
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:
Private WithEvents mService As New ServiceReference1.Service1Client()
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 two lines of code: one to load the DataGrid with data, and one to make the grid visible:
Private Sub mService_GetContactsCompleted( _
ByVal sender As Object, _
ByVal e As ServiceReference1 _
.GetContactsCompletedEventArgs) _
Handles mService.GetContactsCompleted
dataGrid1.ItemsSource = e.Result
dataGrid1.Visibility = Windows.Visibility.Visible
End Sub
The GetContactsCompleted event handler is executed after the WCF service has retrieved the data from 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 one line of code to the btnLoad_Click event handler:
Private Sub btnLoad_Click( _
ByVal sender As System.Object, _
ByVal e As System.Windows.RoutedEventArgs)
mService.GetContactsAsync()
End Sub
So now, the complete code window should look like this:
Partial Public Class MainPage
Inherits UserControl
Private WithEvents mService As New ServiceReference1.Service1Client()
Public Sub New()
InitializeComponent()
End Sub
Private Sub btnLoad_Click( _
ByVal sender As System.Object, _
ByVal e As System.Windows.RoutedEventArgs)
mService.GetContactsAsync()
End Sub
Private Sub mService_GetContactsCompleted( _
ByVal sender As Object, _
ByVal e As ServiceReference1 _
.GetContactsCompletedEventArgs) _
Handles mService.GetContactsCompleted
dataGrid1.ItemsSource = e.Result
dataGrid1.Visibility = Windows.Visibility.Visible
End Sub
End Class
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.
