wasCSharpSQLite – Blame information for rev
?pathlinks?
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 | } |