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(); }
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 (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(); }