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.