Home > Uncategorized > SQLite and .NET

SQLite and .NET

Many applications need to persist data and databases are, well, kind of built for this this task. However databases tend to feel so heavy, so… enterprise (and not in the good sense of the word) that they tend to be avoided even when they should make life easier.  SQLite is a light weight database that tries to keep things simple. Also it is free and it can be used from .net using System.Data.SQLite. I will describe one way to use it along with the entity framework. It still involves a few more steps than I would wish but what can you do? (It is assumed that System.Data.SQLite is installed).

Start by opening visual studio and create a new c# console application. We will now create a database file for our application database. Open up the server explorer window and select “add new connection…”.

createsqlitedb

Click continue.

createconnection

Click “new…” and browse to the visual studio project folder and enter a filename for the database file. Click ok to finish. Now we have a database file in our project folder. However, the database is still empty so we add a table. Anything will do but for this example it will be a person data table. Use the server explorer to add a table and the following columns.:

tabledata

For the sample it is also useful to add some data to the table.

rows

The next step is to create a typed interface to our database using the entity framework. In the data menu in visual studio, select “add new datasource…” and follow the steps below.

ds_step1 ds_step2 ds_step3 ds_step4 ds_step5

Since we want the database file to be a part of our application we add the database file to the project and set build action to content and select “copy if newer”.

adddbfile

We also need to edit the connection string in App.Config. Change the absolute path to a relative for the database file. Ok, now our work have payed off and we can start accessing our database using the typed interface provided by the entity framework. In our main method we can do the following:

 

namespace SqLiteDataTest

{

    class Program

    {

        static void Main(string[] args)

        {

            var context = new AppDatabaseEntities();

            foreach (Person person in context.Person)

            {

                Console.WriteLine("{0}, {1}, {2}", person.Id, person.Name, person.Age);

            }

        }

    }

}

 

You may download the source code here.

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: