1. Technology

How to use SQLite from a C# Application

By

1 of 2

How to use SQLite from a C# Application
Firefox SQLite Manager

In this tutorial learn how to download, install and use SQLite as an embedded database in your C# applications. If you've wanted a small compact, database (just one file) that you can create multiple tables then this tutorial will show you how to.

SQLite is an excellent database and there are some very good free admin tools, The one I use is an Add-on for the Firefox browser. If you have Firefox installed (if not visit http://GetFireFox.com), then on the pull down menu at the top, select add-ons then extensions and if it's not there type in SQLite at the search bar. Or visit sqlite-manager.

Once that's installed and Firefox restarted, access it from the Firefox Web Developer menu off the main Firefox menu. Once it's open as in the screenshot above, from the Database menu create a new Database. I called mine About. The database will be stored in the About.sqlite file, in whatever folder you select. You'll see the Window caption has the path to the file.

Now on the Table menu, click Create Table. We'll create a simple table, call it friends (type it in in the box on the top). Next we'll define a few columns and populate it from a csv file afterwards. I always call the first column idxxxx so make the first one idfriend, select INTEGER in the Data Type combo and click the Primary Key> and Unique? check boxes.

I added three more columns, firstname and lastname that are type VARCHAR and age that is INTEGER. Now click OK and it will create the table after asking are you sure. It will show the SQL which should look something like this.

CREATE TABLE "main"."friends" ("idfriend" INTEGER, "firstname"
VARCHAR, "lastname" VARCHAR, "age" INTEGER)

Click the Yes button to create the table and you should see it on the left hand side under Tables(1).You can modify this definition at any time by selecting Structure on the tabs on the right hand side of the SQLite-manager window. You can select any column and right click Edit Column/Drop Column or add a new column at the bottom and click the Add Column button.

Prepare and Import Data

I typically use Excel to do this. Create a spreadsheet with columns idfriend, firstname, lastname and age and populate a few rows, making sure that the values in idfriend are unique. Now save it out as a csv. Here's an example that you can cut and paste into a csv file. A csv file is just a text file with data in a comma delimited format.

idfriend,firstname,lastname,age
0,David ,Bolton,45
1,Fred,Bloggs,70
2,Simon,Pea,32

On the Database menu, click import, then click Select File, browse to the folder and select the file then click open in the dialog. Now make sure that you enter the name of the table (friends) on the CSV tab, that the First row contains column names is ticked, and Fields Enclosed by is set to none then click ok. It will ask you to click OK before importing so click it then again for Are you sure you want.... and if all went well you'll have three rows imported into the friends table.

Click the Execute SQL and change tablename in SELECT * from tablename to friends and then click the Run SQL button. You should see your data!

Accessing the SQLite Database from a C# Program

Now it's time to setup Visual C# 2010 Express or Visual Studio 2010 (I think this works for the 2008 versions as well). First you need to install the ADO driver. You'll find several depending on 32/64 bit, and Framework (3/.5/4.0) PC on the System.Data.SQLite download page.

Having installed that, it's time to create a blank C# Winforms project. Once that's done and opened, in the Solution Explorer add a reference to Ssystem.Data.SQLite. View the Solution Explorer (it's on the View Menu if not open), now right click on References and click Add Reference. In the Add Reference dialog that opens, click the Browse tab and browse to

C:\Program Files\System.Data.SQLite\2010\bin

It may be in C:\Program Files (x86)\System.Data.SQLite\2010\bin depending on if you are running 64 bit or 32 bit Windows. If you've installed it already it will be in there and in the bin folder you should see System.Data.SQLite.dll. Click ok to select it (in the Add Reference dialog). and it should popup in the list of References. You need to add this for any future SQLite/C# projects you create.


Continued on page two.

  1. About.com
  2. Technology
  3. C / C++ / C#
  4. C# / C Sharp
  5. How To Do Things in C#
  6. How to use SQLite from a C# Application

©2014 About.com. All rights reserved.