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.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Data;

namespace SQLiteTutorial
{
  public class SQLiteException : Exception
  {
    public SQLiteException(string message)
      : base(message)
    {

    }
  }

  public class SQLite
  {
    /// <summary>
    /// Opens or creates SQLite database with 
    /// the specified path
    /// </summary>
    /// <param name="path">Path to SQLite database</param>
    public void OpenDatabase(string path)
    {

    }

    /// <summary>
    /// Closes the SQLite database
    /// </summary>
    public void CloseDatabase()
    {

    }

    /// <summary>
    /// Executes a query that returns no results
    /// </summary>
    /// <param name="query">SQL query to execute</param>
    public void ExecuteNonQuery(string query)
    {

    }


    /// <summary>
    /// Executes a SQL query and stores the results 
    /// in a DataTable
    /// </summary>
    /// <param name="query">SQL query to execute</param>
    /// <returns>DataTable of results</returns>
    public DataTable ExecuteQuery(string query)
    {
      return null;
    }
  }
}

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.

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

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

public class SQLite
{
  [DllImport("sqlite3.dll", EntryPoint = "sqlite3_open")]
  static extern int sqlite3_open(string filename, out IntPtr db);

  ...
}

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.

public class SQLite
{
  const int SQLITE_OK = 0;

  [DllImport("sqlite3.dll", EntryPoint = "sqlite3_open")]
  static extern int sqlite3_open(string filename, out IntPtr db);

  private IntPtr _db; //pointer to SQLite database
  private bool _open; //whether or not the database is open

  /// <summary>
  /// Opens or creates SQLite database with the specified path
  /// </summary>
  /// <param name="path">Path to SQLite database</param>
  public void OpenDatabase(string path)
  {
    if (sqlite3_open(path, out _db) != SQLITE_OK)
      throw new SQLiteException("Could not open database file: " + path);

    _open = true;
  }

  ...
}

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.

public class SQLite
{
  const int SQLITE_OK = 0;

  [DllImport("sqlite3.dll", EntryPoint = "sqlite3_open")]
  static extern int sqlite3_open(string filename, out IntPtr db);

  [DllImport("sqlite3.dll", EntryPoint = "sqlite3_close")]
  static extern int sqlite3_close(IntPtr db);

  private IntPtr _db; //pointer to SQLite database
  private bool _open; //whether or not the database is open

  /// <summary>
  /// Opens or creates SQLite database with the specified path
  /// </summary>
  /// <param name="path">Path to SQLite database</param>
  public void OpenDatabase(string path)
  {
    if (sqlite3_open(path, out _db) != SQLITE_OK)
      throw new SQLiteException("Could not open database file: " + path);

    _open = true;
  }

  /// <summary>
  /// Closes the SQLite database
  /// </summary>
  public void CloseDatabase()
  {
    if(_open)
      sqlite3_close(_db);


    _open = false;
  }


  ...
}

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.

[DllImport("sqlite3.dll", EntryPoint = "sqlite3_prepare_v2")]
static extern int sqlite3_prepare_v2(IntPtr db, string zSql, 
    int nByte, out IntPtr ppStmpt, IntPtr pzTail);

[DllImport("sqlite3.dll", EntryPoint = "sqlite3_step")]
static extern int sqlite3_step(IntPtr stmHandle);

[DllImport("sqlite3.dll", EntryPoint = "sqlite3_finalize")]
static extern int sqlite3_finalize(IntPtr stmHandle);

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.

/// <summary>
/// Prepares a SQL statement for execution
/// </summary>
/// <param name="query">SQL query</param>
/// <returns>Pointer to SQLite prepared statement</returns>
private IntPtr Prepare(string query)
{
  IntPtr stmHandle;

  if (sqlite3_prepare_v2(_db, query, query.Length, 
        out stmHandle, IntPtr.Zero) != SQLITE_OK)
    throw new SQLiteException(sqlite3_errmsg(_db));

  return stmHandle;
}

/// <summary>
/// Finalizes a SQLite statement
/// </summary>
/// <param name="stmHandle">
/// Pointer to SQLite prepared statement
/// </param>
private void Finalize(IntPtr stmHandle)
{
  if (sqlite3_finalize(stmHandle) != SQLITE_OK)
    throw new SQLiteException("Could not finalize SQL statement.");
}

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:

[DllImport("sqlite3.dll", EntryPoint = "sqlite3_errmsg")]
static extern string sqlite3_errmsg(IntPtr db);

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.

