Click here to Skip to main content
Click here to Skip to main content

Simple C# Wrapper for SQLite

, 16 Aug 2007
Rate this:
Please Sign up or sign in to vote.
An article that describes lightweight C# wrapper for SQLite

Introduction

This article describes a very simple wrapper class for SQLite. This class provides only few simple functions: opening and closing database, returning the list of tables and executing queries. Although these are basic functions, they are enough for someone who needs only a storage engine for his/her program.

Background

While writing this class, I've been using ADO.NET Data Provider for SQLite as a reference, which I found here.

Using the Code

Using this class is very easy: add a reference to this DLL (Project->AddReference...) in your project and a line to your code:

using SQLWrapper;

Now you can start using database functions in your program.

Example:

// creates a new instance of SQLiteBase and opens database in file "test.db"
SQLiteBase db = new SQLiteBase("test.db");
// executes SELECT query and store results in new data table
DataTable table = db.ExecuteQuery("SELECT * FROM table1 WHERE id = 1;");
// closes the database
db.CloseDatabase();

Source Code

There are two constructors and and five public functions:

  • OpenDatabase
  • CloseDatabase
  • GetTables
  • ExecuteNonQuery
  • ExecuteQuery

which are all well-documented in the source file, so I don't see a point in explaining them again.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

fbelic
Software Developer (Junior)
Croatia Croatia
No Biography provided

Comments and Discussions

 
QuestionUpdates for .NET 4.5.1 ? Pinmemberkiquenet.com31-Jan-14 4:06 
QuestionProblem Pinmemberdaghune19-Oct-13 0:29 
GeneralThanks Pinmembernikzorom9-Jan-13 10:32 
QuestionOpen database with password Pinmembermoez.hielmy27-Aug-12 17:06 
GeneralEtrow Exception PinmemberAl Baikr16-Jul-10 21:30 
Rant[My vote of 2] Not reacting to malformed queries PinmemberRobhol5-Jul-10 12:44 
GeneralUpdate for Net 4.0 [modified] Pinmemberanss1236-Jun-10 21:04 
For performance reasons Net 4.0 won't automatically figure out the calling convention. I've updated the accordingly:
 
using System;
using System.Collections;
using System.Data;
using System.Runtime.InteropServices;
using System.Text;
 
namespace FDV.SQL_db
{
  /// <summary>
  /// SQLite wrapper with functions for opening, closing and executing queries.
  /// Author: fbelic
  /// Modified by: oren.shnitzer (Fixed memory leaks on errors)
  /// 
  /// http://www.codeproject.com/KB/database/cs_sqlitewrapper.aspx
  /// 
  /// todo: Better error handeling (see http://www.sqlite.org/c3ref/c_abort.html)
  /// </summary>
  public class db_SQLite
  {
    // imports system functions for work with pointers
    [DllImport("kernel32")]
    private extern static IntPtr HeapAlloc(IntPtr heap, UInt32 flags, UInt32 bytes);
 
    [DllImport("kernel32")]
    static extern bool HeapFree(IntPtr heap, UInt32 flags, IntPtr block);
 
    [DllImport("kernel32")]
    private extern static IntPtr GetProcessHeap();
 
    [DllImport("kernel32")]
    private extern static int lstrlen(IntPtr str);
 
    // imports SQLite functions
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern int sqlite3_open(IntPtr fileName, out IntPtr database);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern int sqlite3_close(IntPtr database);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern int sqlite3_exec(IntPtr database, IntPtr query, IntPtr callback, IntPtr arguments, out IntPtr error);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern IntPtr sqlite3_errmsg(IntPtr database);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern int sqlite3_prepare_v2(IntPtr database, IntPtr query, int length, out IntPtr statement, out IntPtr tail);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern int sqlite3_step(IntPtr statement);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern int sqlite3_column_count(IntPtr statement);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern IntPtr sqlite3_column_name(IntPtr statement, int columnNumber);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern int sqlite3_column_type(IntPtr statement, int columnNumber);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern int sqlite3_column_int(IntPtr statement, int columnNumber);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern double sqlite3_column_double(IntPtr statement, int columnNumber);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern IntPtr sqlite3_column_text(IntPtr statement, int columnNumber);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern IntPtr sqlite3_column_blob(IntPtr statement, int columnNumber);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern IntPtr sqlite3_column_table_name(IntPtr statement, int columnNumber);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern int sqlite3_finalize(IntPtr handle);
 
