Introduction

SQLite is a small, fast and embeddable database where the database engine and the interface are combined into a single library. It also has the ability to store all the data in a single file. So if your application requires a standalone database, SQLite is perhaps the perfect choice for you. There are, of course, other reasons for choosing SQLite including:

SQLite has a small memory footprint and only a single library is required to access databases, making it ideal for embedded database applications.

SQLite has been ported to many platforms and runs even on Windows CE and Palm OS.

SQLite is ACID-compliant, meeting all four criteria - Atomicity, Consistency, Isolation, and Durability.

SQLite implements a large subset of the ANSI-92 SQL standard, including views, sub-queries and triggers.

No problem of extra database drivers, ODBC configuration required. Just include the library and the data file with your application.

SQLite has native language APIs for C/C++, PHP, Perl, Python, Tcl etc. Native API for C# is still not present.

Using the Code

To use SQLite in your C# application, you need to download a third party free ADO.NET data provider (SQLite.NET.0.21_x68_dll.zip) from here.

Fig 1: Demo C# SQLite application.

Our demo application uses SQLite database to store id and description value pairs in a single table – mains. We will display the contents of this table in a grid with added functionalities of adding/deleting and modifying records to and from the table. To set up the system, please follow the steps below:

Unzip the archive, and place the *.dll files into the Binary folder.

Use the SQLite.NET.dll in your .NET application by ‘Project -> Add Reference’.

Add Using Finisar.SQLite; in the using directive declaration region of your *.cs file.

To administer SQLite database, an open-source SQLite GUI Database Browser utility is of much use. It can be downloaded from here.

Conclusion

So should SQLite be used for all database driven applications? No.

Like all databases, SQLite has its list of shortcomings. It is not suitable for a client server application or as a networked database. It’s not suited well for a multi user scenario and can have serious file locking issues when accessed simultaneously over the network. Quite for the same reason, SQLite is not suited for a multi-threaded or a multi-process application-database access scenario.

To conclude, SQLite is very suitable for memory constraint systems like WinCE, Palms, Smart Phones, embedded devices and also normal single user desktop applications where its small memory footprint, single library and its copy and paste deployment feature give it a distinct advantage.

Error 1 The type 'System.Data.Common.DbDataAdapter'is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Data.Common, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=969db8053d3322ac, Retargetable=Yes'. C:\Users\Ehydromet\Documents\Visual Studio 2008\Projects\sqlCEChash\Program.cs 1435 sqlCEChash

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;

Thank you Chayan for the code!!! It works like a champ. I see a lot of people are having trouble though, so I hope you don't mind that I posted here in hopes to simplify my experience using the code...

I feel that I am being a bit stupid, but I am making my first database application in C#. I have implemented all of the necessary code but I am not due how or where to import/include the database I have created into Visual Studio. I'm using 2012 if that matters.

This project assumes that the database already exists. You could create a database using a tool, for example sqlite administrator, and once you have the db file you can use it in the SQLiteConnection method. The file can be added to the solution as a resource with output property as "copy allways". Thus, the database file will be in the same directory as the executable.

I got the sample source codes from following link
http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application
I've tried it, but it didn't worked.
I want to develop a application that runs Windows CE or Mobile devices.
Do you have any idea about this?