public void InsertRecords()
{
var db = OpenDB(databaseName);
db.ExecuteNonQuery("BEGIN");
var stmt = new SQLiteVdbe(db, INSERT_Command);
long key = 1999;
for (var i = 0; i < 1000; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
stmt.Reset();
stmt.BindLong(1, key);
stmt.BindText(2, key.ToString());
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery("END");
db.CloseDatabase();
}
/// <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) == CSSQLite.SQLITE_ROW);
// finalize executing this query
statement.Close();
// returns table
return(table);
}
public RowToken (SQLiteVdbe statement) {
Statement = statement;
Columns = new ColumnSet { Row = this };
}
private static void TestcsSQLite()
{
SQLiteDatabase db;
SQLiteVdbe stmt;
SQLiteVdbe c1, c2;
bool found;
int i;
string databaseName = "Benchmark_cs-SQLite.sqlite";
Console.WriteLine( "\n\r" + databaseName );
if ( File.Exists( databaseName ) ) File.Delete( databaseName );
db = new SQLiteDatabase( databaseName );
for (i = 0; i < PRAGMA_Commands.Length; i++) {db.ExecuteNonQuery( PRAGMA_Commands[i]);}
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
for (i = 0; i < CREATE_Commands.Length; i++) {db.ExecuteNonQuery( CREATE_Commands[i]);}
stmt = new SQLiteVdbe( db, INSERT_Command );
long start = DateTime.Now.Ticks;
long key = 1999;
for ( i = 0 ; i < nRecords ; i++ )
{
key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
stmt.Reset();
stmt.BindLong( 1, key );
stmt.BindText( 2, key.ToString() );
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery( "END" );
Console.WriteLine( "inserting " + nRecords + " records: "
+ ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" );
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
start = DateTime.Now.Ticks;
c1 = new SQLiteVdbe( db, SELECT_Bind_i );
c2 = new SQLiteVdbe( db, SELECT_Bind_s);
key = 1999;
for ( i = 0 ; i < nRecords ; i++ )
{
key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
c1.Reset();
c1.BindLong( 1, key );
c1.ExecuteStep();
c2.Reset();
c2.BindText( 1, key.ToString() );
c2.ExecuteStep();
long id = (long)c1.Result_Long( 0 );
Debug.Assert( id == ( long)c2.Result_Long(0) );
}
c1.Close();
c2.Close();
db.ExecuteNonQuery( "END" );
Console.WriteLine( "performing " + nRecords * 2 + " index searches: "
+ ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" );
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
start = DateTime.Now.Ticks;
key = Int64.MinValue;
i = 0;
c1 = new SQLiteVdbe( db, SELECT_Command_i );
while ( c1.ExecuteStep() != csSQLite.SQLITE_DONE )
{
long intKey = (long)c1.Result_Long( 0 );
Debug.Assert( intKey>= key );
key = intKey;
i += 1;
}
c1.Close();
Debug.Assert( i == nRecords );
String strKey = "";
i = 0;
c2 = new SQLiteVdbe( db, SELECT_Command_s );
while ( c2.ExecuteStep() != csSQLite.SQLITE_DONE )
{
string recStrKey = (string)c2.Result_Text( 1 );
Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 );
strKey = recStrKey;
i += 1;
}
c2.Close();
Debug.Assert( i == nRecords );
Console.WriteLine( "iterating through " + ( nRecords * 2 ) + " records: "
+ ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" );
db.ExecuteNonQuery( "END" );
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
start = DateTime.Now.Ticks;
key = 1999;
stmt = new SQLiteVdbe( db, DELETE_Bind);
for ( i = 0 ; i < nRecords ; i++ )
{
key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
stmt.Reset();
stmt.BindLong( 1, key );
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery( "END" );
Console.WriteLine( "deleting " + nRecords + " records: "
+ ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" );
db.CloseDatabase();
}
/// <summary>
/// Executes query that does return something (e.g. SELECT).
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable ExecuteQuery( String query )
{
// compiled query
using (SQLiteVdbe statement = new SQLiteVdbe(this, query)) {
return ExecuteQuery(statement);
}
}
/// <summary>
/// Executes query that does return something (e.g. SELECT).
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable ExecuteQuery (SQLiteVdbe statement) {
// 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) == csSQLite.SQLITE_ROW);
statement.Reset();
// returns table
return table;
}
public void ExecuteNonQuery (SQLiteVdbe statement) {
var vm = statement.VirtualMachine();
while (true) {
var rc = csSQLite.sqlite3_step(vm);
if (rc == csSQLite.SQLITE_DONE)
break;
else if (rc == csSQLite.SQLITE_MISUSE)
throw new InvalidOperationException();
if (db.errCode != csSQLite.SQLITE_OK)
throw new Exception("Error with executing non-query!\n" + csSQLite.sqlite3_errmsg(db));
}
statement.Reset();
}
/// <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 ) == CSSQLite.SQLITE_ROW );
// finalize executing this query
statement.Close();
// returns table
return table;
}