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.

JustLinq

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).

DraggingCustomers

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 (!)

Grid

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,

ListView

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.

EventHandlers

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,

LinqStatements

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,

ProgramRunning

Resources for Learning Linq

Programming C# 3.0 (Programming)
by Jesse Liberty, Donald Xie

Read more about this title…

C# 3.0 Programming Language,The: The Annotated Edition (3rd Edition) (Microsoft .NET Development Series)
by Anders Hejlsberg, Mads Torgersen, Scott Wiltamuth, Peter Golde

Read more about this title…

Pro C# with .NET 3.0, Special Edition (Pro)
by Andrew Troelsen

Read more about this title…

C# 3.0 in a Nutshell: A Desktop Quick Reference (In a Nutshell (O'Reilly))
by Joseph Albahari, Ben Albahari

Read more about this title…

Microsoft Visual Basic 2008 Step by Step
by Michael Halvorson

Read more about this title…

Visual Basic 2008 Programmer's Reference (Programmer to Programmer)
by Rod Stephens

Read more about this title…

About Jesse Liberty

Jesse Liberty is an independent consultant and programmer with three decades of experience writing and delivering software projects. He is the author of 2 dozen books and multiple Pluralsight courses, and has been a Senior Technical Evangelist for Microsoft, a Distinguished Software Engineer for AT&T, a VP for Information Services for Citibank and a Software Architect for PBS. He is a Xamarin Certified Mobile Developer and a Xamarin MVP, Microsoft MVP and Telerik MVP.
This entry was posted in z Silverlight Archives and tagged . Bookmark the permalink.

One Response to LINQ To SQL

  1. So hard, so many operations, so stupid and very very slow. A year ago I spend my time learning Silverlight and I fail, my salvation was ASP.NET Forms, today, a year later, again, I try Silverlight and a grid, with LinqtoSQL and Ria Services spend 34 seconds to load 100 rows of text. In a same application with asp.net the results come in one second. I think the language was failed. But is true XAML is great, controls are beautiful, but for conclusion is useless.

Leave a Reply

Your email address will not be published. Required fields are marked *