wasCSharpSQLite – Rev 1
?pathlinks?
using System;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Text;
using System.Threading;
using System.Collections.Generic;
using Community.CsharpSqlite;
using Community.CsharpSqlite.SQLiteClient;
using System.Globalization;
namespace SQLiteClientTests
{
public class SQLiteClientTestDriver
{
public void Test1()
{
Console.WriteLine( "Test1 Start." );
Console.WriteLine( "Create connection..." );
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format( "Version=3;uri=file:{0}", dbFilename );
Console.WriteLine( "Set connection String: {0}", cs );
if ( File.Exists( dbFilename ) )
File.Delete( dbFilename );
con.ConnectionString = cs;
Console.WriteLine( "Open database..." );
con.Open();
Console.WriteLine( "create command..." );
IDbCommand cmd = con.CreateCommand();
Console.WriteLine( "create table TEST_TABLE..." );
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
Console.WriteLine( "insert row 1..." );
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
Console.WriteLine( "insert row 2..." );
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine( "SELECT data from TEST_TABLE..." );
cmd.CommandText = "SELECT COLA, COLB, COLC FROM TEST_TABLE";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine( "Read the data..." );
while ( reader.Read() )
{
Console.WriteLine( " Row: {0}", r );
int i = reader.GetInt32( reader.GetOrdinal( "COLA" ) );
Console.WriteLine( " COLA: {0}", i );
string s = reader.GetString( reader.GetOrdinal( "COLB" ) );
Console.WriteLine( " COLB: {0}", s );
DateTime dt = reader.GetDateTime( reader.GetOrdinal( "COLC" ) );
Console.WriteLine( " COLB: {0}", dt.ToString( "MM/dd/yyyy HH:mm:ss" ) );
r++;
}
Console.WriteLine( "Rows retrieved: {0}", r );
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
SqliteCommand command = new SqliteCommand( "PRAGMA table_info('TEST_TABLE')", con );
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill( dataTable );
DisplayDataTable( dataTable, "Columns" );
#endif
Console.WriteLine( "Close and cleanup..." );
con.Close();
con = null;
Console.WriteLine( "Test1 Done." );
}
public void Test2()
{
Console.WriteLine( "Test2 Start." );
Console.WriteLine( "Create connection..." );
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format( "Version=3,uri=file:{0}", dbFilename );
Console.WriteLine( "Set connection String: {0}", cs );
if ( File.Exists( dbFilename ) )
File.Delete( dbFilename );
con.ConnectionString = cs;
Console.WriteLine( "Open database..." );
con.Open();
Console.WriteLine( "create command..." );
IDbCommand cmd = con.CreateCommand();
Console.WriteLine( "create table TEST_TABLE..." );
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)";
cmd.ExecuteNonQuery();
Console.WriteLine( "insert row 1..." );
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )";
cmd.ExecuteNonQuery();
Console.WriteLine( "insert row 2..." );
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, 'ä¸æ–‡' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine( "SELECT data from TBL..." );
cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = 'ä¸æ–‡'";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine( "Read the data..." );
while ( reader.Read() )
{
Console.WriteLine( " Row: {0}", r );
int i = reader.GetInt32( reader.GetOrdinal( "ID" ) );
Console.WriteLine( " ID: {0}", i );
string s = reader.GetString( reader.GetOrdinal( "NAME" ) );
Console.WriteLine( " NAME: {0} = {1}", s, s == "ä¸æ–‡" );
r++;
}
Console.WriteLine( "Rows retrieved: {0}", r );
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
SqliteCommand command = new SqliteCommand( "PRAGMA table_info('TEST_TABLE')", con );
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill( dataTable );
DisplayDataTable( dataTable, "Columns" );
#endif
Console.WriteLine( "Close and cleanup..." );
con.Close();
con = null;
Console.WriteLine( "Test1 Done." );
}
public void Test3()
{
Console.WriteLine( "Test3 (Date Paramaters) Start." );
Console.WriteLine( "Create connection..." );
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format( "Version=3,uri=file:{0}", dbFilename );
Console.WriteLine( "Set connection String: {0}", cs );
if ( File.Exists( dbFilename ) )
File.Delete( dbFilename );
con.ConnectionString = cs;
Console.WriteLine( "Open database..." );
con.Open();
Console.WriteLine( "create command..." );
IDbCommand cmd = con.CreateCommand();
Console.WriteLine( "create table TEST_TABLE..." );
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, DATE_TEXT REAL)";
cmd.ExecuteNonQuery();
Console.WriteLine( "insert ..." );
cmd.CommandText = "INSERT INTO TBL ( ID, DATE_TEXT) VALUES ( 1, @DATETEXT)";
cmd.Parameters.Add(
new SqliteParameter
{
ParameterName = "@DATETEXT",
Value = DateTime.Now
}
);
cmd.ExecuteNonQuery();
Console.WriteLine( "SELECT data from TBL..." );
cmd.CommandText = "SELECT * FROM tbl";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine( "Read the data..." );
while ( reader.Read() )
{
Console.WriteLine( " Row: {0}", r );
int i = reader.GetInt32( reader.GetOrdinal( "ID" ) );
Console.WriteLine( " ID: {0}", i );
string s = reader.GetString( reader.GetOrdinal( "DATE_TEXT" ) );
Console.WriteLine( " DATE_TEXT: {0}", s );
r++;
}
Console.WriteLine( "Rows retrieved: {0}", r );
Console.WriteLine( "Close and cleanup..." );
con.Close();
con = null;
Console.WriteLine( "Test3 Done." );
}
//nSoftware code for Threading
string connstring_T4;
public void Test4()
{
string dbFilename = "threading_t4.db";
if ( File.Exists( dbFilename ) )
File.Delete( dbFilename );
connstring_T4 = @"Version=3,busy_timeout=100,uri=file:" + dbFilename;
Setup_T4();
InsertSameTable_T4(); //concurrent inserts
SelectorWrite_T4(); //concurrent selects and inserts
Console.WriteLine( "Testing for Threading done. Press enter to continue" );
Console.In.Read();
}
private void SelectorWrite_T4()
{
//concurrent reads/writes in the same table, if there were only Selects it would be preferable for the sqlite engine not to lock internally.
for ( int i = 0; i < 10; i++ )
{
Console.WriteLine( "SELECT/INSERT ON Thread {0}", i );
Thread worker = new Thread( () =>
{
// Cannot use value of i, since it exceeds the scope of this thread and will be
// reused by multiple threads
int aValue = 100 + Thread.CurrentThread.ManagedThreadId;
int op = aValue % 2;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T4;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
if ( op == 0 )
{
cmd.CommandText = String.Format( "Select * FROM ATABLE" );
cmd.ExecuteReader();
}
else
{
cmd.CommandText = String.Format( "INSERT INTO ATABLE ( A, B, C ) VALUES ({0},'threader', '1' )", aValue );
Console.WriteLine( cmd.CommandText );
cmd.ExecuteNonQuery();
}
} );
worker.Start();
}
}
//we need concurrency support on a table level inside of the database file.
private void InsertSameTable_T4()
{
for ( int i = 0; i < 10; i++ )
{
Console.WriteLine( "INSERTING ON Thread {0}", i );
Thread worker = new Thread( () =>
{
// Cannot use value of i, since it exceeds the scope of this thread and will be
// reused by multiple threads
int aValue = Thread.CurrentThread.ManagedThreadId;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T4;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
cmd.CommandText = String.Format( "INSERT INTO ATABLE ( A, B, C ) VALUES ({0},'threader', '1' )", aValue );
Console.WriteLine( cmd.CommandText );
cmd.ExecuteNonQuery();
}
);
worker.Start();
}
}
private void Setup_T4()
{
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T4;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" );
cmd.ExecuteNonQuery();
}
// Software code for Threading
string connstring_T5;
public void Test5()
{
string dbFilename = "threading_t5.db";
if ( File.Exists( dbFilename ) )
File.Delete( dbFilename );
connstring_T5 = @"Version=3,busy_timeout=2500,uri=file:" + dbFilename;
Setup_T5();
MultiInsertsSameThread_T5(); //concurrent inserts
Console.WriteLine( "Threads are running..." );
Console.In.Read();
}
private void MultiInsertsSameThread_T5()
{
for ( int i = 0; i < 10; i++ )
{
//Console.WriteLine( "SELECT/INSERT ON Thread {0}", i );
Thread worker = new Thread( () =>
{
string commandt = String.Empty;
try
{
// Cannot use value of i, since it exceeds the scope of this thread and will be
// reused by multiple threads
int aValue = 100 + Thread.CurrentThread.ManagedThreadId;
int op = aValue % 2;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T5;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
if ( op == 0 )
{
for ( int j = 0; j < 1000; j++ )
{
int rows;
int retry = 0;
cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES ({0},'threader', '1' )", ( aValue * 10000 ) + j );
commandt = cmd.CommandText;
do
{
rows = cmd.ExecuteNonQuery();
if ( rows == 0 )
{
retry += 1; // Insert Failed
Console.WriteLine( cmd.CommandText );
Console.WriteLine( "retry {0}", retry );
Console.WriteLine( ( (SqliteCommand)cmd ).GetLastError() );
}
} while ( rows == 0 && retry < 5 );
}
}
else
{
cmd.CommandText = String.Format( "Select * FROM ATABLE" );
commandt = cmd.CommandText;
cmd.ExecuteReader();
}
}
catch ( Exception ex )
{
Console.WriteLine( String.Format( "Command {0} threw exception {1}", commandt, ex.Message ) );
}
} );
worker.Start();
}
}
private void Setup_T5()
{
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T5;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" );
cmd.ExecuteNonQuery();
}
// Software code for Threading & Transactions
static string connstring_T6;
public void Test6()
{
string dbFilename = "threading_t6.db";
if ( File.Exists( dbFilename ) )
File.Delete( dbFilename );
connstring_T6 = @"Version=3,busy_timeout=2000,uri=file:" + dbFilename;
Setup_T6();
MultiInsertsTransactionsSameThread_T6(); //concurrent inserts
Console.WriteLine( "Threads are running..." );
Console.In.Read();
}
static Random rnd = new Random();
private void MultiInsertsTransactionsSameThread_T6()
{
for ( int i = 0; i < 20; i++ )
{
Thread.Sleep( rnd.Next( 100, 1000 ) );
Console.WriteLine( "Launching Thread {0}", i );
Thread worker = new Thread( SQLiteClientTestDriver.T6_ThreadStart );
worker.Start( i );
}
}
private static void T6_ThreadStart( object data )
{
string commandt = String.Empty;
int i = (int)data;
try
{
int aValue = 100 + i;
int op = aValue % 2;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T6;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
if ( op == 0 )
{
SqliteTransaction trans = (SqliteTransaction)con.BeginTransaction();
for ( int j = 0; j < 5000; j++ )
{
int rows;
int retry = 0;
cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES ({0},'threader', '1' )", ( aValue * 10000 ) + j );
commandt = cmd.CommandText;
do
{
rows = cmd.ExecuteNonQuery();
if ( rows == 0 )
{
retry += 1; // Insert Failed
Console.WriteLine( "retry {0}:{1}:{2}", retry, ( (SqliteCommand)cmd ).GetLastError(), cmd.CommandText );
Thread.Sleep( rnd.Next( 50, 1000 ) );
}
} while ( rows == 0 && retry < 10 );
}
trans.Commit();
}
else
{
cmd.CommandText = String.Format( "Select * FROM ATABLE" );
commandt = cmd.CommandText;
cmd.ExecuteReader();
}
}
catch ( Exception ex )
{
Console.WriteLine( String.Format( "Command {0} threw exception {1}", commandt, ex.Message ) );
}
}
private void Setup_T6()
{
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T6;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" );
cmd.ExecuteNonQuery();
}
// Software code for Threading
string connstring_T7;
public void Test7()
{
string dbFilename = "threading_t7.db";
if ( File.Exists( dbFilename ) )
File.Delete( dbFilename );
connstring_T7 = @"Version=3,busy_timeout=1000,uri=file:" + dbFilename;
MultiInsertsSameThread_T7(); //concurrent table creation
Console.WriteLine( "Threads are running..." );
Console.In.Read();
}
private void MultiInsertsSameThread_T7()
{
List<Thread> lthread = new List<Thread>();
for ( int i = 0; i < 70; i++ )
{
Thread.Sleep( rnd.Next( 100, 1000 ) );
Console.WriteLine( "Launching Thread {0}", i );
Thread worker = new Thread( this.T7_ThreadStart );
lthread.Add( worker );
worker.Start( i );
}
bool alldone = false;
while ( !alldone )
{
alldone = true;
for ( int i = 0; i < lthread.Count; i++ )
{
if ( lthread[i].ThreadState == ThreadState.Running )
alldone = false;
Thread.Sleep( 100 );
}
}
Console.WriteLine( "finished" );
}
private void T7_ThreadStart( object iSequence )
{
int aValue = (int)iSequence * 1000;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T7;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
string commandt = String.Format( "CREATE TABLE IF NOT EXISTS ATABLE{0}(A integer primary key , B varchar (50), C integer, D varchar (500))", aValue );
cmd.CommandText = commandt;
try
{
cmd.ExecuteNonQuery();
Console.WriteLine( "Created table: ATABLE" + aValue );
}
catch ( Exception ex )
{
Console.WriteLine( String.Format( "Command {0} threw exception {1}", commandt, ex.Message ) );
}
}
private void Setup_T7()
{
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T7;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" );
cmd.ExecuteNonQuery();
}
// Connectio string parsing tests/
public void Test8 ()
{
Console.WriteLine ("Test8 Start.");
Console.WriteLine ("Create connection...");
SqliteConnection con = new SqliteConnection ();
string dbFilename = @"SqliteTest3.db";
// Test Read Only = True, missing db file
string cs = string.Format ("Version=3;Read Only=True;uri=file:{0}", dbFilename);
Console.WriteLine ("Set connection string: {0}", cs);
if (File.Exists (dbFilename))
File.Delete (dbFilename);
con.ConnectionString = cs;
Console.WriteLine ("Open database...");
con.Open ();
Console.WriteLine ("create command...");
IDbCommand cmd = con.CreateCommand ();
Console.WriteLine ("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT )";
bool didFail = false;
try {
cmd.ExecuteNonQuery ();
} catch (Exception ex) {
didFail = true;
}
if (!didFail) {
Console.WriteLine ("Test failed!");
throw new ApplicationException("Test failed!");
}
con.Close ();
// Test Read Only = True, existng db file
cs = string.Format ("Version=3;uri=file:{0}", dbFilename);
Console.WriteLine ("Set connection string: {0}", cs);
if (File.Exists (dbFilename))
File.Delete (dbFilename);
con.ConnectionString = cs;
con.Open ();
cmd = con.CreateCommand ();
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT )";
cmd.ExecuteNonQuery ();
con.Close ();
cs = string.Format ("Version=3;Read Only=True;uri=file:{0}", dbFilename);
Console.WriteLine ("Set connection string: {0}", cs);
con.ConnectionString = cs;
Console.WriteLine ("Open database...");
con.Open ();
Console.WriteLine ("create command...");
cmd = con.CreateCommand ();
Console.WriteLine ("create table TEST_TABLE2...");
cmd.CommandText = "CREATE TABLE TEST_TABLE2 ( COLA INTEGER, COLB TEXT )";
didFail = false;
try {
cmd.ExecuteNonQuery ();
} catch (Exception ex) {
didFail = true;
}
if (didFail) {
Console.WriteLine ("Test failed!");
throw new ApplicationException("Test failed!");
}
// Test FailIfMissing = True, existng db file
cs = string.Format ("Version=3;FailIfMissing=True;uri=file:{0}", dbFilename);
Console.WriteLine ("Set connection string: {0}", cs);
if (File.Exists (dbFilename))
File.Delete (dbFilename);
con.ConnectionString = cs;
Console.WriteLine ("Open database...");
con.Open ();
Console.WriteLine ("create command...");
cmd = con.CreateCommand ();
Console.WriteLine ("create table TEST_TABLE2...");
cmd.CommandText = "CREATE TABLE TEST_TABLE2 ( COLA INTEGER, COLB TEXT )";
didFail = false;
try {
cmd.ExecuteNonQuery ();
} catch (Exception ex) {
didFail = true;
}
if (!didFail) {
Console.WriteLine ("Test failed!");
throw new ApplicationException("Test failed!");
}
Console.WriteLine( "Test8 Done." );
}
public void Issue_65()
{
//alxwest: causes error "Unable to open database" as TempDirectory.ToString() is set to "B:/TEMP/"
//string datasource = "file://" + TempDirectory.ToString() + "myBigDb.s3db";
string datasource = "file://" + "myBigDb.s3db";
using ( IDbConnection conn = new SqliteConnection( "uri=" + datasource ) )
{
long targetFileSize = (long)Math.Pow( 2, 32 ) - 1;
int rowLength = 1024; // 2^10
long loopCount = (int)( targetFileSize / rowLength ) + 10000;
char[] chars = new char[rowLength];
for ( int i = 0; i < rowLength; i++ )
{
chars[i] = 'A';
}
string row = new string( chars );
conn.Open();
IDbCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = "PRAGMA cache_size = 16000; PRAGMA synchronous = OFF; PRAGMA journal_mode = MEMORY;";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table if exists [MyTable]";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table [MyTable] ([MyField] varchar(" + rowLength + ") null)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into [MyTable] ([MyField]) VALUES ('" + row + "')";
for ( int i = 0; i < loopCount; i++ )
{
cmd.ExecuteNonQuery();
}
}
catch
{
Console.WriteLine( ( (SqliteCommand)cmd ).GetLastError() );
}
finally
{
cmd.Cancel();
conn.Close();
conn.Dispose();
}
}
}
// Issue 76 Encryption is not implemented in C#SQLite client connection and command objects
public void Issue_76()
{
Console.WriteLine( "Test for Issue_76 Start." );
Console.WriteLine( "Create connection..." );
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format( "Version=3,uri=file:{0}", dbFilename );
Console.WriteLine( "Set connection String: {0}", cs );
if ( File.Exists( dbFilename ) )
File.Delete( dbFilename );
con.ConnectionString = cs;
Console.WriteLine( "Open database..." );
con.Open();
Console.WriteLine( "create command..." );
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'";
Console.WriteLine( cmd.CommandText );
cmd.ExecuteNonQuery();
cmd.CommandText = "create table a (b); insert into a values ('row 1');select * from a;";
Console.WriteLine( cmd.CommandText );
Console.WriteLine( "Result {0}", cmd.ExecuteScalar() );
Console.WriteLine( "Close & Reopen Connection" );
con.Close();
con.Open();
cmd.CommandText = "select * from a;";
Console.WriteLine( cmd.CommandText );
Console.WriteLine( "Result {0}", cmd.ExecuteScalar() );
Console.WriteLine( "Close & Reopen Connection" );
con.Close();
con.Open();
cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'";
Console.WriteLine( cmd.CommandText );
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from a;";
Console.WriteLine( cmd.CommandText );
Console.WriteLine( "Result {0}", cmd.ExecuteScalar() );
Console.WriteLine( "Close & Reopen Connection with password" );
con.Close();
con.ConnectionString = cs + ",Password=0x73656372657470617373776F72640f11";
con.Open();
cmd.CommandText = "select * from a;";
Console.WriteLine( cmd.CommandText );
Console.WriteLine( "Result {0}", cmd.ExecuteScalar() );
con = null;
Console.WriteLine( "Issue_76 Done." );
}
// Multi thread execution special command or ddl command results in exception..
public void Issue_86()
{
AppDomain.CurrentDomain.UnhandledException +=
( sender, eventArgs ) =>
{
Console.WriteLine( eventArgs.ExceptionObject );
};
int flags = Sqlite3.SQLITE_OPEN_NOMUTEX | Sqlite3.SQLITE_OPEN_READWRITE | Sqlite3.SQLITE_OPEN_CREATE;
for ( int i = 0; i < 10; i++ )
{
Console.WriteLine( "Running Thread {0}", i );
var t = new Thread(
() =>
{
string dbFilename = string.Format( "db{0}.sqlite", Thread.CurrentThread.ManagedThreadId );
if ( File.Exists( dbFilename ) )
File.Delete( dbFilename );
Console.WriteLine( "Using Database {0}", dbFilename );
Sqlite3.sqlite3 db = null;
Sqlite3.sqlite3_open_v2( dbFilename, out db, flags, null );
var command = string.Format( "create table [t{0}] (id, name, amount)", Thread.CurrentThread.ManagedThreadId );
ExecuteCommand( db, command );
Sqlite3.sqlite3_close( db );
} );
t.Start();
}
}
private static void ExecuteCommand( Sqlite3.sqlite3 db, string command )
{
int rc;
Sqlite3.Vdbe vm = null;
if ( Sqlite3.sqlite3_prepare_v2( db, command, command.Length, ref vm, 0 ) != Sqlite3.SQLITE_OK )
{
throw new InvalidOperationException( string.Format( "Query failed ({0}), message: {1}.", db.errCode, Sqlite3.sqlite3_errmsg( db ) ) );
}
rc = Sqlite3.sqlite3_step( vm );
if ( rc != Sqlite3.SQLITE_DONE && rc != Sqlite3.SQLITE_ROW )
{
throw new InvalidOperationException( string.Format( "Query failed ({0}), message: {1}.", db.errCode, Sqlite3.sqlite3_errmsg( db ) ) );
}
Sqlite3.sqlite3_finalize( vm );
}
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
public void DisplayDataTable( DataTable table, string name )
{
Console.WriteLine( "Display DataTable: {0}", name );
int r = 0;
foreach ( DataRow row in table.Rows )
{
Console.WriteLine( "Row {0}", r );
int c = 0;
foreach ( DataColumn col in table.Columns )
{
Console.WriteLine( " Col {0}: {1} {2}", c, col.ColumnName, col.DataType );
Console.WriteLine( " Value: {0}", row[col] );
c++;
}
r++;
}
Console.WriteLine( "Rows in data table: {0}", r );
}
#endif
public void Issue_119()
{
Console.WriteLine( "Test Start." );
Console.WriteLine( "Create connection..." );
SqliteConnection con = new SqliteConnection();
string dbFilename = @"=SqliteTest3=.db";
string cs = string.Format( "Version=3,uri=file:{0}", dbFilename );
Console.WriteLine( "Set connection String: {0}", cs );
if ( File.Exists( dbFilename ) )
File.Delete( dbFilename );
con.ConnectionString = cs;
Console.WriteLine( "Open database..." );
con.Open();
Console.WriteLine( "create command..." );
IDbCommand cmd = con.CreateCommand();
Console.WriteLine( "create table TEST_TABLE..." );
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
Console.WriteLine( "insert row 1..." );
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
Console.WriteLine( "insert row 2..." );
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
Console.WriteLine( "SELECT data from TEST_TABLE..." );
cmd.CommandText = "SELECT RowID, COLA, COLB, COLC FROM TEST_TABLE";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine( "Read the data..." );
while ( reader.Read() )
{
Console.WriteLine( " Row: {0}", r );
int rowid = reader.GetInt32( reader.GetOrdinal( "RowID" ) );
Console.WriteLine( " RowID: {0}", rowid );
int i = reader.GetInt32( reader.GetOrdinal( "COLA" ) );
Console.WriteLine( " COLA: {0}", i );
string s = reader.GetString( reader.GetOrdinal( "COLB" ) );
Console.WriteLine( " COLB: {0}", s );
DateTime dt = reader.GetDateTime( reader.GetOrdinal( "COLC" ) );
Console.WriteLine( " COLB: {0}", dt.ToString( "MM/dd/yyyy HH:mm:ss" ) );
r++;
}
Console.WriteLine( "Close and cleanup..." );
con.Close();
con = null;
Console.WriteLine( "Test Done." );
}
public void Issue_124()
{
Console.WriteLine( "Test Start." );
Sqlite3.sqlite3 db = null;
Sqlite3.sqlite3_open( ":memory:", out db );
Sqlite3.Vdbe stmt = null;
string zero = null;
string val;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
//create table
{
Sqlite3.sqlite3_prepare_v2( db, "create table Test (val REAL NOT NULL)", -1, ref stmt, ref zero );
Sqlite3.sqlite3_step( stmt );
Sqlite3.sqlite3_finalize( stmt );
}
//insert 0.1
{
Sqlite3.sqlite3_prepare_v2( db, "insert into Test(val) values ('0.1')", -1, ref stmt, ref zero );
Sqlite3.sqlite3_step( stmt );
Sqlite3.sqlite3_finalize( stmt );
}
//insert 0.1
{
Sqlite3.sqlite3_prepare_v2( db, "insert into Test(val) values ('0.2')", -1, ref stmt, ref zero );
Sqlite3.sqlite3_step( stmt );
Sqlite3.sqlite3_finalize( stmt );
}
//insert 0.000000001
{
Sqlite3.sqlite3_prepare_v2( db, "insert into Test(val) values ('0.000000001')", -1, ref stmt, ref zero );
Sqlite3.sqlite3_step( stmt );
Sqlite3.sqlite3_finalize( stmt );
}
//invariant culture
{
System.Console.WriteLine( "invariant culture" );
Sqlite3.sqlite3_prepare_v2( db, "select val from Test", -1, ref stmt, ref zero );
Sqlite3.sqlite3_step( stmt );
val = Sqlite3.sqlite3_column_text( stmt, 0 );
System.Console.WriteLine( "value: " + val );
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture( "ru" );
Sqlite3.sqlite3_step( stmt );
val = Sqlite3.sqlite3_column_text( stmt, 0 );
System.Console.WriteLine( "value: " + val );
Sqlite3.sqlite3_step( stmt );
val = Sqlite3.sqlite3_column_text( stmt, 0 );
System.Console.WriteLine( "value: " + val );
Sqlite3.sqlite3_finalize( stmt );
}
//ru-ru culture
{
System.Console.WriteLine( "ru" );
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture( "ru" );
Sqlite3.sqlite3_prepare_v2( db, "select val from Test", -1, ref stmt, ref zero );
Sqlite3.sqlite3_step( stmt );
val = Sqlite3.sqlite3_column_text( stmt, 0 );
System.Console.WriteLine( "value: " + val );
Sqlite3.sqlite3_step( stmt );
val = Sqlite3.sqlite3_column_text( stmt, 0 );
System.Console.WriteLine( "value: " + val );
Sqlite3.sqlite3_finalize( stmt );
}
Console.WriteLine( "Test Done." );
}
public void Issue_149 ()
{
Console.WriteLine ("Test Issue 149 - Test Start.");
SqliteConnection con = new SqliteConnection ();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format ("Version=3,uri=file:{0}", dbFilename);
if (File.Exists (dbFilename))
File.Delete (dbFilename);
con.ConnectionString = cs;
con.Open ();
Console.WriteLine ("create command...");
IDbCommand cmd = con.CreateCommand ();
cmd.CommandText = "CREATE TABLE TestZeroArrayTable (TheField BLOB)";
cmd.ExecuteNonQuery ();
cmd.CommandText = "INSERT INTO TestZeroArrayTable VALUES(?)";
IDbDataParameter field6 = cmd.CreateParameter ();
cmd.Parameters.Add (field6);
byte[] data = new byte[] {};
field6.Value = data;
cmd.ExecuteNonQuery ();
cmd.CommandText = "SELECT TheField FROM TestZeroArrayTable";
byte[] res = (byte[])cmd.ExecuteScalar ();
if (res == null)
throw new ArgumentException ("res == null");
if (res.Length != 0)
throw new ArgumentException ("res.Length != 0");
Console.WriteLine ("Test Done.");
}
public static int Main( string[] args )
{
SQLiteClientTestDriver tests = new SQLiteClientTestDriver();
int Test = 1;
switch ( Test )
{
case 1:
tests.Test1();
break;
case 2:
tests.Test2();
break;
case 3:
tests.Test3();
break;
case 4:
tests.Test4();
break;
case 5:
tests.Test5();
break;
case 6:
tests.Test6();
break;
case 7:
tests.Test7();
break;
case 8:
tests.Test8();
break;
case 65:
tests.Issue_65();
break;
case 76:
tests.Issue_76();
break;
case 86:
tests.Issue_86();
break;
case 119:
tests.Issue_119();
break;
case 124:
tests.Issue_124();
break;
case 149:
tests.Issue_149();
break;
}
Console.WriteLine( "Press Enter to Continue" );
Console.ReadKey();
tests = null;
return 0;
}
}
}