Silverlight / WPF - Binding data from sql server to silverlight grid

Asked By Anandh Ramanujam on 20-May-11 04:06 AM
Hi,

Im very  new to silverlight.

How we can fetch data from sql server and binding to silverlight grid..

Please help me in this regard.

Thanks.
Ravi S replied to Anandh Ramanujam on 20-May-11 04:17 AM
Ravi S replied to Anandh Ramanujam on 20-May-11 04:19 AM
HI

here is the example

The DataGrid

image

refer the link for details

http://odetocode.com/code/740.aspx

Mahendar Nanamala replied to Anandh Ramanujam on 20-May-11 04:22 AM

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:

Collapse
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:

Collapse
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:

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)

    '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 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
'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 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

    '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:

Riley K replied to Anandh Ramanujam on 20-May-11 04:22 AM

Binding with Database


Silverlight project doesn't support normal ADO.NET objects like DataTable, DataSet, DataColumn, Database connection providers like SqlConnection, OledbConnection objects. You can use System.Data namespace but that contains Services related stuffs not ADO.NET stuffs. To get the data from the database we need to create WCF Data Services or WCF RIA Services… Read “Introduction to WCF Services” to get more details.

Create Database


Let’s fist create a database and a table called tblItem as following:

CREATE TABLE [dbo].[tblItem](

    [ItemNumber] [varchar](50) NULL,
    [ItemDescription] [varchar](50) NULL,
    [Quantity] [int] NULL
)

Creating WCF Service

Now create “CItem” class in web project for Item as following...

using System.Runtime.Serialization;
namespace DataBinding.Web
{
    [DataContract]
    public class CItem
    {
      [DataMember]
      public string ItemNumber { get; set;}
      [DataMember]
      public string ItemDescription { get; set; }
      [DataMember]
      public int Quantity { get; set; }
    }
}

Let’s add Silverlight-enabled WCF Service in the Web project called “DataService.svc”

New WCF Service

Let’s create a function in the wcf service so that it return the list of items as following:

using System;

using System.Collections.Generic;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Data;
using System.Data.SqlClient;
namespace DataBinding.Web
{
    [ServiceContract(Namespace = "")]
    public class DataService
    {
      static string connectionString = "Data Source=.\\sql2008;Initial Catalog=DataBinding;Integrated Security=True;";
      [OperationContract]
      public List<CItem> GetItems()
      {
        SqlConnection sqlConnection = new SqlConnection(connectionString);
        DataSet objSet = new DataSet();

 
        SqlCommand sqlCommand = new SqlCommand();
        sqlCommand.Connection = sqlConnection;
        sqlCommand.CommandText = "Select * FROM tblItem";
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
        sqlDataAdapter.SelectCommand = sqlCommand;
        sqlDataAdapter.Fill(objSet);

 
        List<CItem> lstResult = new List<CItem>();
        CItem objEmployee;

        if (objSet.Tables.Count > 0)
        {
         foreach (DataRow dr in objSet.Tables[0].Rows)
          {
            objEmployee = new CItem();
            objEmployee.Quantity = Convert.ToInt32(dr["Quantity"]);
            objEmployee.ItemDescription = dr["ItemDescription"].ToString();
            objEmployee.ItemNumber = dr["ItemNumber"].ToString();
            lstResult.Add(objEmployee);
          }
        }
        return lstResult;
      }
    }
}

Consuming WCF Service in Silverlight project

To consume the WCF service we just created we need to add Service Reference of that WCF service in the Silverlight project.

WCF Service Reference

Now we need to create object of the WCF service client and event handler which will get the results sent by the WCF service as following:

private void ReadDataFromDatabase()
{
    DaService.DataServiceClient  t = new DaService.DataServiceClient();
    t.GetItemsCompleted += new EventHandler<DaService.GetItemsCompletedEventArgs>(GetItemCompleted);
    t.GetItemsAsync();
}


void GetItemCompleted(object sender, DaService.GetItemsCompletedEventArgs e)
{
    dataGrid7.ItemsSource = e.Result;
}

You are done… Run the project you will see the grid is show data from the database with three rows and column we inserted.

