wasCSharpSQLite

Subversion Repositories:
Compare Path: Rev
With Path: Rev
?path1? @ 3  →  ?path2? @ 4
/trunk/Community.CsharpSqlite.Benchmark/Classes/SQLiteDatabase.cs
@@ -0,0 +1,313 @@
// $Header$
using System;
using System.Collections;
using System.Data;
 
namespace Community.CsharpSqlite
{
 
using sqlite = Sqlite3.sqlite3;
using Vdbe = Sqlite3.Vdbe;
/// <summary>
/// C#-SQLite wrapper with functions for opening, closing and executing queries.
/// </summary>
public class SQLiteDatabase
{
// pointer to database
private sqlite db;
 
/// <summary>
/// Creates new instance of SQLiteBase class with no database attached.
/// </summary>
public SQLiteDatabase()
{
db = null;
}
/// <summary>
/// Creates new instance of SQLiteDatabase class and opens database with given name.
/// </summary>
/// <param name="DatabaseName">Name (and path) to SQLite database file</param>
public SQLiteDatabase( String DatabaseName )
{
OpenDatabase( DatabaseName );
}
 
/// <summary>
/// Opens database.
/// </summary>
/// <param name="DatabaseName">Name of database file</param>
public void OpenDatabase( String DatabaseName )
{
// opens database
if (
#if NET_35
Sqlite3.Open
#else
Sqlite3.sqlite3_open
#endif
( DatabaseName, out db ) != Sqlite3.SQLITE_OK )
{
// if there is some error, database pointer is set to 0 and exception is throws
db = null;
throw new Exception( "Error with opening database " + DatabaseName + "!" );
}
}
 
/// <summary>
/// Closes opened database.
/// </summary>
public void CloseDatabase()
{
// closes the database if there is one opened
if ( db != null )
{
#if NET_35
Sqlite3.Close
#else
Sqlite3.sqlite3_close
#endif
( db );
}
}
 
/// <summary>
/// Returns connection
/// </summary>
public sqlite Connection()
{
return db;
}
 
/// <summary>
/// Returns the list of tables in opened database.
/// </summary>
/// <returns></returns>
public ArrayList GetTables()
{
// executes query that select names of all tables in master table of the database
String query = "SELECT name FROM sqlite_master " +
"WHERE type = 'table'" +
"ORDER BY 1";
DataTable table = ExecuteQuery( query );
 
// Return all table names in the ArrayList
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
Sqlite3.exec( db, query, 0, 0, 0 );
// if there is error, excetion is thrown
if ( db.errCode != Sqlite3.SQLITE_OK )
throw new Exception( "Error with executing non-query: \"" + query + "\"!\n" +
#if NET_35
Sqlite3.Errmsg
#else
Sqlite3.sqlite3_errmsg
#endif
( db ) );
}
 
/// <summary>
/// Executes query that does return something (e.g. SELECT).
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable ExecuteQuery( String query )
{
// compiled query
SQLiteVdbe statement = new SQLiteVdbe( this, query );
 
// table for result of query
DataTable table = new DataTable();
 
// create new instance of DataTable with name "resultTable"
table = new DataTable( "resultTable" );
 
// reads rows
do { } while ( ReadNextRow( statement.VirtualMachine(), table ) == Sqlite3.SQLITE_ROW );
// finalize executing this query
statement.Close();
 
// returns table
return table;
}
 
// private function for reading rows and creating table and columns
private int ReadNextRow( Vdbe vm, DataTable table )
{
int columnCount = table.Columns.Count;
if ( columnCount == 0 )
{
if ( ( columnCount = ReadColumnNames( vm, table ) ) == 0 ) return Sqlite3.SQLITE_ERROR;
}
 
int resultType;
if ( ( resultType =
#if NET_35
Sqlite3.Step
#else
Sqlite3.sqlite3_step
#endif
( vm ) ) == Sqlite3.SQLITE_ROW )
{
object[] columnValues = new object[columnCount];
 
for ( int i = 0; i < columnCount; i++ )
{
int columnType =
#if NET_35
Sqlite3.ColumnType
#else
Sqlite3.sqlite3_column_type
#endif
( vm, i );
switch ( columnType )
{
case Sqlite3.SQLITE_INTEGER:
{
table.Columns[i].DataType = typeof( Int64 );
columnValues[i] =
#if NET_35
Sqlite3.ColumnInt
#else
Sqlite3.sqlite3_column_int
#endif
( vm, i );
break;
}
case Sqlite3.SQLITE_FLOAT:
{
table.Columns[i].DataType = typeof( Double );
columnValues[i] =
#if NET_35
Sqlite3.ColumnDouble
#else
Sqlite3.sqlite3_column_double
#endif
( vm, i );
break;
}
case Sqlite3.SQLITE_TEXT:
{
table.Columns[i].DataType = typeof( String );
columnValues[i] =
#if NET_35
Sqlite3.ColumnText
#else
Sqlite3.sqlite3_column_text
#endif
( vm, i );
break;
}
case Sqlite3.SQLITE_BLOB:
{
table.Columns[i].DataType = typeof( Byte[] );
columnValues[i] =
#if NET_35
Sqlite3.ColumnBlob
#else
Sqlite3.sqlite3_column_blob
#endif
( vm, i );
break;
}
default:
{
table.Columns[i].DataType = null;
columnValues[i] = "";
break;
}
}
}
table.Rows.Add( columnValues );
}
return resultType;
}
// private function for creating Column Names
// Return number of colums read
private int ReadColumnNames( Vdbe vm, DataTable table )
{
 
String columnName = "";
int columnType = 0;
// returns number of columns returned by statement
int columnCount =
#if NET_35
Sqlite3.ColumnCount
#else
Sqlite3.sqlite3_column_count
#endif
( vm );
object[] columnValues = new object[columnCount];
 
try
{
// reads columns one by one
for ( int i = 0; i < columnCount; i++ )
{
columnName =
#if NET_35
Sqlite3.ColumnName
#else
Sqlite3.sqlite3_column_name
#endif
( vm, i );
columnType =
#if NET_35
Sqlite3.ColumnType
#else
Sqlite3.sqlite3_column_type
#endif
( vm, i );
 
switch ( columnType )
{
case Sqlite3.SQLITE_INTEGER:
{
// adds new integer column to table
table.Columns.Add( columnName, Type.GetType( "System.Int64" ) );
break;
}
case Sqlite3.SQLITE_FLOAT:
{
table.Columns.Add( columnName, Type.GetType( "System.Double" ) );
break;
}
case Sqlite3.SQLITE_TEXT:
{
table.Columns.Add( columnName, Type.GetType( "System.String" ) );
break;
}
case Sqlite3.SQLITE_BLOB:
{
table.Columns.Add( columnName, Type.GetType( "System.byte[]" ) );
break;
}
default:
{
table.Columns.Add( columnName, Type.GetType( "System.String" ) );
break;
}
}
}
}
catch
{
return 0;
}
return table.Columns.Count;
}
 
}
}
/trunk/Community.CsharpSqlite.Benchmark/Classes/SQLiteVdbe.cs
@@ -0,0 +1,210 @@
// $Header$
using System;
 
namespace Community.CsharpSqlite
{
 
using Vdbe = Sqlite3.Vdbe;
 
/// <summary>
/// C#-SQLite wrapper with functions for opening, closing and executing queries.
/// </summary>
public class SQLiteVdbe
{
private Vdbe vm = null;
private string LastError = "";
private int LastResult = 0;
 
/// <summary>
/// Creates new instance of SQLiteVdbe class by compiling a statement
/// </summary>
/// <param name="query"></param>
/// <returns>Vdbe</returns>
public SQLiteVdbe( SQLiteDatabase db, String query )
{
vm = null;
 
// prepare and compile
#if NET_35
Sqlite3.PrepareV2NoTail
#else
Sqlite3.sqlite3_prepare_v2
#endif
( db.Connection(), query, query.Length, ref vm, 0 );
}
 
/// <summary>
/// Return Virtual Machine Pointer
/// </summary>
/// <param name="query"></param>
/// <returns>Vdbe</returns>
public Vdbe VirtualMachine()
{
return vm;
}
 
/// <summary>
/// <summary>
/// BindInteger
/// </summary>
/// <param name="index"></param>
/// <param name="bInteger"></param>
/// <returns>LastResult</returns>
public int BindInteger( int index, int bInteger )
{
if ( ( LastResult =
#if NET_35
Sqlite3.BindInt
#else
Sqlite3.sqlite3_bind_int
#endif
( vm, index, bInteger ) ) == Sqlite3.SQLITE_OK )
{ LastError = ""; }
else
{
LastError = "Error " + LastError + "binding Integer [" + bInteger + "]";
}
return LastResult;
}
 
/// <summary>
/// <summary>
/// BindLong
/// </summary>
/// <param name="index"></param>
/// <param name="bLong"></param>
/// <returns>LastResult</returns>
public int BindLong( int index, long bLong )
{
if ( ( LastResult =
#if NET_35
Sqlite3.BindInt64
#else
Sqlite3.sqlite3_bind_int64
#endif
( vm, index, bLong ) ) == Sqlite3.SQLITE_OK )
{ LastError = ""; }
else
{
LastError = "Error " + LastError + "binding Long [" + bLong + "]";
}
return LastResult;
}
 
/// <summary>
/// BindText
/// </summary>
/// <param name="index"></param>
/// <param name="bLong"></param>
/// <returns>LastResult</returns>
public int BindText( int index, string bText )
{
if ( ( LastResult =
#if NET_35
Sqlite3.BindText
#else
Sqlite3.sqlite3_bind_text
#endif
( vm, index, bText, -1, null ) ) == Sqlite3.SQLITE_OK )
{ LastError = ""; }
else
{
LastError = "Error " + LastError + "binding Text [" + bText + "]";
}
return LastResult;
}
 
/// <summary>
/// Execute statement
/// </summary>
/// </param>
/// <returns>LastResult</returns>
public int ExecuteStep()
{
// Execute the statement
int LastResult =
#if NET_35
Sqlite3.Step
#else
Sqlite3.sqlite3_step
#endif
( vm );
return LastResult;
}
 
/// <summary>
/// Returns Result column as Long
/// </summary>
/// </param>
/// <returns>Result column</returns>
public long Result_Long( int index )
{
return
#if NET_35
Sqlite3.ColumnInt64
#else
Sqlite3.sqlite3_column_int64
#endif
( vm, index );
}
 
/// <summary>
/// Returns Result column as Text
/// </summary>
/// </param>
/// <returns>Result column</returns>
public string Result_Text( int index )
{
return
#if NET_35
Sqlite3.ColumnText
#else
Sqlite3.sqlite3_column_text
#endif
( vm, index );
}
 
 
/// <summary>
/// Returns Count of Result Rows
/// </summary>
/// </param>
/// <returns>Count of Results</returns>
public int ResultColumnCount()
{
return vm.pResultSet == null ? 0 : vm.pResultSet.Length;
}
 
/// <summary>
/// Reset statement
/// </summary>
/// </param>
/// </returns>
public void Reset()
{
// Reset the statment so it's ready to use again
#if NET_35
Sqlite3.Reset
#else
Sqlite3.sqlite3_reset
#endif
( vm );
}
 
/// <summary>
/// Closes statement
/// </summary>
/// </param>
/// <returns>LastResult</returns>
public void Close()
{
#if NET_35
Sqlite3.Finalize
#else
Sqlite3.sqlite3_finalize
#endif
( vm );
}
 
}
}