wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 using System;
2 using System.Data;
3 using System.Data.Common;
4 using System.IO;
5 using System.Text;
6 using System.Threading;
7 using System.Collections.Generic;
8 using Community.CsharpSqlite;
9 using Community.CsharpSqlite.SQLiteClient;
10 using System.Globalization;
11  
12 namespace SQLiteClientTests
13 {
14 public class SQLiteClientTestDriver
15 {
16 public void Test1()
17 {
18 Console.WriteLine( "Test1 Start." );
19  
20 Console.WriteLine( "Create connection..." );
21 SqliteConnection con = new SqliteConnection();
22  
23 string dbFilename = @"SqliteTest3.db";
24 string cs = string.Format( "Version=3;uri=file:{0}", dbFilename );
25  
26 Console.WriteLine( "Set connection String: {0}", cs );
27  
28 if ( File.Exists( dbFilename ) )
29 File.Delete( dbFilename );
30  
31 con.ConnectionString = cs;
32  
33 Console.WriteLine( "Open database..." );
34 con.Open();
35  
36 Console.WriteLine( "create command..." );
37 IDbCommand cmd = con.CreateCommand();
38  
39 Console.WriteLine( "create table TEST_TABLE..." );
40 cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
41 cmd.ExecuteNonQuery();
42  
43 Console.WriteLine( "insert row 1..." );
44 cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
45  
46 cmd.ExecuteNonQuery();
47  
48 Console.WriteLine( "insert row 2..." );
49 cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
50 cmd.ExecuteNonQuery();
51  
52 //Console.WriteLine("commit...");
53 //cmd.CommandText = "COMMIT";
54 //cmd.ExecuteNonQuery();
55  
56 Console.WriteLine( "SELECT data from TEST_TABLE..." );
57 cmd.CommandText = "SELECT COLA, COLB, COLC FROM TEST_TABLE";
58 IDataReader reader = cmd.ExecuteReader();
59 int r = 0;
60 Console.WriteLine( "Read the data..." );
61 while ( reader.Read() )
62 {
63 Console.WriteLine( " Row: {0}", r );
64 int i = reader.GetInt32( reader.GetOrdinal( "COLA" ) );
65 Console.WriteLine( " COLA: {0}", i );
66  
67 string s = reader.GetString( reader.GetOrdinal( "COLB" ) );
68 Console.WriteLine( " COLB: {0}", s );
69  
70 DateTime dt = reader.GetDateTime( reader.GetOrdinal( "COLC" ) );
71 Console.WriteLine( " COLB: {0}", dt.ToString( "MM/dd/yyyy HH:mm:ss" ) );
72  
73 r++;
74 }
75 Console.WriteLine( "Rows retrieved: {0}", r );
76  
77 //alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
78 #if !SQLITE_SILVERLIGHT
79 SqliteCommand command = new SqliteCommand( "PRAGMA table_info('TEST_TABLE')", con );
80 DataTable dataTable = new DataTable();
81 SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
82 dataAdapter.SelectCommand = command;
83 dataAdapter.Fill( dataTable );
84 DisplayDataTable( dataTable, "Columns" );
85 #endif
86  
87 Console.WriteLine( "Close and cleanup..." );
88 con.Close();
89 con = null;
90  
91 Console.WriteLine( "Test1 Done." );
92 }
93 public void Test2()
94 {
95 Console.WriteLine( "Test2 Start." );
96  
97 Console.WriteLine( "Create connection..." );
98 SqliteConnection con = new SqliteConnection();
99  
100 string dbFilename = @"SqliteTest3.db";
101 string cs = string.Format( "Version=3,uri=file:{0}", dbFilename );
102  
103 Console.WriteLine( "Set connection String: {0}", cs );
104  
105 if ( File.Exists( dbFilename ) )
106 File.Delete( dbFilename );
107  
108 con.ConnectionString = cs;
109  
110 Console.WriteLine( "Open database..." );
111 con.Open();
112  
113 Console.WriteLine( "create command..." );
114 IDbCommand cmd = con.CreateCommand();
115  
116 Console.WriteLine( "create table TEST_TABLE..." );
117 cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)";
118 cmd.ExecuteNonQuery();
119  
120 Console.WriteLine( "insert row 1..." );
121 cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )";
122 cmd.ExecuteNonQuery();
123  
124 Console.WriteLine( "insert row 2..." );
125 cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, '中文' )";
126 cmd.ExecuteNonQuery();
127  
128 //Console.WriteLine("commit...");
129 //cmd.CommandText = "COMMIT";
130 //cmd.ExecuteNonQuery();
131  
132 Console.WriteLine( "SELECT data from TBL..." );
133 cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = '中文'";
134 IDataReader reader = cmd.ExecuteReader();
135 int r = 0;
136 Console.WriteLine( "Read the data..." );
137 while ( reader.Read() )
138 {
139 Console.WriteLine( " Row: {0}", r );
140 int i = reader.GetInt32( reader.GetOrdinal( "ID" ) );
141 Console.WriteLine( " ID: {0}", i );
142  
143 string s = reader.GetString( reader.GetOrdinal( "NAME" ) );
144 Console.WriteLine( " NAME: {0} = {1}", s, s == "中文" );
145 r++;
146 }
147 Console.WriteLine( "Rows retrieved: {0}", r );
148  
149 //alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
150 #if !SQLITE_SILVERLIGHT
151 SqliteCommand command = new SqliteCommand( "PRAGMA table_info('TEST_TABLE')", con );
152 DataTable dataTable = new DataTable();
153 SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
154 dataAdapter.SelectCommand = command;
155 dataAdapter.Fill( dataTable );
156 DisplayDataTable( dataTable, "Columns" );
157 #endif
158  
159 Console.WriteLine( "Close and cleanup..." );
160 con.Close();
161 con = null;
162  
163 Console.WriteLine( "Test1 Done." );
164 }
165 public void Test3()
166 {
167 Console.WriteLine( "Test3 (Date Paramaters) Start." );
168  
169 Console.WriteLine( "Create connection..." );
170 SqliteConnection con = new SqliteConnection();
171  
172 string dbFilename = @"SqliteTest3.db";
173 string cs = string.Format( "Version=3,uri=file:{0}", dbFilename );
174  
175 Console.WriteLine( "Set connection String: {0}", cs );
176  
177 if ( File.Exists( dbFilename ) )
178 File.Delete( dbFilename );
179  
180 con.ConnectionString = cs;
181  
182 Console.WriteLine( "Open database..." );
183 con.Open();
184  
185 Console.WriteLine( "create command..." );
186 IDbCommand cmd = con.CreateCommand();
187  
188 Console.WriteLine( "create table TEST_TABLE..." );
189 cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, DATE_TEXT REAL)";
190 cmd.ExecuteNonQuery();
191  
192 Console.WriteLine( "insert ..." );
193 cmd.CommandText = "INSERT INTO TBL ( ID, DATE_TEXT) VALUES ( 1, @DATETEXT)";
194 cmd.Parameters.Add(
195 new SqliteParameter
196 {
197 ParameterName = "@DATETEXT",
198 Value = DateTime.Now
199 }
200 );
201  
202 cmd.ExecuteNonQuery();
203  
204  
205 Console.WriteLine( "SELECT data from TBL..." );
206 cmd.CommandText = "SELECT * FROM tbl";
207 IDataReader reader = cmd.ExecuteReader();
208 int r = 0;
209 Console.WriteLine( "Read the data..." );
210 while ( reader.Read() )
211 {
212 Console.WriteLine( " Row: {0}", r );
213 int i = reader.GetInt32( reader.GetOrdinal( "ID" ) );
214 Console.WriteLine( " ID: {0}", i );
215  
216 string s = reader.GetString( reader.GetOrdinal( "DATE_TEXT" ) );
217 Console.WriteLine( " DATE_TEXT: {0}", s );
218 r++;
219 }
220 Console.WriteLine( "Rows retrieved: {0}", r );
221  
222  
223 Console.WriteLine( "Close and cleanup..." );
224 con.Close();
225 con = null;
226  
227 Console.WriteLine( "Test3 Done." );
228 }
229  
230 //nSoftware code for Threading
231 string connstring_T4;
232 public void Test4()
233 {
234 string dbFilename = "threading_t4.db";
235 if ( File.Exists( dbFilename ) )
236 File.Delete( dbFilename );
237 connstring_T4 = @"Version=3,busy_timeout=100,uri=file:" + dbFilename;
238  
239 Setup_T4();
240 InsertSameTable_T4(); //concurrent inserts
241 SelectorWrite_T4(); //concurrent selects and inserts
242 Console.WriteLine( "Testing for Threading done. Press enter to continue" );
243 Console.In.Read();
244 }
245 private void SelectorWrite_T4()
246 {
247 //concurrent reads/writes in the same table, if there were only Selects it would be preferable for the sqlite engine not to lock internally.
248 for ( int i = 0; i < 10; i++ )
249 {
250 Console.WriteLine( "SELECT/INSERT ON Thread {0}", i );
251 Thread worker = new Thread( () =>
252 {
253 // Cannot use value of i, since it exceeds the scope of this thread and will be
254 // reused by multiple threads
255 int aValue = 100 + Thread.CurrentThread.ManagedThreadId;
256 int op = aValue % 2;
257  
258 SqliteConnection con = new SqliteConnection();
259 con.ConnectionString = connstring_T4;
260 con.Open();
261 IDbCommand cmd = con.CreateCommand();
262 cmd = con.CreateCommand();
263 if ( op == 0 )
264 {
265 cmd.CommandText = String.Format( "Select * FROM ATABLE" );
266 cmd.ExecuteReader();
267 }
268 else
269 {
270 cmd.CommandText = String.Format( "INSERT INTO ATABLE ( A, B, C ) VALUES ({0},'threader', '1' )", aValue );
271 Console.WriteLine( cmd.CommandText );
272 cmd.ExecuteNonQuery();
273 }
274 } );
275 worker.Start();
276 }
277 }
278 //we need concurrency support on a table level inside of the database file.
279 private void InsertSameTable_T4()
280 {
281 for ( int i = 0; i < 10; i++ )
282 {
283 Console.WriteLine( "INSERTING ON Thread {0}", i );
284 Thread worker = new Thread( () =>
285 {
286 // Cannot use value of i, since it exceeds the scope of this thread and will be
287 // reused by multiple threads
288  
289 int aValue = Thread.CurrentThread.ManagedThreadId;
290  
291 SqliteConnection con = new SqliteConnection();
292 con.ConnectionString = connstring_T4;
293 con.Open();
294 IDbCommand cmd = con.CreateCommand();
295 cmd = con.CreateCommand();
296 cmd.CommandText = String.Format( "INSERT INTO ATABLE ( A, B, C ) VALUES ({0},'threader', '1' )", aValue );
297 Console.WriteLine( cmd.CommandText );
298 cmd.ExecuteNonQuery();
299 }
300 );
301 worker.Start();
302 }
303 }
304  
305 private void Setup_T4()
306 {
307 SqliteConnection con = new SqliteConnection();
308 con.ConnectionString = connstring_T4;
309 con.Open();
310 IDbCommand cmd = con.CreateCommand();
311 cmd = con.CreateCommand();
312 cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
313 cmd.ExecuteNonQuery();
314 cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
315 cmd.ExecuteNonQuery();
316 cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" );
317 cmd.ExecuteNonQuery();
318 }
319  
320 // Software code for Threading
321 string connstring_T5;
322 public void Test5()
323 {
324 string dbFilename = "threading_t5.db";
325 if ( File.Exists( dbFilename ) )
326 File.Delete( dbFilename );
327 connstring_T5 = @"Version=3,busy_timeout=2500,uri=file:" + dbFilename;
328  
329 Setup_T5();
330 MultiInsertsSameThread_T5(); //concurrent inserts
331 Console.WriteLine( "Threads are running..." );
332 Console.In.Read();
333 }
334  
335 private void MultiInsertsSameThread_T5()
336 {
337 for ( int i = 0; i < 10; i++ )
338 {
339 //Console.WriteLine( "SELECT/INSERT ON Thread {0}", i );
340 Thread worker = new Thread( () =>
341 {
342 string commandt = String.Empty;
343 try
344 {
345 // Cannot use value of i, since it exceeds the scope of this thread and will be
346 // reused by multiple threads
347 int aValue = 100 + Thread.CurrentThread.ManagedThreadId;
348 int op = aValue % 2;
349  
350 SqliteConnection con = new SqliteConnection();
351 con.ConnectionString = connstring_T5;
352 con.Open();
353 IDbCommand cmd = con.CreateCommand();
354 cmd = con.CreateCommand();
355 if ( op == 0 )
356 {
357 for ( int j = 0; j < 1000; j++ )
358 {
359 int rows;
360 int retry = 0;
361 cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES ({0},'threader', '1' )", ( aValue * 10000 ) + j );
362 commandt = cmd.CommandText;
363 do
364 {
365 rows = cmd.ExecuteNonQuery();
366 if ( rows == 0 )
367 {
368 retry += 1; // Insert Failed
369 Console.WriteLine( cmd.CommandText );
370 Console.WriteLine( "retry {0}", retry );
371 Console.WriteLine( ( (SqliteCommand)cmd ).GetLastError() );
372 }
373 } while ( rows == 0 && retry < 5 );
374 }
375 }
376 else
377 {
378 cmd.CommandText = String.Format( "Select * FROM ATABLE" );
379 commandt = cmd.CommandText;
380 cmd.ExecuteReader();
381 }
382 }
383 catch ( Exception ex )
384 {
385 Console.WriteLine( String.Format( "Command {0} threw exception {1}", commandt, ex.Message ) );
386 }
387 } );
388  
389 worker.Start();
390 }
391 }
392  
393 private void Setup_T5()
394 {
395 SqliteConnection con = new SqliteConnection();
396 con.ConnectionString = connstring_T5;
397 con.Open();
398 IDbCommand cmd = con.CreateCommand();
399 cmd = con.CreateCommand();
400 cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
401 cmd.ExecuteNonQuery();
402 cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
403 cmd.ExecuteNonQuery();
404 cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" );
405 cmd.ExecuteNonQuery();
406 }
407  
408 // Software code for Threading & Transactions
409 static string connstring_T6;
410 public void Test6()
411 {
412 string dbFilename = "threading_t6.db";
413 if ( File.Exists( dbFilename ) )
414 File.Delete( dbFilename );
415 connstring_T6 = @"Version=3,busy_timeout=2000,uri=file:" + dbFilename;
416  
417 Setup_T6();
418 MultiInsertsTransactionsSameThread_T6(); //concurrent inserts
419 Console.WriteLine( "Threads are running..." );
420 Console.In.Read();
421 }
422  
423 static Random rnd = new Random();
424 private void MultiInsertsTransactionsSameThread_T6()
425 {
426 for ( int i = 0; i < 20; i++ )
427 {
428 Thread.Sleep( rnd.Next( 100, 1000 ) );
429 Console.WriteLine( "Launching Thread {0}", i );
430 Thread worker = new Thread( SQLiteClientTestDriver.T6_ThreadStart );
431 worker.Start( i );
432 }
433 }
434 private static void T6_ThreadStart( object data )
435 {
436 string commandt = String.Empty;
437 int i = (int)data;
438 try
439 {
440 int aValue = 100 + i;
441 int op = aValue % 2;
442  
443 SqliteConnection con = new SqliteConnection();
444 con.ConnectionString = connstring_T6;
445 con.Open();
446 IDbCommand cmd = con.CreateCommand();
447 cmd = con.CreateCommand();
448 if ( op == 0 )
449 {
450 SqliteTransaction trans = (SqliteTransaction)con.BeginTransaction();
451 for ( int j = 0; j < 5000; j++ )
452 {
453 int rows;
454 int retry = 0;
455 cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES ({0},'threader', '1' )", ( aValue * 10000 ) + j );
456 commandt = cmd.CommandText;
457 do
458 {
459 rows = cmd.ExecuteNonQuery();
460 if ( rows == 0 )
461 {
462 retry += 1; // Insert Failed
463 Console.WriteLine( "retry {0}:{1}:{2}", retry, ( (SqliteCommand)cmd ).GetLastError(), cmd.CommandText );
464 Thread.Sleep( rnd.Next( 50, 1000 ) );
465 }
466 } while ( rows == 0 && retry < 10 );
467 }
468 trans.Commit();
469 }
470 else
471 {
472 cmd.CommandText = String.Format( "Select * FROM ATABLE" );
473 commandt = cmd.CommandText;
474 cmd.ExecuteReader();
475 }
476 }
477 catch ( Exception ex )
478 {
479 Console.WriteLine( String.Format( "Command {0} threw exception {1}", commandt, ex.Message ) );
480 }
481 }
482 private void Setup_T6()
483 {
484 SqliteConnection con = new SqliteConnection();
485 con.ConnectionString = connstring_T6;
486 con.Open();
487 IDbCommand cmd = con.CreateCommand();
488 cmd = con.CreateCommand();
489 cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
490 cmd.ExecuteNonQuery();
491 cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
492 cmd.ExecuteNonQuery();
493 cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" );
494 cmd.ExecuteNonQuery();
495 }
496  
497 // Software code for Threading
498 string connstring_T7;
499 public void Test7()
500 {
501 string dbFilename = "threading_t7.db";
502 if ( File.Exists( dbFilename ) )
503 File.Delete( dbFilename );
504 connstring_T7 = @"Version=3,busy_timeout=1000,uri=file:" + dbFilename;
505  
506 MultiInsertsSameThread_T7(); //concurrent table creation
507 Console.WriteLine( "Threads are running..." );
508 Console.In.Read();
509 }
510  
511 private void MultiInsertsSameThread_T7()
512 {
513 List<Thread> lthread = new List<Thread>();
514 for ( int i = 0; i < 70; i++ )
515 {
516 Thread.Sleep( rnd.Next( 100, 1000 ) );
517 Console.WriteLine( "Launching Thread {0}", i );
518 Thread worker = new Thread( this.T7_ThreadStart );
519 lthread.Add( worker );
520 worker.Start( i );
521 }
522 bool alldone = false;
523  
524 while ( !alldone )
525 {
526 alldone = true;
527 for ( int i = 0; i < lthread.Count; i++ )
528 {
529 if ( lthread[i].ThreadState == ThreadState.Running )
530 alldone = false;
531 Thread.Sleep( 100 );
532 }
533 }
534 Console.WriteLine( "finished" );
535 }
536  
537 private void T7_ThreadStart( object iSequence )
538 {
539 int aValue = (int)iSequence * 1000;
540  
541 SqliteConnection con = new SqliteConnection();
542 con.ConnectionString = connstring_T7;
543 con.Open();
544 IDbCommand cmd = con.CreateCommand();
545 cmd = con.CreateCommand();
546 string commandt = String.Format( "CREATE TABLE IF NOT EXISTS ATABLE{0}(A integer primary key , B varchar (50), C integer, D varchar (500))", aValue );
547 cmd.CommandText = commandt;
548 try
549 {
550 cmd.ExecuteNonQuery();
551 Console.WriteLine( "Created table: ATABLE" + aValue );
552 }
553 catch ( Exception ex )
554 {
555 Console.WriteLine( String.Format( "Command {0} threw exception {1}", commandt, ex.Message ) );
556 }
557 }
558  
559 private void Setup_T7()
560 {
561 SqliteConnection con = new SqliteConnection();
562 con.ConnectionString = connstring_T7;
563 con.Open();
564 IDbCommand cmd = con.CreateCommand();
565 cmd = con.CreateCommand();
566 cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
567 cmd.ExecuteNonQuery();
568 cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
569 cmd.ExecuteNonQuery();
570 cmd.CommandText = String.Format( "INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )" );
571 cmd.ExecuteNonQuery();
572 }
573  
574 // Connectio string parsing tests/
575 public void Test8 ()
576 {
577 Console.WriteLine ("Test8 Start.");
578  
579 Console.WriteLine ("Create connection...");
580 SqliteConnection con = new SqliteConnection ();
581  
582 string dbFilename = @"SqliteTest3.db";
583  
584 // Test Read Only = True, missing db file
585 string cs = string.Format ("Version=3;Read Only=True;uri=file:{0}", dbFilename);
586  
587 Console.WriteLine ("Set connection string: {0}", cs);
588  
589 if (File.Exists (dbFilename))
590 File.Delete (dbFilename);
591  
592 con.ConnectionString = cs;
593  
594 Console.WriteLine ("Open database...");
595 con.Open ();
596  
597 Console.WriteLine ("create command...");
598 IDbCommand cmd = con.CreateCommand ();
599  
600 Console.WriteLine ("create table TEST_TABLE...");
601 cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT )";
602 bool didFail = false;
603 try {
604 cmd.ExecuteNonQuery ();
605 } catch (Exception ex) {
606 didFail = true;
607 }
608 if (!didFail) {
609 Console.WriteLine ("Test failed!");
610 throw new ApplicationException("Test failed!");
611 }
612  
613 con.Close ();
614  
615 // Test Read Only = True, existng db file
616 cs = string.Format ("Version=3;uri=file:{0}", dbFilename);
617  
618 Console.WriteLine ("Set connection string: {0}", cs);
619  
620 if (File.Exists (dbFilename))
621 File.Delete (dbFilename);
622  
623 con.ConnectionString = cs;
624 con.Open ();
625  
626 cmd = con.CreateCommand ();
627 cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT )";
628 cmd.ExecuteNonQuery ();
629 con.Close ();
630  
631 cs = string.Format ("Version=3;Read Only=True;uri=file:{0}", dbFilename);
632  
633 Console.WriteLine ("Set connection string: {0}", cs);
634  
635 con.ConnectionString = cs;
636  
637 Console.WriteLine ("Open database...");
638 con.Open ();
639  
640 Console.WriteLine ("create command...");
641 cmd = con.CreateCommand ();
642  
643 Console.WriteLine ("create table TEST_TABLE2...");
644 cmd.CommandText = "CREATE TABLE TEST_TABLE2 ( COLA INTEGER, COLB TEXT )";
645 didFail = false;
646 try {
647 cmd.ExecuteNonQuery ();
648 } catch (Exception ex) {
649 didFail = true;
650 }
651 if (didFail) {
652 Console.WriteLine ("Test failed!");
653 throw new ApplicationException("Test failed!");
654 }
655  
656 // Test FailIfMissing = True, existng db file
657 cs = string.Format ("Version=3;FailIfMissing=True;uri=file:{0}", dbFilename);
658  
659 Console.WriteLine ("Set connection string: {0}", cs);
660  
661 if (File.Exists (dbFilename))
662 File.Delete (dbFilename);
663  
664 con.ConnectionString = cs;
665  
666 Console.WriteLine ("Open database...");
667 con.Open ();
668  
669 Console.WriteLine ("create command...");
670 cmd = con.CreateCommand ();
671  
672 Console.WriteLine ("create table TEST_TABLE2...");
673 cmd.CommandText = "CREATE TABLE TEST_TABLE2 ( COLA INTEGER, COLB TEXT )";
674 didFail = false;
675 try {
676 cmd.ExecuteNonQuery ();
677 } catch (Exception ex) {
678 didFail = true;
679 }
680 if (!didFail) {
681 Console.WriteLine ("Test failed!");
682 throw new ApplicationException("Test failed!");
683 }
684  
685 Console.WriteLine( "Test8 Done." );
686 }
687  
688 public void Issue_65()
689 {
690 //alxwest: causes error "Unable to open database" as TempDirectory.ToString() is set to "B:/TEMP/"
691 //string datasource = "file://" + TempDirectory.ToString() + "myBigDb.s3db";
692 string datasource = "file://" + "myBigDb.s3db";
693  
694 using ( IDbConnection conn = new SqliteConnection( "uri=" + datasource ) )
695 {
696 long targetFileSize = (long)Math.Pow( 2, 32 ) - 1;
697 int rowLength = 1024; // 2^10
698  
699 long loopCount = (int)( targetFileSize / rowLength ) + 10000;
700  
701 char[] chars = new char[rowLength];
702 for ( int i = 0; i < rowLength; i++ )
703 {
704 chars[i] = 'A';
705 }
706  
707 string row = new string( chars );
708  
709 conn.Open();
710 IDbCommand cmd = conn.CreateCommand();
711  
712 try
713 {
714 cmd.CommandText = "PRAGMA cache_size = 16000; PRAGMA synchronous = OFF; PRAGMA journal_mode = MEMORY;";
715 cmd.ExecuteNonQuery();
716  
717 cmd.CommandText = "drop table if exists [MyTable]";
718 cmd.ExecuteNonQuery();
719  
720 cmd.CommandText = "create table [MyTable] ([MyField] varchar(" + rowLength + ") null)";
721 cmd.ExecuteNonQuery();
722  
723 cmd.CommandText = "insert into [MyTable] ([MyField]) VALUES ('" + row + "')";
724 for ( int i = 0; i < loopCount; i++ )
725 {
726 cmd.ExecuteNonQuery();
727 }
728 }
729 catch
730 {
731 Console.WriteLine( ( (SqliteCommand)cmd ).GetLastError() );
732 }
733 finally
734 {
735 cmd.Cancel();
736 conn.Close();
737 conn.Dispose();
738 }
739 }
740 }
741  
742 // Issue 76 Encryption is not implemented in C#SQLite client connection and command objects
743 public void Issue_76()
744 {
745 Console.WriteLine( "Test for Issue_76 Start." );
746  
747 Console.WriteLine( "Create connection..." );
748 SqliteConnection con = new SqliteConnection();
749  
750 string dbFilename = @"SqliteTest3.db";
751 string cs = string.Format( "Version=3,uri=file:{0}", dbFilename );
752  
753 Console.WriteLine( "Set connection String: {0}", cs );
754  
755 if ( File.Exists( dbFilename ) )
756 File.Delete( dbFilename );
757  
758 con.ConnectionString = cs;
759  
760 Console.WriteLine( "Open database..." );
761 con.Open();
762  
763 Console.WriteLine( "create command..." );
764 IDbCommand cmd = con.CreateCommand();
765  
766 cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'";
767 Console.WriteLine( cmd.CommandText );
768 cmd.ExecuteNonQuery();
769  
770 cmd.CommandText = "create table a (b); insert into a values ('row 1');select * from a;";
771 Console.WriteLine( cmd.CommandText );
772 Console.WriteLine( "Result {0}", cmd.ExecuteScalar() );
773  
774 Console.WriteLine( "Close & Reopen Connection" );
775 con.Close();
776 con.Open();
777  
778 cmd.CommandText = "select * from a;";
779 Console.WriteLine( cmd.CommandText );
780 Console.WriteLine( "Result {0}", cmd.ExecuteScalar() );
781  
782 Console.WriteLine( "Close & Reopen Connection" );
783 con.Close();
784 con.Open();
785 cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'";
786 Console.WriteLine( cmd.CommandText );
787 cmd.ExecuteNonQuery();
788  
789 cmd.CommandText = "select * from a;";
790 Console.WriteLine( cmd.CommandText );
791 Console.WriteLine( "Result {0}", cmd.ExecuteScalar() );
792  
793 Console.WriteLine( "Close & Reopen Connection with password" );
794 con.Close();
795  
796 con.ConnectionString = cs + ",Password=0x73656372657470617373776F72640f11";
797 con.Open();
798 cmd.CommandText = "select * from a;";
799 Console.WriteLine( cmd.CommandText );
800 Console.WriteLine( "Result {0}", cmd.ExecuteScalar() );
801  
802 con = null;
803  
804 Console.WriteLine( "Issue_76 Done." );
805 }
806  
807 // Multi thread execution special command or ddl command results in exception..
808 public void Issue_86()
809 {
810 AppDomain.CurrentDomain.UnhandledException +=
811 ( sender, eventArgs ) =>
812 {
813 Console.WriteLine( eventArgs.ExceptionObject );
814 };
815 int flags = Sqlite3.SQLITE_OPEN_NOMUTEX | Sqlite3.SQLITE_OPEN_READWRITE | Sqlite3.SQLITE_OPEN_CREATE;
816 for ( int i = 0; i < 10; i++ )
817 {
818 Console.WriteLine( "Running Thread {0}", i );
819 var t = new Thread(
820 () =>
821 {
822 string dbFilename = string.Format( "db{0}.sqlite", Thread.CurrentThread.ManagedThreadId );
823 if ( File.Exists( dbFilename ) )
824 File.Delete( dbFilename );
825 Console.WriteLine( "Using Database {0}", dbFilename );
826 Sqlite3.sqlite3 db = null;
827 Sqlite3.sqlite3_open_v2( dbFilename, out db, flags, null );
828 var command = string.Format( "create table [t{0}] (id, name, amount)", Thread.CurrentThread.ManagedThreadId );
829 ExecuteCommand( db, command );
830 Sqlite3.sqlite3_close( db );
831 } );
832 t.Start();
833 }
834 }
835  
836 private static void ExecuteCommand( Sqlite3.sqlite3 db, string command )
837 {
838 int rc;
839 Sqlite3.Vdbe vm = null;
840 if ( Sqlite3.sqlite3_prepare_v2( db, command, command.Length, ref vm, 0 ) != Sqlite3.SQLITE_OK )
841 {
842 throw new InvalidOperationException( string.Format( "Query failed ({0}), message: {1}.", db.errCode, Sqlite3.sqlite3_errmsg( db ) ) );
843 }
844 rc = Sqlite3.sqlite3_step( vm );
845 if ( rc != Sqlite3.SQLITE_DONE && rc != Sqlite3.SQLITE_ROW )
846 {
847 throw new InvalidOperationException( string.Format( "Query failed ({0}), message: {1}.", db.errCode, Sqlite3.sqlite3_errmsg( db ) ) );
848 }
849 Sqlite3.sqlite3_finalize( vm );
850 }
851  
852 //alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
853 #if !SQLITE_SILVERLIGHT
854 public void DisplayDataTable( DataTable table, string name )
855 {
856 Console.WriteLine( "Display DataTable: {0}", name );
857 int r = 0;
858 foreach ( DataRow row in table.Rows )
859 {
860 Console.WriteLine( "Row {0}", r );
861 int c = 0;
862 foreach ( DataColumn col in table.Columns )
863 {
864  
865 Console.WriteLine( " Col {0}: {1} {2}", c, col.ColumnName, col.DataType );
866 Console.WriteLine( " Value: {0}", row[col] );
867 c++;
868 }
869 r++;
870 }
871 Console.WriteLine( "Rows in data table: {0}", r );
872  
873 }
874 #endif
875  
876 public void Issue_119()
877 {
878 Console.WriteLine( "Test Start." );
879  
880 Console.WriteLine( "Create connection..." );
881 SqliteConnection con = new SqliteConnection();
882  
883 string dbFilename = @"=SqliteTest3=.db";
884 string cs = string.Format( "Version=3,uri=file:{0}", dbFilename );
885  
886 Console.WriteLine( "Set connection String: {0}", cs );
887  
888 if ( File.Exists( dbFilename ) )
889 File.Delete( dbFilename );
890  
891 con.ConnectionString = cs;
892  
893 Console.WriteLine( "Open database..." );
894 con.Open();
895  
896 Console.WriteLine( "create command..." );
897 IDbCommand cmd = con.CreateCommand();
898  
899 Console.WriteLine( "create table TEST_TABLE..." );
900 cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
901 cmd.ExecuteNonQuery();
902  
903 Console.WriteLine( "insert row 1..." );
904 cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
905 cmd.ExecuteNonQuery();
906  
907 Console.WriteLine( "insert row 2..." );
908 cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
909 cmd.ExecuteNonQuery();
910  
911 Console.WriteLine( "SELECT data from TEST_TABLE..." );
912 cmd.CommandText = "SELECT RowID, COLA, COLB, COLC FROM TEST_TABLE";
913 IDataReader reader = cmd.ExecuteReader();
914 int r = 0;
915 Console.WriteLine( "Read the data..." );
916 while ( reader.Read() )
917 {
918 Console.WriteLine( " Row: {0}", r );
919 int rowid = reader.GetInt32( reader.GetOrdinal( "RowID" ) );
920 Console.WriteLine( " RowID: {0}", rowid );
921  
922 int i = reader.GetInt32( reader.GetOrdinal( "COLA" ) );
923 Console.WriteLine( " COLA: {0}", i );
924  
925 string s = reader.GetString( reader.GetOrdinal( "COLB" ) );
926 Console.WriteLine( " COLB: {0}", s );
927  
928 DateTime dt = reader.GetDateTime( reader.GetOrdinal( "COLC" ) );
929 Console.WriteLine( " COLB: {0}", dt.ToString( "MM/dd/yyyy HH:mm:ss" ) );
930  
931 r++;
932 }
933  
934 Console.WriteLine( "Close and cleanup..." );
935 con.Close();
936 con = null;
937  
938 Console.WriteLine( "Test Done." );
939 }
940  
941 public void Issue_124()
942 {
943 Console.WriteLine( "Test Start." );
944  
945 Sqlite3.sqlite3 db = null;
946 Sqlite3.sqlite3_open( ":memory:", out db );
947 Sqlite3.Vdbe stmt = null;
948 string zero = null;
949 string val;
950  
951 Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
952  
953 //create table
954 {
955 Sqlite3.sqlite3_prepare_v2( db, "create table Test (val REAL NOT NULL)", -1, ref stmt, ref zero );
956 Sqlite3.sqlite3_step( stmt );
957 Sqlite3.sqlite3_finalize( stmt );
958 }
959  
960 //insert 0.1
961 {
962 Sqlite3.sqlite3_prepare_v2( db, "insert into Test(val) values ('0.1')", -1, ref stmt, ref zero );
963 Sqlite3.sqlite3_step( stmt );
964 Sqlite3.sqlite3_finalize( stmt );
965 }
966 //insert 0.1
967 {
968 Sqlite3.sqlite3_prepare_v2( db, "insert into Test(val) values ('0.2')", -1, ref stmt, ref zero );
969 Sqlite3.sqlite3_step( stmt );
970 Sqlite3.sqlite3_finalize( stmt );
971 }
972  
973 //insert 0.000000001
974 {
975 Sqlite3.sqlite3_prepare_v2( db, "insert into Test(val) values ('0.000000001')", -1, ref stmt, ref zero );
976 Sqlite3.sqlite3_step( stmt );
977 Sqlite3.sqlite3_finalize( stmt );
978 }
979  
980 //invariant culture
981 {
982 System.Console.WriteLine( "invariant culture" );
983 Sqlite3.sqlite3_prepare_v2( db, "select val from Test", -1, ref stmt, ref zero );
984 Sqlite3.sqlite3_step( stmt );
985 val = Sqlite3.sqlite3_column_text( stmt, 0 );
986 System.Console.WriteLine( "value: " + val );
987 Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture( "ru" );
988 Sqlite3.sqlite3_step( stmt );
989 val = Sqlite3.sqlite3_column_text( stmt, 0 );
990 System.Console.WriteLine( "value: " + val );
991 Sqlite3.sqlite3_step( stmt );
992 val = Sqlite3.sqlite3_column_text( stmt, 0 );
993 System.Console.WriteLine( "value: " + val );
994 Sqlite3.sqlite3_finalize( stmt );
995 }
996  
997 //ru-ru culture
998 {
999 System.Console.WriteLine( "ru" );
1000 Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture( "ru" );
1001 Sqlite3.sqlite3_prepare_v2( db, "select val from Test", -1, ref stmt, ref zero );
1002 Sqlite3.sqlite3_step( stmt );
1003 val = Sqlite3.sqlite3_column_text( stmt, 0 );
1004 System.Console.WriteLine( "value: " + val );
1005 Sqlite3.sqlite3_step( stmt );
1006 val = Sqlite3.sqlite3_column_text( stmt, 0 );
1007 System.Console.WriteLine( "value: " + val );
1008 Sqlite3.sqlite3_finalize( stmt );
1009 }
1010  
1011 Console.WriteLine( "Test Done." );
1012 }
1013  
1014 public void Issue_149 ()
1015 {
1016 Console.WriteLine ("Test Issue 149 - Test Start.");
1017  
1018 SqliteConnection con = new SqliteConnection ();
1019  
1020 string dbFilename = @"SqliteTest3.db";
1021 string cs = string.Format ("Version=3,uri=file:{0}", dbFilename);
1022  
1023 if (File.Exists (dbFilename))
1024 File.Delete (dbFilename);
1025  
1026 con.ConnectionString = cs;
1027 con.Open ();
1028  
1029 Console.WriteLine ("create command...");
1030 IDbCommand cmd = con.CreateCommand ();
1031  
1032 cmd.CommandText = "CREATE TABLE TestZeroArrayTable (TheField BLOB)";
1033 cmd.ExecuteNonQuery ();
1034  
1035 cmd.CommandText = "INSERT INTO TestZeroArrayTable VALUES(?)";
1036 IDbDataParameter field6 = cmd.CreateParameter ();
1037 cmd.Parameters.Add (field6);
1038  
1039 byte[] data = new byte[] {};
1040  
1041 field6.Value = data;
1042 cmd.ExecuteNonQuery ();
1043  
1044 cmd.CommandText = "SELECT TheField FROM TestZeroArrayTable";
1045 byte[] res = (byte[])cmd.ExecuteScalar ();
1046  
1047 if (res == null)
1048 throw new ArgumentException ("res == null");
1049 if (res.Length != 0)
1050 throw new ArgumentException ("res.Length != 0");
1051  
1052 Console.WriteLine ("Test Done.");
1053 }
1054  
1055 public static int Main( string[] args )
1056 {
1057 SQLiteClientTestDriver tests = new SQLiteClientTestDriver();
1058  
1059 int Test = 1;
1060 switch ( Test )
1061 {
1062 case 1:
1063 tests.Test1();
1064 break;
1065 case 2:
1066 tests.Test2();
1067 break;
1068 case 3:
1069 tests.Test3();
1070 break;
1071 case 4:
1072 tests.Test4();
1073 break;
1074 case 5:
1075 tests.Test5();
1076 break;
1077 case 6:
1078 tests.Test6();
1079 break;
1080 case 7:
1081 tests.Test7();
1082 break;
1083 case 8:
1084 tests.Test8();
1085 break;
1086 case 65:
1087 tests.Issue_65();
1088 break;
1089 case 76:
1090 tests.Issue_76();
1091 break;
1092 case 86:
1093 tests.Issue_86();
1094 break;
1095 case 119:
1096 tests.Issue_119();
1097 break;
1098 case 124:
1099 tests.Issue_124();
1100 break;
1101 case 149:
1102 tests.Issue_149();
1103 break;
1104 }
1105 Console.WriteLine( "Press Enter to Continue" );
1106 Console.ReadKey();
1107 tests = null;
1108  
1109 return 0;
1110 }
1111 }
1112 }