Click here to Skip to main content
Click here to Skip to main content

Silverlight 3: Displaying SQL Server Data

, 17 Sep 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
This article demonstrates how to display SQL Server data in a Silverlight DataGrid.

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()
        ' Add your operation implementation here
    End Sub

    ' Add more operations here and mark them 
    '  with <OperationContract()>

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)

    'Use the LINQ to SQL Class to access
    '  the Contact table in the Adventure-
    '  Works database.
    Dim db As New DataClasses1DataContext()

    'Use LINQ to retrieve the rows from the
    '  Contact table.
    '  Notice that the ".Take(1000)" method
    '  returns the first (top) 1000 rows 
    '  of the result set.
    Dim contacts = _
        (From contact _
        In db.Contacts _
        Order By contact.FirstName, _
            contact.LastName _
        Select contact).Take(1000)

    'Create a new generic list of Contact-
    '  Record type.  This list will be
    '  returned to the Silverlight client
    '  application.
    Dim list As New List(Of ContactRecord)

    'Loop through each row of the Contact
    '  table and add it to the list.
    For Each c In contacts

        list.Add( _
            New ContactRecord With _
                 {.FirstName = c.FirstName, _
                  .LastName = c.LastName, _
                  .Email = c.EmailAddress})

    Next

    'Return the list
    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:

'Create a new instance of 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

    'Create a new instance of our WCF Service
    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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

CS Rocks
Software Developer DataPrint, LLC
United States United States

Comments and Discussions

 
QuestionSilverlight Saving to db PinmemberMani895-Aug-14 20:47 
QuestionDataGrid not filled by data when web application is hosted [modified] PinmemberAbhijat Chauhan23-Aug-13 3:32 
AnswerRe: DataGrid not filled by data when web application is hosted PinmemberAbhijat Chauhan28-Aug-13 1:37 
Questionsilverlight beginner PinmemberAmarsaikhan11-Jun-13 17:13 
Questionhi friend Pinmemberarmanr23-Feb-13 22:58 
GeneralMy vote of 5 PinmemberPTA_UK14-Jun-12 2:49 
QuestionThank Pinmemberomidrn25-Mar-12 14:46 
Questionn2012 comes, in order to thank everyone, characteristic, novel style, varieties, low price and good quality, and the low sale price. Thank everyone ==== ( http://www.fullmalls.com ) ===== ==== ( http://www.fullmalls.com ) ===== $33 True Religion Pingroupkjlfdgehe9-Mar-12 16:27 
Questionhelp PinmemberReza MN4-Feb-12 17:09 
AnswerRe: help Pinmembertvviewer20-Mar-12 11:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 17 Sep 2009
Article Copyright 2009 by CS Rocks
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid