|
How about your wrapper perfomance? How much it slower than sqlite.dll?
|
|
|
|
|
any updates about it ?
kiquenet.com
|
|
|
|
|
Hello
Thank You For This DLL
But My Problem is That When Wanna To Connect to DB
Sqlite3.dll
Is Not Found????
Please Help Me ,...
Thanks
|
|
|
|
|
Many thanks for providing class!
I'm been looking for something like this.
|
|
|
|
|
How do I open sqlite db with password or setup password for database through this wrapper class?
Rgds,
Moez.
|
|
|
|
|
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??
|
|
|
|
|
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.
|
|
|
|
|
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
{
public class db_SQLite
{
[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", 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);
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;
private readonly string _database;
public db_SQLite(String baseName)
{
_database = baseName;
}
protected 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);
database = IntPtr.Zero;
}
}
public void Open() { if (database == IntPtr.Zero) OpenDatabase(_database); }
public void Close() { }
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(string.Format("Error with executing non-query: \"{0}\"!\n", query) + errorMessage);
}
HeapFree(GetProcessHeap(), 0, ptr);
}
public DataTable ExecuteQuery(String query)
{
IntPtr statement;
IntPtr excessData;
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");
DataTable table = new DataTable();
int result = ReadFirstRow(statement, ref table);
while (result == SQL_ROW)
{
result = ReadNextRow(statement, ref table);
}
sqlite3_finalize(statement);
HeapFree(GetProcessHeap(), 0, ptr);
return table;
}
private int ReadFirstRow(IntPtr statement, ref DataTable table)
{
table = new DataTable("resultTable");
int resultType = sqlite3_step(statement);
if (resultType == SQL_ROW)
{
int columnCount = sqlite3_column_count(statement);
String columnName = "";
int columnType = 0;
object[] columnValues = new object[columnCount];
for (int i = 0; i < columnCount; i++)
{
columnName = PointerToString(sqlite3_column_name(statement, i));
columnType = sqlite3_column_type(statement, i);
switch (columnType)
{
case (int)SQLiteDataTypes.INT:
{
table.Columns.Add(columnName, Type.GetType("System.Int32"));
columnValues[i] = sqlite3_column_int(statement, i);
break;
}
case (int)SQLiteDataTypes.FLOAT:
{
table.Columns.Add(columnName, Type.GetType("System.Single"));
columnValues[i] = sqlite3_column_double(statement, i);
break;
}
case (int)SQLiteDataTypes.TEXT:
{
table.Columns.Add(columnName, Type.GetType("System.String"));
columnValues[i] = PointerToString(sqlite3_column_text(statement, i));
break;
}
case (int)SQLiteDataTypes.BLOB:
{
table.Columns.Add(columnName, Type.GetType("System.String"));
columnValues[i] = PointerToString(sqlite3_column_blob(statement, i));
break;
}
default:
{
table.Columns.Add(columnName, Type.GetType("System.String"));
columnValues[i] = "";
break;
}
}
}
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");
return sqlite3_step(statement);
}
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);
}
private IntPtr StringToPointer(String str)
{
if (str == null)
{
return IntPtr.Zero;
}
else
{
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;
}
}
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);
}
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?
|
|
|
|
|
Just me forgetting to strip that out.
|
|
|
|
|
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?
|
|
|
|
|
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!!
|
|
|
|
|
|
hi everyone!
how can I create a new database in file e.g. test.db?
|
|
|
|
|
Just call
SQLiteBase db = new SQLiteBase("test.db");
If the file doesn't exist, it's created by default
|
|
|
|
|
"SQLiteBase db = new SQLiteBase("test.db");"
throws an exception no matter what I do...
|
|
|
|
|
What's the text of the exception?
|
|
|
|
|
"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
|
|
|
|
|
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 , 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);
}
public DataTable ExecuteQuery(String query)
{
IntPtr statement;
IntPtr excessData;
IntPtr ptr = StringToPointer(query);
sqlite3_prepare_v2(database, ptr, GetPointerLenght(ptr), out statement, out excessData);
DataTable table = new DataTable();
int result = ReadFirstRow(statement, ref table);
while (result == SQL_ROW)
{
result = ReadNextRow(statement, ref table);
}
sqlite3_finalize(statement);
HeapFree(GetProcessHeap(), 0, ptr);
return table;
}
private int ReadFirstRow(IntPtr statement, ref DataTable table)
{
table = new DataTable("resultTable");
int resultType = sqlite3_step(statement);
if (resultType == SQL_ROW)
{
int columnCount = sqlite3_column_count(statement);
String columnName = "";
int columnType = 0;
object[] columnValues = new object[columnCount];
for (int i = 0; i < columnCount; i++)
{
columnName = PointerToString(sqlite3_column_name(statement, i));
columnType = sqlite3_column_type(statement, i);
switch (columnType)
{
case (int)SQLiteDataTypes.INT:
{
table.Columns.Add(columnName, Type.GetType("System.Int32"));
columnValues[i] = sqlite3_column_int(statement, i);
break;
}
case (int)SQLiteDataTypes.FLOAT:
{
table.Columns.Add(columnName, Type.GetType("System.Single"));
columnValues[i] = sqlite3_column_double(statement, i);
break;
}
case (int)SQLiteDataTypes.TEXT:
{
table.Columns.Add(columnName, Type.GetType("System.String"));
columnValues[i] = PointerToString(sqlite3_column_text(statement, i));
break;
}
case (int)SQLiteDataTypes.BLOB:
{
table.Columns.Add(columnName, Type.GetType("System.String"));
columnValues[i] = PointerToString(sqlite3_column_blob(statement, i));
break;
}
default:
{
table.Columns.Add(columnName, Type.GetType("System.String"));
columnValues[i] = "";
break;
}
}
}
table.Rows.Add(columnValues);
}
return sqlite3_step(statement);
}
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);
}
private IntPtr StringToPointer(String str)
{
if (str == null)
{
return IntPtr.Zero;
}
else
{
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;
}
}
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);
}
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.
|
|
|
|
|
I used oren.shnitzer's modified code and inserted ~20 million records into a table without any leaks.
Thanks Oren
---------------
Sten Hjelmqvist
|
|
|
|
|
I wish to thank you for your input the hard work is appreciated
|
|
|
|
|