System.Data.SQLite

Check-in [bb2c450c63]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Optimize and fix the DataAdapter tests, shorten the locktest duration
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | sourceforge
Files: files | file ages | folders
SHA1: bb2c450c632f7f92ae8e0e5531dc4d24c9863a78
User & Date: rmsimpson 2006-01-12 20:55:03.000
Context
2006-01-13
03:35
no message check-in: 83d82c5bea user: rmsimpson tags: sourceforge
2006-01-12
20:55
Optimize and fix the DataAdapter tests, shorten the locktest duration check-in: bb2c450c63 user: rmsimpson tags: sourceforge
20:54
Latest Jan 12 CVS HEAD check-in: a7f98646af user: rmsimpson tags: sourceforge
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/TestCases.cs.
︙︙
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337

          if (b[2000] != 4) throw new ArgumentException();
          if (b[3000] != 5) throw new ArgumentException();

          using (DbConnection clone = (DbConnection)((ICloneable)cnn).Clone())
          {
            using (DbCommand newcmd = clone.CreateCommand())
            {
              newcmd.CommandText = "CREATE TABLE Bar(ID INTEGER PRIMARY KEY)";
              newcmd.CommandTimeout = 10;
              int cmdStart = Environment.TickCount;
              int cmdEnd;

              try
              {
                newcmd.ExecuteNonQuery(); // should fail because there's a reader on the database
                throw new ArgumentException(); // If we got here, the test failed
              }
              catch
              {
                cmdEnd = Environment.TickCount;
                if (cmdEnd - cmdStart < 10000 || cmdEnd - cmdStart > 11000)
                  throw new ArgumentException(); // Didn't wait the right amount of time

              }
            }
          }
        }
      }








|
|











|








310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337

          if (b[2000] != 4) throw new ArgumentException();
          if (b[3000] != 5) throw new ArgumentException();

          using (DbConnection clone = (DbConnection)((ICloneable)cnn).Clone())
          {
            using (DbCommand newcmd = clone.CreateCommand())
            {
              newcmd.CommandText = "DELETE FROM TestCase WHERE Field6 IS NULL";
              newcmd.CommandTimeout = 2;
              int cmdStart = Environment.TickCount;
              int cmdEnd;

              try
              {
                newcmd.ExecuteNonQuery(); // should fail because there's a reader on the database
                throw new ArgumentException(); // If we got here, the test failed
              }
              catch
              {
                cmdEnd = Environment.TickCount;
                if (cmdEnd - cmdStart < 2000 || cmdEnd - cmdStart > 3000)
                  throw new ArgumentException(); // Didn't wait the right amount of time

              }
            }
          }
        }
      }
︙︙
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392

393
394
395
396
397
398
399

400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422

423
424
425
426
427
428
429

      using (DbTransaction dbTrans = cnn.BeginTransaction())
      {
        using (DbDataAdapter adp = fact.CreateDataAdapter())
        {
          using (DbCommand cmd = cnn.CreateCommand())
          {
            cmd.Transaction = dbTrans;
            cmd.CommandText = "SELECT * FROM TestCase";
            adp.SelectCommand = cmd;

            // We're deliberately not loading the data adapter with an insert command
            // unless we're doing the identity fetch.  This tests the CommandBuilder's ability
            // to autogenerate that command when the insert occurs, which consequently tests
            // the SQLiteDataAdapter's ability to raise events.

            using (DbCommandBuilder bld = fact.CreateCommandBuilder())
            {
              bld.DataAdapter = adp;


              if (bWithIdentity)
              {
                adp.InsertCommand = bld.GetInsertCommand();
                adp.InsertCommand.CommandText += ";SELECT [ID] FROM TestCase WHERE RowID = last_insert_rowid()";
                adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
              }


              using (DataTable tbl = new DataTable())
              {
                adp.Fill(tbl);
                for (int n = 0; n < 10000; n++)
                {
                  DataRow row = tbl.NewRow();
                  row[1] = n + 10000;
                  tbl.Rows.Add(row);
                }

                Console.WriteLine(String.Format("          Inserting using CommandBuilder and DataAdapter\r\n          ->{0} (10,000 rows) ...", (bWithIdentity == true) ? "(with identity fetch)" : ""));
                int dtStart = Environment.TickCount;
                adp.Update(tbl);
                int dtEnd = Environment.TickCount;
                dtEnd -= dtStart;
                Console.Write(String.Format("          -> Insert Ends in {0} ms ... ", (dtEnd)));

                dtStart = Environment.TickCount;
                dbTrans.Commit();
                dtEnd = Environment.TickCount;
                dtEnd -= dtStart;
                Console.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));

              }
            }
          }
        }
      }
    }









|


<
<
<
<
<



>
|
|
|
<
|
|
|
>

|
|
|
|
|
|
|
|
|

|
|
|
|
|
|

|
|
|
|
|
>








375
376
377
378
379
380
381
382
383
384





385
386
387
388
389
390
391

392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426

      using (DbTransaction dbTrans = cnn.BeginTransaction())
      {
        using (DbDataAdapter adp = fact.CreateDataAdapter())
        {
          using (DbCommand cmd = cnn.CreateCommand())
          {
            cmd.Transaction = dbTrans;
            cmd.CommandText = "SELECT * FROM TestCase WHERE 1 = 2";
            adp.SelectCommand = cmd;






            using (DbCommandBuilder bld = fact.CreateCommandBuilder())
            {
              bld.DataAdapter = adp;
              using (adp.InsertCommand = (DbCommand)((ICloneable)bld.GetInsertCommand()).Clone())
              {
                if (bWithIdentity)
                {

                  adp.InsertCommand.CommandText += ";SELECT last_insert_rowid() AS [ID]";
                  adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
                }
                bld.DataAdapter = null;

                using (DataTable tbl = new DataTable())
                {
                  adp.Fill(tbl);
                  for (int n = 0; n < 10000; n++)
                  {
                    DataRow row = tbl.NewRow();
                    row[1] = n + (50000 * ((bWithIdentity == true) ? 2 : 1));
                    tbl.Rows.Add(row);
                  }

                  Console.WriteLine(String.Format("          Inserting using CommandBuilder and DataAdapter\r\n          ->{0} (10,000 rows) ...", (bWithIdentity == true) ? "(with identity fetch)" : ""));
                  int dtStart = Environment.TickCount;
                  adp.Update(tbl);
                  int dtEnd = Environment.TickCount;
                  dtEnd -= dtStart;
                  Console.Write(String.Format("          -> Insert Ends in {0} ms ... ", (dtEnd)));

                  dtStart = Environment.TickCount;
                  dbTrans.Commit();
                  dtEnd = Environment.TickCount;
                  dtEnd -= dtStart;
                  Console.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
                }
              }
            }
          }
        }
      }
    }
︙︙
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455


          cmd.Parameters.Add(Field1);

          Console.WriteLine(String.Format("          Fast insert using parameters and prepared statement\r\n          -> (100,000 rows) Begins ... "));
          dtStart = Environment.TickCount;
          for (int n = 0; n < 100000; n++)
          {
            Field1.Value = n + 100000;
            cmd.ExecuteNonQuery();
          }

          dtEnd = Environment.TickCount;
          dtEnd -= dtStart;
          Console.Write(String.Format("          -> Ends in {0} ms ... ", (dtEnd)));
        }








|








438
439
440
441
442
443
444
445
446
447
448
449
450
451
452


          cmd.Parameters.Add(Field1);

          Console.WriteLine(String.Format("          Fast insert using parameters and prepared statement\r\n          -> (100,000 rows) Begins ... "));
          dtStart = Environment.TickCount;
          for (int n = 0; n < 100000; n++)
          {
            Field1.Value = n + 200000;
            cmd.ExecuteNonQuery();
          }

          dtEnd = Environment.TickCount;
          dtEnd -= dtStart;
          Console.Write(String.Format("          -> Ends in {0} ms ... ", (dtEnd)));
        }
︙︙
Changes to testce/TestCases.cs.
︙︙
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338

          if (b[2000] != 4) throw new ArgumentException();
          if (b[3000] != 5) throw new ArgumentException();

          using (DbConnection clone = (DbConnection)((ICloneable)cnn).Clone())
          {
            using (DbCommand newcmd = clone.CreateCommand())
            {
              newcmd.CommandText = "CREATE TABLE Bar(ID INTEGER PRIMARY KEY)";
              newcmd.CommandTimeout = 10;
              int cmdStart = Environment.TickCount;
              int cmdEnd;

              try
              {
                newcmd.ExecuteNonQuery(); // should fail because there's a reader on the database
                throw new ArgumentException(); // If we got here, the test failed
              }
              catch
              {
                cmdEnd = Environment.TickCount;
                if (cmdEnd - cmdStart < 10000 || cmdEnd - cmdStart > 11000)
                  throw new ArgumentException(); // Didn't wait the right amount of time
              }
            }
          }
        }
      }
    }








|
|











|








311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338

          if (b[2000] != 4) throw new ArgumentException();
          if (b[3000] != 5) throw new ArgumentException();

          using (DbConnection clone = (DbConnection)((ICloneable)cnn).Clone())
          {
            using (DbCommand newcmd = clone.CreateCommand())
            {
              newcmd.CommandText = "DELETE FROM TestCase WHERE Field6 IS NULL";
              newcmd.CommandTimeout = 2;
              int cmdStart = Environment.TickCount;
              int cmdEnd;

              try
              {
                newcmd.ExecuteNonQuery(); // should fail because there's a reader on the database
                throw new ArgumentException(); // If we got here, the test failed
              }
              catch
              {
                cmdEnd = Environment.TickCount;
                if (cmdEnd - cmdStart < 2000 || cmdEnd - cmdStart > 3000)
                  throw new ArgumentException(); // Didn't wait the right amount of time
              }
            }
          }
        }
      }
    }
︙︙
380
381
382
383
384
385
386
387
388

389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416

417
418
419
420
421
422
423

            cmd.Transaction = dbTrans;
            cmd.CommandText = "SELECT * FROM TestCase WHERE 1=2";
            adp.SelectCommand = cmd;

            using (DbCommandBuilder bld = new SQLiteCommandBuilder())
            {
              bld.DataAdapter = adp;
              adp.InsertCommand = bld.GetInsertCommand();


              if (bWithIdentity)
              {
                adp.InsertCommand.CommandText += ";SELECT [ID] FROM TestCase WHERE RowID = last_insert_rowid()";
                adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
              }

              using (DataTable tbl = new DataTable())
              {
                adp.Fill(tbl);
                for (int n = 0; n < nmax; n++)
                {
                  DataRow row = tbl.NewRow();
                  row[1] = n + nmax;
                  tbl.Rows.Add(row);
                }

                frm.Write(String.Format("          InsertMany{0} ({1} rows) Begins ... ", (bWithIdentity == true) ? "WithIdentityFetch":"                 ", nmax));
                int dtStart = Environment.TickCount;
                adp.Update(tbl);
                int dtEnd = Environment.TickCount;
                dtEnd -= dtStart;
                frm.Write(String.Format("Ends in {0} ms ... ", (dtEnd)));

                dtStart = Environment.TickCount;
                dbTrans.Commit();
                dtEnd = Environment.TickCount;
                dtEnd -= dtStart;
                frm.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));

              }
            }
          }
        }
      }
    }









|
|
>
|
|
|
|
|

|
|
|
|
|
|
|
|
|

|
|
|
|
|
|

|
|
|
|
|
>








380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425

            cmd.Transaction = dbTrans;
            cmd.CommandText = "SELECT * FROM TestCase WHERE 1=2";
            adp.SelectCommand = cmd;

            using (DbCommandBuilder bld = new SQLiteCommandBuilder())
            {
              bld.DataAdapter = adp;
              using (adp.InsertCommand = (SQLiteCommand)((ICloneable)bld.GetInsertCommand()).Clone())
              {
                bld.DataAdapter = null;
                if (bWithIdentity)
                {
                  adp.InsertCommand.CommandText += ";SELECT last_insert_rowid() AS [ID]";
                  adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
                }

                using (DataTable tbl = new DataTable())
                {
                  adp.Fill(tbl);
                  for (int n = 0; n < nmax; n++)
                  {
                    DataRow row = tbl.NewRow();
                    row[1] = n + nmax;
                    tbl.Rows.Add(row);
                  }

                  frm.Write(String.Format("          InsertMany{0} ({1} rows) Begins ... ", (bWithIdentity == true) ? "WithIdentityFetch" : "                 ", nmax));
                  int dtStart = Environment.TickCount;
                  adp.Update(tbl);
                  int dtEnd = Environment.TickCount;
                  dtEnd -= dtStart;
                  frm.Write(String.Format("Ends in {0} ms ... ", (dtEnd)));

                  dtStart = Environment.TickCount;
                  dbTrans.Commit();
                  dtEnd = Environment.TickCount;
                  dtEnd -= dtStart;
                  frm.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
                }
              }
            }
          }
        }
      }
    }
︙︙