SQLite is, as the website says, a software library that implements a self-contained T-SQL database engine. The framework for SQLite is in the public domain, which has resulted in a rather large number of development companies utilising the technology.
This article will demonstrate first how to create and interface with SQLite databases, then how we can interact with these databases programmatically.
For more information on any of the tools mentioned here, please see the references at the end of the article.
Interfacing with a SQLite Database
A quick Google search will give you hundreds of SQLite GUI applications, but which one is the best one to use? As with internet browsers and webmail services, these are a matter of taste or choice. Personally, I have been using an application called SQLite Administrator as the interface is not too dissimilar to SQL Server Management Studio.
After downloading, unzipping and running SQLite Administrator, we are greeted with an empty management window, so the first thing to do is create a new database (
).
Once the database is created, add a table (
) and a couple of fields (
). After this is done, we can see the table in the database, and write a little SQL to add some dummy data in as follows:
So now we have a database, with a table and some data in.
Retrieving Data from a SQLite Database
Our next step is to retrieve the data we just added in, which will allow us to then alter it through code.
The most popular tool to interface with SQLite databases in Visual Studio 2005 and 2008 is an open source ADO.NET provider called System.Data.SQLite, unfortunately however this is not yet available for .NET 4.0, nor does it work with the express editions of Visual Studio.
The data provider I have been using in Visual Studio 2010 is called dotConnect for SQLite, which performs the same functions as System.Data.SQLite, but has limited functionality unless you purchase a license.
Using dotConnect to retrieve the data in the user database previously created, the following code was added to Form_Load (with the addition of the Devart.Data and Devart.Data.SQLite references):
SQLiteConnection db = new SQLiteConnection();
db.ConnectionString = @"Data Source=D:\Development\UsersDatabase.s3db";
db.Open();
string SQL = "SELECT * FROM Users";
SQLiteCommand comm = new SQLiteCommand(SQL, db);
SQLiteDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
MessageBox.Show("ID : " + reader.GetString(reader.GetOrdinal("UserID")) +
" >>> Name : " + reader.GetString(reader.GetOrdinal("FirstName")) +
" " + reader.GetString(reader.GetOrdinal("LastName")));
}
reader.Close();
db.Close();
Application.Exit();
This displays a message box for each record in the User table, and it really is as simple as that. For more information, please visit the links below.
References
- SQLite
- SQLite Administrator
http://sqliteadmin.orbmu2k.de/
- Data.SQLite
http://sqlite.phxsoftware.com/
- dotConnect for SQLite