If you find any problem you can mail me at moc.liamg@oirakp (do not copy paste, it is written rtl and set ltr by CSS) or you can leave a message. I you need a feature, that I did not implement, you can tell me, but no promises here.
The usage of the classes is covered in the next post: Sqlite c# usage. Include the following classes in your project. Put all classes in one file (Sqlite.cs).
(Don't forget "using System.Runtime.InteropServices;" Class sqlite (Database):
public class SQLiteException: Exception {public SQLiteException(string message) : base(message) {}}
public class sqlite {
public enum Access: int {
EXISTS = 0,
READWRITE = 1,
READ = 2
}
public enum Result: int {
OK = 0,
ERROR = 1,
INTERNAL = 2,
PERM = 3,
ABORT = 4,
BUSY = 5,
LOCKED = 6,
NOMEM = 7,
READONLY = 8,
INTERRUPT = 9,
IOERR = 10,
CORRUPT = 11,
NOTFOUND = 12,
FULL = 13,
CANTOPEN = 14,
PROTOCOL = 15,
EMPTY = 16,
SCHEMA = 17,
TOOBIG = 18,
CONSTRAINT = 19,
MISMATCH = 20,
MISUSE = 21,
NOLFS = 22,
AUTH = 23,
FORMAT = 24,
RANGE = 25,
NOTADB = 26,
ROW = 100,
DONE = 101
}
public enum DataType: int {
INTEGER = 1,
FLOAT = 2,
TEXT = 3,
BLOB = 4,
NULL = 5
}
public enum Parameter: int {
TRANSIENT = -1,
STATIC = 0
}
[DllImport("kernel32.dll", EntryPoint = "HeapAlloc")]
private extern static IntPtr HeapAlloc(IntPtr heap, UInt32 flags, UInt32 bytes);
[DllImport("kernel32.dll", EntryPoint = "GetProcessHeap")]
private extern static IntPtr GetProcessHeap();
[DllImport("kernel32.dll", EntryPoint = "lstrlen")]
public extern static int lstrlen(IntPtr hStr);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_open", CallingConvention = CallingConvention.Cdecl)]
public static extern sqlite.Result open(string filename, out IntPtr hDb);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_close", CallingConvention = CallingConvention.Cdecl)]
public static extern int close(IntPtr hDb);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_exec", CallingConvention = CallingConvention.Cdecl)] //
public static extern Result exec(IntPtr hDb, string sql, IntPtr callback, IntPtr args, out IntPtr errorMessage);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_prepare_v2", CallingConvention = CallingConvention.Cdecl)]
//public static extern sqlite.Result prepare_v2(IntPtr hDb, string zSql, int nByte, out IntPtr ppStmpt, IntPtr pzTail);
public static extern sqlite.Result prepare_v2(IntPtr hDb, IntPtr zSql, int nByte, out IntPtr ppStmpt, IntPtr pzTail);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_step", CallingConvention = CallingConvention.Cdecl)]
public static extern sqlite.Result step(IntPtr hStm);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_finalize", CallingConvention = CallingConvention.Cdecl)]
public static extern sqlite.Result finalize(IntPtr hStm);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_reset", CallingConvention = CallingConvention.Cdecl)] //
public static extern sqlite.Result reset(IntPtr hStm);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_clear_bindings", CallingConvention = CallingConvention.Cdecl)] //
public static extern sqlite.Result clear_bindings(IntPtr hStm);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_errmsg", CallingConvention = CallingConvention.Cdecl)]
public static extern string errmsg(IntPtr hDb);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_last_insert_rowid", CallingConvention = CallingConvention.Cdecl)]
public static extern long last_insert_rowid(IntPtr hDb);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_count", CallingConvention = CallingConvention.Cdecl)]
public static extern int column_count(IntPtr hStm);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_name", CallingConvention = CallingConvention.Cdecl)] //
public static extern IntPtr column_name(IntPtr hStm, int nCol);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_origin_name", CallingConvention = CallingConvention.Cdecl)]
public static extern string column_origin_name(IntPtr hStm, int nCol);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_type", CallingConvention = CallingConvention.Cdecl)]
public static extern sqlite.DataType column_type(IntPtr hStm, int nCol);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_int", CallingConvention = CallingConvention.Cdecl)]
public static extern int column_int(IntPtr hStm, int nCol);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_text", CallingConvention = CallingConvention.Cdecl)]
public static extern IntPtr column_text(IntPtr hStm, int nCol);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_double", CallingConvention = CallingConvention.Cdecl)]
public static extern double column_double(IntPtr hStm, int nCol);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_blob", CallingConvention = CallingConvention.Cdecl)] //
public static extern IntPtr column_blob(IntPtr hStm, int nCol);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_bytes", CallingConvention = CallingConvention.Cdecl)] //
public static extern double column_bytes(IntPtr hStm, int nCol);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_bind_parameter_count", CallingConvention = CallingConvention.Cdecl)] //
public static extern int bind_parameter_count(IntPtr hStm);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_bind_parameter_name", CallingConvention = CallingConvention.Cdecl)] //
public static extern IntPtr bind_parameter_name(IntPtr hStm, int n); // UTF-8 encoded return
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_bind_blob", CallingConvention = CallingConvention.Cdecl)] //
public static extern sqlite.Result bind_blob(IntPtr hStm, int n, byte[] blob, int length, IntPtr freetype);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_bind_double", CallingConvention = CallingConvention.Cdecl)] //
public static extern sqlite.Result bind_double(IntPtr hStm, int n, double value);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_bind_int", CallingConvention = CallingConvention.Cdecl)] //
public static extern sqlite.Result bind_int(IntPtr hStm, int n, int value);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_bind_int64", CallingConvention = CallingConvention.Cdecl)] //
public static extern sqlite.Result bind_int64(IntPtr hStm, int n, long value);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_bind_null", CallingConvention = CallingConvention.Cdecl)] //
public static extern sqlite.Result bind_null(IntPtr hStm, int n);
[DllImport("sqlite3.dll", EntryPoint = "sqlite3_bind_text", CallingConvention = CallingConvention.Cdecl)] //
public static extern sqlite.Result bind_text(IntPtr hStm, int n, byte[] value, int length, IntPtr freetype);
#region statics
public static byte[] ToUtf8(string sText) {
byte[] byteArray;
int nLen = Encoding.UTF8.GetByteCount(sText) + 1;
byteArray = new byte[nLen];
nLen = Encoding.UTF8.GetBytes(sText, 0, sText.Length, byteArray, 0);
byteArray[nLen] = 0;
return byteArray;
}
public static string Utf8ToString(IntPtr hStr) {
if (hStr == IntPtr.Zero) return "";
int nLen = lstrlen(hStr);
byte[] byteArray = new byte[nLen];
Marshal.Copy(hStr, byteArray, 0, nLen);
return Encoding.UTF8.GetString(byteArray, 0, nLen);
}
public static IntPtr Str2h(string str) {
if (str == null) { return IntPtr.Zero; }
byte[] buffer = Encoding.UTF8.GetBytes(str);
int length = buffer.Length + 1;
IntPtr hStr = HeapAlloc(GetProcessHeap(), 0, (UInt32)length);
Marshal.Copy(buffer, 0, hStr, buffer.Length);
Marshal.WriteByte(hStr, buffer.Length, 0);
return hStr;
}
public static String h2Str(IntPtr hStr) {
if (hStr == IntPtr.Zero) { return null; }
int nLen = GetPointerLength(hStr);
byte[] buffer = new byte[nLen];
Marshal.Copy(hStr, buffer, 0, nLen);
return Encoding.UTF8.GetString(buffer, 0, nLen);
}
private static int GetPointerLength(IntPtr hStr) {
if (hStr == IntPtr.Zero) { return 0; }
return lstrlen(hStr);
}
#endregion statics
public string sPath;
public IntPtr hDb = IntPtr.Zero;
public sqlite(string sPath) {
this.sPath = sPath;
if (sqlite.open(sPath, out hDb) != sqlite.Result.OK) throw new SQLiteException("Could not open database file: " + sPath);
}
~sqlite() {
if (hDb != IntPtr.Zero) {
sqlite.close(hDb);
hDb = IntPtr.Zero;
}
}
public sqliteStm Prepare(string SQL) {
if (hDb == IntPtr.Zero) throw new SQLiteException("Database closed: " + sPath);
return new sqliteStm(this, SQL);
}
public void OpenDatabase() {
if (sqlite.open(sPath, out hDb) != sqlite.Result.OK) throw new SQLiteException("Could not open database file: " + sPath);
}
public void CloseDatabase() {
if (hDb != IntPtr.Zero) {
sqlite.close(hDb);
hDb = IntPtr.Zero;
}
}
public long LastInsertRowid() {
return sqlite.last_insert_rowid(hDb);
}
public void Exec(string sSQL) {
IntPtr hError;
Result nResult;
if (hDb == IntPtr.Zero) throw new SQLiteException("SQLite database is not open.");
nResult = exec(hDb, sSQL, IntPtr.Zero, IntPtr.Zero, out hError);
if (nResult != Result.OK) throw new SQLiteException("Could not execute SQL statement.");
}
}
Class sqliteField (Query result column):These field are used to read an sqlite table.
public class sqliteField {
public string sName;
public int nColumn;
public sqlite.DataType eType;
public long nValue;
public string sValue;
public double fValue;
public bool bNull;
private IntPtr hStm = IntPtr.Zero;
public sqliteField(IntPtr hStm) {
this.hStm = hStm;
}
public void Read() {
bNull = false;
nValue = 0;
sValue = "";
fValue = 0d;
eType = sqlite.column_type(hStm, nColumn);
switch (eType) {
case sqlite.DataType.NULL:
bNull = true;
break;
case sqlite.DataType.INTEGER:
nValue = sqlite.column_int(hStm, nColumn);
break;
case sqlite.DataType.TEXT:
sValue = sqlite.Utf8ToString(sqlite.column_text(hStm, nColumn));
break;
case sqlite.DataType.FLOAT:
fValue = sqlite.column_double(hStm, nColumn);
break;
case sqlite.DataType.BLOB:
sValue = sqlite.Utf8ToString(sqlite.column_blob(hStm, nColumn));
break;
default:
throw new SQLiteException("Unknown datatype");
}
}
public bool IsNull() {
return bNull;
}
public bool BOOL {
get {
if (eType != sqlite.DataType.INTEGER) throw new SQLiteException("Datatype: " + eType.ToString());
return (nValue != 0);
}
}
public string TEXT {
get {
if (eType != sqlite.DataType.TEXT) throw new SQLiteException("Datatype: " + eType.ToString());
return sValue;
}
}
public long INTEGER {
get {
if (eType != sqlite.DataType.INTEGER) throw new SQLiteException("Datatype: " + eType.ToString());
return nValue;
}
}
public double DOUBLE {
get {
if (eType == sqlite.DataType.FLOAT) {
return fValue;
} else if (eType == sqlite.DataType.INTEGER) {
return (double)nValue;
} else {
throw new SQLiteException("Datatype: " + eType.ToString());
}
}
}
public DateTime DATE {
get {
if (eType != sqlite.DataType.TEXT) throw new SQLiteException("Datatype: " + eType.ToString());
DateTime dValue = DateTime.MinValue;
DateTime.TryParseExact(sValue, "yyyyMMdd", (IFormatProvider)null, System.Globalization.DateTimeStyles.None, out dValue);
return dValue;
}
}
public DateTime DATETIME {
get {
if (eType != sqlite.DataType.TEXT) throw new SQLiteException("Datatype: " + eType.ToString());
DateTime dValue = DateTime.MinValue;
DateTime.TryParseExact(sValue, "yyyyMMddTHHmmss", (IFormatProvider)null, System.Globalization.DateTimeStyles.None, out dValue);
return dValue;
}
}
public decimal DECIMAL(int nDec) {
if (eType != sqlite.DataType.INTEGER) throw new SQLiteException("Datatype: " + eType.ToString());
decimal n = nValue;
return nValue;
}
}
Class sqliteStm (SQL statement that needs parameters and/or returns resultset).public class sqliteStm {
internal sqliteStm(sqlite db, string sSQL) {
this.db = db;
this.nColumns = 0;
this.sSQL = sSQL;
this.hStm = IntPtr.Zero;
IntPtr hStr = sqlite.Str2h(sSQL);
sqlite.Result eResult = sqlite.prepare_v2(db.hDb, hStr, sqlite.lstrlen(hStr), out hStm, IntPtr.Zero);
if (eResult != sqlite.Result.OK) { throw new SQLiteException(sqlite.errmsg(db.hDb)); }
}
~sqliteStm() {
if (hStm != IntPtr.Zero) { FinalizeStm(); }
}
private readonly sqlite db;
private readonly string sSQL;
private IntPtr hStm;
private int nColumns;
public Dictionary Fields = new Dictionary();
public sqliteStm BindInt(int nParam, int nValue) {
if (sqlite.bind_int(hStm, nParam, nValue) != sqlite.Result.OK) throw new SQLiteException(sqlite.errmsg(db.hDb) + "\nBind failed, Parameter:" + nParam.ToString() + ", Value:" + nValue.ToString());
return this;
}
public sqliteStm BindBool(int nParam, bool bValue) {
if (sqlite.bind_int(hStm, nParam, bValue ? 1 : 0) != sqlite.Result.OK) throw new SQLiteException(sqlite.errmsg(db.hDb) + "\nBind failed, Parameter:" + nParam.ToString() + ", Value:" + (bValue ? "true" : "false"));
return this;
}
public sqliteStm BindNull(int nParam) {
if (sqlite.bind_null(hStm, nParam) != sqlite.Result.OK) throw new SQLiteException(sqlite.errmsg(db.hDb) + "\nBind failed, Parameter:" + nParam.ToString());
return this;
}
public sqliteStm BindText(int nParam, string sValue) {
if (sqlite.bind_text(hStm, nParam, sqlite.ToUtf8(sValue), -1, new IntPtr(-1)) != sqlite.Result.OK) throw new SQLiteException(sqlite.errmsg(db.hDb) + "\nBind failed, Parameter:" + nParam.ToString());
return this;
}
public sqliteStm BindDate(int nParam, DateTime dValue) {
if (sqlite.bind_text(hStm, nParam, sqlite.ToUtf8(dValue.ToString("yyyyMMdd")), -1, new IntPtr(-1)) != sqlite.Result.OK) throw new SQLiteException(sqlite.errmsg(db.hDb) + "\nBind failed, Parameter:" + nParam.ToString());
return this;
}
public sqliteStm BindDateTime(int nParam, DateTime dValue) {
if (sqlite.bind_text(hStm, nParam, sqlite.ToUtf8(dValue.ToString("yyyyMMddTHHmmss")), -1, new IntPtr(-1)) != sqlite.Result.OK) throw new SQLiteException(sqlite.errmsg(db.hDb) + "\nBind failed, Parameter:" + nParam.ToString());
return this;
}
public sqliteStm BindDec(int nParam, decimal nValue, int nDec) {
for (int n = nDec; n > 0; n--) { nValue *= 10; }
if (sqlite.bind_int(hStm, nParam, (int)Decimal.Round(nValue, MidpointRounding.AwayFromZero)) != sqlite.Result.OK) throw new SQLiteException(sqlite.errmsg(db.hDb) + "\nBind failed, Parameter:" + nParam.ToString() + ", Value:" + nValue.ToString());
return this;
}
public sqliteStm Step() {
sqlite.Result eResult = sqlite.step(hStm);
if (eResult != sqlite.Result.DONE) throw new SQLiteException(sqlite.errmsg(db.hDb));
return this;
}
public bool Read() {
bool bContinue = false;
sqliteField oField;
sqlite.Result eResult = sqlite.step(hStm);
if (eResult == sqlite.Result.DONE) {
//return false;
} else if (eResult == sqlite.Result.ROW) {
if (nColumns == 0) {
nColumns = sqlite.column_count(hStm);
Fields.Clear();
for (int nColumn = 0; nColumn < nColumns; nColumn++) {
oField = new sqliteField(hStm);
oField.nColumn = nColumn;
oField.sName = sqlite.Utf8ToString(sqlite.column_name(hStm, nColumn));
Fields.Add(oField.sName, oField);
}
}
foreach (string sColumn in Fields.Keys) { Fields[sColumn].Read(); }
bContinue = true;
} else {
throw new SQLiteException(eResult.ToString() + "\n" + sqlite.errmsg(db.hDb));
}
return bContinue;
}
public sqliteStm Reset() {
if (sqlite.reset(hStm) != sqlite.Result.OK) throw new SQLiteException(sqlite.errmsg(db.hDb));
return this;
}
public sqliteStm ClearBindings() {
if (sqlite.clear_bindings(hStm) != sqlite.Result.OK) throw new SQLiteException(sqlite.errmsg(db.hDb));
return this;
}
public sqliteStm FinalizeStm() {
if (sqlite.finalize(hStm) != sqlite.Result.OK) throw new SQLiteException(sqlite.errmsg(db.hDb));
hStm = IntPtr.Zero;
nColumns = 0;
return this;
}
}
Very nice and helpful post.
ReplyDeleteFor another open sorce dotnet provider for sqlite please see
http://bimbim.in/post/2011/03/10/Using-SQLite-Database-With-Dotnet-C-Sharp.aspx
What is the Dictionary data type? Which namespace it belongs to? It does not compile.
ReplyDeletewhat is the license for this code ?
ReplyDeleteThe code is "as it is". If you like it and you can guarantee that it works just copy it.
DeleteI would like a note of you on the use, but nothing mandatory. If you want.
Hey, great implementation. However, I wonder whether you have the implementation for the callback function in sqlite3_exec. In my small project I only need sqlite3_open and sqlite3_exec, and I cant seem to get the marshalling for the callback function correctly. The callback function is called by sqlite3, and I received the number of rows correctly, but I could not get the data correctly. Do you have any advise?
ReplyDelete