Click here to Skip to main content
Click here to Skip to main content
Go to top

WPF and SQLite Database

, 3 Feb 2011
Rate this:
Please Sign up or sign in to vote.
Master-Detail binding in WPF to SQLite database
aplication_image.jpg

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:

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.

SQLite_select.jpg

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 “CustomerDataGrid, 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 “CustomersDatagrid and then filter the “OrdersDatagrid 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

  • 3rd February, 2011: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dimitris Nemtsov
Software Developer (Junior) webpixel.gr
Greece Greece
Dimitris is founder of www.webpixel.gr. His interests includes new generation applications, web development, Search Engine Optimization (SEO) and Web Marketing. (Amongst others, I also studied Chemical Engineering and piano)

Comments and Discussions

 
GeneralMy vote of 3 PinmemberJanHalfar23-Jul-14 6:29 
Questionhttp://sqlite.phxsoftware.com/ not found Pinmemberkiquenet.com31-Jan-14 5:54 
AnswerRe: http://sqlite.phxsoftware.com/ not found PinmemberDimitris Nemtsov31-Jan-14 6:58 
QuestionUpdates for .NET 4.5.1 ? Pinmemberkiquenet.com31-Jan-14 4:05 
AnswerRe: Updates for .NET 4.5.1 ? PinmemberDimitris Nemtsov31-Jan-14 7:00 
GeneralMy vote of 5 PinmemberSinisaRuzin28-Jun-12 5:34 
QuestionXamlParseException Occured [modified] PinmemberKrischu12-Oct-11 5:35 
AnswerRe: XamlParseException Occured PinmemberKrischu17-Oct-11 6:13 
GeneralRe: XamlParseException Occured Pinmemberkrishnakumartm21-Oct-11 2:43 
GeneralRe: XamlParseException Occured PinmemberKrischu21-Oct-11 3:55 
GeneralMy vote of 5 PinmemberBasarat Ali Syed3-Feb-11 15:21 
GeneralRe: My vote of 5 PinmemberDimitris Nemtsov18-May-11 2:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140921.1 | Last Updated 3 Feb 2011
Article Copyright 2011 by Dimitris Nemtsov
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid