WPF and SQLite Database

• Download demo - 446.46 KB 

Introduction

After reading a lot of a good stuff about SQLite, I’ve decided to setup a simple WPF project which will handle Master-Detail relationship between two DataGrids and the SQLite database under the hood. This is a beginner article, so I am not going to dive into database layers, LINQ, etc.

Requirements

To build the article project, you will need the following:

• Visual Studio 2010
• SQLite.NET
• SQLite Northwind database
• If you are using .NET 3.5, download the WPF Toolkit

Install the ADO.NET 2.0 Provider for SQLite, and make sure to install SQLite Designer (which is included in the package); this way, you can manage the database directly in your VS 2010 environment. Unzip and copy the SQLite Northwind database, preferably into your project folder.

Getting Started

Provided that you have successfully installed the above packages, create a WPF project, name it as you wish, and “throw” two DataGrids on your main window (MainWindow.xaml). At this point, I am not interested in styling my application, rather focus on its functionality.

Before proceeding any further, set the DataGrid's AutoGenerateColumns property to true; this will automatically generate a column for each field. For this case, it is quite useful; however, if you plan to make a full blown application, this approach does not give much control over the generated column.

Let the fun begin! On the Data Source panel, click Add New Data Source. Select the Database, next Dataset and finally create New Connection. Change the data to SQLite Database file, browse for the file and finally check Test Connection. If everything is OK, you should have connected successfully. The next step is to create “NorthwindDataSet” to which we will be referring afterwards.

WPF C# Code Behind

Let’s add some code to our application. In our example, we are going to have two tables “Customers” and “Orders”. Each time the client click on “Customer” DataGrid, the detail DataGrid “Orders” is going to be updated automatically.

Enter the following code to your MainWindow.xaml.cs:

NorthwindDataSet ds;

// Private var
 
public MainWindow()
{
    InitializeComponent();

    //Create new DataSeta
    ds = new NorthwindDataSet();

    //Create table adapter for Customers
    NorthwindDataSetTableAdapters.CustomersTableAdapter customerAdapter = 
            new NorthwindDataSetTableAdapters.CustomersTableAdapter();

    //Populate adapter
    customerAdapter.Fill(ds.Customers);

    //Bind adapter to datagrid
    dataGrid1.ItemsSource = ds.Customers.DefaultView;

    //Populate the second datagrid "Orders"
    NorthwindDataSetTableAdapters.OrdersTableAdapter orderAdapter = 
          new NorthwindDataSetTableAdapters.OrdersTableAdapter();
    orderAdapter.Fill(ds.Orders);
    dataGrid2.ItemsSource = ds.Orders;
}

Next, double click on first datagrid “Customers”. The method “dataGrid1_SelectionChanged” should be created meaning that every time you change the selection, this method will be invoked. I must admit that I’ve searched a lot for an easy way to create master-detail relationship in WPF between DataGrid’s controls, but with no avail. So I came up with the following code:

private void dataGrid1_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    //Get selected Row
    DataRowView row = (DataRowView) dataGrid1.SelectedItem;

    //Get selected Row Cell base on which the datagrid will be changed
    string customerId = row.Row["CustomerID"].ToString();

    //Check if everything is OK
    if (customerId == null || customerId == string.Empty)
    {
        return ;
    }

    //Change view based on RowFilet
    DataView view = ds.Orders.DefaultView;
    view.RowFilter = string.Format("CustomerID='{0}'", customerId);
    
}

The idea is to find the selected “CustomerID” in “Customers” Datagrid and then filter the “Orders” Datagrid based on that selection. I think the above code is self-explanatory. The full code looks like this:

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
 
namespace SQLite_demo
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        NorthwindDataSet ds;
 
        public MainWindow()
        {
            InitializeComponent();
 
            //Create new DataSeta
            ds = new NorthwindDataSet();
 
            //Create table adapter for Customers
            NorthwindDataSetTableAdapters.CustomersTableAdapter customerAdapter = 
                      new NorthwindDataSetTableAdapters.CustomersTableAdapter();
 
            //Populate adapter
            customerAdapter.Fill(ds.Customers);
 
            //Bind adapter to datagrid
            dataGrid1.ItemsSource = ds.Customers.DefaultView;
 
            //Populate the second datagrid "Orders"
            NorthwindDataSetTableAdapters.OrdersTableAdapter orderAdapter = 
                     new NorthwindDataSetTableAdapters.OrdersTableAdapter();
            orderAdapter.Fill(ds.Orders);
            dataGrid2.ItemsSource = ds.Orders;
        }
 
        private void dataGrid1_SelectionChanged
		(object sender, SelectionChangedEventArgs e)
        {
            //Get selected Row
            DataRowView row = (DataRowView) dataGrid1.SelectedItem;
 
            //Get selected Row Cell base on which the datagrid will be changed
            string customerId = row.Row["CustomerID"].ToString();
 
            //Check if everything is OK
            if (customerId == null || customerId == string.Empty)
            {
                return ;
            }
 
            //Change view based on RowFilet
            DataView view = ds.Orders.DefaultView;
            view.RowFilter = string.Format("CustomerID='{0}'", customerId);
        }
    }
}

Addition Information

If you are developing against .??? 4 Framework, and you should do so, then it is necessary to add the following lines to your “app.config”.

<configuration>
    <configsections>      
    </configsections>
  <startup uselegacyv2runtimeactivationpolicy="true">
    <supportedruntime version="v4.0">
  </supportedruntime></startup>
</configuration>

This way, you will avoid nasty compatibility errors between .NET 4.0 and .NET 2.0. This is happening because ADO.NET 2.0 Provider for SQLite is built against version .NET 2.0. Another solution would be to download the source code and recompile it for .NET 4.0, I never tried it so it is totally up to you.

Conclusion

This is a basic introduction on using SQLite databases with WPF. With these two, you can give to your users an alternative of using non-Microsoft database with the same, or maybe greater performance.

History

• 3^rd February, 2011: Initial post