(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 , 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
{
public class SQLiteBase
{
[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);
[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);
private const int SQL_OK = 0;
private const int SQL_ROW = 100;
private const int SQL_DONE = 101;
public enum SQLiteDataTypes {
INT = 1,
FLOAT,
TEXT,
BLOB,
NULL };
private IntPtr database;
public SQLiteBase()
{
database = IntPtr.Zero;
}
public SQLiteBase(String baseName)
{
OpenDatabase(baseName);
}
public void OpenDatabase(String baseName)
{
IntPtr ptr = StringToPointer(baseName);
if (sqlite3_open(ptr, out database) != SQL_OK)
{
database = IntPtr.Zero;
HeapFree(GetProcessHeap(), 0, ptr);
throw new Exception("Error with opening database " + baseName + "!");
}
HeapFree(GetProcessHeap(), 0, ptr);
}
public void CloseDatabase()
{
if (database != IntPtr.Zero)
{
sqlite3_close(database);
}
}
public ArrayList GetTables()
{
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);
ArrayList list = new ArrayList();
foreach (DataRow row in table.Rows)
{
list.Add(row.ItemArray[0].ToString());
}
return list;
}
public void ExecuteNonQuery(String query)
{
IntPtr error;
IntPtr ptr = StringToPointer(query);
sqlite3_exec(database, ptr, IntPtr.Zero, IntPtr.Zero, out error);
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
|
