wasCSharpSQLite – Rev 1
?pathlinks?
using System;
using System.Data.Common;
using System.Data;
using Community.CsharpSqlite.SQLiteClient;
namespace test
{
/// <summary>
/// Scalar user-defined function. In this example, the same class is declared twice with
/// different function names to demonstrate how to use alias names for user-defined functions.
/// </summary>
//[SQLiteFunction(Name = "Foo", Arguments = 2, FuncType = FunctionType.Scalar)]
//[SQLiteFunction(Name = "TestFunc", Arguments = 2, FuncType = FunctionType.Scalar)]
class TestFunc// : SQLiteFunction
{
public object Invoke(object[] args)
{
if (args[0].GetType() != typeof(int)) return args[0];
int Param1 = Convert.ToInt32(args[0]); // First parameter
int Param2 = Convert.ToInt32(args[1]); // Second parameter
return Param1 + Param2;
}
}
/// <summary>
/// Aggregate user-defined function. Arguments = -1 means any number of arguments is acceptable
/// </summary>
// [SQLiteFunction(Name = "MyCount", Arguments = -1, FuncType = FunctionType.Aggregate)]
class MyCount //: SQLiteFunction
{
public void Step(object[] args, int nStep, ref object contextData)
{
if (contextData == null)
{
contextData = 1;
}
else
contextData = (int)contextData + 1;
}
public object Final(object contextData)
{
return contextData;
}
}
/// <summary>
/// User-defined collating sequence.
/// </summary>
//[SQLiteFunction(Name = "MYSEQUENCE", FuncType = FunctionType.Collation)]
class MySequence // : SQLiteFunction
{
public int Compare(string param1, string param2)
{
// Make sure the string "Field3" is sorted out of order
if (param1 == "Field3") return 1;
if (param2 == "Field3") return -1;
return String.Compare(param1, param2);
}
}
internal class TestCases
{
internal Test.WP.MainPage frm;
internal void Run(IDbConnection cnn, Test.WP.MainPage frm2)
{
frm = frm2;
// frm.Show();
frm.WriteLine("\r\nBeginning Test on " + cnn.GetType().ToString());
try { CreateTable(cnn); frm.WriteLine("SUCCESS - CreateTable"); }
catch (Exception) { frm.WriteLine("FAIL - CreateTable"); }
try { DataTypeTest(cnn); frm.WriteLine("SUCCESS - DataType Test"); }
catch (Exception) { frm.WriteLine("FAIL - DataType Test"); }
//not enabled yet
//try { FullTextTest(cnn); frm.WriteLine("SUCCESS - Full Text Search"); }
//catch (Exception) { frm.WriteLine("FAIL - Full Text Search"); }
//Not possible without datatable to reade the schema
//try { KeyInfoTest(cnn); frm.WriteLine("SUCCESS - KeyInfo Fetch"); }
//catch (Exception) { frm.WriteLine("FAIL - KeyInfo Fetch"); }
try { InsertTable(cnn); frm.WriteLine("SUCCESS - InsertTable"); }
catch (Exception) { frm.WriteLine("FAIL - InsertTable"); }
try { VerifyInsert(cnn); frm.WriteLine("SUCCESS - VerifyInsert"); }
catch (Exception) { frm.WriteLine("FAIL - VerifyInsert"); }
try { CoersionTest(cnn); frm.WriteLine("FAIL - CoersionTest"); }
catch (Exception) { frm.WriteLine("SUCCESS - CoersionTest"); }
try { ParameterizedInsert(cnn); frm.WriteLine("SUCCESS - ParameterizedInsert"); }
catch (Exception) { frm.WriteLine("FAIL - ParameterizedInsert"); }
try { BinaryInsert(cnn); frm.WriteLine("SUCCESS - BinaryInsert"); }
catch (Exception) { frm.WriteLine("FAIL - BinaryInsert"); }
try { VerifyBinaryData(cnn); frm.WriteLine("SUCCESS - VerifyBinaryData"); }
catch (Exception) { frm.WriteLine("FAIL - VerifyBinaryData"); }
try { LockTest(cnn); frm.WriteLine("SUCCESS - LockTest"); }
catch (Exception) { frm.WriteLine("FAIL - LockTest"); }
try { ParameterizedInsertMissingParams(cnn); frm.WriteLine("FAIL - ParameterizedInsertMissingParams"); }
catch (Exception) { frm.WriteLine("SUCCESS - ParameterizedInsertMissingParams"); }
//try { InsertMany(cnn, false); frm.WriteLine("SUCCESS - InsertMany"); }
//catch (Exception) { frm.WriteLine("FAIL - InsertMany"); }
//try { InsertMany(cnn, true); frm.WriteLine("SUCCESS - InsertManyWithIdentityFetch"); }
//catch (Exception) { frm.WriteLine("FAIL - InsertManyWithIdentityFetch"); }
try { FastInsertMany(cnn); frm.WriteLine("SUCCESS - FastInsertMany"); }
catch (Exception) { frm.WriteLine("FAIL - FastInsertMany"); }
//try { IterationTest(cnn); frm.WriteLine("SUCCESS - Iteration Test"); }
//catch (Exception) { frm.WriteLine("FAIL - Iteration Test"); }
//try { UserFunction(cnn); frm.WriteLine("SUCCESS - UserFunction"); }
//catch (Exception) { frm.WriteLine("FAIL - UserFunction"); }
//try { UserAggregate(cnn); frm.WriteLine("SUCCESS - UserAggregate"); }
//catch (Exception) { frm.WriteLine("FAIL - UserAggregate"); }
//try { UserCollation(cnn); frm.WriteLine("SUCCESS - UserCollation"); }
//catch (Exception) { frm.WriteLine("FAIL - UserCollation"); }
try { DropTable(cnn); frm.WriteLine("SUCCESS - DropTable"); }
catch (Exception) { frm.WriteLine("FAIL - DropTable"); }
frm.WriteLine("\r\nTests Finished.");
}
internal static void KeyInfoTest(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
// First test against integer primary key (optimized) keyinfo fetch
cmd.CommandText = "Create table keyinfotest (id integer primary key, myuniquevalue integer unique not null, myvalue varchar(50))";
cmd.ExecuteNonQuery();
cmd.CommandText = "Select * from keyinfotest";
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
if (reader.FieldCount != 3) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
}
cmd.CommandText = "SELECT MyValue FROM keyinfotest";
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
if (reader.FieldCount != 2) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
}
cmd.CommandText = "DROP TABLE keyinfotest";
cmd.ExecuteNonQuery();
// Now test against non-integer primary key (unoptimized) subquery keyinfo fetch
cmd.CommandText = "Create table keyinfotest (id char primary key, myuniquevalue integer unique not null, myvalue varchar(50))";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT MyValue FROM keyinfotest";
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
if (reader.FieldCount != 2) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
}
cmd.CommandText = "Select * from keyinfotest";
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
if (reader.FieldCount != 3) throw new ArgumentOutOfRangeException("Wrong number of columns returned");
}
//// Make sure commandbuilder can generate an update command with the correct parameter count
//using (DbDataAdapter adp = new SQLiteDataAdapter())
//using (DbCommandBuilder builder = new SQLiteCommandBuilder())
//{
// adp.SelectCommand = cmd;
// builder.DataAdapter = adp;
// builder.ConflictOption = ConflictOption.OverwriteChanges;
// using (IDbCommand updatecmd = builder.GetUpdateCommand())
// {
// if (updatecmd.Parameters.Count != 4)
// throw new ArgumentOutOfRangeException("Wrong number of parameters in update command!");
// }
//}
}
}
internal static void DataTypeTest(IDbConnection cnn)
{
DateTime now = DateTime.Now;
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "create table datatypetest(id integer primary key, myvalue, datetimevalue datetime, decimalvalue decimal)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into datatypetest(myvalue, datetimevalue, decimalvalue) values(?,?,?)";
IDbDataParameter p1 = cmd.CreateParameter();
IDbDataParameter p2 = cmd.CreateParameter();
IDbDataParameter p3 = cmd.CreateParameter();
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
p1.Value = 1;
p2.Value = DateTime.MinValue;
p3.Value = (Decimal)1.05;
cmd.ExecuteNonQuery();
//p1.ResetDbType();
//p2.ResetDbType();
//p3.ResetDbType();
p1.Value = "One";
p2.Value = "2001-01-01";
p3.Value = (float)1.123;
cmd.ExecuteNonQuery();
//p1.ResetDbType();
//p2.ResetDbType();
//p3.ResetDbType();
p1.Value = 1.01;
DateTime nw = now;
p2.Value = nw;
p3.Value = (Double )9.91;
cmd.ExecuteNonQuery();
cmd.CommandText = "select myvalue, datetimevalue, decimalvalue from datatypetest";
using (IDataReader reader = cmd.ExecuteReader())
{
for (int n = 0; n < 3; n++)
{
reader.Read();
if (reader.GetValue(1).GetType() != reader.GetDateTime(1).GetType()) throw new ArgumentOutOfRangeException();
if (reader.GetValue(2).GetType() != reader.GetDouble(2).GetType()) throw new ArgumentOutOfRangeException();
switch (n)
{
case 0:
if (reader.GetValue(0).GetType() != typeof(long)) throw new ArgumentOutOfRangeException();
if (reader.GetValue(0).Equals((long)1) == false) throw new ArgumentOutOfRangeException();
if (reader.GetValue(1).Equals(DateTime.MinValue) == false) throw new ArgumentOutOfRangeException();
if (reader.GetDecimal(2).Equals((Decimal)1.05) == false) throw new ArgumentOutOfRangeException();
if (reader.GetInt64(0) != (long)1) throw new ArgumentOutOfRangeException();
if (reader.GetValue(1).Equals(reader.GetDateTime(1)) == false) throw new ArgumentOutOfRangeException();
if (reader.GetValue(2).Equals(reader.GetDouble(2)) == false) throw new ArgumentOutOfRangeException();
break;
case 1:
if (reader.GetValue(0).GetType() != typeof(string)) throw new ArgumentOutOfRangeException();
if (reader.GetValue(0).Equals("One") == false) throw new ArgumentOutOfRangeException();
if (reader.GetValue(1).Equals(new DateTime(2001, 1, 1)) == false) throw new ArgumentOutOfRangeException();
if (reader.GetFloat(2).Equals((float)1.123) == false) throw new ArgumentOutOfRangeException();
if (reader.GetString(0) != "One") throw new ArgumentOutOfRangeException();
if (reader.GetValue(1).Equals(reader.GetDateTime(1)) == false) throw new ArgumentOutOfRangeException();
if (reader.GetValue(2).Equals(reader.GetDouble(2)) == false) throw new ArgumentOutOfRangeException();
break;
case 2:
if (reader.GetValue(0).GetType() != typeof(double)) throw new ArgumentOutOfRangeException();
if (reader.GetValue(0).Equals(1.01) == false) throw new ArgumentOutOfRangeException();
//Something weird comparing datetime values...
if (((DateTime)reader.GetValue(1)).ToString("s").Equals(nw.ToString("s")) == false) throw new ArgumentOutOfRangeException();
if (reader.GetDouble(2).Equals((Double)9.91) == false) throw new ArgumentOutOfRangeException();
if (reader.GetDouble(0) != 1.01) throw new ArgumentOutOfRangeException();
if (reader.GetValue(1).Equals(reader.GetDateTime(1)) == false) throw new ArgumentOutOfRangeException();
if (reader.GetValue(2).Equals(reader.GetDouble(2)) == false) throw new ArgumentOutOfRangeException();
break;
}
}
}
}
}
internal static void FullTextTest(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "CREATE VIRTUAL TABLE FullText USING FTS3(name, ingredients);";
cmd.ExecuteNonQuery();
string[] names = { "broccoli stew", "pumpkin stew", "broccoli pie", "pumpkin pie" };
string[] ingredients = { "broccoli peppers cheese tomatoes", "pumpkin onions garlic celery", "broccoli cheese onions flour", "pumpkin sugar flour butter" };
int n;
cmd.CommandText = "insert into FullText (name, ingredients) values (@name, @ingredient);";
IDbDataParameter name = cmd.CreateParameter();
IDbDataParameter ingredient = cmd.CreateParameter();
name.ParameterName = "@name";
ingredient.ParameterName = "@ingredient";
cmd.Parameters.Add(name);
cmd.Parameters.Add(ingredient);
for (n = 0; n < names.Length; n++)
{
name.Value = names[n];
ingredient.Value = ingredients[n];
cmd.ExecuteNonQuery();
}
cmd.CommandText = "select rowid, name, ingredients from FullText where name match 'pie';";
int[] rowids = { 3, 4 };
n = 0;
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (reader.GetInt64(0) != rowids[n++])
throw new ArgumentException("Unexpected rowid returned");
if (n > rowids.Length) throw new ArgumentException("Too many rows returned");
}
}
}
}
internal void CreateTable(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "CREATE TABLE TestCase (ID integer primary key autoincrement, Field1 Integer, Field2 Float, Field3 VARCHAR(50), Field4 CHAR(10), Field5 DateTime, Field6 Image)";
//cmd.CommandText = "CREATE TABLE TestCase (ID bigint primary key identity, Field1 Integer, Field2 Float, Field3 VARCHAR(50), Field4 CHAR(10), Field5 DateTime, Field6 Image)";
cmd.ExecuteNonQuery();
}
}
internal void DropTable(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "DROP TABLE TestCase";
cmd.ExecuteNonQuery();
}
}
internal void InsertTable(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "INSERT INTO TestCase(Field1, Field2, Field3, Field4, Field5) VALUES(1, 3.14159, 'Field3', 'Field4', '2005-01-01 13:49:00')";
cmd.ExecuteNonQuery();
}
}
internal void VerifyInsert(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT Field1, Field2, Field3, Field4, Field5 FROM TestCase";
cmd.Prepare();
using (IDataReader rd = cmd.ExecuteReader())
{
if (rd.Read())
{
long Field1 = rd.GetInt64(0);
double Field2 = rd.GetDouble(1);
string Field3 = rd.GetString(2);
string Field4 = rd.GetString(3).TrimEnd();
DateTime Field5 = rd.GetDateTime(4);
if (Field1 != 1) throw new ArgumentOutOfRangeException("Non-Match on Field1");
if (Field2 != 3.14159) throw new ArgumentOutOfRangeException("Non-Match on Field2");
if (Field3 != "Field3") throw new ArgumentOutOfRangeException("Non-Match on Field3");
if (Field4 != "Field4") throw new ArgumentOutOfRangeException("Non-Match on Field4");
if (Field5.CompareTo(DateTime.Parse("2005-01-01 13:49:00")) != 0) throw new ArgumentOutOfRangeException("Non-Match on Field5");
}
else throw new ArgumentOutOfRangeException("No data in table");
}
}
}
internal void CoersionTest(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT Field1, Field2, Field3, Field4, Field5, 'A', 1, 1 + 1, 3.14159 FROM TestCase";
using (IDataReader rd = cmd.ExecuteReader())
{
if (rd.Read())
{
object Field1 = rd.GetInt32(0);
object Field2 = rd.GetDouble(1);
object Field3 = rd.GetString(2);
object Field4 = rd.GetString(3).TrimEnd();
object Field5 = rd.GetDateTime(4);
// The next statement should cause an exception
Field1 = rd.GetString(0);
Field2 = rd.GetString(1);
Field3 = rd.GetString(2);
Field4 = rd.GetString(3);
Field5 = rd.GetString(4);
Field1 = rd.GetInt32(0);
Field2 = rd.GetInt32(1);
Field3 = rd.GetInt32(2);
Field4 = rd.GetInt32(3);
Field5 = rd.GetInt32(4);
Field1 = rd.GetDecimal(0);
Field2 = rd.GetDecimal(1);
Field3 = rd.GetDecimal(2);
Field4 = rd.GetDecimal(3);
Field5 = rd.GetDecimal(4);
}
else throw new ArgumentOutOfRangeException("No data in table");
}
}
}
internal void ParameterizedInsert(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "INSERT INTO TestCase(Field1, Field2, Field3, Field4, Field5) VALUES(?,?,?,?,?)";
IDbDataParameter Field1 = cmd.CreateParameter();
IDbDataParameter Field2 = cmd.CreateParameter();
IDbDataParameter Field3 = cmd.CreateParameter();
IDbDataParameter Field4 = cmd.CreateParameter();
IDbDataParameter Field5 = cmd.CreateParameter();
Field1.Value = 2;
Field2.Value = 3.14159;
Field3.Value = "Param Field3";
Field4.Value = "Field4 Par";
Field5.Value = DateTime.Now;
cmd.Parameters.Add(Field1);
cmd.Parameters.Add(Field2);
cmd.Parameters.Add(Field3);
cmd.Parameters.Add(Field4);
cmd.Parameters.Add(Field5);
cmd.ExecuteNonQuery();
}
}
internal void BinaryInsert(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "INSERT INTO TestCase(Field6) VALUES(?)";
IDbDataParameter Field6 = cmd.CreateParameter();
byte[] b = new byte[4000];
b[0] = 1;
b[100] = 2;
b[1000] = 3;
b[2000] = 4;
b[3000] = 5;
Field6.Value = b;
cmd.Parameters.Add(Field6);
cmd.ExecuteNonQuery();
}
}
internal void VerifyBinaryData(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT Field6 FROM TestCase WHERE Field6 IS NOT NULL";
byte[] b = new byte[4000];
using (IDataReader rd = cmd.ExecuteReader())
{
if (rd.Read() == false) throw new ArgumentOutOfRangeException();
rd.GetBytes(0, 0, b, 0, 4000);
if (b[0] != 1) throw new ArgumentException();
if (b[100] != 2) throw new ArgumentException();
if (b[1000] != 3) throw new ArgumentException();
if (b[2000] != 4) throw new ArgumentException();
if (b[3000] != 5) throw new ArgumentException();
}
}
}
internal static void LockTest(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "SELECT Field6 FROM TestCase WHERE Field6 IS NOT NULL";
byte[] b = new byte[4000];
using (IDataReader rd = cmd.ExecuteReader())
{
if (rd.Read() == false) throw new ArgumentOutOfRangeException();
rd.GetBytes(0, 0, b, 0, 4000);
if (b[0] != 1) throw new ArgumentException();
if (b[100] != 2) throw new ArgumentException();
if (b[1000] != 3) throw new ArgumentException();
if (b[2000] != 4) throw new ArgumentException();
if (b[3000] != 5) throw new ArgumentException();
using (IDbConnection clone = (IDbConnection)((ICloneable)cnn).Clone())
{
using (IDbCommand 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;
//TODO: commandtimeout and retry on lock.
//if (cmdEnd - cmdStart < 2000 || cmdEnd - cmdStart > 3000)
//throw new ArgumentException(); // Didn't wait the right amount of time
}
}
}
}
}
}
internal void ParameterizedInsertMissingParams(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "INSERT INTO TestCase(Field1, Field2, Field3, Field4, Field5) VALUES(?,?,?,?,?)";
IDbDataParameter Field1 = cmd.CreateParameter();
IDbDataParameter Field2 = cmd.CreateParameter();
IDbDataParameter Field3 = cmd.CreateParameter();
IDbDataParameter Field4 = cmd.CreateParameter();
IDbDataParameter Field5 = cmd.CreateParameter();
Field1.DbType = System.Data.DbType.Int32;
Field1.Value = 2;
Field2.Value = 3.14159;
Field3.Value = "Field3 Param";
Field4.Value = "Field4 Par";
Field5.Value = DateTime.Now;
cmd.Parameters.Add(Field1);
cmd.Parameters.Add(Field2);
cmd.Parameters.Add(Field3);
cmd.Parameters.Add(Field4);
// Assertion here, not enough parameters
cmd.ExecuteNonQuery();
}
}
// Utilizes the SQLiteCommandBuilder, which in turn utilizes SQLiteDataReader's GetSchemaTable() functionality
//internal void InsertMany(IDbConnection cnn, bool bWithIdentity)
//{
// int nmax = 1000;
// using (IDbTransaction dbTrans = cnn.BeginTransaction())
// {
// using (DbDataAdapter adp = new SQLiteDataAdapter())
// {
// using (IDbCommand cmd = cnn.CreateCommand())
// {
// 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)));
// }
// }
// }
// }
// }
// }
//}
internal void FastInsertMany(IDbConnection cnn)
{
using (IDbTransaction dbTrans = cnn.BeginTransaction())
{
int dtStart;
int dtEnd;
using (IDbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "INSERT INTO TestCase(Field1) VALUES(?)";
IDbDataParameter Field1 = cmd.CreateParameter();
cmd.Parameters.Add(Field1);
frm.WriteLine("Fast insert using parameters and prepared ");
frm.WriteLine("statement-> (10,000 rows) Begins ... ");
dtStart = Environment.TickCount;
for (int n = 0; n < 10000; n++)
{
Field1.Value = n + 100000;
cmd.ExecuteNonQuery();
}
dtEnd = Environment.TickCount;
dtEnd -= dtStart;
frm.WriteLine(" -> Ends in "+dtEnd+" ms ... ");
}
dtStart = Environment.TickCount;
dbTrans.Rollback();
dtEnd = Environment.TickCount;
dtEnd -= dtStart;
frm.WriteLine("Rolled back in " + dtEnd + " ms");
}
}
// Causes the user-defined function to be called
internal void UserFunction(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
int nTimes;
int dtStart;
nTimes = 0;
cmd.CommandText = "SELECT Foo('ee','foo')";
dtStart = Environment.TickCount;
while (Environment.TickCount - dtStart < 1000)
{
cmd.ExecuteNonQuery();
nTimes++;
}
frm.WriteLine(String.Format(" User (text) command executed {0} times in 1 second.", nTimes));
nTimes = 0;
cmd.CommandText = "SELECT Foo(10,11)";
dtStart = Environment.TickCount;
while (Environment.TickCount - dtStart < 1000)
{
cmd.ExecuteNonQuery();
nTimes++;
}
frm.WriteLine(String.Format(" UserFunction command executed {0} times in 1 second.", nTimes));
nTimes = 0;
cmd.CommandText = "SELECT ABS(1)";
dtStart = Environment.TickCount;
while (Environment.TickCount - dtStart < 1000)
{
cmd.ExecuteNonQuery();
nTimes++;
}
frm.WriteLine(String.Format(" Intrinsic command executed {0} times in 1 second.", nTimes));
nTimes = 0;
cmd.CommandText = "SELECT lower('FOO')";
dtStart = Environment.TickCount;
while (Environment.TickCount - dtStart < 1000)
{
cmd.ExecuteNonQuery();
nTimes++;
}
frm.WriteLine(String.Format(" Intrin (txt) command executed {0} times in 1 second.", nTimes));
nTimes = 0;
cmd.CommandText = "SELECT 1";
dtStart = Environment.TickCount;
while (Environment.TickCount - dtStart < 1000)
{
cmd.ExecuteNonQuery();
nTimes++;
}
frm.WriteLine(String.Format(" Raw Value command executed {0} times in 1 second.", nTimes));
}
}
internal void IterationTest(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
int dtStart;
int dtEnd;
int nCount;
long n;
cmd.CommandText = "SELECT Foo(ID, ID) FROM TestCase";
cmd.Prepare();
dtStart = Environment.TickCount;
nCount = 0;
using (IDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
n = rd.GetInt64(0);
nCount++;
}
dtEnd = Environment.TickCount;
}
frm.WriteLine(String.Format(" User Function iteration of {0} records in {1} ms", nCount, (dtEnd - dtStart)));
cmd.CommandText = "SELECT ID FROM TestCase";
cmd.Prepare();
dtStart = Environment.TickCount;
nCount = 0;
using (IDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
n = rd.GetInt64(0);
nCount++;
}
dtEnd = Environment.TickCount;
}
frm.WriteLine(String.Format(" Raw iteration of {0} records in {1} ms", nCount, (dtEnd - dtStart)));
cmd.CommandText = "SELECT ABS(ID) FROM TestCase";
cmd.Prepare();
dtStart = Environment.TickCount;
nCount = 0;
using (IDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
n = rd.GetInt64(0);
nCount++;
}
dtEnd = Environment.TickCount;
}
frm.WriteLine(String.Format(" Intrinsic Function iteration of {0} records in {1} ms", nCount, (dtEnd - dtStart)));
}
}
// Causes the user-defined aggregate to be iterated through
internal void UserAggregate(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
int dtStart;
int n = 0;
int nCount;
cmd.CommandText = "SELECT MyCount(*) FROM TestCase";
nCount = 0;
dtStart = Environment.TickCount;
while (Environment.TickCount - dtStart < 1000)
{
n = Convert.ToInt32(cmd.ExecuteScalar());
nCount++;
}
if (n != 2003) throw new ArgumentOutOfRangeException("Unexpected count");
frm.WriteLine(String.Format(" UserAggregate executed {0} times in 1 second.", nCount));
}
}
// Causes the user-defined collation sequence to be iterated through
internal void UserCollation(IDbConnection cnn)
{
using (IDbCommand cmd = cnn.CreateCommand())
{
// Using a default collating sequence in descending order, "Param Field3" will appear at the top
// and "Field3" will be next, followed by a NULL. Our user-defined collating sequence will
// deliberately place them out of order so Field3 is first.
cmd.CommandText = "SELECT Field3 FROM TestCase ORDER BY Field3 COLLATE MYSEQUENCE DESC";
string s = (string)cmd.ExecuteScalar();
if (s != "Field3") throw new ArgumentOutOfRangeException("MySequence didn't sort properly");
}
}
}
}