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 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.
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:
Scott Guthrie's blog: Silverlight 2 End-to-End tutorial
Shivprasad Koirala's article: 7 Simple steps to connect SQL Server using WCF from Silverlight
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 tried to capture all of the detail 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 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:
CollapsePublic 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:
Collapse
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:
Collapse
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 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:
Collapse
<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 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:
Collapse
<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:
Collapse
<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 the bindingConfiguration, as follows:
Collapse
<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 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.
Collapse
<Grid x:Name="LayoutRoot"
Margin="10" ShowGridLines="True" >
</Grid>
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:
Collapse
<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 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.
Collapse
<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.
Collapse
<Border BorderBrush="Maroon" BorderThickness="1" >
<Grid Background="LightYellow" Grid.Column="0" Grid.Row="0" >
</Grid>
</Border>
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.
Collapse
<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 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”.
Collapse
<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 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).
Collapse
<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 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.
Collapse
<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:
Collapse
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 2 lines of code: one to load the DataGrid with data, and one to make the grid visible:
Collapse
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 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:
Collapse
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:
Collapse
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: