C# Tutorial - Writing a .NET Wrapper for SQLite

A couple of weeks ago we posted a tutorial on using SQLite in PHP. I thought I'd expand on that tutorial and demonstrate how to work with SQLite using C# and .NET. In this tutorial, we're going to build a simple wrapper class around the SQLite c/c++ interface.

Unlike PHP, .NET doesn't have any built-in mechanisms for working with SQLite. This means that in order to work with it, we have to write our own. There are several existing .NET wrappers for SQLite that I would recommend using before creating one, but it's always good to know what happens under the surface.

The most important resource you'll need for this tutorial is the SQLite documentation. I wouldn't rank the documentation as the best I've seen, but it was good enough to get the job done. After reading through that, you'll notice all transactions go through a series of function calls that we're going to have to make available to our wrapper.

• sqlite3_open
• sqlite3_prepare
• sqlite3_step
• sqlite3_column
• sqlite3_finalize
• sqlite3_close

There are several other helper functions we'll need, however these are the core set of functions required for all transactions.

Before we start bringing function calls into C# code, let's build the framework for our wrapper object, which I'll call SQLite.

What I've created is a very basic wrapper around typical database operations. Let's start at the top and work our way down. If there are any errors, this wrapper will throw a custom exception with the message that caused the error. I simply created a new object that inherits from Exception. I then created a function to open the database. This function takes the path of the SQLite database file. If the file doesn't exist, this function will create it. Next is a function to close the database when the user is finished with it. I then created two functions for executing queries. The first one executes a query that does not return any results, such as INSERT or UPDATE. The next one, ExecuteQuery will fill a DataTable with the results returned by the query. And that does it, that's the entire framework for our SQLite wrapper. Now let's start filling in some details.

The first thing you're going to have to do before we can start calling functions is to actually download the SQLite library. I chose the Windows DLL without Tcl/Tk bindings. This will give us a nice interface we can access using Interoperability.

All right, you've got the DLL, we've got the framework, now let's start by opening a SQLite database. Below is the signature of the open function we need to bring into our wrapper. Remember, all of these functions can be found in the SQLite documentation.

Let's use DllImport to bring this function into our wrapper:

I've always marveled at .NET's ability to marshal types to and from native code. So here we're passing in a string as the filename and an IntPtr that will hold a pointer to the SQLite database object. We have to pass this in as an out parameter since the function will be populating this for us.

Now we're ready to populate the OpenDatabase function.

I added a constant called SQLITE_OK, which is defined in the documentation, that is returned by several SQLite functions if everything executed without errors. I added a member field to hold the database pointer since other functions will need this to operate on the database. I added another member field to simply keep track of whether or not the database is open. In the OpenDatabase function, I simply call the function we just brought in and check its return value. If it's not SQLITE_OK, I throw one of our custom exceptions with a message indicating the database could not be opened.

Now that we've got a function to open databases, let's create the analogous function, CloseDatabase.

This function is very simple. If the database isn't open, it doesn't do anything. If it is open, it calls sqlite3_close, passing it the database pointer that is created by the open call.

Now we're ready to build ExecuteNonQuery. Executing queries with no results requires 3 of the core functions I listed above: prepare, step, and finalize. The first thing we need to do is bring those functions into our wrapper.

Since prepare and finalize are used in both execute functions, I'm going to create methods in our class to wrap those calls. I can't wrap the step function since it's used differently when results are returned.

SQL queries passed into SQLite must first be converted to byte code by the prepare function. This function is where SQLite will complain about invalid SQL syntax. You might have noticed the call to sqlite3_errmsg. This function returns the last error message generated by the library. Here's the import call required to use it:

The prepare function returns a pointer to a SQLite statement. This pointer is used later by the step function to actually execute the queries and retrieve results. The finalize function is used to delete a prepared statement, freeing its memory.

Now we're ready for the ExecuteNonQuery method.

The first thing this function does is check to make sure the database is open. It then prepares the query passed into it using the function we just created. Lastly, it calls sqlite3_step to execute the query. As a mentioned early, the wrapper we're building today is not fully featured. This function has some limitations. First, it will only execute the first statement if the SQL contains multiple statements. Second, if the SQL passed contains results, this function will throw an exception since it's not expecting any. These are easy limitation to get around, but I'll leave those up to you.

It's now time to build the fun method, ExecuteQuery. Let's start at the top and work our way down - here's the entire method.

Just like with the ExecuteNonQuery function, I first make sure the database is open and prepare the query. I then get the number of columns that will be returned by the query using the function, sqlite3_column_count.

Using the column count, I start building the columns in my DataTable. The function, sqlite3_column_origin_name returns the name of the column at the specified index. I used that to give each column in the DataTable a name.

After I've got my columns built, I start stepping through results. sqlite3_step will be called for each row of results returned. I continually call it while its return value is SQLITE_ROW, which is another constant I've added to my class. In order to get values, SQLite provides a column method for each data type. This wrapper supports int, double, and text. The only SQLite datatype it doesn't support is blob, but it should be pretty straight-forward to implement.

So to read results, I loop through each column returned in the result row and get its datatype. I then call the appropriate column function to retrieve the data. After I've read every column in a result row, I simply add the row to my DataTable. I then call Finalize and return the populated DataTable.

That's it for the wrapper. Here's the entire source all in one spot. I'll demonstrate how to use the wrapper class right after.

Now that we've got this wrapper class, it's probably a good idea to see how it's used. I've created a small command line application that creates a database, adds a table, inserts some data, then reads that data back out again. It's the same example I used in our previous SQLite and PHP tutorial.

When this program executes, you should see the following output:

Of course, you can only run the program once before it throws an exception saying the Movies table already exists, but you get the idea.

I think that does it. Remember, this wrapper is meant to be used as a starting point for more fully featured versions. I don't recommend using this one, or even writing your own in production environments. There are several well-tested solutions just a Google search away. Other than that, I hope you learned something about how SQLite works and as always, comments and questions are welcome. You can download all of the source code above in a Visual Studio 2008 solution, here.