Simple 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

About the Author

fbelic

Software Developer (Junior)

Croatia

Member

No Biography provided

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board.

Search this forum
	Profile popups Spacing Noise Layout Per page

Many thanks for providing class! I'm been looking for something like this.
Sign In·View Thread·Permalink

How do I open sqlite db with password or setup password for database through this wrapper class? Rgds, Moez.
Sign In·View Thread·Permalink

hay I am using your code in one of my project. But it throw an exception like this "Could not load type 'SQLiteWrapper.SQLiteBase' from assembly 'SQLiteWrapper, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'." What should i do??
Sign In·View Thread·Permalink

So.. nothing happens even if you feed it complete gibberish as a query or non-query.. that means this is rather difficult to use if you generate queries on the fly.
Sign In·View Thread·Permalink

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

Your code extends the classes from db_wrapper and resultset. What are those?
Sign In·View Thread·Permalink

Just me forgetting to strip that out.
Sign In·View Thread·Permalink

I'm having a problem with SQLite. I run a several INSERT queries, and if I execute a specific sequence into the database, it issues the error "library routine called out of sequence" Considering that I'm not using any kind of threads, I have no idea why this is happening. Could it be related to the Wrapper implementation?
Sign In·View Thread·Permalink

Try inserting this snipped of code before "return sqlite3_step(statement);" in the "ReadFirstRow" function:

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");

Then execute the Insert statements using "ExecuteQuery", not "ExecuteNonQuery". Look up the error code and you should have a better idea of what's going on.

Great find not sure it is a replacement for the ADO Interface BUT most definitly an important peice of code My thanks to you good job!!
Sign In·View Thread·Permalink