So far we set the AutoGenerateColumns property of data grid to True. So those columns are generated automatically for all the properties of Item Class.Let’s define the column and set the binding property in XAML:

<sdk:DataGrid AutoGenerateColumns="False"
          Height="100" HorizontalAlignment="Left"
          Margin="341,21,0,0" Name="dataGrid2"
          VerticalAlignment="Top" Width="311" >
    <sdk:DataGrid.Columns>
      <sdk:DataGridTextColumn CanUserReorder="True" CanUserResize="True"
          CanUserSort="True" Width="Auto"
          Binding="{Binding ItemNumber}"
          Header="ItemNumber"/>
      <sdk:DataGridTextColumn CanUserReorder="True" CanUserResize="True"
          CanUserSort="True" Width="Auto"
          Binding="{Binding ItemDescription}"
          Header="ItemDescription"/>
      <sdk:DataGridTextColumn CanUserReorder="True" CanUserResize="True"
          CanUserSort="True" Width="Auto"
          Binding="{Binding Quantity}"
          Header="Quantity"/>
    </sdk:DataGrid.Columns>
</sdk:DataGrid>

Mahendar Nanamala replied to Anandh Ramanujam on 20-May-11 04:24 AM
If u feel that is lenghthy than try this.......



Different Ways to Bind Data Grid in Silverlight

In the previous article I described the basics of Data Binding in Silverlight. In this article I will demonstrate How to Bind Data grid with different data sources like:

  • Binding with Static Collection/List
  • Binding with XML
  • Binding with Database via WCF Services
  • Binding with Database via WCF RIA Services

To setup an example let’s create a new Silverlight project is VS 2010 called “DataBinding”. See How to create Silverlight application in VS 2010” for more information. Let’s create add a new Class file called “Item.cs” with Item Class as following:

01.namespace DataBinding
02.{
03.public class Item
04.{
05.public string ItemNumber { get; set;}
06.public string ItemDescription { get; set; }
07.public int Quantity { get; set; }
08.public Item()
09.{
10.}
11.public Item(string ItemNumber, string ItemDescription, int Quantity)
12.{
13.this.ItemNumber= ItemNumber;
14.this.ItemDescription = ItemDescription;
15.this.Quantity = Quantity;
16.}
17.}
18.}

We will refer the above class to bind the data gird. Let’s add a DataGrid on the MainPage.xaml page by drag & drop the data grid control from the toolbox. You will see the below in MainPate.xaml page..

01.<UserControl x:Class="DataBinding.MainPage"
06.mc:Ignorable="d"
07.d:DesignHeight="513" d:DesignWidth="662"
09.>
10.<Grid x:Name="LayoutRoot" Background="White" Height="540" Width="664">
11.<sdk:DataGrid AutoGenerateColumns="False"
12.Height="100" HorizontalAlignment="Left"
13.Margin="12,21,0,0" Name="dataGrid1"
14.VerticalAlignment="Top" Width="311" />
15.</Grid>
16.</UserControl>

Let’s change the property AutoGenerateColumns to True and create a Collection of Item class in Item.cs class file as following:

01.public class ItemsCollection : Collection
02.{
03.public ItemsCollection()
04.{
05.Add(new Item("1", "Item Name1", 1));
06.Add(new Item("2", "Item Name2", 2));
07.Add(new Item("3", "Item Name3", 3));
08.}
09.}

On the MainPage’s constructor write following to bind the data grid with collection object

1.dataGrid1.ItemsSource = new ItemsCollection();

Same thing you can also do by setting the ItemSource property in XAML and then assign DataContext of datagrid as following:

1.ItemsSource="{Binding}"
2. 
3.dataGrid1.DataContext = new ItemsCollection();

Now the run the project you will the data grid have three column and three rows of items we added in the collection.

Binding with XML

In Silverlight, you cannot bind Data Grid directly to XML data. A possible workaround for this is to convert the XML to CLR objects, and then bind to the CLR object.

Let say we have following XML file called “ItemList.xml” for the list of items…

1.<?xml version="1.0" encoding="utf-8" ?>
2.<ItemList>
3.<Item ItemNumber="1" ItemDescription="Item Name1" Quantity="1" />
4.<Item ItemNumber="2" ItemDescription="Item Name2" Quantity="2" />
5.<Item ItemNumber="3" ItemDescription="Item Name3" Quantity="3" />
6.</ItemList>

Let’s first create a function which returns the Item object by passing the XML Element

1.private Item GetItem(XElement el)
2.{
3.Item s = new Item();
4.s.ItemDescription = el.Attribute("ItemDescription").Value;
5.s.ItemNumber = el.Attribute("ItemNumber").Value;
6.s.Quantity = Convert.ToInt32(el.Attribute("Quantity").Value.ToString());
7.return s;
8.}

We can have two cases.

  1. XML is in Silverlight project
  2. XML is on Sever Side(we have URL or xml file is in the Client Bin Folder)

When XML is in Silverlight project

Let’s a create function which return the list of items

01.public List<Item> GetItemList()
02.{
03.List<Item> itemList = new List<Item>();
04.XElement doc = XElement.Load(@"Data/ItemList.xml");
05. 
06.itemList = (from el in doc.Elements()
07.select GetItem(el)).ToList();
08.return itemList;
09.}

We can assign the Itemsource of data grid as following :

1.dataGrid1.ItemsSource = GetItemList();

When XML is on Sever Side

We need to create a asynchronous method to get the content of XML file from the server and then create the List of items from the xml. See the code below

01.private void ReadDataFromXML()
02.{
03.Uri url = new Uri("ItemList.xml", UriKind.Relative);
04.WebClient client = new WebClient();
05.client.DownloadStringCompleted += new DownloadStringCompletedEventHandler(client_DownloadStringCompleted);
06.client.DownloadStringAsync(url);
07.}
08. 
09.void client_DownloadStringCompleted(object sender, DownloadStringCompletedEventArgs e)
10.{
11.dataGrid1.ItemsSource = GetItemList(e.Result);
12.}
13.public List<Item> GetItemList(string xmlcontent)
14.{
15.List<Item> itemList = new List<Item>();
16. 
17.XElement doc = XElement.Parse(xmlcontent);
18. 
19.itemList = (from el in doc.Elements()
20.select GetItem(el)).ToList();
21. 
22.return itemList;
23.}

You will need to add reference of System.Xml.Linq.

Binding with Database

Silverlight project doesn't support normal ADO.NET objects like DataTable, DataSet, DataColumn, Database connection providers like SqlConnection, OledbConnection objects. You can use System.Data namespace but that contains Services related stuffs not ADO.NET stuffs. To get the data from the database we need to create WCF Data Services or WCF RIA Services… Read “Introduction to WCF Services” to get more details.

Create Database

Let’s fist create a database and a table called tblItem as following:

1.CREATE TABLE [dbo].[tblItem](
2.[ItemNumber] [varchar](50) NULL,
3.[ItemDescription] [varchar](50) NULL,
4.[Quantity] [int] NULL
5.)

Let’s insert few records in the table…

1.INSERT INTO [tblItem]
2.([ItemNumber]
3.,[ItemDescription]
4.,[Quantity])
5.VALUES ('1','Item Name1',1),
6.('2','Item Name3',2),
7.('3','Item Name3',3)

Creating WCF Service

Now create “CItem” class in web project for Item as following...

01.using System.Runtime.Serialization;
02.namespace DataBinding.Web
03.{
04.[DataContract]
05.public class CItem
06.{
07.[DataMember]
08.public string ItemNumber { get; set;}
09.[DataMember]
10.public string ItemDescription { get; set; }
11.[DataMember]
12.public int Quantity { get; set; }
13.}
14.}

Let’s add Silverlight-enabled WCF Service in the Web project called “DataService.svc”

New WCF Service

Let’s create a function in the wcf service so that it return the list of items as following:

