Binding SQL Compact data to WPF in less than 100 lines of code

In this walkthrough, I will show how you can bind a WPF Listview to data from a SQL Compact database table using LINQ to SQL, and also show how persist changes to the list in the SQL Compact database. I am using Visual Studio 2008 SP1 and SQL Compact 3.5 SP1.

Start by creating a new C# WPF project, called SQLCeWPF. Do not create a directory for the solution. You should now have a folder like this:

 

Then copy the 3.5 Northwind.sdf from %ProgramFiles%\C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples to the project folder (in my case c:\projects\SQLCeWPF).

Then generate the LINQ to SQL dbml file:

1. Open a Visual Studio 2008 Command Prompt (shortcut under Programs, VS 2008, VS Tools)

2. Make the project directory folder the current folder:

cd c:\projects\sqlcewpf

and run this SQLMetal command:

sqlmetal /dbml:Northwind.dbml Northwind.sdf

and close the command window.

This will generate a LINQ to SQL dbml file in the project folder. Now include this file in your project, select Show All Files in Solution Explorer and include the dbml file (do NOT include the sdf file).

In the WPF Designer, add a listbox, a textbox and 3 buttons to the form. Rename the buttons from buttonx to add, delete and save. The form now looks like so in the designer:

Add event handlers for the 3 buttons in the code behind file by double clicking each button.

Now add a class file to the project, name it ObservableShippers.cs. This class connects the UI to the LINQ to SQL generated datacontext, and persists changes to the database.

using System;
using System.Linq;
using System.Collections.ObjectModel;
using System.Data.Linq;

namespace SQLCeWPF
{
    public class ObservableShippers : ObservableCollection<Shippers>
    {
        private Northwind dataDC;
        private bool ignoreEvents;

        public ObservableShippers()
        {
            this.ignoreEvents = true;
            this.dataDC = new Northwind("Data Source=C:\\Projects\\SQLCeWPF\\Northwind.sdf");
            var shipperList = from shipper in dataDC.Shippers 
                              orderby shipper.CompanyName
                              select shipper;
            // Cannot add multiple items to ObservableCollection in single step :-(
            foreach (Shippers shipper in shipperList)
            {
                this.Add(shipper);
            }
            this.ignoreEvents = false;
        }
        
        protected override void OnCollectionChanged(System.Collections.Specialized.NotifyCollectionChangedEventArgs e)
        {
            if (!ignoreEvents)
            {
                switch (e.Action)
                {
                    case System.Collections.Specialized.NotifyCollectionChangedAction.Add:
                        foreach (Shippers shipper in e.NewItems)
                        {
                            dataDC.Shippers.InsertOnSubmit(shipper);
                        }
                        break;
                    case System.Collections.Specialized.NotifyCollectionChangedAction.Remove:
                        foreach (Shippers shipper in e.OldItems)
                        {
                            dataDC.Shippers.DeleteOnSubmit(shipper);
                        }
                        break;
                    case System.Collections.Specialized.NotifyCollectionChangedAction.Replace:
                        break;
                    default:
                        break;
                }
            }
            base.OnCollectionChanged(e);
        }

        public void Save()
        {
            if (this.dataDC != null)
            {
                this.dataDC.SubmitChanges();
            }
        }
    }
}
 

This class inherits from ObservableCollection, which exposes the OnCollectionChanged event. Handling this events allows synchronization with the LINQ to SQL DataContext.

Now bind the WPF form to this collection like so, in the Window1.xaml.cs file (add the lines in bold):

private ObservableShippers shippers;

public Window1()

{


    InitializeComponent();


    ShowShippers();


}

private void ShowShippers()

{


      shippers = new ObservableShippers();


       this.listBox1.ItemsSource = shippers;


}

Press F5 to run the app, and it should look something like this:

(Don’t worry - We fix the Shipper name later).

Now add the button event handler code:

Add button:

if (textBox1.Text.Length > 0)

{


    Shippers newShipper = new Shippers();


    newShipper.CompanyName = textBox1.Text;


    shippers.Add(newShipper);


    textBox1.Text = "";


}

Delete button:

if (this.listBox1.SelectedItem != null)

           {


               shippers.Remove((Shipper)listBox1.SelectedItem);


           }

Save button:

shippers.Save();

ShowShippers();

Now to show the Supplier name in the list, override the Shippers class ToString() method. To do this, add  a new class file to the project, called Shippers.cs with the following content:

namespace SQLCeWPF

{


    public partial class Shippers


    {


        public override string ToString()


        {


            return this.CompanyName;


        }


    }


}

Now try to run the app. Shippers can be added and deleted from the Shippers table using WPF, and the project does not contain any reference to System.Data.SqlServerCe. All in less than 100 lines of (your own) code. The complete sample can be downloaded from here:

http://cid-47e59e4beb70472b.office.live.com/self.aspx/SQL%20Compact/SQLCeWPF.zip

Thanks to these 2 CodeProject articles for inspiration:

http://www.codeproject.com/KB/WPF/binding_in_linq-sql.aspx

and

http://www.codeproject.com/KB/linq/Compact_LINQ.aspx

Technorati Tags: SQLCE,SQL Compact