SQLite on .NET - Get up and running in 3 minutes.
January 15th, 2008 | grokable
You found it! The quick and dirty guide to setting up SQLite with .Net in 3 minutes. Small, fast, and ass-kicking like a transactional Jackie Chan. At least that’s what this campy image created just for this post says. (Shout out to righteous graphics dude Brian Cook for the sweet photoshoppery)
SQLite: The compelling introduction
You’re writing some smallish app. You know the type. Those little one-offs where you clean up some data, scrape your competitor’s web site, change traffic light colors, blah blah blah. It’s cool enough to warrant it’s own solution, but not so big that you are going to tie into your company’s SQL Server instances. Let’s face it, you don’t really need the hassle of dealing power tripping DBA / IT guys for this project, especially now that their delusions of grandeur have been magnified by the return of American Gladiators to prime time.
Enter SQLite: the quick skinny / speed-date overview:
SQLite, if you weren’t already aware, is an open source, tiny (600K!), *zero install*, transactional database. But theres more! SQLite is as fast or faster than some of the big guys you use every day, largely because there is no server process, no listeners, no FD&C Yellow #5, none of that crapola. The database is just a single file and you communicate with it through a linked in library. Think of the old access days, but not really.
SQLite implements most of the SQL-92 standard. All of the query related stuff you actually care about is there, triggers, views, etc. The only thing missing is stored procedures but that’s probably not a requirement for your project, if at all. And no, I don’t want to get in to the old sps vs. not-sps tangent, man thats so aggressively nerdy in a Kirk vs Picard way.
… But I digest….
As the the SQLite home page will happily tell you, “SQLite is the most widely deployed SQL database engine in the world.” It’s used by little guys you may have heard of like, oh, I don’t know, Adobe, Apple, FireFox, GE, Google, Skype, Sun, and several shadow governments best left un-named. The crazy part is that you rarely hear about SQLite as a real .Net Dude / Dudette developer. I think this is because it’s often bundled inside of things you didn’t really think were using a database like FireFox or Google Gears, but the research bears out that this bad boy is more than capable for real-life tasks like being the back-end db for dynamic web sites with some pretty serious traffic.
SQLite is great for that small-mediumish application we already spec-ed out so lets get into the quick and dirty setup to get this hog rockin’ on .NET.
SQLite : The Quick and Dirty Setup for .NET.
1) Download SQLite
While you can get the generic windows binary on the SQLite download page, I’m going to recommend you instead grab the ADO.NET 2.0 Provider for SQLite from sourceforge. I’m not saying this is the most performant version (it does have an ADO wrapper with its attendant malarkey), but it really is a super-easy starting implementation that’s probably good enough for the long haul.
2) Copy the resultant DLL (System.Data.SQLite.DLL) to your project and add a reference.
3) Download and install one of the billions of SQLite GUI clients. I’ve been using the aptly named “SQLite Administrator” (FREE) which has a sweet, Query Analyzer-alike interface. You can find a big list of SLQLite gui clients here http://www.sqlite.org/cvstrac/wiki?p=ManagementTools if you are so inclined.
4) Through the GUI, create a database and make a test table of whatever floats your boat. The result will be a single file with a .s3db extension.
5) There is no step 5! DONE! You can now query, insert, update, delete, create, truncate, etc, to your heart’s content using the System.Data.SQLite ADO wrapper. Here is a little helper db util type class to show you the basic schleck:
public static DataTable GetDataTable (string sql)
{
DataTable dt = new DataTable();
try
{
SQLiteConnection cnn = new SQLiteConnection(“DataSource=C:CheckoutWorldDominator.s3db”);
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = sql;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
cnn.Close();
} catch {
// Catching exceptions is for communists
}
return dt;
}public static int ExecuteNonQuery(string sql)
{
SQLiteConnection cnn = new SQLiteConnection(“Data Source=C:CheckoutWorldDominator.s3db”);
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = sql;
int rowsUpdated = mycommand.ExecuteNonQuery();
cnn.Close();
return rowsUpdated;
}
public static string ExecuteScalar(string sql)
{
SQLiteConnection cnn = new SQLiteConnection(“DataSource=C:CheckoutWorldDominator.s3db”);
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = sql;
object value = mycommand.ExecuteScalar();
cnn.Close();
if (value != null)
{
return value.ToString();
}
return “”;
}
NOTE: Above code is quicky crap. It’s just to show you the gist.
Some uses for SQLite to consider:
Configs / Settings: SQLite is a good alternative to xml config files or registry settings for things like user account info and application preferences. It supports encryption, so feel free to keep your brilliant patent ideas in there.
Persistent Caching: You can use SQLite as a DB for cache data that needs to persist through reboots / application recycles. Maybe you have some expensive one-time-on-loadup queries from your enterprise db that you cache up and use in your website or app. By timestamping the data into a local SQLite db, you can live through application restarts and only refresh your cache at the threshold you want to.
Slicing and Dicing data: Load in some data and query to your heart’s content. Great for analyzing data at your leisure, worming through subsets of data, etc. Since its just a little db on your on box, no one is going to hassle you. Managers who were once developers will appreciate being to query through data with SQLite vs. using excel as they usually are too crusty to still have permissions on the real db.
Full DB for One-off apps: Sometimes you write a quickie app that just harvests something in a funky way and collects data. You can output the data as you are grabbing it in all kinds of ways, but throwing into a db is ideal.
Linkage to some more in-depth stuff ……….
- SQLite is pretty hip with with the alt.net scene, you can follow on from here to check out a SQLite NHibernate provider, or here for a SQLite Subsonic provider.
- If you are rockin’ the 3.0 framework there is even a SQLite Linq provider.
- For a comprehensive SQLite how-to (emphasizing command line, non-MS specific) : SQLite Tutorial
- And in case you missed it up top, the main SQLite project page is here.
Have a good idea for ways to use SQLite in .Net projects? Been hit in the groin with a football for even suggesting using something like this at your company? Have some award-winning successes or outlandish failures already with SQLite? Leave a funky-fresh comment!
No comments:
Post a Comment