wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 using System;
2 using System.Data.Common;
3 using System.Data;
4 using Community.CsharpSqlite.SQLiteClient;
5  
6 namespace test
7 {
8  
9 /// <summary>
10 /// Scalar user-defined function. In this example, the same class is declared twice with
11 /// different function names to demonstrate how to use alias names for user-defined functions.
12 /// </summary>
13 //[SQLiteFunction(Name = "Foo", Arguments = 2, FuncType = FunctionType.Scalar)]
14 //[SQLiteFunction(Name = "TestFunc", Arguments = 2, FuncType = FunctionType.Scalar)]
15 class TestFunc// : SQLiteFunction
16 {
17 public object Invoke(object[] args)
18 {
19 if (args[0].GetType() != typeof(int)) return args[0];
20  
21 int Param1 = Convert.ToInt32(args[0]); // First parameter
22 int Param2 = Convert.ToInt32(args[1]); // Second parameter
23  
24 return Param1 + Param2;
25 }
26 }
27  
28 /// <summary>
29 /// Aggregate user-defined function. Arguments = -1 means any number of arguments is acceptable
30 /// </summary>
31 // [SQLiteFunction(Name = "MyCount", Arguments = -1, FuncType = FunctionType.Aggregate)]
32 class MyCount //: SQLiteFunction
33 {
34 public void Step(object[] args, int nStep, ref object contextData)
35 {
36 if (contextData == null)
37 {
38 contextData = 1;
39 }
40 else
41 contextData = (int)contextData + 1;
42 }
43  
44 public object Final(object contextData)
45 {
46 return contextData;
47 }
48 }
49  
50 /// <summary>
51 /// User-defined collating sequence.
52 /// </summary>
53 //[SQLiteFunction(Name = "MYSEQUENCE", FuncType = FunctionType.Collation)]
54 class MySequence // : SQLiteFunction
55 {
56 public int Compare(string param1, string param2)
57 {
58 // Make sure the string "Field3" is sorted out of order
59 if (param1 == "Field3") return 1;
60 if (param2 == "Field3") return -1;
61 return String.Compare(param1, param2);
62 }
63 }
64  
65 internal class TestCases
66 {
67 internal Test.WP.MainPage frm;
68  
69 internal void Run(IDbConnection cnn, Test.WP.MainPage frm2)
70 {
71  
72 frm = frm2;
73 // frm.Show();
74  
75 frm.WriteLine("\r\nBeginning Test on " + cnn.GetType().ToString());
76 try { CreateTable(cnn); frm.WriteLine("SUCCESS - CreateTable"); }
77 catch (Exception) { frm.WriteLine("FAIL - CreateTable"); }
78  
79 try { DataTypeTest(cnn); frm.WriteLine("SUCCESS - DataType Test"); }
80 catch (Exception) { frm.WriteLine("FAIL - DataType Test"); }
81  
82 //not enabled yet
83 //try { FullTextTest(cnn); frm.WriteLine("SUCCESS - Full Text Search"); }
84 //catch (Exception) { frm.WriteLine("FAIL - Full Text Search"); }
85  
86 //Not possible without datatable to reade the schema
87 //try { KeyInfoTest(cnn); frm.WriteLine("SUCCESS - KeyInfo Fetch"); }
88 //catch (Exception) { frm.WriteLine("FAIL - KeyInfo Fetch"); }
89  
90 try { InsertTable(cnn); frm.WriteLine("SUCCESS - InsertTable"); }
91 catch (Exception) { frm.WriteLine("FAIL - InsertTable"); }
92  
93 try { VerifyInsert(cnn); frm.WriteLine("SUCCESS - VerifyInsert"); }
94 catch (Exception) { frm.WriteLine("FAIL - VerifyInsert"); }
95  
96 try { CoersionTest(cnn); frm.WriteLine("FAIL - CoersionTest"); }
97 catch (Exception) { frm.WriteLine("SUCCESS - CoersionTest"); }
98  
99 try { ParameterizedInsert(cnn); frm.WriteLine("SUCCESS - ParameterizedInsert"); }
100 catch (Exception) { frm.WriteLine("FAIL - ParameterizedInsert"); }
101  
102 try { BinaryInsert(cnn); frm.WriteLine("SUCCESS - BinaryInsert"); }
103 catch (Exception) { frm.WriteLine("FAIL - BinaryInsert"); }
104  
105 try { VerifyBinaryData(cnn); frm.WriteLine("SUCCESS - VerifyBinaryData"); }
106 catch (Exception) { frm.WriteLine("FAIL - VerifyBinaryData"); }
107  
108 try { LockTest(cnn); frm.WriteLine("SUCCESS - LockTest"); }
109 catch (Exception) { frm.WriteLine("FAIL - LockTest"); }
110  
111 try { ParameterizedInsertMissingParams(cnn); frm.WriteLine("FAIL - ParameterizedInsertMissingParams"); }
112 catch (Exception) { frm.WriteLine("SUCCESS - ParameterizedInsertMissingParams"); }
113  
114 //try { InsertMany(cnn, false); frm.WriteLine("SUCCESS - InsertMany"); }
115 //catch (Exception) { frm.WriteLine("FAIL - InsertMany"); }
116  
117 //try { InsertMany(cnn, true); frm.WriteLine("SUCCESS - InsertManyWithIdentityFetch"); }
118 //catch (Exception) { frm.WriteLine("FAIL - InsertManyWithIdentityFetch"); }
119  
120 try { FastInsertMany(cnn); frm.WriteLine("SUCCESS - FastInsertMany"); }
121 catch (Exception) { frm.WriteLine("FAIL - FastInsertMany"); }
122  
123 //try { IterationTest(cnn); frm.WriteLine("SUCCESS - Iteration Test"); }
124 //catch (Exception) { frm.WriteLine("FAIL - Iteration Test"); }
125  
126 //try { UserFunction(cnn); frm.WriteLine("SUCCESS - UserFunction"); }
127 //catch (Exception) { frm.WriteLine("FAIL - UserFunction"); }
128  
129 //try { UserAggregate(cnn); frm.WriteLine("SUCCESS - UserAggregate"); }
130 //catch (Exception) { frm.WriteLine("FAIL - UserAggregate"); }
131  
132 //try { UserCollation(cnn); frm.WriteLine("SUCCESS - UserCollation"); }
133 //catch (Exception) { frm.WriteLine("FAIL - UserCollation"); }
134  
135 try { DropTable(cnn); frm.WriteLine("SUCCESS - DropTable"); }
136 catch (Exception) { frm.WriteLine("FAIL - DropTable"); }
137  
138 frm.WriteLine("\r\nTests Finished.");
139 }
140  
141 internal static void KeyInfoTest(IDbConnection cnn)
142 {
143 using (IDbCommand cmd = cnn.CreateCommand())
144 {
145 // First test against integer primary key (optimized) keyinfo fetch
146 cmd.CommandText = "Create table keyinfotest (id integer primary key, myuniquevalue integer unique not null, myvalue varchar(50))";
147 cmd.ExecuteNonQuery();
148  
149 cmd.CommandText = "Select * from keyinfotest";
150 using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
151 {
152 if (reader.FieldCount != 3) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
153 }
154  
155 cmd.CommandText = "SELECT MyValue FROM keyinfotest";
156 using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
157 {
158 if (reader.FieldCount != 2) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
159 }
160  
161 cmd.CommandText = "DROP TABLE keyinfotest";
162 cmd.ExecuteNonQuery();
163  
164 // Now test against non-integer primary key (unoptimized) subquery keyinfo fetch
165 cmd.CommandText = "Create table keyinfotest (id char primary key, myuniquevalue integer unique not null, myvalue varchar(50))";
166 cmd.ExecuteNonQuery();
167  
168 cmd.CommandText = "SELECT MyValue FROM keyinfotest";
169 using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
170 {
171 if (reader.FieldCount != 2) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
172 }
173  
174 cmd.CommandText = "Select * from keyinfotest";
175 using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
176 {
177 if (reader.FieldCount != 3) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
178 }
179  
180 //// Make sure commandbuilder can generate an update command with the correct parameter count
181 //using (DbDataAdapter adp = new SQLiteDataAdapter())
182 //using (DbCommandBuilder builder = new SQLiteCommandBuilder())
183 //{
184 // adp.SelectCommand = cmd;
185 // builder.DataAdapter = adp;
186 // builder.ConflictOption = ConflictOption.OverwriteChanges;
187  
188 // using (IDbCommand updatecmd = builder.GetUpdateCommand())
189 // {
190 // if (updatecmd.Parameters.Count != 4)
191 // throw new ArgumentOutOfRangeException("Wrong number of parameters in update command!");
192 // }
193 //}
194 }
195 }
196  
197 internal static void DataTypeTest(IDbConnection cnn)
198 {
199 DateTime now = DateTime.Now;
200 using (IDbCommand cmd = cnn.CreateCommand())
201 {
202 cmd.CommandText = "create table datatypetest(id integer primary key, myvalue, datetimevalue datetime, decimalvalue decimal)";
203 cmd.ExecuteNonQuery();
204  
205 cmd.CommandText = "insert into datatypetest(myvalue, datetimevalue, decimalvalue) values(?,?,?)";
206 IDbDataParameter p1 = cmd.CreateParameter();
207 IDbDataParameter p2 = cmd.CreateParameter();
208 IDbDataParameter p3 = cmd.CreateParameter();
209  
210 cmd.Parameters.Add(p1);
211 cmd.Parameters.Add(p2);
212 cmd.Parameters.Add(p3);
213  
214 p1.Value = 1;
215 p2.Value = DateTime.MinValue;
216 p3.Value = (Decimal)1.05;
217 cmd.ExecuteNonQuery();
218  
219 //p1.ResetDbType();
220 //p2.ResetDbType();
221 //p3.ResetDbType();
222  
223 p1.Value = "One";
224 p2.Value = "2001-01-01";
225 p3.Value = (float)1.123;
226 cmd.ExecuteNonQuery();
227  
228 //p1.ResetDbType();
229 //p2.ResetDbType();
230 //p3.ResetDbType();
231  
232 p1.Value = 1.01;
233 DateTime nw = now;
234 p2.Value = nw;
235 p3.Value = (Double )9.91;
236 cmd.ExecuteNonQuery();
237  
238 cmd.CommandText = "select myvalue, datetimevalue, decimalvalue from datatypetest";
239 using (IDataReader reader = cmd.ExecuteReader())
240 {
241 for (int n = 0; n < 3; n++)
242 {
243 reader.Read();
244 if (reader.GetValue(1).GetType() != reader.GetDateTime(1).GetType()) throw new ArgumentOutOfRangeException();
245 if (reader.GetValue(2).GetType() != reader.GetDouble(2).GetType()) throw new ArgumentOutOfRangeException();
246  
247 switch (n)
248 {
249 case 0:
250 if (reader.GetValue(0).GetType() != typeof(long)) throw new ArgumentOutOfRangeException();
251  
252 if (reader.GetValue(0).Equals((long)1) == false) throw new ArgumentOutOfRangeException();
253 if (reader.GetValue(1).Equals(DateTime.MinValue) == false) throw new ArgumentOutOfRangeException();
254 if (reader.GetDecimal(2).Equals((Decimal)1.05) == false) throw new ArgumentOutOfRangeException();
255  
256 if (reader.GetInt64(0) != (long)1) throw new ArgumentOutOfRangeException();
257 if (reader.GetValue(1).Equals(reader.GetDateTime(1)) == false) throw new ArgumentOutOfRangeException();
258 if (reader.GetValue(2).Equals(reader.GetDouble(2)) == false) throw new ArgumentOutOfRangeException();
259 break;
260 case 1:
261 if (reader.GetValue(0).GetType() != typeof(string)) throw new ArgumentOutOfRangeException();
262 if (reader.GetValue(0).Equals("One") == false) throw new ArgumentOutOfRangeException();
263 if (reader.GetValue(1).Equals(new DateTime(2001, 1, 1)) == false) throw new ArgumentOutOfRangeException();
264 if (reader.GetFloat(2).Equals((float)1.123) == false) throw new ArgumentOutOfRangeException();
265  
266 if (reader.GetString(0) != "One") throw new ArgumentOutOfRangeException();
267 if (reader.GetValue(1).Equals(reader.GetDateTime(1)) == false) throw new ArgumentOutOfRangeException();
268 if (reader.GetValue(2).Equals(reader.GetDouble(2)) == false) throw new ArgumentOutOfRangeException();
269 break;
270 case 2:
271 if (reader.GetValue(0).GetType() != typeof(double)) throw new ArgumentOutOfRangeException();
272 if (reader.GetValue(0).Equals(1.01) == false) throw new ArgumentOutOfRangeException();
273 //Something weird comparing datetime values...
274 if (((DateTime)reader.GetValue(1)).ToString("s").Equals(nw.ToString("s")) == false) throw new ArgumentOutOfRangeException();
275 if (reader.GetDouble(2).Equals((Double)9.91) == false) throw new ArgumentOutOfRangeException();
276  
277 if (reader.GetDouble(0) != 1.01) throw new ArgumentOutOfRangeException();
278 if (reader.GetValue(1).Equals(reader.GetDateTime(1)) == false) throw new ArgumentOutOfRangeException();
279 if (reader.GetValue(2).Equals(reader.GetDouble(2)) == false) throw new ArgumentOutOfRangeException();
280 break;
281 }
282 }
283 }
284 }
285 }
286  
287 internal static void FullTextTest(IDbConnection cnn)
288 {
289 using (IDbCommand cmd = cnn.CreateCommand())
290 {
291 cmd.CommandText = "CREATE VIRTUAL TABLE FullText USING FTS3(name, ingredients);";
292 cmd.ExecuteNonQuery();
293  
294 string[] names = { "broccoli stew", "pumpkin stew", "broccoli pie", "pumpkin pie" };
295 string[] ingredients = { "broccoli peppers cheese tomatoes", "pumpkin onions garlic celery", "broccoli cheese onions flour", "pumpkin sugar flour butter" };
296 int n;
297  
298 cmd.CommandText = "insert into FullText (name, ingredients) values (@name, @ingredient);";
299 IDbDataParameter name = cmd.CreateParameter();
300 IDbDataParameter ingredient = cmd.CreateParameter();
301  
302 name.ParameterName = "@name";
303 ingredient.ParameterName = "@ingredient";
304  
305 cmd.Parameters.Add(name);
306 cmd.Parameters.Add(ingredient);
307  
308 for (n = 0; n < names.Length; n++)
309 {
310 name.Value = names[n];
311 ingredient.Value = ingredients[n];
312  
313 cmd.ExecuteNonQuery();
314 }
315  
316 cmd.CommandText = "select rowid, name, ingredients from FullText where name match 'pie';";
317  
318 int[] rowids = { 3, 4 };
319 n = 0;
320  
321 using (IDataReader reader = cmd.ExecuteReader())
322 {
323 while (reader.Read())
324 {
325 if (reader.GetInt64(0) != rowids[n++])
326 throw new ArgumentException("Unexpected rowid returned");
327  
328 if (n > rowids.Length) throw new ArgumentException("Too many rows returned");
329 }
330 }
331 }
332 }
333  
334 internal void CreateTable(IDbConnection cnn)
335 {
336 using (IDbCommand cmd = cnn.CreateCommand())
337 {
338 cmd.CommandText = "CREATE TABLE TestCase (ID integer primary key autoincrement, Field1 Integer, Field2 Float, Field3 VARCHAR(50), Field4 CHAR(10), Field5 DateTime, Field6 Image)";
339 //cmd.CommandText = "CREATE TABLE TestCase (ID bigint primary key identity, Field1 Integer, Field2 Float, Field3 VARCHAR(50), Field4 CHAR(10), Field5 DateTime, Field6 Image)";
340 cmd.ExecuteNonQuery();
341 }
342 }
343  
344 internal void DropTable(IDbConnection cnn)
345 {
346 using (IDbCommand cmd = cnn.CreateCommand())
347 {
348 cmd.CommandText = "DROP TABLE TestCase";
349 cmd.ExecuteNonQuery();
350 }
351 }
352  
353 internal void InsertTable(IDbConnection cnn)
354 {
355 using (IDbCommand cmd = cnn.CreateCommand())
356 {
357 cmd.CommandText = "INSERT INTO TestCase(Field1, Field2, Field3, Field4, Field5) VALUES(1, 3.14159, 'Field3', 'Field4', '2005-01-01 13:49:00')";
358 cmd.ExecuteNonQuery();
359 }
360 }
361  
362 internal void VerifyInsert(IDbConnection cnn)
363 {
364 using (IDbCommand cmd = cnn.CreateCommand())
365 {
366 cmd.CommandText = "SELECT Field1, Field2, Field3, Field4, Field5 FROM TestCase";
367 cmd.Prepare();
368 using (IDataReader rd = cmd.ExecuteReader())
369 {
370 if (rd.Read())
371 {
372 long Field1 = rd.GetInt64(0);
373 double Field2 = rd.GetDouble(1);
374 string Field3 = rd.GetString(2);
375 string Field4 = rd.GetString(3).TrimEnd();
376 DateTime Field5 = rd.GetDateTime(4);
377  
378 if (Field1 != 1) throw new ArgumentOutOfRangeException("Non-Match on Field1");
379 if (Field2 != 3.14159) throw new ArgumentOutOfRangeException("Non-Match on Field2");
380 if (Field3 != "Field3") throw new ArgumentOutOfRangeException("Non-Match on Field3");
381 if (Field4 != "Field4") throw new ArgumentOutOfRangeException("Non-Match on Field4");
382 if (Field5.CompareTo(DateTime.Parse("2005-01-01 13:49:00")) != 0) throw new ArgumentOutOfRangeException("Non-Match on Field5");
383 }
384 else throw new ArgumentOutOfRangeException("No data in table");
385 }
386 }
387 }
388  
389 internal void CoersionTest(IDbConnection cnn)
390 {
391 using (IDbCommand cmd = cnn.CreateCommand())
392 {
393 cmd.CommandText = "SELECT Field1, Field2, Field3, Field4, Field5, 'A', 1, 1 + 1, 3.14159 FROM TestCase";
394 using (IDataReader rd = cmd.ExecuteReader())
395 {
396 if (rd.Read())
397 {
398 object Field1 = rd.GetInt32(0);
399 object Field2 = rd.GetDouble(1);
400 object Field3 = rd.GetString(2);
401 object Field4 = rd.GetString(3).TrimEnd();
402 object Field5 = rd.GetDateTime(4);
403  
404 // The next statement should cause an exception
405 Field1 = rd.GetString(0);
406 Field2 = rd.GetString(1);
407 Field3 = rd.GetString(2);
408 Field4 = rd.GetString(3);
409 Field5 = rd.GetString(4);
410  
411 Field1 = rd.GetInt32(0);
412 Field2 = rd.GetInt32(1);
413 Field3 = rd.GetInt32(2);
414 Field4 = rd.GetInt32(3);
415 Field5 = rd.GetInt32(4);
416  
417 Field1 = rd.GetDecimal(0);
418 Field2 = rd.GetDecimal(1);
419 Field3 = rd.GetDecimal(2);
420 Field4 = rd.GetDecimal(3);
421 Field5 = rd.GetDecimal(4);
422 }
423 else throw new ArgumentOutOfRangeException("No data in table");
424 }
425 }
426 }
427  
428 internal void ParameterizedInsert(IDbConnection cnn)
429 {
430 using (IDbCommand cmd = cnn.CreateCommand())
431 {
432 cmd.CommandText = "INSERT INTO TestCase(Field1, Field2, Field3, Field4, Field5) VALUES(?,?,?,?,?)";
433 IDbDataParameter Field1 = cmd.CreateParameter();
434 IDbDataParameter Field2 = cmd.CreateParameter();
435 IDbDataParameter Field3 = cmd.CreateParameter();
436 IDbDataParameter Field4 = cmd.CreateParameter();
437 IDbDataParameter Field5 = cmd.CreateParameter();
438  
439 Field1.Value = 2;
440 Field2.Value = 3.14159;
441 Field3.Value = "Param Field3";
442 Field4.Value = "Field4 Par";
443 Field5.Value = DateTime.Now;
444  
445 cmd.Parameters.Add(Field1);
446 cmd.Parameters.Add(Field2);
447 cmd.Parameters.Add(Field3);
448 cmd.Parameters.Add(Field4);
449 cmd.Parameters.Add(Field5);
450  
451 cmd.ExecuteNonQuery();
452 }
453 }
454  
455 internal void BinaryInsert(IDbConnection cnn)
456 {
457 using (IDbCommand cmd = cnn.CreateCommand())
458 {
459 cmd.CommandText = "INSERT INTO TestCase(Field6) VALUES(?)";
460 IDbDataParameter Field6 = cmd.CreateParameter();
461  
462 byte[] b = new byte[4000];
463 b[0] = 1;
464 b[100] = 2;
465 b[1000] = 3;
466 b[2000] = 4;
467 b[3000] = 5;
468  
469 Field6.Value = b;
470  
471 cmd.Parameters.Add(Field6);
472  
473 cmd.ExecuteNonQuery();
474 }
475 }
476  
477 internal void VerifyBinaryData(IDbConnection cnn)
478 {
479 using (IDbCommand cmd = cnn.CreateCommand())
480 {
481 cmd.CommandText = "SELECT Field6 FROM TestCase WHERE Field6 IS NOT NULL";
482 byte[] b = new byte[4000];
483  
484 using (IDataReader rd = cmd.ExecuteReader())
485 {
486 if (rd.Read() == false) throw new ArgumentOutOfRangeException();
487  
488 rd.GetBytes(0, 0, b, 0, 4000);
489  
490 if (b[0] != 1) throw new ArgumentException();
491 if (b[100] != 2) throw new ArgumentException();
492 if (b[1000] != 3) throw new ArgumentException();
493 if (b[2000] != 4) throw new ArgumentException();
494 if (b[3000] != 5) throw new ArgumentException();
495 }
496 }
497 }
498  
499 internal static void LockTest(IDbConnection cnn)
500 {
501 using (IDbCommand cmd = cnn.CreateCommand())
502 {
503 cmd.CommandText = "SELECT Field6 FROM TestCase WHERE Field6 IS NOT NULL";
504 byte[] b = new byte[4000];
505  
506 using (IDataReader rd = cmd.ExecuteReader())
507 {
508 if (rd.Read() == false) throw new ArgumentOutOfRangeException();
509  
510 rd.GetBytes(0, 0, b, 0, 4000);
511  
512 if (b[0] != 1) throw new ArgumentException();
513 if (b[100] != 2) throw new ArgumentException();
514 if (b[1000] != 3) throw new ArgumentException();
515 if (b[2000] != 4) throw new ArgumentException();
516 if (b[3000] != 5) throw new ArgumentException();
517  
518 using (IDbConnection clone = (IDbConnection)((ICloneable)cnn).Clone())
519 {
520 using (IDbCommand newcmd = clone.CreateCommand())
521 {
522 newcmd.CommandText = "DELETE FROM TestCase WHERE Field6 IS NULL";
523 newcmd.CommandTimeout = 2;
524 int cmdStart = Environment.TickCount;
525 int cmdEnd;
526  
527 try
528 {
529 newcmd.ExecuteNonQuery(); // should fail because there's a reader on the database
530 throw new ArgumentException(); // If we got here, the test failed
531 }
532 catch
533 {
534 cmdEnd = Environment.TickCount;
535 //TODO: commandtimeout and retry on lock.
536 //if (cmdEnd - cmdStart < 2000 || cmdEnd - cmdStart > 3000)
537 //throw new ArgumentException(); // Didn't wait the right amount of time
538 }
539 }
540 }
541 }
542 }
543 }
544  
545 internal void ParameterizedInsertMissingParams(IDbConnection cnn)
546 {
547 using (IDbCommand cmd = cnn.CreateCommand())
548 {
549 cmd.CommandText = "INSERT INTO TestCase(Field1, Field2, Field3, Field4, Field5) VALUES(?,?,?,?,?)";
550 IDbDataParameter Field1 = cmd.CreateParameter();
551 IDbDataParameter Field2 = cmd.CreateParameter();
552 IDbDataParameter Field3 = cmd.CreateParameter();
553 IDbDataParameter Field4 = cmd.CreateParameter();
554 IDbDataParameter Field5 = cmd.CreateParameter();
555  
556 Field1.DbType = System.Data.DbType.Int32;
557  
558 Field1.Value = 2;
559 Field2.Value = 3.14159;
560 Field3.Value = "Field3 Param";
561 Field4.Value = "Field4 Par";
562 Field5.Value = DateTime.Now;
563  
564 cmd.Parameters.Add(Field1);
565 cmd.Parameters.Add(Field2);
566 cmd.Parameters.Add(Field3);
567 cmd.Parameters.Add(Field4);
568  
569 // Assertion here, not enough parameters
570 cmd.ExecuteNonQuery();
571 }
572 }
573  
574 // Utilizes the SQLiteCommandBuilder, which in turn utilizes SQLiteDataReader's GetSchemaTable() functionality
575 //internal void InsertMany(IDbConnection cnn, bool bWithIdentity)
576 //{
577 // int nmax = 1000;
578  
579 // using (IDbTransaction dbTrans = cnn.BeginTransaction())
580 // {
581 // using (DbDataAdapter adp = new SQLiteDataAdapter())
582 // {
583 // using (IDbCommand cmd = cnn.CreateCommand())
584 // {
585 // cmd.Transaction = dbTrans;
586 // cmd.CommandText = "SELECT * FROM TestCase WHERE 1=2";
587 // adp.SelectCommand = cmd;
588  
589 // using (DbCommandBuilder bld = new SQLiteCommandBuilder())
590 // {
591 // bld.DataAdapter = adp;
592 // using (adp.InsertCommand = (SQLiteCommand)((ICloneable)bld.GetInsertCommand()).Clone())
593 // {
594 // bld.DataAdapter = null;
595 // if (bWithIdentity)
596 // {
597 // adp.InsertCommand.CommandText += ";SELECT last_insert_rowid() AS [ID]";
598 // adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
599 // }
600  
601 // using (DataTable tbl = new DataTable())
602 // {
603 // adp.Fill(tbl);
604 // for (int n = 0; n < nmax; n++)
605 // {
606 // DataRow row = tbl.NewRow();
607 // row[1] = n + nmax;
608 // tbl.Rows.Add(row);
609 // }
610  
611 // frm.Write(String.Format(" InsertMany{0} ({1} rows) Begins ... ", (bWithIdentity == true) ? "WithIdentityFetch" : " ", nmax));
612 // int dtStart = Environment.TickCount;
613 // adp.Update(tbl);
614 // int dtEnd = Environment.TickCount;
615 // dtEnd -= dtStart;
616 // frm.Write(String.Format("Ends in {0} ms ... ", (dtEnd)));
617  
618 // dtStart = Environment.TickCount;
619 // dbTrans.Commit();
620 // dtEnd = Environment.TickCount;
621 // dtEnd -= dtStart;
622 // frm.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
623 // }
624 // }
625 // }
626 // }
627 // }
628 // }
629 //}
630  
631 internal void FastInsertMany(IDbConnection cnn)
632 {
633 using (IDbTransaction dbTrans = cnn.BeginTransaction())
634 {
635 int dtStart;
636 int dtEnd;
637  
638 using (IDbCommand cmd = cnn.CreateCommand())
639 {
640 cmd.CommandText = "INSERT INTO TestCase(Field1) VALUES(?)";
641 IDbDataParameter Field1 = cmd.CreateParameter();
642  
643 cmd.Parameters.Add(Field1);
644  
645 frm.WriteLine("Fast insert using parameters and prepared ");
646 frm.WriteLine("statement-> (10,000 rows) Begins ... ");
647 dtStart = Environment.TickCount;
648 for (int n = 0; n < 10000; n++)
649 {
650 Field1.Value = n + 100000;
651 cmd.ExecuteNonQuery();
652 }
653  
654 dtEnd = Environment.TickCount;
655 dtEnd -= dtStart;
656 frm.WriteLine(" -> Ends in "+dtEnd+" ms ... ");
657 }
658  
659 dtStart = Environment.TickCount;
660 dbTrans.Rollback();
661 dtEnd = Environment.TickCount;
662 dtEnd -= dtStart;
663 frm.WriteLine("Rolled back in " + dtEnd + " ms");
664 }
665 }
666  
667 // Causes the user-defined function to be called
668 internal void UserFunction(IDbConnection cnn)
669 {
670 using (IDbCommand cmd = cnn.CreateCommand())
671 {
672 int nTimes;
673 int dtStart;
674  
675 nTimes = 0;
676 cmd.CommandText = "SELECT Foo('ee','foo')";
677 dtStart = Environment.TickCount;
678 while (Environment.TickCount - dtStart < 1000)
679 {
680 cmd.ExecuteNonQuery();
681 nTimes++;
682 }
683 frm.WriteLine(String.Format(" User (text) command executed {0} times in 1 second.", nTimes));
684  
685 nTimes = 0;
686 cmd.CommandText = "SELECT Foo(10,11)";
687 dtStart = Environment.TickCount;
688 while (Environment.TickCount - dtStart < 1000)
689 {
690 cmd.ExecuteNonQuery();
691 nTimes++;
692 }
693 frm.WriteLine(String.Format(" UserFunction command executed {0} times in 1 second.", nTimes));
694  
695 nTimes = 0;
696 cmd.CommandText = "SELECT ABS(1)";
697 dtStart = Environment.TickCount;
698 while (Environment.TickCount - dtStart < 1000)
699 {
700 cmd.ExecuteNonQuery();
701 nTimes++;
702 }
703 frm.WriteLine(String.Format(" Intrinsic command executed {0} times in 1 second.", nTimes));
704  
705 nTimes = 0;
706 cmd.CommandText = "SELECT lower('FOO')";
707 dtStart = Environment.TickCount;
708 while (Environment.TickCount - dtStart < 1000)
709 {
710 cmd.ExecuteNonQuery();
711 nTimes++;
712 }
713 frm.WriteLine(String.Format(" Intrin (txt) command executed {0} times in 1 second.", nTimes));
714  
715 nTimes = 0;
716 cmd.CommandText = "SELECT 1";
717 dtStart = Environment.TickCount;
718 while (Environment.TickCount - dtStart < 1000)
719 {
720 cmd.ExecuteNonQuery();
721 nTimes++;
722 }
723 frm.WriteLine(String.Format(" Raw Value command executed {0} times in 1 second.", nTimes));
724 }
725 }
726  
727 internal void IterationTest(IDbConnection cnn)
728 {
729 using (IDbCommand cmd = cnn.CreateCommand())
730 {
731 int dtStart;
732 int dtEnd;
733 int nCount;
734 long n;
735  
736 cmd.CommandText = "SELECT Foo(ID, ID) FROM TestCase";
737 cmd.Prepare();
738 dtStart = Environment.TickCount;
739 nCount = 0;
740 using (IDataReader rd = cmd.ExecuteReader())
741 {
742 while (rd.Read())
743 {
744 n = rd.GetInt64(0);
745 nCount++;
746 }
747 dtEnd = Environment.TickCount;
748 }
749 frm.WriteLine(String.Format(" User Function iteration of {0} records in {1} ms", nCount, (dtEnd - dtStart)));
750  
751 cmd.CommandText = "SELECT ID FROM TestCase";
752 cmd.Prepare();
753 dtStart = Environment.TickCount;
754 nCount = 0;
755 using (IDataReader rd = cmd.ExecuteReader())
756 {
757 while (rd.Read())
758 {
759 n = rd.GetInt64(0);
760 nCount++;
761 }
762 dtEnd = Environment.TickCount;
763 }
764 frm.WriteLine(String.Format(" Raw iteration of {0} records in {1} ms", nCount, (dtEnd - dtStart)));
765  
766 cmd.CommandText = "SELECT ABS(ID) FROM TestCase";
767 cmd.Prepare();
768 dtStart = Environment.TickCount;
769 nCount = 0;
770 using (IDataReader rd = cmd.ExecuteReader())
771 {
772 while (rd.Read())
773 {
774 n = rd.GetInt64(0);
775 nCount++;
776 }
777 dtEnd = Environment.TickCount;
778 }
779 frm.WriteLine(String.Format(" Intrinsic Function iteration of {0} records in {1} ms", nCount, (dtEnd - dtStart)));
780  
781 }
782 }
783  
784 // Causes the user-defined aggregate to be iterated through
785 internal void UserAggregate(IDbConnection cnn)
786 {
787 using (IDbCommand cmd = cnn.CreateCommand())
788 {
789 int dtStart;
790 int n = 0;
791 int nCount;
792  
793 cmd.CommandText = "SELECT MyCount(*) FROM TestCase";
794  
795 nCount = 0;
796 dtStart = Environment.TickCount;
797 while (Environment.TickCount - dtStart < 1000)
798 {
799 n = Convert.ToInt32(cmd.ExecuteScalar());
800 nCount++;
801 }
802 if (n != 2003) throw new ArgumentOutOfRangeException("Unexpected count");
803 frm.WriteLine(String.Format(" UserAggregate executed {0} times in 1 second.", nCount));
804 }
805 }
806  
807 // Causes the user-defined collation sequence to be iterated through
808 internal void UserCollation(IDbConnection cnn)
809 {
810 using (IDbCommand cmd = cnn.CreateCommand())
811 {
812 // Using a default collating sequence in descending order, "Param Field3" will appear at the top
813 // and "Field3" will be next, followed by a NULL. Our user-defined collating sequence will
814 // deliberately place them out of order so Field3 is first.
815 cmd.CommandText = "SELECT Field3 FROM TestCase ORDER BY Field3 COLLATE MYSEQUENCE DESC";
816 string s = (string)cmd.ExecuteScalar();
817 if (s != "Field3") throw new ArgumentOutOfRangeException("MySequence didn't sort properly");
818 }
819 }
820 }
821 }