Entries Tagged '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("Data Source=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("Data Source=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!

NOTE: “Data Source” typo now fixed in code sample.

It’s been a while since I rapped at ya, so I figured I’d write a little about one of my good friends, HttpContext.Current.Items.

Generally, HttpContext.Current.Items doesn’t get all that much hot blog press, but let me tell you, I’m here to change all that. For those out of the know, System.Web.HttpContext.Current.Items is a sweet key-value pair collection you can use to pass objects around up and through all components that participate in a single HTTP request. What does this mean? This means that in a way similar to sticking something in the Session or cache, you can jam some values into the HttpContext.Current.Items for your request, say in a HttpModule way down low before you’ve even begun to fetch a page, and have those values readable/writable later from your page and all its usercontrols. The Items only persist through this one-night-stand of a single request and then supposedly “lose your number” but that’s ok, because we don’t really need all their drama after that anyway.

1) Preparing objects down low, on the down-low.

As alluded to earlier, one sweet use of HCI (as we call in on the street) is in HttpModules. Let’s say you are doing some url-rewriting for user-friendliness or SEO reasons. While you’re at it, why not do a little preparation for your page? If you have a query param of state abbreviation that comes in commonly, populate an additional full state name display field ahead of time. Clean up your strings with proper casing, do whatever utils you think you can get away with while you have the request in your hand. I have a little object of getters and setters that I populate in the the HttpModule, and stick it in the Current.Items for its way up the stack. Now my pages and usercontrols can pull out my cleaned up custom object from the Context.Items and act accordingly, pass it down the line to methods, whatever. The vibe is maybe a hint at a smidgen of a wee bit Model View Controller-ish, but not really.

2) Making params and pages more unit-testable.

With something like #1 in place, you can pull objects of params and prepared goodness out of the request and process them in your code behind, presenter, usercontrol, whatever strikes your pattern fancy. If this bundle of params is a in a nice little object that implements an interface, this makes unit testing logic that under normal circumstances relies upon getting info from the System.Web namespace (querystring params mostly) nice and easily decoupled. Pros of this are that your view calling your presenter can stay super lean and mean without having to populate a bunch info from query strings which will end up going through standard transformations you could have handled earlier on. A con is that the population of these params might be a bit mysterious to other devs who don’t see the HttpModule in action, sort of a like a table that gets populated from somewhere or other, but you don’t know what trigger where. I hate that.

3) Populating usercontrols without the hassle.

If you know you have values in your hand at page on_load or earlier, it’s pretty damn convenient to stick them in the HttpContext.Current.Items and then just read them out from whatever usercontrols may or may not be dynamically included on the page. No finding child controls from the page, no finding parent info from the controls. No casting, scoping or otherwise thinking about precisely what order what will fire. If you have the data at page_load, your controls can get it. Don’t call me, and I won’t call you either. Ta-dow (does anyone say that anymore?).

So there you have it, HttpContext.Current.Items, arcane enough to give you guru-cred to the mid-range noobs, simple enough that it can be leveraged for good and / or evil. Awesome.