John Russell Plant - LINQ to SQL Part 1

Tuesday, August 21, 2007

I'm sure everyone so far has at least heard of LINQ. If you haven't, it is Microsoft's new technology for Language Integrated Querying. LINQ is part of the upcoming 3.5 .Net Framework which will be part of Visual Studio 2008 ( codename Orcas ). LINQ will be able to query in memory objects such as Generics or datasets in addition to external items like XML documents and SQL databases. As of this post, Visual Studio 2008 is currently in Beta 2 and available for download from Microsoft.

The flavors of LINQ include:

LINQ to SQL
LINQ to Objects
LINQ to Datasets
LINQ to XML

This will be a LINQ to SQL example so I will be using the standard Northwind database for this article, you will need it installed onto your machine. If you will be using the SQL Express Edition I would also recommend installing SQL Management Studio Express. It will make the management of your database much easier.

The first thing we need to do is to create a DataContext for the our Northwind database. There are two ways to do this: 1. Use SQLMETAL to create a entity class if you need to model a complete database automatically. This way is also useful if you need have a constantly changing scheme since you can have SQLMETAL run each time you build your application using the Pre-Build Event. 2. The second way is to create a DataContext via the LINQ to SQL template under 'Add new Item to Project', but I won't be going into that way in this article.

Open a command prompt and go to your project directory and type:

SQLMETAL /SERVER:ORCASBETA2_VSTS\SQLEXPRESS /DATABASE:NORTHWIND /CODE:NWIND.CS

This will generate a class file that you can add to you project by right clicking Project->Add->Existing Items and selecting the nwind.cs for the file list. You will next need to add a reference to System.Data.Linq to your project and then your will be ready to add your code. The following code queries the customer table for all records and adds the CustomerName to a listbox on the screen.

   1: namespace Linq001

   2: {

   3:     /// <summary>

   4:     /// Interaction logic for Window1.xaml

   5:     /// </summary>

   6:     public partial class Window1 : Window

   7:     {

   8:         public Window1()

   9:         {

  10:             InitializeComponent();

  11:         }

12:

  13:         private void LoadButton_Click(object sender, RoutedEventArgs e)

  14:         {

15:

  16:             Northwind nw = new Northwind(@Properties.Settings.Default.NorthwindConnectionString );

17:

  18:             var CustomerList = from Customers in nw.Customers

  19:                                select Customers;

20:

  21:             foreach(Customers c in CustomerList)

  22:             {

  23:                 CustomerListBox.Items.Add(c.CompanyName);

  24:             }

25:

  26:         }

  27:     }

  28: }

As you can see, this process is pretty straight forward. Notice the reverse SQL statement order. The from clause comes before the select statement. The result is put into a anonymous type that can be iterated through with a for each loop and added to the listbox.