/// <summary>
/// Executes a query that returns no results
/// </summary>
/// <param name="query">SQL query to execute</param>
public void ExecuteNonQuery(string query)
{
  if (!_open)
    throw new SQLiteException("SQLite database is not open.");

  //prepare the statement
  IntPtr stmHandle = Prepare(query);

  if (sqlite3_step(stmHandle) != SQLITE_DONE)
    throw new SQLiteException("Could not execute SQL statement.");

  Finalize(stmHandle);
}

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.

public DataTable ExecuteQuery(string query)
{
  if (!_open)
    throw new SQLiteException("SQLite database is not open.");

  //prepare the statement
  IntPtr stmHandle = Prepare(query);

  //get the number of returned columns
  int columnCount = sqlite3_column_count(stmHandle);

  //create datatable and columns
  DataTable dTable = new DataTable();
  for (int i = 0; i < columnCount; i++)
    dTable.Columns.Add(sqlite3_column_origin_name(stmHandle, i));

  //populate datatable
  while (sqlite3_step(stmHandle) == SQLITE_ROW)
  {
    object[] row = new object[columnCount];
    for (int i = 0; i < columnCount; i++)
    {
      switch (sqlite3_column_type(stmHandle, i))
      {
        case SQLITE_INTEGER:
          row[i] = sqlite3_column_int(stmHandle, i);
          break;
        case SQLITE_TEXT:
          row[i] = sqlite3_column_text(stmHandle, i);
          break;
        case SQLITE_FLOAT:
          row[i] = sqlite3_column_double(stmHandle, i);
          break;
      }
    }

    dTable.Rows.Add(row);
  }

  Finalize(stmHandle);

  return dTable;
}

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.

