Monday, December 28, 2009

SQLite and .Net

I needed a light weight database replacement for some of my SQL Server functions. Rather than using SQL Server Express, Access, Mysql, etc., all of which would require an awkward setup workaround, I found the SQLite for .Net as the perfect "drop-in" replacement. By simply including one .dll file in your project (and adding a reference to that .dll), you now have access to all of your SQL calls, through the SQLite database. You can find the download for the .Net version of SQLite here. Currently, I am using the 1.0.65.0 version, for my project. Additionally, I've found the sqliteadmin tool to be a nice editor for creating, editing data, and viewing all of my SQLite databases.
You'll find the .Net SQLite libraries can be used anywhere you might need to persist data, but don't have the option to use a full, distributed database.

To access your database, a simple connection, that includes the path, will allow you to connect as follows, using a datareader:

using System.Data.SQLite;
using System.Data;

public void getData()
{
try
{
SQLiteConnection sc = new SQLiteConnection("Data Source=" +
Application.StartupPath + "\\test1.s3db");
sc.Open();
SQLiteCommand scmd = new SQLiteCommand("Select * from main1", sc);
SQLiteDataReader dr = scmd.ExecuteReader();
while (dr.Read())
{
listBox1.Items.Add(dr[1].ToString() +
" " + dr[2].ToString());
}
dr.Close();
sc.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}