See an alternative file storage engine to SQLite (c#.NET)/[^] bla
Sign In·View Thread·Permalink

hi everyone! how can I create a new database in file e.g. test.db?
Sign In·View Thread·Permalink

Just call SQLiteBase db = new SQLiteBase("test.db"); If the file doesn't exist, it's created by default
Sign In·View Thread·Permalink

"SQLiteBase db = new SQLiteBase("test.db");" throws an exception no matter what I do...
Sign In·View Thread·Permalink

What's the text of the exception?
Sign In·View Thread·Permalink

"Cannot create instance of 'Window1' defined in assembly 'sqlSample, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'. Exception has been thrown by the target of an invocation. Error in markup file 'Window1.xaml' Line 1 Position 9."

In debugging the error, it happens when I try to open a database (the file does not currently exist). But it should make the db file if it doesn't exist correct?

I am not entirely sure that I am implementing this correctly. From a blank solution all I have to do is insert the dll you provided into the project as a reference and I can start making/using sqlite?

I seem to be getting an out of memory error(taskman says i am not even using 40% of mem). Here is the full execption message: Error with executing non-query: "INSERT INTO user(username,password,email,network) VALUES('bob','dylan','bob.dylan@live.com','bd')"! out of memory
Sign In·View Thread·Permalink

The documentation for SQLite reads:

"The error message passed back through the 5th parameter is held in memory obtained from sqlite3_malloc(). To avoid a memory leak, the calling application should call sqlite3_free() on any error message returned through the 5th parameter when it has finished using the error message."

A possible fix could be:
1. add the sqlite3_free function pointer

[DllImport("sqlite3")]
private static extern IntPtr sqlite3_free(IntPtr error);

2. free the error memory in the catch of the ExecuteNonQuery function

string errorMessage = PointerToString(sqlite3_errmsg(error));
sqlite3_free(error);
throw new SqlQueryException("Error with executing non-query: \"" + query + "\"!\n" + errorMessage);

(fixed a problem in the order of the free and execute query. sorry)
thanks for the article! seems like a really KISS solution that could help in simple cases (I am pretty stuck trying to add BLOBs using it Frown | :(

, but it works nicely for simple tasks)

I went according to comment #1 and the above comment. seems like there are a few small leaks. I took the liberty of fixing both issues. here is my code (it's not throughly checked but it seems to work).

using System;
using System.Collections;
using System.Data;
using System.Runtime.InteropServices;
using System.Text;
 
namespace SQLiteWrapper
{
    /// <summary>
    /// SQLite wrapper with functions for opening, closing and executing queries.
    /// </summary>
    public class SQLiteBase
    {
        // 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")]
        private static extern int sqlite3_open(IntPtr fileName, out IntPtr database);
 
        [DllImport("sqlite3")]
        private static extern int sqlite3_close(IntPtr database);
 
        [DllImport("sqlite3")]
        private static extern int sqlite3_exec(IntPtr database, IntPtr query, IntPtr callback, IntPtr arguments, out IntPtr error);
 
        [DllImport("sqlite3")]
        private static extern IntPtr sqlite3_errmsg(IntPtr database);
 
        [DllImport("sqlite3")]
        private static extern int sqlite3_prepare_v2(IntPtr database, IntPtr query, int length, out IntPtr statement, out IntPtr tail);
 
        [DllImport("sqlite3")]
        private static extern int sqlite3_step(IntPtr statement);
 
        [DllImport("sqlite3")]
        private static extern int sqlite3_column_count(IntPtr statement);
 
        [DllImport("sqlite3")]
        private static extern IntPtr sqlite3_column_name(IntPtr statement, int columnNumber);
 
        [DllImport("sqlite3")]
        private static extern int sqlite3_column_type(IntPtr statement, int columnNumber);
 
        [DllImport("sqlite3")]
        private static extern int sqlite3_column_int(IntPtr statement, int columnNumber);
 
        [DllImport("sqlite3")]
        private static extern double sqlite3_column_double(IntPtr statement, int columnNumber);
 
        [DllImport("sqlite3")]
        private static extern IntPtr sqlite3_column_text(IntPtr statement, int columnNumber);
 
        [DllImport("sqlite3")]
        private static extern IntPtr sqlite3_column_blob(IntPtr statement, int columnNumber);
 
        [DllImport("sqlite3")]
        private static extern IntPtr sqlite3_column_table_name(IntPtr statement, int columnNumber);
 
        [DllImport("sqlite3")]
        private static extern int sqlite3_finalize(IntPtr handle);
 
		[DllImport("sqlite3")]
		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;
 
        /// <summary>
        /// Creates new instance of SQLiteBase class with no database attached.
        /// </summary>
        public SQLiteBase()
        {
            database = IntPtr.Zero;
        }
 
        /// <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 SQLiteBase(String baseName)
        {
            OpenDatabase(baseName);
        }
 
        /// <summary>
        /// Opens database. 
        /// </summary>
        /// <param name="baseName">Name of database file</param>
        public 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);
            }
        }
 
        /// <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, excetion is thrown
            if (error != IntPtr.Zero) {
				HeapFree(GetProcessHeap(), 0, ptr);
				string errorMessage = PointerToString(sqlite3_errmsg(error));
				sqlite3_free(error);
				throw new SystemException("Error with executing non-query: \"" + query + "\"!\n" + errorMessage);
			}
        	HeapFree(GetProcessHeap(), 0, ptr);
        }
 
        /// <summary>
        /// Executes query that does return something (e.g. SELECT).
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        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);
            sqlite3_prepare_v2(database, ptr, GetPointerLenght(ptr), out statement, out excessData);
            // 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);
            }
 
            // 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 Wednesday, January 7, 2009 3:07 AM

Great fix, thanks! I found no more leaks.
Sign In·View Thread·Permalink

I used oren.shnitzer's modified code and inserted ~20 million records into a table without any leaks. Thanks Oren --------------- Sten Hjelmqvist
Sign In·View Thread·Permalink

I wish to thank you for your input the hard work is appreciated
Sign In·View Thread·Permalink

Hi I am getting a simmilar error message: Unable to load DLL 'sqlite3': The specified module could not be found. Any ideas?
Sign In·View Thread·Permalink

Figured it out... go to http://www.sqlite.org/sqlite-3_6_2.zip[^] and download the sqlite3.dll. Place this in the bin\debug folder and you'll be away.
Sign In·View Thread·Permalink

hii.. i have some error like this after debug.. "Unable to find an entry point named 'sqlite3_prepare_v2' in DLL 'sqlite3'" i already add reference SQLiteWrapper.. Can you help me.. Tq.. need some guide chalAdiera
Sign In·View Thread·Permalink