September 26, 2009 by Ivan Kristianto

[HowTo] Use SqLite ADO.NET with C#

SQLite SQLite is software library written in C that implement self-contained (very minimal support from external libraries), serverless (read/write process directly to database file), cross-platform (run in any Operating System), zero-configuration (no setup/installation needed), transactional (implement serializeable transaction) SQL database engine. SQLite is relatively small, approx 275 KB in size and single database file.

For me SQLite is better solution rather than use MS.Access for medium to small scale application, because it cross-platform and i don’t need to install Microsoft Office in client computer. And because it doesn’t need any configuration, just plug and play application and run anywhere. Such a convenient. And also SQLite support custom function and trigger function. In Ms.Access you need to write your custom function in VBA, but in SQLite you can write your custom function in your own language and later it will automatically bind to SQLite process.

Advantage of SQLite:
1. Zero-Configuration
2. Serverless
3. Single Database File
4. Stable Cross Platform Database File
5. Compact Size
6. Variable-length records
7. Free with Public Domain license

Disadvantage of SQLite:
1. In most case database size is restricted to 2GB
2. Not fully SQL92 compliant
3. Lock whole file while writing
4. No caching mechanism
5. Not very scalable

Next is i will show you how to use SQLite with C# programming language. To access SQLite in C# you need to use SQLite ADO.NET that you can download here. And now follow this instruction:
1. Open your Visual Studio 2005/2008 (I’m using 2008 express)
2. Create C# project, you can call it “SqliteTest”

3. Import the dlls from sqlite ADO.NET (System.Data.SQLite.dll)
4. Change the “Copy Local” property of System.Data.SQLite.dll to true, so this dll will be copied when compiled

5. Then Add new folder called Data, we are going to put the database file here.
6. Add the database file, then change the ”Copy To Output Directory” property to “Always”

7. Now select the form1 design and insert a DataGridView, adjust the properties as you like

8. Double click Form1 so you will go to text editor and in Form1_Load events
9. Add this code:

private void Form1_Load(object sender, EventArgs e)
{
SQLiteConnection ObjConnection = new SQLiteConnection("Data Source=Data/data.db3;");
SQLiteCommand ObjCommand = new SQLiteCommand("SELECT * FROM PERSON", ObjConnection);
ObjCommand.CommandType = CommandType.Text;
SQLiteDataAdapter ObjDataAdapter = new SQLiteDataAdapter(ObjCommand);
DataSet dataSet = new DataSet();
ObjDataAdapter.Fill(dataSet, "Person");
dataGridView1.DataSource = dataSet.Tables["Person"];
}

10. And the last thing don’t forget to add this code to the top of the file:

using System.Data.SQLite;

11. Compile and running the file. And you are connected to SQLite database. You can do any experiment like Insert, Update and Delete operation or do other complex query.

You can download the example project and database file from those instruction above here.

Thanks for reading this article, please leave me any comment or any tips you would like to share. Good luck.

What people search:

Article by Ivan Kristianto

Ivan Kristianto is a profesional web developer and blogger. Very interest with free and open source software. Follow my Twitter: @ivankrisdotcom or Facebook profile IvanKristiantoBlog or Email me at ivan@ivankristianto.com

Category: Featured Content, Visual Studio.NET
Tags: .NET, c#, C# sqlite, how to, Software Development, sqlite, sqlite ado.net, sqlite c, sqlite C3

35 comments 3 people listening

Comment help
Get Livefyre

Sort: Newest | Oldest

rogerx19 5 pts

Excellent tutorial!

I'm using Visual C# 2010 Express.

Some of the things that tripped me up, but I was able to figure out:

1) The SQLite ADO.NET file that is needed is now available at:

http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Just download the sqlite-netFx version that matches your version of Windows (32 bit or 64 bit) and .NET (3.5 or 4).

The downloaded file is in .zip format, so you need to un-zip it. Make a note of where it is going to Extract the files to, so that you can find them again.

Once you have it uncompressed, go back to your Visual Studio project and look in the Solution Explorer window.

Right click on "References" then click Add Reference. click the "Browse tab, and find the folder with the System.Data.SQLite.dll file in it. Click on that file, then click OK.

Back in Solution Explorer, right click on System.Data.SQLite and click Properties. In the Properties window, change "Copy Local" from False to True, and Save your work.

2) Step 5 "Add new folder Data". In Solution Explorer, right click on SQLite Test (in his example) and click Add, then New Folder. Rename it to Data.

3) Step 6 "Add the database file". In his example, the database file has already been created using SQLite and named "data.db3" (yours may be different).

Right click on the new Data folder, and click Add, then Existing Item. Use the file finder window to find your existing db3 file.

Once your db3 file is shown in Solution Explorer in the Data folder, click it once to open its properties in the Properties Window (or right click the db3 file and click Properties). In the Properties window, change Copy to Output Directory to "copy always".

4) Step 9 in the Source Code that he has you add, if your db3 file is not named data.db3, then change "Data Source=Data/data.db3 " to match your filename.

Also change all instances of "Person" to the name of the table in your database file that you want to display

Ivan Kristianto Blog

[HowTo] Use SqLite ADO.NET with C#

What people search:

Other Interesting Articles:

Article by Ivan Kristianto

About Author

Popular Posts

Categories

Archives

TagClouds

Blogroll