Portable databases (II): using SQLite with Entity Framework

On the previous post, we saw that it's possible to make use of ADO.NET to create an application that uses SQLite as database. Thereby, with a local database widely used, it's possible to radically enhance the portability of our application if it doesn't require the power of a "traditional" database engine. Anyway, using ADO.NET directly implies to leave aside all the progress implemented by Microsoft along these years in object-relational mapping topics.

Supposing we know how to use Entity Framework, why not apply it to SQLite and stopping code SQL manually. Let's see how to do that.

Installing the provider using NuGet

First of all, we must to install in our solution the SQLite Data Provider through NuGet. To do so, we must to go to Tools > Library Package Manager > Manage NuGet Packages for solution...

Let's choose the Online section and write SQLite inside the textbox located on the upper-right corner. After that, we push ENTER and we'll wait the search to be performed. Choose the first package: System.Data.SQLite (x86/x64).

Once the package has been downloaded, we'll select the project in which we want to install the provider.

Creating the Data Model

Next step will be creating the data model, the Entity Model. We perform right click over our project, select "Add element" and, in the Data section, we'll check the model we're looking for, assigning a name in the textbox.

We assume we've read the previous post, where the database was created. So, using that database, we check the Generate from database option. Of course, it's also possible to generate the model manually, but in this case, we'll let Visual Studio to create it for us.

Create a new connection pushing over New connection

As data source, we're going to select System.Data.SQLite Database File

Now, we search for the file which contains the database and push over OK

Finally, we select the tables to map. We have only one table, so we check it to be mapped.

The result will be the following:

We musn't to forget to modify the value of StoreGeneratedPattern indicating that the database must be the one which manages the identifiers of the records (remember: they're auto-incrementing). To do so, we'll change the value from None to Identify.

A little of code

I won't extend so much on the explanation of the code, because it's just standard LINQ to Entities. Anyway, I'll show the counterpart to the ADO.NET methods of the previous post. Let's begin with the SELECT clause.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
private void searchEF()
{
    // We remove the RowEnter handler to avoid being triggered when
    // it performs the search.
    dataGrid.RowEnter -= dataGrid_RowEnter;
 
    // Instantiate the context and load a user list
    var context = new clientsEntities();
    var users = from user in context.User
                select user;
 
    // Load the DataGrid with the data
    dataGrid.DataSource = users.ToList();
 
    // Restore the event handler
    dataGrid.RowEnter += dataGrid_RowEnter;
}

Insert and update:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
private void saveEF()
{
    var context = new clientsEntities();
 
     // If the ID textbox is empty, it will be an INSERT
    if (String.IsNullOrEmpty(txtId.Text))
    {
        // Create a new user and add it, saving then the changes.
        User peter = new User { Name = txtName.Text, Surname = txtSurname.Text };
        context.User.Add(peter);
        context.SaveChanges();
    }
 
    // Otherwise, it will be an UPDATE
    else
    {
        // Retrieve the user whose ID matches with the ID textbox
        User u = context.User.Where(user => user.UserId == int.Parse(txtId.Text)).First();
 
        // Modify the content
        u.Name = txtName.Text;
        u.Surname = txtSurname.Text;
 
        // Save changes
        context.SaveChanges();
    }
}

Delete:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
private void deleteEF()
{
    var context = new clientsEntities();
 
    if (!String.IsNullOrEmpty(txtId.Text))
    {
        // Retrieve the user whose ID matches with the ID textbox
        int id = int.Parse(txtId.Text);
        User u = context.User.Where(user => user.UserId == id).First();
 
        // Delete and save changes
        context.User.Remove(u);
        context.SaveChanges();
    }
}

The result will be, as we can imagine, similar to the previous post execution. The UI hasn't changed, only the data access layer, so it must be transparent (as actually is) to the user.

You can download the full example (including the ADO.NET methods seen in the previous post) from this link.

You can find the Spanish version of this post here.

Print | posted on Sunday, December 22, 2013 4:37 PM

Feedback

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Comment:
Verification: