SQLite in WPF with Entity Framework 6

The WPF code sample below demonstrates how to write applications that use SQLite as a database with Entity Framework 6 using code first approach.

Introduction
SQLite is the compact database system used by client applications to manage local data. SQLite is lightweight and easy to use.

Why SQLite
In SQLite, the database engine and the interface are combined into a single library and SQLite DB stores all the data in a single file. So this way, it becomes right choice for an application that requires a standalone & lightweight local database. There are also other reasons that makes sense to use SQLite in your application.

• Small memory footprint and a single library for deployment makes it ideal for embedded database applications.
• ACID-compliant means it is meeting all four criteria of ACID rule such as Atomicity, Consistency, Isolation, and Durability.
•  No need of additional database drivers or ODBC. Just include the library and database file and you are good to go.

Perquisites
If you prefer starting from scratch by creating new WPF Application project, Install following two NuGet packages. You can also download sample code at the end of this article to skip this setup. But I recommend starting from scratch so that you experience awesomeness of NuGet! The sample code is developed in Visual Studio 2012. Install following NuGet package using Package Manager Console in Visual Studio 2012.

1. EntityFramework 6.0.1
We need to use the forked version of EF6 from Brice's excellent post on this. The current official package of EntityFramework has breaking changes that prevents you to use it with SQLite.
PM> Install-Package System.Data.SQLite.Linq -Pre -Source https://www.myget.org/F/bricelam/


2. Download Northwind SQLite database
http://download.vive.net/Northwind.zip

Sample application
We will develop very simple application where we will have DataGrid control that we will populate from Northwind SQLite database using Entity Framework. We will create very simple model class of Employee and will bind collection of it to the DataGrid control. Let's quickly wire up this step by step.

1. In your newly created WPF Application project, add following markup in MainPage.xaml.

<Window x:Class="WPF_SQLite.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <DataGrid x:Name="dataGrid" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" />
    </Grid>
</Window>

Listing 1.0 – XAML markup for MainPage.xaml

Note that I have my project name WPF_SQLite in the Listing 1.0 and for you it can be different.

2. Now switch to code behind and following class that will act as data model.
    public class Employee
    {
         public int EmployeeID { get; set; }
        public string FirstName { get; set; }
         public string LastName { get; set; }
    }

Listing 1.1 – Employee class

As you see in Listing 1.1, Employee class has three properties where EmployeeID is primary key.

3. Now we will create DBContext class for our application. In Entity Framework, Context is the window to the database for all operations such as insert, update or delete.

     public class NorthwindContext : DbContext
    {
         public DbSet<Employee> Employees { get; set; }
    }

Listing 1.2 – NorthwindContext class

As per Listing 1.2, we are just creating DbSet of Employee that we will later bind to our WPF DataGrid.

4. Now it's time to add some code to MainWindow class as following.

    public partial class MainWindow : Window
    {
         public MainWindow()
        {
             InitializeComponent();
            this.Loaded += MainWindow_Loaded;
        }

        void MainWindow_Loaded(object sender, RoutedEventArgs e)
        {
            NorthwindContext context = new NorthwindContext();
            context.Employees.OrderBy(c => c.FirstName).Load();
             this.dataGrid.ItemsSource = context.Employees.Local;
         }
    }

Listing 1.3 – Code for loading employees from database and binding to DataGrid

Listing 1.3 shows code where in Loaded event of the MainWindow, we are creating the instance of NorthwindContext class and ordering the Employees using Linq. Note that we need to use Load method on DbSet to execute this query and need to use Local property of the Employees DbSet to bind it to the ItemsSource property of the DataGrid. Now we are all done from code point of view. It's time to quickly add Northwind SQLite database file and few configuration elements to App.config file of the application.

5. Download and extract Northwind SQLite database from the link given in Perquisites section. Create the new folder named DataFile in project and put the file there. Right click the database file and select Properties. From Properties pane, set Copy to Output Directory to Copy if newer.

6. In the App.config, inside configuration element, add connection string and register database provider for SQLite as following.

  <connectionStrings>
    <add name="NorthwindContext" connectionString="Data Source=.\DataFile\Northwind.sl3" providerName="System.Data.SQLite" />
  </connectionStrings>
   <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description="Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
</system.data>

Listing 1.4 – Connection string and database provider registration in App.config

Note that as shown in Listing 1.4, we are supplying relative path for Northwind.sl3 database. This is good as when you deploy, it would work without fail as long as you put the database file in DataFile folder.

7. Now at last, we need to register this provider for entity framework and also need to set default connection factory. So add following code in configuration section.

<entityFramework>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.SQLiteProviderServices, System.Data.SQLite.Linq, Version=2.0.88.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </providers>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
     </defaultConnectionFactory>
  </entityFramework>

Listing 1.5 – Entity framework providers and default connection factory

As per Listing 1.5, I have given fully qualified reference of System.Data.SQLite.Linq assembly using version and public key token. I am asked many times about getting correct public key token for the assembly to use it in such kind of configuration. I use dotPeek from Jetbrains to get all meta data as such. You can give this tool a try here.

After doing all these steps, we are good to press F5 and see our application in action! You will see screen as following.


Figure 1.0 – WPF application using SQLite with Entity framework 6 and Northwind database

The sample code is here.

By jay nanavati   Popularity  (17303 Views)