ItProjects For You

The price of the projects include source code, abstract, report and support for development and deployment. Please use our contact us form or send email to Support@srishtis.com

Displaying SQL Database Data in a DataGrid using LINQ and WCF

Getting Started

To begin, create a project named SQLData, but be sure to choose Web Application Project as we want both a Silverlight project and also a Server project in which we can create a WCF Web Service (to connect to the database).

Visual Studio 2008 will create two projects under one solution.

The solution and first project are named SQLData. That first project is the Silverlight application and has the same files that you've seen in previous tutorials.The second project, SQLData_Web is created for you as a test environment for the Silverlight project and it has four potential entry points,

Default.aspx
Silverlight.js
SQLDataTestPage.aspx
SQLDataTestPage.html

SQLDataTestPage.aspx is specifically designed to test the Silverlight controls and quick examination shows that it includes an AJAX ScriptManager and an ASP:Silverlight control whose source is the .xap file (pronounced zap file) that will be produced by the Silverlight project,

Adding Linq Classes

To begin right click on the server project and choosing Add->New Item, and then choose the LinqToSql Classes . Notice that the explanation below the window says "LINQ to SQL classes mapped to relational objects."

When the Object Relational Designer window opens, open the Server Explorer (View->Server Explorer) and navigate to the AdventureWorkLTs database (installed with SQL Server or available from Micrsoft.com).

Expand to reveal the tables and drag the Customer table onto the DataClasses1.dbml Designer workspace.Note that this may mean right clicking on the Data Connections and choosing Add Connection. In the Choose Data Source dialog you'll choose Microsoft Sql Server , leave the other options as is, and click Continue That will open the Add Connection dialog, where you will enter the name of the server on which you loaded the AdventureworksLT database (if you didn't load that database, you can pause here, download the file from Microsoft and load it to your local machine). If you loaded it to your local machine, type in localhost and choose Windows Authentication or Sql Server Authentication (if you are not sure, choose the former).

When you do this, you may receive the warning.Click OK

Make the resulting LINQ class Serializable

While a LINQ class will be generated for you corresponding to the Customer table the default is for that class not to be serializable, but for it to usable in a web service we need to change that.. Click on the design surface to bring up the properties of the entire class, and set the Serilaization mode from None to Unidirectional

Create the Web Service

You created the LINQ class (though not the query) first so that the web service (and Intellisense) will know about the Customer class and its members. With that in place we can ask Visual Studio 2008 to help create the Web Service. Right click on the web project and choose Add New and from the templates choose WCF Service,

The result is the creation of three new files that hold the service contract for your WCF web service,

Open the first file, IService1.cs which contains the contract that was created by Visual Studio 2008.

public interface IService1
{
[OperationContract]
void DoWork();
}

We can replace this "dummy" contract with whatever contract we want our web service to provide. For the purposes of this tutorial we want to contract that our web service will return a list of Customer objects given a string that represents the beginning of a customer's last name.Thus, we'll modify the method from returning void to returning List

.. However, as soon as you start to change the return type Intellisense is able to pop up to help you, specifically because we created this type in the LINQ class we defined earlier.

The convention for a method that returns a set of foo given a bar is to name it GetFoosByBar. Thus, we'll name this GetCustomersByLastName.

public interface IService1
{
[OperationContract]
List<Customer> GetCustomersByLastName(string lastName);
}

Having changed the contract in the interface, you must be sure to change the implementation in the .cs file. But why work so hard? When you get to the cs file, just click on the interface and a smart tag will appear. Open the tag and it will offer to create the implementation skeleton for you!Throw away the DoWork method and fill in the GetCustomersByLastName with the LINQ query,

public class Service1 : IService1
{

     #region IService1 Members

     public List<Customer> GetCustomersByLastName(string lastName)
     {
         DataClasses1DataContext db = new DataClasses1DataContext();
         var matchingCustomers = from cust in db.Customers
                                  where cust.LastName.StartsWith(lastName)
                                 select cust;
         return matchingCustomers.ToList();
     }

     #endregion
}

WCF uses wsHttpBinding as its default binding, in the Web.config file,

Silverlight, however, supports only basic binding (SOAP 1.1, etc.), so you will need to change the binding accordingly,

Creating the Silverlight Application The next step is to create the Silverlight Application that will interact with this web service. To do so, right-click on the references in the Silverlight project and choose Add Service Reference.When the Add Service Reference comes up click on Discover and choose Services in Solution. The service you created will be found. Before clicking OK notice that by clicking on the Service, the operation you created (GetCustoemrByLastName is discovered. Clicking OK adds the service to your project. You will access the Web Service (and its method) through this reference. Creating the XAML In the Page.xaml I'll create a very simple UI that will consist of a top row to enter the user's last name and a bottom row to display the results. To start, I'll layout the Grid's rows and columns, <Grid x:Name="LayoutRoot" Background="White" ShowGridLines="True">
   <Grid.RowDefinitions>
        <RowDefinition Height="10" /> 
        <RowDefinition Height="50" /> 
        <RowDefinition Height="*" /> 
        <RowDefinition Height="10" /> 
    </Grid.RowDefinitions>
    <Grid.ColumnDefinitions>
        <ColumnDefinition Width="10" /> 
        <ColumnDefinition Width="*" /> 
        <ColumnDefinition Width="10" /> 
    </Grid.ColumnDefinitions>
</Grid>

Placing controls in the top row

Finally, drag a DataGrid from the Toolbox onto the XAML.

<my:DataGrid x:Name="theDataGrid" AlternatingRowBackground="Beige" AutoGenerateColumns="True" Width="700" Height="500" Grid.Row="2" Grid.Column="1" CanUserResizeColumns="True" /><my:DataGrid x:Name="theDataGrid" AlternatingRowBackground="Beige" AutoGenerateColumns="True" Width="700" Height="500" Grid.Row="2" Grid.Column="1" CanUserResizeColumns="True" />

You'll notice that it is given the prefix my and that a new namespace is declared to support it,

xmlns:my="clr-namespace:System.Windows.Controls;assembly=System.windows.
Controls.Data"

Download code for this tutorial