SQLite EntityFramework 6 Tutorial

Hello,

UPDATE 13/10/2015/
I made another post about MVVM and EntityFramework. After reading this, you should really check that one out!
It’s really worth it to use MVVM 🙂
Link to another post: Click Here

I’ll explain the basics to get SQLite working with EntityFramework 6. It’s a straight forwarded tutorial / explanation. I will not tell you everything about EF (there are a lot of tuturials on the web). Instead I’ll show you the most basic example to get EF working with SQLite, after all it wasn’t that easy!

After my first post (SQLite CollectionViewSource) someone asked me how to add users without the datagrid on a “clean” way.
I didn’t know the answer on that. I searched if there was a way to automatically generate an ADD query with the CollectionViewSource. The only thing I found was that the CollectionViewSource is for viewing data not for adding…
So the only way that I know to solve this problem was to write an ADD query yourself. It’s not that big of a problem but it’s not that nice as with the datagrid!

So isn’t there really a better solution?
Ofcourse there is 😀

You could use an ORM (Object Relation Mapping), this part in your project takes care of the SQL side. It converts your (SQLite, …) database in “C# Language” a.k.a. Object Oriented. A small example: A database is actually the same as a list of objects. And that is exact the thing that an ORM does…
The ORM will also handle the manipulation you did with that list. If you add an object to the list, the ORM will execute the ADD query. If you change an object in the list, the ORM will execute the UPDATE query.

So TL;DR:  The ORM will take care of the queries 🙂

First step (Installation):

Make sure you added the last version of SQLite to your Visual Studio Project with the NUGET manager.
All the other packages should also be installed (normally they install automatically with SQLite…).

Remember the version number. Normally (if you have the latest version) this version number is the same you I’ll find on the download page:
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

This page is a total mess! But you have to search for the x86 version and the .NET framework version you are targetting in your project. Last you need to download the bundle version!
(There is a text next to it that indicated the correct one: “This is the only setup package that is capable of installing the design-time components for Visual Studio 2013.”)

One last note, you really need the x86 version! Even if you use the x64 version of Visual Studio!!!

In my case I needed this one: sqlite-netFx451-setup-bundle-x86-2013-1.0.97.0.exe

If you installed this be sure to restart Visual Studio! Also rebuild your project!

Second step (Add Data Model):

Now we need to add the Entity Data Model. This will contain the necessary code you need to talk OO with your SQL(ite) Database.

You need to add a new file. As template choose ADO.NET Entity Data Model. The next screen should open:

Because we already created our SQLite database we need to make a design from an existing database (the first option).
There is also an option to use Code First with EF. If you use Code First you start from the beginning with OO. A.k.a. you will first create your classes and etc. EF will create the correct database than. I’m not going to use this…

This should be the next screen you see! Add a new connection.

The default setting will use a Microsoft SQL server. This is not what we need! So click change.

So the tricky part starts. If you don’t see System.Data.SQLite Database File you installed the wrong bundle from SQLite!
But first check which .NET version you target. The default setting in VS 2013 is .NET 4.5. I downloaded the .NET 4.5.1 version of the SQLite software. So don’t forget to change this or install the correct version for your needs!
If this isn’t the problem make sure you have a x86 version! Don’t forget to remove previous installations of the SQLite software and to restart VS every time you (re)install the SQLite software!

If you click on OK you can choose where your SQLite database is located. The rest of the settings can normally keep the default value. At last try to connect to the database, you should be able to do that @ this point!

Click OK and check if the checkbox for including the connection in App.Config is enabled (this is default). Click again on next. This screen will let you choose the information you want to add in the EF Model. In my case I want the “Schoolresultaten” in my model! Here you can also change the name for your model. For simplicity I’ll keep this unchanged.

My first try to do this failed because I was using an old version of the SQLite software. So please make sure you have the latest version and that this version is also the same version as the SQLite package you added with NuGet!

When you now click on finished, the model will be generated! You’ll see your model in your Solution Explorer! In this model there is a lot code generated for you. I’ll not explain all of this…
But somewhere there should be a .cs file with the same name as your table name in SQLite! In this file there is the class that should have the same fields as your table!

Third step (UI + The first code behind):

I used exactly the same UI as in my previous post! But there is a nice way to add the datagrid to your UI.
Open Datasources (View -> Other Windows -> Data Sources)  and click add one.

Choose Object

Now you can chose which class(es) is your “Data Source”. In my case the “Schoolresultaten” class. If you can’t see your class please rebuild your solution!

Than click finish.
If you get an error about that the file is already used, please rebuild your solution!

If everything is okay your class is added as Date Source. Now you can easily add the datagrid, make sure your on your Window than click the arrow next to your class. Now you can choose different types of presenting your data. I included the DataGrid view and the details. You will see that the CollectinViewSource already is created for you!

So now code behind 🙂

First we will search for the CollectionViewSource that is created in our UI. And than we will fill it with data.

MyDatabaseEntities _entities;
CollectionViewSource _schoolresultatenViewSource;

public MainWindow()
{
    InitializeComponent();
    _schoolresultatenViewSource = ((CollectionViewSource)(this.FindResource("schoolresultatenViewSource")));
    Refresh();
}

So the Refresh() method will get the data. First we check if there isn’t already data filled (we do this so we can reuse this method). If there is already data filled we dispose the object.

Next we make a entity. This is a class that holds all the data. In our case is only has one list of Schoolresultaten. This class was automatically made by EF. If you want you can check this class, it’s in Model.Context.tt!

We will load the data. In the background the ORM will now execute a SQL query, but as programmer you now don’t have to worry about queries! To make sure you can load the data you have to add using System.Data.Entity;

Than we will add the the List as datacontext to our CollectionViewSource. If this works we basically are done !!! But it didn’t go that smoothly…

private void Refresh()
{
    if (_entities != null)
        _entities.Dispose();

    _entities = new MyDatabaseEntities();
    _entities.Schoolresultaten.Load();
    _schoolresultatenViewSource.Source = _entities.Schoolresultaten.Local;
}

But yeah, I couldn’t get it working. I always got this fantastic error:
Unable to determine the provider name for provider factory of type ‘System.Data.SQLite.SQLiteFactory’. Make sure that the ADO.NET provider is installed or registered in the application config

After a lot of searching I found that the automatic creating of App.config is not correct. This is a bug…
(I hope it’s fixed when you read this :)) But now how to fix it? Open your App.config and add this line in the DbProviderFactories:

<add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

My hole App.Config file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1" />
  </startup>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
  </system.data>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
  <connectionStrings>
    <add name="MyDatabaseEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SQLite.EF6;provider connection string='data source=&quot;F:\Dropbox\AP Hogeschool\Semester 4\Programmeren\VoorbereidingExamen\SQLiteEntityFramework\MyDatabase.sqlite&quot;'" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

After I added that line every thing works 🙂

Fourth step (Adding functionality):

The code for scrolling to the data is the same as my previous post. This is code is self-explanatory!

Next & Previous button:

private void ButtonNext_Click(object sender, RoutedEventArgs e)
{
    if (_schoolresultatenViewSource.View.CurrentPosition < _entities.Schoolresultaten.Local.Count() - 1) _schoolresultatenViewSource.View.MoveCurrentToNext(); 
} 

private void ButtonPrevious_Click(object sender, RoutedEventArgs e) { if (_schoolresultatenViewSource.View.CurrentPosition > 0)
        _schoolresultatenViewSource.View.MoveCurrentToPrevious();
}

Cancel button: Button to undo the changes, just reload the data (same method as above):

private void ButtonCancel_Click(object sender, RoutedEventArgs e)
{
    Refresh();
}

Add a student without the datagrid (this is the reason I made this tutorial in the first place :))
I wanted to use the same TextBoxes. I also didn’t want to read every field separately. So instead I used databinding.
I change the DataContext of my stackpanel where my textboxes are in to a new “Schoolresultaat”. Because we use two way databinding  the object will be updated! So I you press on the Save button and you filled everything in you just add that “Schoolresultaat” into our list from our entitity.

So how do we actually save the data know? In my past project I had to make an Update query or use the command builder to generate the update query. But know with EF as ORM we just have to execute an update() function. The ORM will totally take care of the SQL queries that are needed to make the changes! This is the main reason why I built this tutorial in the first place. I think it’s a very powerful way to communicate with the DB!

Schoolresultaten _addResult;

private void ButtonAdd_Click(object sender, RoutedEventArgs e)
{
    _addResult = new Schoolresultaten();
    StackPanelInfo.DataContext = _addResult;

    ButtonAdd.IsEnabled = false;
}

private void ButtonUpdate_Click(object sender, RoutedEventArgs e)
{
    if (_addResult != null)
    {
         _entities.Schoolresultaten.Add(_addResult);
    }

    _entities.SaveChanges();
    Refresh();
}

So the only thing left is the delete button:
Just remove that object from the list, simple as that 🙂

private void ButtonDelete_Click(object sender, RoutedEventArgs e)
{
    _entities.Schoolresultaten.Remove((Schoolresultaten) _schoolresultatenViewSource.View.CurrentItem);
    _entities.SaveChanges();
    Refresh();
}

Fifth step (Autoincrement):

The last thing I want to say, is something about auto increments. Is use an unique Id as primary key in my database. Ofcourse I want to automatically generate this id. Otherwise you always have to check if the number already exists…

In my SQLite DB the PK was auto incremented but when I added a new “Schoolresultaat” with the grid or the text fields, my program crashed because he always used 0 as PK. You still have to say to EF that the DB will take care of the ID! Normally this happens automatically. But I changed my DB after I already made my model!

You could do regenerate the model.Open the model browser (View -> Other Window -> Model Browser). Than right click on your model Update Model from Database. If this doesn’t solve your problem you could still change it manually:

Click on your model. You will see your fields. Search the one where you use auto increment. Open the properties of that field and change StoredGeneratedPattern to Identify!

That where my basic examples. Ofcourse you should add some validation to the data and etc.!
But know you can search for normal SQL EF tutorials if you have questions (there are a lot of them!)…
Hope you learnt something or found your answer!

So know you’re ready to advance to MVVM! I have a post about it, I also use this example!
MVVM EntityFramework

If you think this was to difficult but still want a nice way to implement SQLite in your C# program
Check my other post: SQLite CollectionViewSource!

If you have any questions or comments feel free to post them!
Thanks for reading!

Here is the link to my solution!: My Solution