    [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
    private static extern IntPtr sqlite3_free(IntPtr error);
 
    // SQLite constants 
    private const int SQL_OK = 0;
    private const int SQL_ROW = 100;
    private const int SQL_DONE = 101;
 
    /// <summary>
    /// SQLite data types.
    /// </summary>
    public enum SQLiteDataTypes
    {
      /// <summary>
      /// Integer numbers.
      /// </summary>
      INT = 1,
      /// <summary>
      /// Decimal numbers.
      /// </summary>
      FLOAT,
      /// <summary>
      /// All kinds of texts.
      /// </summary>
      TEXT,
      /// <summary>
      /// Blob objects - binary large objects.
      /// </summary>
      BLOB,
      /// <summary>
      /// Nothing.
      /// </summary>
      NULL
    };
 
    // pointer to database
    private IntPtr database;
    private readonly string _database;
 
    /// <summary>
    /// Creates new instance of SQLiteBase class and opens database with given name.
    /// </summary>
    /// <param name="baseName">Name (and path) to SQLite database file</param>
    public db_SQLite(String baseName)
    {
      _database = baseName;
    }
 
    /// <summary>
    /// Opens database. 
    /// </summary>
    /// <param name="baseName">Name of database file</param>
    protected void OpenDatabase(String baseName)
    {
      IntPtr ptr = StringToPointer(baseName);
      // opens database 
      if (sqlite3_open(ptr, out database) != SQL_OK)
      {
        // if there is some error, database pointer is set to 0 and exception is throws
        database = IntPtr.Zero;
        HeapFree(GetProcessHeap(), 0, ptr);
        throw new Exception("Error with opening database " + baseName + "!");
      }
      HeapFree(GetProcessHeap(), 0, ptr);
    }
 
    /// <summary>
    /// Closes opened database.
    /// </summary>
    public void CloseDatabase()
    {
      // closes the database if there is one opened
      if (database != IntPtr.Zero)
      {
        sqlite3_close(database);
        database = IntPtr.Zero;
      }
    }
 
    public void Open() { if (database == IntPtr.Zero) OpenDatabase(_database); }
 
    /// <summary>
    /// Closes the connection to the database.
    /// </summary>
    public void Close() { /*CloseDatabase();*/ }
 
    /// <summary>
    /// Returns the list of tables in opened database.
    /// </summary>
    /// <returns></returns>
    public ArrayList GetTables()
    {
      // executes query that select names of all tables and views in master table of every database
      String query = "SELECT name FROM sqlite_master " +
                                  "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'" +
                                  "UNION ALL " +
                                  "SELECT name FROM sqlite_temp_master " +
                                  "WHERE type IN ('table','view') " +
                                  "ORDER BY 1";
      DataTable table = ExecuteQuery(query);
 
      // when table is generater, it writes all table names in list that is returned
      ArrayList list = new ArrayList();
      foreach (DataRow row in table.Rows)
      {
        list.Add(row.ItemArray[0].ToString());
      }
      return list;
    }
 
    /// <summary>
    /// Executes query that does not return anything (e.g. UPDATE, INSERT, DELETE).
    /// </summary>
    /// <param name="query"></param>
    public void ExecuteNonQuery(String query)
    {
      // calles SQLite function that executes non-query
      IntPtr error;
      IntPtr ptr = StringToPointer(query);
      sqlite3_exec(database, ptr, IntPtr.Zero, IntPtr.Zero, out error);
      // if there is error, exception is thrown
      if (error != IntPtr.Zero)
      {
        HeapFree(GetProcessHeap(), 0, ptr);
        string errorMessage = PointerToString(sqlite3_errmsg(error));
        sqlite3_free(error);
        throw new SystemException(string.Format("Error with executing non-query: \"{0}\"!\n", query) + errorMessage);
      }
      HeapFree(GetProcessHeap(), 0, ptr);
    }
 
    /// <summary>
    /// Executes a query. Remeber to call open first.
    /// </summary>
    /// <remarks>Remeber to close the connection</remarks>
    public DataTable ExecuteQuery(String query)
    {
      // processed query
      IntPtr statement;
 
      // excess data, it has no use
      IntPtr excessData;
 
      // process query and make statement
      IntPtr ptr = StringToPointer(query);
      int ret = sqlite3_prepare_v2(database, ptr, GetPointerLenght(ptr), out statement, out excessData);
      if (ret != 0)
        throw new Exception("Could not prepare statement");
      // table for result of function
      DataTable table = new DataTable();
 
      // reads first row - it is different from next rows because it also creates table columns
      // result - returns SLQ_ROW while there is next row
      int result = ReadFirstRow(statement, ref table);
 
      // reads rows
      while (result == SQL_ROW)
      {
        result = ReadNextRow(statement, ref table);
      }
 
      // finalize executing this query
      sqlite3_finalize(statement);
 
      HeapFree(GetProcessHeap(), 0, ptr);
 
      // returns table
      return table;
    }
 
    // private function for reading firs row and creating DataTable
    private int ReadFirstRow(IntPtr statement, ref DataTable table)
    {
      // create new instance of DataTable with name "resultTable"
      table = new DataTable("resultTable");
 
      // evaluates statement 
      int resultType = sqlite3_step(statement);
 
      // if result of statement is SQL_ROW, create new table and write row in it
      if (resultType == SQL_ROW)
      {
        // returns number of columns returned by statement
        int columnCount = sqlite3_column_count(statement);
 
        // declartaion of variables for reading first row
        String columnName = "";
        int columnType = 0;
        object[] columnValues = new object[columnCount];
 
        // reads columns one by one
        for (int i = 0; i < columnCount; i++)
        {
          // returns the name of current column
          columnName = PointerToString(sqlite3_column_name(statement, i));
 
          // returns the type of current column
          columnType = sqlite3_column_type(statement, i);
 
          // checks type of columns - neccessary because different functions are required for different types
          switch (columnType)
          {
            // in case of integer column
            case (int)SQLiteDataTypes.INT:
              {
                // adds new integer column to table
                table.Columns.Add(columnName, Type.GetType("System.Int32"));
 
                // writes column value in object array
                columnValues[i] = sqlite3_column_int(statement, i);
                break;
              }
            // same as for integer, this one is for float
            case (int)SQLiteDataTypes.FLOAT:
              {
                table.Columns.Add(columnName, Type.GetType("System.Single"));
                columnValues[i] = sqlite3_column_double(statement, i);
                break;
              }
            // ... for text
            case (int)SQLiteDataTypes.TEXT:
              {
                table.Columns.Add(columnName, Type.GetType("System.String"));
                columnValues[i] = PointerToString(sqlite3_column_text(statement, i));
                break;
              }
            // ... for blob - blob are written in table as strings!!
            case (int)SQLiteDataTypes.BLOB:
              {
                table.Columns.Add(columnName, Type.GetType("System.String"));
                columnValues[i] = PointerToString(sqlite3_column_blob(statement, i));
                break;
              }
            // in case of something other, value is read as string
            default:
              {
                table.Columns.Add(columnName, Type.GetType("System.String"));
                columnValues[i] = "";
                break;
              }
          }
        }
 
        // writes column values to table
        table.Rows.Add(columnValues);
      }
      else
        if (resultType != 101 && resultType != 0)
          throw new Exception("There was an error of type: "+resultType+" - See http://www.sqlite.org/c3ref/c_abort.html");
 
      // evalute statemnet for next results
      return sqlite3_step(statement);
    }
 
    // private function for reading rows other than first
    // it' same like first row, only without creating table and columns
    private int ReadNextRow(IntPtr statement, ref DataTable table)
    {
      int columnCount = sqlite3_column_count(statement);
 
      int columnType = 0;
      object[] columnValues = new object[columnCount];
 
      for (int i = 0; i < columnCount; i++)
      {
        columnType = sqlite3_column_type(statement, i);
 
        switch (columnType)
        {
          case (int)SQLiteDataTypes.INT:
            {
              columnValues[i] = sqlite3_column_int(statement, i);
              break;
            }
          case (int)SQLiteDataTypes.FLOAT:
            {
              columnValues[i] = sqlite3_column_double(statement, i);
              break;
            }
          case (int)SQLiteDataTypes.TEXT:
            {
              columnValues[i] = PointerToString(sqlite3_column_text(statement, i));
              break;
            }
          case (int)SQLiteDataTypes.BLOB:
            {
              columnValues[i] = PointerToString(sqlite3_column_blob(statement, i));
              break;
            }
          default:
            {
              columnValues[i] = "";
              break;
            }
        }
      }
      table.Rows.Add(columnValues);
      return sqlite3_step(statement);
    }
 
    // converts string to pointer
    private IntPtr StringToPointer(String str)
    {
      // if string is null, pointer is 0
      if (str == null)
      {
        return IntPtr.Zero;
      }
      else
      {
        // else, convert it to pointer
        Encoding encoding = Encoding.UTF8;
        Byte[] bytes = encoding.GetBytes(str);
        int length = bytes.Length + 1;
        IntPtr pointer = HeapAlloc(GetProcessHeap(), 0, (UInt32)length);
        Marshal.Copy(bytes, 0, pointer, bytes.Length);
        Marshal.WriteByte(pointer, bytes.Length, 0);
        return pointer;
      }
    }
 
    // convert pointer to string
    private String PointerToString(IntPtr ptr)
    {
      if (ptr == IntPtr.Zero)
        return null;
 
      Encoding encoding = Encoding.UTF8;
 
      int length = GetPointerLenght(ptr);
      Byte[] bytes = new Byte[length];
      Marshal.Copy(ptr, bytes, 0, length);
      return encoding.GetString(bytes, 0, length);
    }
 
    // returns length of pointer
    private int GetPointerLenght(IntPtr ptr)
    {
      if (ptr == IntPtr.Zero)
        return 0;
      return lstrlen(ptr);
    }
  }
}

modified on Friday, October 8, 2010 3:14 AM

GeneralRe: Update for Net 4.0 Pinmemberupsfeup7-Jul-10 4:24 
GeneralRe: Update for Net 4.0 Pinmemberanss12327-Aug-10 3:59 
GeneralHaving Error: library routine called out of sequence Pinmemberupsfeup26-May-10 2:44 
GeneralRe: Having Error: library routine called out of sequence Pinmemberanss1234-Jun-10 3:02 
GeneralMy Sincerest THANKS!! Pinmemberkidzopa15-May-10 16:12 
GeneralA possible alternative; NFileStorage Pinmembersnip117-Mar-09 6:54 
Generalcreate a database PinmemberChaozzster16-Feb-09 6:28 
GeneralRe: create a database Pinmemberthk_sompi1-Mar-09 12:16 
GeneralRe: create a database PinmemberKen Eucker3-Jun-09 9:25 
GeneralRe: create a database Pinmemberfbelic3-Jun-09 23:14 
GeneralRe: create a database PinmemberKen Eucker4-Jun-09 21:19 
Generalout of memory Pinmembersaifsail24-Jan-09 8:32 
GeneralPossible memory leak PinmemberJacky__E24-Nov-08 3:34 
GeneralRe: Possible memory leak [modified] Pinmemberoren.shnitzer22-Dec-08 10:21 
GeneralRe: Possible memory leak PinmemberJacky__E23-Dec-08 3:16 
GeneralNo more memory leak PinmemberSten Hjelmqvist23-Sep-09 2:19 
GeneralRe: Possible memory leak Pinmemberkidzopa15-May-10 16:28 
GeneralSame Error PinmemberMember 331205521-Sep-08 8:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140814.1 | Last Updated 16 Aug 2007
Article Copyright 2007 by fbelic
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid