System.Data.SQLite
Check-in [bb2c450c63]
Not logged in

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 | SQL archive
Timelines: family | ancestors | descendants | both | sourceforge
Files: files | file ages | folders
SHA1: bb2c450c632f7f92ae8e0e5531dc4d24c9863a78
User & Date: rmsimpson 2006-01-12 20:55:03
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
Hide Diffs Unified Diffs Show Whitespace Changes 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
...
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
...
422
423
424
425
426
427
428

429
430
431
432
433
434
435
...
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
          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

              }
            }
          }
        }
      }
................................................................................
      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;
................................................................................
                Console.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
              }
            }
          }
        }
      }
    }


    internal static void FastInsertMany(DbConnection cnn)
    {
      using (DbTransaction dbTrans = cnn.BeginTransaction())
      {
        int dtStart;
        int dtEnd;
................................................................................

          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)));
        }







|
|











|







 







|


<
<
<
<
<



>
|
|
|
<
|
|
|
>







|







 







>







 







|







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
...
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
...
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
...
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
          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

              }
            }
          }
        }
      }
................................................................................
      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;
................................................................................
                  Console.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
                }
              }
            }
          }
        }
      }
    }

    internal static void FastInsertMany(DbConnection cnn)
    {
      using (DbTransaction dbTrans = cnn.BeginTransaction())
      {
        int dtStart;
        int dtEnd;
................................................................................

          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
...
380
381
382
383
384
385
386
387
388

389
390
391
392
393
394
395
396
397
398
...
416
417
418
419
420
421
422

423
424
425
426
427
428
429
          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
              }
            }
          }
        }
      }
    }
................................................................................
            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++)
................................................................................
                frm.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
              }
            }
          }
        }
      }
    }


    internal void FastInsertMany(DbConnection cnn)
    {
      using (DbTransaction dbTrans = cnn.BeginTransaction())
      {
        int dtStart;
        int dtEnd;







|
|











|







 







|
|
>
|
|
|







 







>







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
...
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
...
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
          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
              }
            }
          }
        }
      }
    }
................................................................................
            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++)
................................................................................
                  frm.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
                }
              }
            }
          }
        }
      }
    }

    internal void FastInsertMany(DbConnection cnn)
    {
      using (DbTransaction dbTrans = cnn.BeginTransaction())
      {
        int dtStart;
        int dtEnd;