[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_count")]
static extern int sqlite3_column_count(IntPtr stmHandle);

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.

[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_origin_name")]
static extern string sqlite3_column_origin_name(IntPtr stmHandle, int iCol);

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.

[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_type")]
static extern int sqlite3_column_type(IntPtr stmHandle, int iCol);

[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_int")]
static extern int sqlite3_column_int(IntPtr stmHandle, int iCol);

[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_text")]
static extern string sqlite3_column_text(IntPtr stmHandle, int iCol);

[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_double")]
static extern double sqlite3_column_double(IntPtr stmHandle, int iCol);

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.

using System;
using System.Data;
using System.Runtime.InteropServices;


namespace SQLiteTutorial
{
  public class SQLiteException : Exception
  {
    public SQLiteException(string message): 
      base(message)
    {

    }
  }

  public class SQLite
  {
    const int SQLITE_OK = 0;
    const int SQLITE_ROW = 100;
    const int SQLITE_DONE = 101;
    const int SQLITE_INTEGER = 1;
    const int SQLITE_FLOAT = 2;
    const int SQLITE_TEXT = 3;
    const int SQLITE_BLOB = 4;
    const int SQLITE_NULL = 5;

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_open")]
    static extern int sqlite3_open(string filename, out IntPtr db);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_close")]
    static extern int sqlite3_close(IntPtr db);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_prepare_v2")]
    static extern int sqlite3_prepare_v2(IntPtr db, string zSql, 
        int nByte, out IntPtr ppStmpt, IntPtr pzTail);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_step")]
    static extern int sqlite3_step(IntPtr stmHandle);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_finalize")]
    static extern int sqlite3_finalize(IntPtr stmHandle);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_errmsg")]
    static extern string sqlite3_errmsg(IntPtr db);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_count")]
    static extern int sqlite3_column_count(IntPtr stmHandle);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_origin_name")]
    static extern string sqlite3_column_origin_name(
        IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_type")]
    static extern int sqlite3_column_type(IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_int")]
    static extern int sqlite3_column_int(IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_text")]
    static extern string sqlite3_column_text(IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_double")]
    static extern double sqlite3_column_double(IntPtr stmHandle, int iCol);

    private IntPtr _db; //pointer to SQLite database
    private bool _open; //whether or not the database is open

    /// <summary>
    /// Opens or creates SQLite database with the specified path
    /// </summary>
    /// <param name="path">Path to SQLite database</param>
    public void OpenDatabase(string path)
    {
      if (sqlite3_open(path, out _db) != SQLITE_OK)
        throw new SQLiteException("Could not open database file: " + path);

      _open = true;
    }

    /// <summary>
    /// Closes the SQLite database
    /// </summary>
    public void CloseDatabase()
    {
      if(_open)
        sqlite3_close(_db);

      _open = false;
    }

    /// <summary>
    /// Executes a query that returns no results
    /// </summary>
    /// <param name="query">SQL query to execute</param>
    public void ExecuteNonQuery(string query)
    {
      if (!_open)
        throw new SQLiteException("SQLite database is not open.");

      //prepare the statement
      IntPtr stmHandle = Prepare(query);

      if (sqlite3_step(stmHandle) != SQLITE_DONE)
        throw new SQLiteException("Could not execute SQL statement.");

      Finalize(stmHandle);
    }

    /// <summary>
    /// Executes a query and stores the results in 
    /// a DataTable
    /// </summary>
    /// <param name="query">SQL query to execute</param>
    /// <returns>DataTable of results</returns>
    public DataTable ExecuteQuery(string query)
    {
      if (!_open)
        throw new SQLiteException("SQLite database is not open.");

      //prepare the statement
      IntPtr stmHandle = Prepare(query);

      //get the number of returned columns
      int columnCount = sqlite3_column_count(stmHandle);

      //create datatable and columns
      DataTable dTable = new DataTable();
      for (int i = 0; i < columnCount; i++)
        dTable.Columns.Add(sqlite3_column_origin_name(stmHandle, i));

      //populate datatable
      while (sqlite3_step(stmHandle) == SQLITE_ROW)
      {
        object[] row = new object[columnCount];
        for (int i = 0; i < columnCount; i++)
        {
          switch (sqlite3_column_type(stmHandle, i))
          {
            case SQLITE_INTEGER:
              row[i] = sqlite3_column_int(stmHandle, i);
              break;
            case SQLITE_TEXT:
              row[i] = sqlite3_column_text(stmHandle, i);
              break;
            case SQLITE_FLOAT:
              row[i] = sqlite3_column_double(stmHandle, i);
              break;
          }
        }

        dTable.Rows.Add(row);
      }

      Finalize(stmHandle);

      return dTable;
    }

    /// <summary>
    /// Prepares a SQL statement for execution
    /// </summary>
    /// <param name="query">SQL query</param>
    /// <returns>Pointer to SQLite prepared statement</returns>
    private IntPtr Prepare(string query)
    {
      IntPtr stmHandle;

      if (sqlite3_prepare_v2(_db, query, query.Length, 
            out stmHandle, IntPtr.Zero) != SQLITE_OK)
        throw new SQLiteException(sqlite3_errmsg(_db));

      return stmHandle;
    }

    /// <summary>
    /// Finalizes a SQLite statement
    /// </summary>
    /// <param name="stmHandle">
    /// Pointer to SQLite prepared statement
    /// </param>
    private void Finalize(IntPtr stmHandle)
    {
      if (sqlite3_finalize(stmHandle) != SQLITE_OK)
        throw new SQLiteException("Could not finalize SQL statement.");
    }
  }
}

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.

static void Main(string[] args)
{
  //create SQLite wrapper class
  SQLite db = new SQLite();

  try
  {
    //open/create the database
    db.OpenDatabase("myDatabase.sqlite");

    //add a table
    db.ExecuteNonQuery("CREATE Table Movies " +
        "(Name TEXT, Director TEXT, Year INTEGER)");

    //add rows to table
    db.ExecuteNonQuery("INSERT INTO Movies (Name, Director, Year) " +
        "VALUES ('The Dark Knight', 'Christopher Nolan', 2008)");

    db.ExecuteNonQuery("INSERT INTO Movies (Name, Director, Year) " +
        "VALUES ('Cloverfield', 'Matt Reeves', 2008)");

    db.ExecuteNonQuery("INSERT INTO Movies (Name, Director, Year) " +
        "VALUES ('Beverly Hills Chihuahua', 'Raja Gosnell', 2008)");

    //get entries
    DataTable table = db.ExecuteQuery("SELECT * FROM Movies");

    foreach (DataRow row in table.Rows)
    {
      for (int i = 0; i < table.Columns.Count; i++)
        Console.WriteLine(row[i]);

      Console.WriteLine("\n"); //empty line between records
    }
  }
  catch (SQLiteException ex)
  {
    Console.WriteLine(ex.Message);
  }
}

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

The Dark Knight
Christopher Nolan
2008

Cloverfield
Matt Reeves
2008

Beverly Hills Chihuahua
Raja Gosnell
2008

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. You can download all of the source code above in a Visual Studio 2008 solution, here.

Source Files:

• SQLiteTutorial.zip