public void AddParameterTest()
{
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
AddParameters(target);
string pd = target.ParamDelimiter;
string expectedStmnt = "INSERT INTO [{0}] ([somestring], [somereal], [someint], [somedt]) VALUES ({1}somestring, {2}somereal, {3}someint, {4}somedt)";
expectedStmnt = string.Format(expectedStmnt, m_testTableName, pd, pd, pd, pd);
Assert.AreEqual(expectedStmnt, target.CommandText);
}
public void TableNameTest()
{
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
Assert.AreEqual(m_testTableName, target.TableName);
}
private void AddParameters(SQLiteBulkInsert target)
{
target.AddParameter("somestring", DbType.String);
target.AddParameter("somereal", DbType.String);
target.AddParameter("someint", DbType.Int32);
target.AddParameter("somedt", DbType.DateTime);
}
public void SQLiteBulkInsertConstructorTest()
{
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
Assert.AreEqual(m_testTableName, target.TableName);
bool wasException = false;
try {
string a = target.CommandText;
}
catch (SQLiteException ex) { wasException = true; }
Assert.IsTrue(wasException);
}
public void InsertTest()
{
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
bool didThrow = false;
try {
target.Insert("hello"); //object.length must equal the number of parameters added
}
catch (Exception ex) { didThrow = true; }
Assert.IsTrue(didThrow);
AddParameters(target);
target.CommitMax = 4;
DateTime dt1 = DateTime.Now; DateTime dt2 = DateTime.Now; DateTime dt3 = DateTime.Now; DateTime dt4 = DateTime.Now;
target.Insert("john", 3.45f, 10, dt1);
target.Insert("paul", -0.34f, 100, dt2 );
target.Insert("ringo", 1000.98f, 1000, dt3 );
target.Insert("george", 5.0f, 10000, dt4 );
long count = CountRecords();
Assert.AreEqual(4, count);
SQLiteDataReader reader = SelectAllRecords();
Assert.IsTrue(reader.Read());
Assert.AreEqual("john", reader.GetString(1)); Assert.AreEqual(3.45f, reader.GetFloat(2));
Assert.AreEqual(10, reader.GetInt32(3)); Assert.AreEqual(dt1, reader.GetDateTime(4));
Assert.IsTrue(reader.Read());
Assert.AreEqual("paul", reader.GetString(1)); Assert.AreEqual(-0.34f, reader.GetFloat(2));
Assert.AreEqual(100, reader.GetInt32(3)); Assert.AreEqual(dt2, reader.GetDateTime(4));
Assert.IsTrue(reader.Read());
Assert.AreEqual("ringo", reader.GetString(1)); Assert.AreEqual(1000.98f, reader.GetFloat(2));
Assert.AreEqual(1000, reader.GetInt32(3)); Assert.AreEqual(dt3, reader.GetDateTime(4));
Assert.IsTrue(reader.Read());
Assert.AreEqual("george", reader.GetString(1)); Assert.AreEqual(5.0f, reader.GetFloat(2));
Assert.AreEqual(10000, reader.GetInt32(3)); Assert.AreEqual(dt4, reader.GetDateTime(4));
Assert.IsFalse(reader.Read());
DeleteRecords();
count = CountRecords();
Assert.AreEqual(0, count);
}
public void FlushTest()
{
string[] names = new string[] { "metalica", "beatles", "coldplay", "tiesto", "t-pain", "blink 182", "plain white ts", "staind", "pink floyd" };
Random rand = new Random(Environment.TickCount);
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
AddParameters(target);
target.CommitMax = 1000;
//Insert less records than commitmax
for (int x = 0; x < 50; x++)
target.Insert(names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(50), DateTime.Now);
//Close connect to verify records were not inserted
m_dbCon.Close();
m_dbCon = new SQLiteConnection(m_connectionString);
m_dbCon.Open();
long count = CountRecords();
Assert.AreEqual(0, count);
//Now actually verify flush worked
target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
AddParameters(target);
target.CommitMax = 1000;
//Insert less records than commitmax
for (int x = 0; x < 50; x++)
target.Insert(names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(50), DateTime.Now);
target.Flush();
count = CountRecords();
Assert.AreEqual(50, count);
//Close connect to verify flush worked
m_dbCon.Close();
m_dbCon = new SQLiteConnection(m_connectionString);
m_dbCon.Open();
count = CountRecords();
Assert.AreEqual(50, count);
DeleteRecords();
count = CountRecords();
Assert.AreEqual(0, count);
}
public void CommitMaxTest()
{
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
target.CommitMax = 4;
Assert.AreEqual(4, target.CommitMax);
target.CommitMax = 1000;
Assert.AreEqual(1000, target.CommitMax);
}
public void AllowBulkInsertTest()
{
string[] names = new string[] { "metalica", "beatles", "coldplay", "tiesto", "t-pain", "blink 182", "plain white ts", "staind", "pink floyd"};
Random rand = new Random(Environment.TickCount);
SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName);
AddParameters(target);
const int COUNT = 100;
target.CommitMax = COUNT;
DateTime start1 = DateTime.Now;
for (int x = 0; x < COUNT; x++)
target.Insert(names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(COUNT), DateTime.Now);
DateTime end1 = DateTime.Now;
TimeSpan delta1 = end1 - start1;
DeleteRecords();
target.AllowBulkInsert = false;
DateTime start2 = DateTime.Now;
for (int x = 0; x < COUNT; x++)
target.Insert(names[rand.Next(names.Length)], (float)rand.NextDouble(), rand.Next(COUNT), DateTime.Now);
DateTime end2 = DateTime.Now;
TimeSpan delta2 = end2 - start2;
//THIS MAY FAIL DEPENDING UPON THE MACHINE THE TEST IS RUNNING ON.
Assert.IsTrue(delta1.TotalSeconds < 0.1); //approx true for 100 recs Assert.IsTrue(delta2.TotalSeconds > 1.0); //approx true for 100 recs;
//UNCOMMENT THIS TO MAKE IT FAIL AND SEE ACTUAL NUMBERS IN FAILED REPORT
//Assert.AreEqual(delta1.TotalSeconds, delta2.TotalSeconds);
DeleteRecords();
}