01.using System;
02.using System.Collections.Generic;
03.using System.ServiceModel;
04.using System.ServiceModel.Activation;
05.using System.Data;
06.using System.Data.SqlClient;
07.namespace DataBinding.Web
08.{
09.[ServiceContract(Namespace = "")]
10.public class DataService
11.{
12.static string connectionString = "Data Source=.\\sql2008;Initial Catalog=DataBinding;Integrated Security=True;";
13.[OperationContract]
14.public List<CItem> GetItems()
15.{
16.SqlConnection sqlConnection = new SqlConnection(connectionString);
17.DataSet objSet = new DataSet();
18. 
19.SqlCommand sqlCommand = new SqlCommand();
20.sqlCommand.Connection = sqlConnection;
21.sqlCommand.CommandText = "Select * FROM tblItem";
22.SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
23.sqlDataAdapter.SelectCommand = sqlCommand;
24.sqlDataAdapter.Fill(objSet);
25. 
26.List<CItem> lstResult = new List<CItem>();
27.CItem objEmployee;
28. 
29.if (objSet.Tables.Count > 0)
30.{
31.foreach (DataRow dr in objSet.Tables[0].Rows)
32.{
33.objEmployee = new CItem();
34.objEmployee.Quantity = Convert.ToInt32(dr["Quantity"]);
35.objEmployee.ItemDescription = dr["ItemDescription"].ToString();
36.objEmployee.ItemNumber = dr["ItemNumber"].ToString();
37.lstResult.Add(objEmployee);
38.}
39.}
40.return lstResult;
41.}
42.}
43.}

Consuming WCF Service in Silverlight project

To consume the WCF service we just created we need to add Service Reference of that WCF service in the Silverlight project.

WCF Service Reference

Now we need to create object of the WCF service client and event handler which will get the results sent by the WCF service as following:

01.private void ReadDataFromDatabase()
02.{
03.DaService.DataServiceClient  t = new DaService.DataServiceClient();
04.t.GetItemsCompleted += new EventHandler<DaService.GetItemsCompletedEventArgs>(GetItemCompleted);
05.t.GetItemsAsync();
06.}
07. 
08.void GetItemCompleted(object sender, DaService.GetItemsCompletedEventArgs e)
09.{
10.dataGrid7.ItemsSource = e.Result;
11.}

You are done… Run the project you will see the grid is show data from the database with three rows and column we inserted.

So far we set the AutoGenerateColumns property of data grid to True. So those columns are generated automatically for all the properties of Item Class.Let’s define the column and set the binding property in XAML:

01.<sdk:DataGrid AutoGenerateColumns="False"
02.Height="100" HorizontalAlignment="Left"
03.Margin="341,21,0,0" Name="dataGrid2"
04.VerticalAlignment="Top" Width="311" >
05.<sdk:DataGrid.Columns>
06.<sdk:DataGridTextColumn CanUserReorder="True" CanUserResize="True"
07.CanUserSort="True" Width="Auto"
08.Binding="{Binding ItemNumber}"
09.Header="ItemNumber"/>
10.<sdk:DataGridTextColumn CanUserReorder="True" CanUserResize="True"
11.CanUserSort="True" Width="Auto"
12.Binding="{Binding ItemDescription}"
13.Header="ItemDescription"/>
14.<sdk:DataGridTextColumn CanUserReorder="True" CanUserResize="True"
15.CanUserSort="True" Width="Auto"
16.Binding="{Binding Quantity}"
17.Header="Quantity"/>
18.</sdk:DataGrid.Columns>
19.</sdk:DataGrid>

In the next article we will see “How to Bind Data Grid with Database via WCT RIA Services” and what are different column types in Datagrid…

You may find below little bit interesting…

What is difference between ItemSource and DataContext

  1. DataContext expects an object type where ItemsSource expects IEnumerable type objects.
  2. DataContext is a dependency property is exposed by FrameworkElement base class,where as ItemsSource is defined by the ItemsControl class. All the descendants of FrameworkElement can utilize the DataContext property and set an object to its value. But we can only set a type of IEnumerable (or instance of class that derives from).
  3. DataContext does not generate template, it only used to hold common data for other controls to bind. In terms of ItemsSource property, it is mainly used to generate template regardless of you set it in XAML or in the code behind. 
  4. DataContext is mainly used to hold common data that other child want to share. Thus it can be inherited by other child elements without problem. But for ItemsSource, it not used to share data in the visual tree. It is only valid for the element that defined. There is still one thing to be noted is that the child element can override the DataContext of the perent DataContext no mater directly or indirectly.