Using LINQ to SQL with SQLMetal & WPF

Introduction

I had an opportunity to evaluate LINQ for use in a production application and was surprisingly impressed.  Microsoft often tends to release new versions of Visual Studio & .NET framework that contain few real enhancements to improving the speed efficiency of developing a real world application.  The combination of LINQ, SQLMetal & WPF is a major improvement for developing data-driven application.

My next blog post will be related to using SQLMetal to generate serializable classes for use with Window Communications Foundation (WCF).  This will be very exciting to investigate and on the surface appears almost completely transparent to the rest of the client application and it's WPF bindings.

Database Setup

First you have to setup your database model before you can use SQLMetal to generate your ORM layer.  Create your tables, primary keys, indexes and foreign keys.  It's very important to setup your foreign keys as they will be used to generate your object relations in SQLMetal, making it super easy to reference related data from a parent record (Ex. Employee.Province.Code)

SQLMetal Generated ORM Layer

Now we can use SQLMetal to generate the ORM layer to use in the application.

SQLMetal

The SQLMetal file is included in the Windows SDK that is installed with Visual Studio. By default, the file is located at drive:\Program Files\Microsoft SDKs\Windows\vn.nn\bin. If you do not install Visual Studio, you can also get the SQLMetal file by downloading the Windows SDK

          For complete usage information of SQLMetal visit Microsoft's website. 


My initial command line usage of SQLMetal is pretty simple.

sqlmetal /server:localhost /database:employee /code:employee.cs    

Solution

My solution file consists of a WPF application and a Class Library. Add your SQLMetal generated class file to your Class Library and have you WPF application reference that Library.

Connecting to your database from inside your WPF application is as simple as creating your database object with a supplied connection string.

DB = new Employee ("server=localhost; database=employee; Trusted_Connection=True;");

Using LINQ

Now accessing your databases objects in code is super simple. The code sample below retrieves a list of all provinces in the database. The Provinces class is generated by SQLMetal and has properties that correspond with the underline table.

var provincelist = new List<Provinces>(from p in DB.Provinces select p)

WPF Bindings

While accessing the database from code is great, often data changes are performed by a user.  So displaying the data, accepting user changes and saving back to database is key.  This is where WPF bindings save a lot of time.

In your C# code, get a list of employee's

var employees = from c in db.Employee select c

Pass an employee object to a UserControl for displaying, set the UserControl.DataContext property to the employee object returns from the LINQ call.

this.DataContext = employee;

The XAML below showing binding controls on a UserControl to the employee object.

<TextBox Text="{Binding Path=First}" Height="23" HorizontalAlignment="Left" Margin="0,10,5,0" VerticalAlignment="Top" MinWidth="150" Width="auto" />

<ComboBox Name="provList" DisplayMemberPath="Code" ItemsSource="{Binding Source={x:Static list:GlobalList.ProvinceList}}" SelectedItem="{Binding Path=Provinces}" Width="75" Height="23" HorizontalAlignment="Left" Margin="5,0,0,0" VerticalAlignment="Top"/>

A Text box to display the employee's first name is pretty easy, just bind the Text property to the First property on the employee object.

The combo box is was a little troublesome at first because I was setting the Employee.Province (int) using SelectedValuePath.  While this was correctly setting the Employee object I ran into a LINQ issues.

The member 'X' was changed to be inconsistent with the association member 'Y'

This was because the Employee.Provinces (Class) object was still null even though I set the foreign key value.  The solution was to set the SelectedItem and let LINQ take care of synchronizing the foreign key value. 

This was also true for ComboBox columns inside DataGrid's, I used the following XAML to correctly bind my DataGrid drop-down lists.


<DataGridComboBoxColumn 
Header="WorkingType"
DisplayMemberPath="Name"
SelectedItemBinding="{
Binding Path=WorkTypes, 
Mode=TwoWay, 
UpdateSourceTrigger=PropertyChanged}">

    <DataGridComboBoxColumn.ElementStyle>
        <Style TargetType="{x:Type ComboBox}">
            <Setter Property="ItemsSource" 
Value="{Binding Source={x:Static list:GlobalList.WorkTypesList}}" />
        </Style>
    </DataGridComboBoxColumn.ElementStyle>
    <DataGridComboBoxColumn.EditingElementStyle>
        <Style TargetType="{x:Type ComboBox}">
            <Setter Property="ItemsSource" 
Value="{Binding Source={x:Static list:GlobalList.WorkTypesList}}" />
        </Style>
    </DataGridComboBoxColumn.EditingElementStyle>
    
</DataGridComboBoxColumn>

Saving Data

Now for the easy part, saving the data back to the database after the user has altered it using the WPF controls.

This one statement applied all inserts, updates and deletes back to the database.

DB.SubmitChanges();

So the above handles querying data and editing it, but what about insert, this is also very easy.  Add a new button to your code and call the following function.


            employee = new Employee();
            DB.Employee.InsertOnSubmit(employee);
            UserControl.DataContext = employee


This code add a new employee, allows the user to edit it and when DB.SubmitChanges() is called, the record will be inserted. RemoveOnSubmit is also available for deletes.

Conclusion

Microsoft appears to have a real winning combination here, just imagine its usage with WCF. Creating a responsive data driven application for clients is fast, stable and contains little code.  The biggest challenge so far seem to be using the correct WPF bindings for the LINQ generated objects.