wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 using System;
2 using System.Collections.Generic;
3 using System.Data;
4 using System.IO;
5 using System.Linq;
6 using System.Threading;
7 using System.Threading.Tasks;
8 using Community.CsharpSqlite.SQLiteClient;
9 using Windows.Foundation;
10 using Windows.Foundation.Collections;
11 using Windows.Storage;
12 using Windows.UI;
13 using Windows.UI.Popups;
14 using Windows.UI.Xaml;
15 using Windows.UI.Xaml.Controls;
16 using Windows.UI.Xaml.Controls.Primitives;
17 using Windows.UI.Xaml.Data;
18 using Windows.UI.Xaml.Input;
19 using Windows.UI.Xaml.Media;
20 using Windows.UI.Xaml.Navigation;
21  
22 // The Blank Page item template is documented at http://go.microsoft.com/fwlink/?LinkId=234238
23  
24 namespace MetroSQLiteClientTest2
25 {
26 /// <summary>
27 /// An empty page that can be used on its own or navigated to within a Frame.
28 /// </summary>
29 public sealed partial class MainPage : Page
30 {
31 public MainPage()
32 {
33 SyncContext = SynchronizationContext.Current;
34 this.InitializeComponent();
35 _testComboBox.Items.Add("Test 1");
36 _testComboBox.Items.Add("Test 2");
37 _testComboBox.Items.Add("Test 3");
38 _testComboBox.Items.Add("Test 4");
39 _testComboBox.Items.Add("Test 5");
40 _testComboBox.Items.Add("Test 6");
41 _testComboBox.Items.Add("Test 7");
42 _testComboBox.Items.Add("Issue 65");
43 _testComboBox.Items.Add("Issue 76");
44 _testComboBox.Items.Add("Issue 86");
45 _testComboBox.Items.Add("Issue 119");
46 _testComboBox.Items.Add("Issue 124");
47 _testComboBox.SelectedIndex = 0;
48 }
49  
50 /// <summary>
51 /// Invoked when this page is about to be displayed in a Frame.
52 /// </summary>
53 /// <param name="e">Event data that describes how this page was reached. The Parameter
54 /// property is typically used to configure the page.</param>
55 protected override void OnNavigatedTo(NavigationEventArgs e)
56 {
57 }
58  
59 #region Synchronous File Operations
60  
61 static private bool FileExists(string path)
62 {
63 bool exists = true;
64 try
65 {
66 Task<StorageFile> fileTask = StorageFile.GetFileFromPathAsync(path).AsTask<StorageFile>();
67 fileTask.Wait();
68 }
69 catch (Exception e)
70 {
71 AggregateException ae = e as AggregateException;
72 if(ae != null && ae.InnerException is FileNotFoundException)
73 exists = false;
74 }
75 return exists;
76 }
77 static private void FileDelete(string path)
78 {
79 Task<StorageFile> fileTask = StorageFile.GetFileFromPathAsync(path).AsTask<StorageFile>();
80 fileTask.Wait();
81 fileTask.Result.DeleteAsync().AsTask().Wait();
82 }
83  
84 #endregion
85  
86 #region "Logging" Methods
87  
88 private void ConsoleWriteLine(string value)
89 {
90 if (SynchronizationContext.Current != SyncContext)
91 {
92 SyncContext.Post(delegate
93 {
94 ConsoleWriteLine(value);
95 }, null);
96 return;
97 }
98  
99 TextBlock block = new TextBlock();
100 block.Text = value;
101 _resultsStackPanel.Children.Add(block);
102 }
103  
104 private void ConsoleWriteError(string value)
105 {
106 if (SynchronizationContext.Current != SyncContext)
107 {
108 SyncContext.Post(delegate
109 {
110 ConsoleWriteError(value);
111 }, null);
112 return;
113 }
114  
115 TextBlock block = new TextBlock();
116 block.Foreground = new SolidColorBrush(Color.FromArgb(255, 255, 0, 0));
117 block.Text = value;
118 _resultsStackPanel.Children.Add(block);
119 }
120  
121 #endregion
122  
123 #region Test1
124  
125 public IAsyncAction Test1Async()
126 {
127 return Task.Run(() => { Test1(); }).AsAsyncAction();
128 }
129  
130 public void Test1()
131 {
132 try
133 {
134 ConsoleWriteLine("Test1 Start.");
135  
136 ConsoleWriteLine("Create connection...");
137 SqliteConnection con = new SqliteConnection();
138  
139 string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"SqliteTest3.db");
140 string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
141  
142 ConsoleWriteLine(String.Format("Set connection String: {0}", cs));
143  
144 if (FileExists(dbFilename))
145 FileDelete(dbFilename);
146  
147 con.ConnectionString = cs;
148  
149 ConsoleWriteLine("Open database...");
150 con.Open();
151  
152 ConsoleWriteLine("create command...");
153 IDbCommand cmd = con.CreateCommand();
154  
155 ConsoleWriteLine("create table TEST_TABLE...");
156 cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
157 cmd.ExecuteNonQuery();
158  
159 ConsoleWriteLine("insert row 1...");
160 cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
161 cmd.ExecuteNonQuery();
162  
163 ConsoleWriteLine("insert row 2...");
164 cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
165 cmd.ExecuteNonQuery();
166  
167 //Console.WriteLine("commit...");
168 //cmd.CommandText = "COMMIT";
169 //cmd.ExecuteNonQuery();
170  
171 ConsoleWriteLine("SELECT data from TEST_TABLE...");
172 cmd.CommandText = "SELECT COLA, COLB, COLC FROM TEST_TABLE";
173 IDataReader reader = cmd.ExecuteReader();
174 int r = 0;
175 ConsoleWriteLine("Read the data...");
176 while (reader.Read())
177 {
178 ConsoleWriteLine(String.Format(" Row: {0}", r));
179 int i = reader.GetInt32(reader.GetOrdinal("COLA"));
180 ConsoleWriteLine(String.Format(" COLA: {0}", i));
181  
182 string s = reader.GetString(reader.GetOrdinal("COLB"));
183 ConsoleWriteLine(String.Format(" COLB: {0}", s));
184  
185 DateTime dt = reader.GetDateTime(reader.GetOrdinal("COLC"));
186 ConsoleWriteLine(String.Format(" COLB: {0}", dt.ToString("MM/dd/yyyy HH:mm:ss")));
187  
188 r++;
189 }
190 ConsoleWriteLine(String.Format("Rows retrieved: {0}", r));
191  
192 ConsoleWriteLine("Close and cleanup...");
193 con.Close();
194 con = null;
195  
196 ConsoleWriteLine("Test1 Done.");
197 }
198 catch (Exception e)
199 {
200 ConsoleWriteError("ERROR: " + e.Message);
201 ConsoleWriteError(e.StackTrace);
202 }
203 }
204  
205 #endregion
206  
207 #region Test2
208  
209 public IAsyncAction Test2Async()
210 {
211 return Task.Run(() => { Test2(); }).AsAsyncAction();
212 }
213  
214 public void Test2()
215 {
216 try
217 {
218 ConsoleWriteLine("Test2 Start.");
219  
220 ConsoleWriteLine("Create connection...");
221 SqliteConnection con = new SqliteConnection();
222  
223 string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"SqliteTest3.db");
224 string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
225  
226 ConsoleWriteLine(String.Format("Set connection String: {0}", cs));
227  
228 if (FileExists(dbFilename))
229 FileDelete(dbFilename);
230  
231 con.ConnectionString = cs;
232  
233 ConsoleWriteLine("Open database...");
234 con.Open();
235  
236 ConsoleWriteLine("create command...");
237 IDbCommand cmd = con.CreateCommand();
238  
239 ConsoleWriteLine("create table TEST_TABLE...");
240 cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)";
241 cmd.ExecuteNonQuery();
242  
243 ConsoleWriteLine("insert row 1...");
244 cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )";
245 cmd.ExecuteNonQuery();
246  
247 ConsoleWriteLine("insert row 2...");
248 cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, '中文' )";
249 cmd.ExecuteNonQuery();
250  
251 //Console.WriteLine("commit...");
252 //cmd.CommandText = "COMMIT";
253 //cmd.ExecuteNonQuery();
254  
255 ConsoleWriteLine("SELECT data from TBL...");
256 cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = '中文'";
257 IDataReader reader = cmd.ExecuteReader();
258 int r = 0;
259 ConsoleWriteLine("Read the data...");
260 while (reader.Read())
261 {
262 ConsoleWriteLine(String.Format(" Row: {0}", r));
263 int i = reader.GetInt32(reader.GetOrdinal("ID"));
264 ConsoleWriteLine(String.Format(" ID: {0}", i));
265  
266 string s = reader.GetString(reader.GetOrdinal("NAME"));
267 ConsoleWriteLine(String.Format(" NAME: {0} = {1}", s, s == "中文"));
268 r++;
269 }
270 ConsoleWriteLine(String.Format("Rows retrieved: {0}", r));
271  
272 ConsoleWriteLine("Close and cleanup...");
273 con.Close();
274 con = null;
275  
276 ConsoleWriteLine("Test2 Done.");
277 }
278 catch (Exception e)
279 {
280 ConsoleWriteError("ERROR: " + e.Message);
281 ConsoleWriteError(e.StackTrace);
282 }
283 }
284  
285 #endregion
286  
287 #region Test3
288  
289 public IAsyncAction Test3Async()
290 {
291 return Task.Run(() => { Test3(); }).AsAsyncAction();
292 }
293  
294 public void Test3()
295 {
296 try
297 {
298  
299 ConsoleWriteLine("Test3 (Date Paramaters) Start.");
300  
301 ConsoleWriteLine("Create connection...");
302 SqliteConnection con = new SqliteConnection();
303  
304 string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"SqliteTest3.db");
305 string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
306  
307 ConsoleWriteLine(String.Format("Set connection String: {0}", cs));
308  
309 if (FileExists(dbFilename))
310 FileDelete(dbFilename);
311  
312 con.ConnectionString = cs;
313  
314 ConsoleWriteLine("Open database...");
315 con.Open();
316  
317 ConsoleWriteLine("create command...");
318 IDbCommand cmd = con.CreateCommand();
319  
320 ConsoleWriteLine("create table TEST_TABLE...");
321 cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, DATE_TEXT REAL)";
322 cmd.ExecuteNonQuery();
323  
324 ConsoleWriteLine("insert ...");
325 cmd.CommandText = "INSERT INTO TBL ( ID, DATE_TEXT) VALUES ( 1, @DATETEXT)";
326 cmd.Parameters.Add(
327 new SqliteParameter
328 {
329 ParameterName = "@DATETEXT",
330 Value = DateTime.Now
331 }
332 );
333  
334 cmd.ExecuteNonQuery();
335  
336  
337 ConsoleWriteLine("SELECT data from TBL...");
338 cmd.CommandText = "SELECT * FROM tbl";
339 IDataReader reader = cmd.ExecuteReader();
340 int r = 0;
341 ConsoleWriteLine("Read the data...");
342 while (reader.Read())
343 {
344 ConsoleWriteLine(String.Format(" Row: {0}", r));
345 int i = reader.GetInt32(reader.GetOrdinal("ID"));
346 ConsoleWriteLine(String.Format(" ID: {0}", i));
347  
348 string s = reader.GetString(reader.GetOrdinal("DATE_TEXT"));
349 ConsoleWriteLine(String.Format(" DATE_TEXT: {0}", s));
350 r++;
351 }
352 ConsoleWriteLine(String.Format("Rows retrieved: {0}", r));
353  
354  
355 ConsoleWriteLine("Close and cleanup...");
356 con.Close();
357 con = null;
358  
359 ConsoleWriteLine("Test3 Done.");
360 }
361 catch (Exception e)
362 {
363 ConsoleWriteError("ERROR: " + e.Message);
364 ConsoleWriteError(e.StackTrace);
365 }
366 }
367  
368 #endregion
369  
370 #region Test4
371  
372 public IAsyncAction Test4Async()
373 {
374 return Task.Run(() => { Test4(); }).AsAsyncAction();
375 }
376  
377 //nSoftware code for Threading
378 string connstring_T4;
379 public void Test4()
380 {
381 string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"threading_t4.db");
382 if (FileExists(dbFilename))
383 FileDelete(dbFilename);
384 connstring_T4 = @"Version=3,busy_timeout=100,uri=file:" + dbFilename;
385  
386 Setup_T4();
387 InsertSameTable_T4(); //concurrent inserts
388 SelectorWrite_T4(); //concurrent selects and inserts
389 ConsoleWriteLine("Testing for Threading done.");
390 }
391 private void SelectorWrite_T4()
392 {
393 //concurrent reads/writes in the same table, if there were only Selects it would be preferable for the sqlite engine not to lock internally.
394 List<Task> tasks = new List<Task>();
395 for (int i = 0; i < 10; i++)
396 {
397 Task worker = Task.Factory.StartNew((state) =>
398 {
399 // Cannot use value of i, since it exceeds the scope of this thread and will be
400 // reused by multiple threads
401 int aValue = 100 + (int)state;
402 int op = aValue % 2;
403 ConsoleWriteLine(String.Format("SELECT/INSERT ON Thread {0}", state));
404  
405 using (SqliteConnection con = new SqliteConnection())
406 {
407 try
408 {
409 con.ConnectionString = connstring_T4;
410 con.Open();
411 IDbCommand cmd = con.CreateCommand();
412 if (op == 0)
413 {
414 cmd.CommandText = String.Format("Select * FROM ATABLE");
415 cmd.ExecuteReader();
416 }
417 else
418 {
419 cmd.CommandText = String.Format("INSERT INTO ATABLE ( A, B, C ) VALUES ({0},'threader', '1' )", aValue);
420 ConsoleWriteLine(cmd.CommandText);
421 cmd.ExecuteNonQuery();
422 }
423 }
424 catch (Exception e)
425 {
426 ConsoleWriteError("ERROR: " + e.Message);
427 ConsoleWriteError(e.StackTrace);
428 }
429 }
430 },i);
431 tasks.Add(worker);
432 }
433 ConsoleWriteLine("Waiting for select/write tasks...");
434 Task.WaitAll(tasks.ToArray<Task>());
435 ConsoleWriteLine("All select/write tasks complete");
436 }
437 //we need concurrency support on a table level inside of the database file.
438 private void InsertSameTable_T4()
439 {
440 List<Task> tasks = new List<Task>();
441 for (int i = 0; i < 10; i++)
442 {
443 Task worker = Task.Factory.StartNew((state) =>
444 {
445 // Cannot use value of i, since it exceeds the scope of this thread and will be
446 // reused by multiple threads
447  
448 ConsoleWriteLine(String.Format("INSERTING ON Thread {0}", state));
449 int aValue = (int)state;
450  
451 using (SqliteConnection con = new SqliteConnection())
452 {
453 try
454 {
455 con.ConnectionString = connstring_T4;
456 ConsoleWriteLine(String.Format("About to Open Thread {0}", state));
457 con.Open();
458 ConsoleWriteLine(String.Format("Open complete Thread {0}", state));
459 IDbCommand cmd = con.CreateCommand();
460 cmd = con.CreateCommand();
461 cmd.CommandText = String.Format("INSERT INTO ATABLE ( A, B, C ) VALUES ({0},'threader', '1' )", aValue);
462 ConsoleWriteLine(cmd.CommandText);
463 cmd.ExecuteNonQuery();
464 }
465 catch (Exception e)
466 {
467 ConsoleWriteError("ERROR: " + e.Message);
468 ConsoleWriteError(e.StackTrace);
469 }
470 }
471 },i);
472 tasks.Add(worker);
473 }
474 ConsoleWriteLine("Waiting for insert tasks...");
475 Task.WaitAll(tasks.ToArray<Task>());
476 ConsoleWriteLine("All insert tasks complete");
477 }
478  
479 private void Setup_T4()
480 {
481 using (SqliteConnection con = new SqliteConnection())
482 {
483 con.ConnectionString = connstring_T4;
484 con.Open();
485 IDbCommand cmd = con.CreateCommand();
486 cmd = con.CreateCommand();
487 cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
488 cmd.ExecuteNonQuery();
489 cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
490 cmd.ExecuteNonQuery();
491 cmd.CommandText = String.Format("INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )");
492 cmd.ExecuteNonQuery();
493 }
494 }
495 #endregion
496  
497 #region Issue 119
498  
499 public IAsyncAction Issue_119Async()
500 {
501 return Task.Run(() => { Issue_119(); }).AsAsyncAction();
502 }
503  
504 public void Issue_119()
505 {
506 try
507 {
508 ConsoleWriteLine("Issue 119 Start.");
509  
510 ConsoleWriteLine("Create connection...");
511 SqliteConnection con = new SqliteConnection();
512  
513 string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"SqliteTest3.db");
514 string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
515  
516 ConsoleWriteLine(String.Format("Set connection String: {0}", cs));
517  
518 if (FileExists(dbFilename))
519 FileDelete(dbFilename);
520  
521 con.ConnectionString = cs;
522  
523 ConsoleWriteLine("Open database...");
524 con.Open();
525  
526 ConsoleWriteLine("create command...");
527 IDbCommand cmd = con.CreateCommand();
528  
529 ConsoleWriteLine("create table TEST_TABLE...");
530 cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
531 cmd.ExecuteNonQuery();
532  
533 ConsoleWriteLine("insert row 1...");
534 cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
535 cmd.ExecuteNonQuery();
536  
537 ConsoleWriteLine("insert row 2...");
538 cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
539 cmd.ExecuteNonQuery();
540  
541 ConsoleWriteLine("SELECT data from TEST_TABLE...");
542 cmd.CommandText = "SELECT RowID, COLA, COLB, COLC FROM TEST_TABLE";
543 IDataReader reader = cmd.ExecuteReader();
544 int r = 0;
545 ConsoleWriteLine("Read the data...");
546 while (reader.Read())
547 {
548 ConsoleWriteLine(String.Format(" Row: {0}", r));
549 int rowid = reader.GetInt32(reader.GetOrdinal("RowID"));
550 ConsoleWriteLine(String.Format(" RowID: {0}", rowid));
551  
552 int i = reader.GetInt32(reader.GetOrdinal("COLA"));
553 ConsoleWriteLine(String.Format(" COLA: {0}", i));
554  
555 string s = reader.GetString(reader.GetOrdinal("COLB"));
556 ConsoleWriteLine(String.Format(" COLB: {0}", s));
557  
558 DateTime dt = reader.GetDateTime(reader.GetOrdinal("COLC"));
559 ConsoleWriteLine(String.Format(" COLB: {0}", dt.ToString("MM/dd/yyyy HH:mm:ss")));
560  
561 r++;
562 }
563  
564 ConsoleWriteLine("Close and cleanup...");
565 con.Close();
566 con = null;
567  
568 ConsoleWriteLine("Issue 119 Done.");
569 }
570 catch (Exception e)
571 {
572 ConsoleWriteError("ERROR: " + e.Message);
573 ConsoleWriteError(e.StackTrace);
574 }
575 }
576 #endregion
577  
578 public SynchronizationContext SyncContext { get; set; }
579  
580 private async void Button_Click_1(object sender, RoutedEventArgs e)
581 {
582 _runButton.IsEnabled = false;
583 _resultsStackPanel.Children.Clear();
584 switch (_testComboBox.SelectedItem.ToString())
585 {
586 case "Test 1":
587 await Test1Async();
588 break;
589 case "Test 2":
590 await Test2Async();
591 break;
592 case "Test 3":
593 await Test3Async();
594 break;
595 case "Test 4":
596 await Test4Async();
597 break;
598 case "Issue 119":
599 await Issue_119Async();
600 break;
601  
602 default:
603 await new MessageDialog(_testComboBox.SelectedItem.ToString() + " Not Implemented Yet").ShowAsync();
604 break;
605 }
606 _runButton.IsEnabled = true;
607 }
608  
609 }
610 }