LINQ To SQL

A number of folk have written to me in response to my 3rd tutorial asking that I spend some time focusing on how to obtain data from a SQL database. This is the topic for tutorial #4, to be released on April 1.

A few interesting issues arose when writing that tutorial, and so I thought I'd start to discuss them even before the tutorial appears.

Architecture

The most straightforward way to deal with using Silverlight to present data from a Database is to create a Web Service whose job is to query the database in response to data coming from Silverlight and to return data in a form that your Silverlight application can use.

Thus, the various skills that the tutorial must bring together include

1. Creating a simple WCF Web Service
2. Using LINQ to Sql to create the Queries
3. Making sure the class is serializable
4. Creating the Service
5. Modifying the contract
6. Implementing the contract
7. Freezing the port
8. Setting the Silverlight App to see the Web Service
9. Calling a method in the service and getting back data
10. Displaying the data
11. Adding a DataGrid
12. Understanding DataGrid properties
13. Binding data to DataGrids

While all of this is covered in the tutorial, there is plenty to dive into further, and I'll be doing so in this blog over the next few weeks.

Using LINQ to Retrieve Data

The tutorial assumes that you are somewhat familiar with LINQ. This new and powerful aspect of VB and C# is likely to be central to any Silverlight applications that interact with data, and you will want to take the time to learn about it (sooner or later). A good way to start is with ScottGu's tutorial – if you find you want more you might want to take a look at a good tutorial on VB 9 or on C# 3 (see some suggested titles at the bottom of this entry)

Source Code

To get started, open a new project of type WPF Application (!) — We're going to do this in WPF to avoid dealing with web services for now. The point of this entry is just to look at the LINQ part.

Visual Studio will set up your WPF application (look familiar? Isn't it cool how close WPF and Silverlight are? Don't get distracted! Pay attention in class!)

Right click on the project and choose Add… When the Add New Item window opens, curse (again) that the Templates are not in alphabetical order and click on Linq To SQL Classes. Accept the default name (DataClasses1.dbml) and click Add. That will add DataClasses1.dbml to your project and open the Object Relational Designer  which is just too cool a name.

Open your Server explorer and create a connection to your favorite test database (which in my case is AdventureWorksLT (and I used to know why I preferred the LT version!) and open the tables and drag one (I use Customers) onto the design surface).

When you drop the customers table onto the Object Relational Designer surface a class is created for you in your project. Very cool.

Create the UI

To get all this working, we'll create the same UI in WPF that we'll later create for Silverlight. Simple, ugly, but workable. Open Window1.xaml and notice the grid. To save time, I'll just replace that with the grid from my Silverlight project (!)

It all just works. Nice.  Notice that we've added three controls in the stack panel: a prompt, a text box and a button. The user enters text and presses the button and we search based on the text. All we need is a way to display the results.

WPF doesn't have a DataGrid so let's throw in a simple ListView. Since this is a Silverlight site and not a WPF site, let's use the code right out of the documentation to show a few columns from the customer table,

What is missing from this ListView is an ItemSource property — the source that will serve for the bindings. That will be added dynamically based on the user's input.

Event Handlers

Setting up the event handlers in WPF is exactly like setting them up in Silverlight; which makes this very easy. I've added a Loaded event and an event for clicking on the Search button.

The job of Search_Click is to gather the data entered by the user and create a LINQ statement that searches the Customers table for any customer whose name begins with the letters entered and return a list of those records, which can be used as the source for our ListView,

We begin by getting a reference to the Linq classes created earlier. We then create a standard LINQ query passing in the text from the LastName control and receiving the results into a type-safe object marked var. This is an extension of C# 3.0 and will hold an inferred type of IEnumerable<Customer> which is exactly what is needed by the ItemSource property of the CustList ListView.

The user enters all or part of a last name, clicks the button, the LINQ query is executed and the results are displayed in the ListView,

Resources for Learning Linq