public void TestNoPrepareNamedParamIn()
{
using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
{
connection.Open();
new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery();
new NuoDbCommand("create procedure nunit_test(in p1 string, in p2 string) as throw p1; end_procedure", connection).ExecuteNonQuery();
NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection);
cmd.CommandType = CommandType.StoredProcedure;
NuoDbParameter param1 = new NuoDbParameter();
param1.ParameterName = "p2";
param1.Direction = ParameterDirection.Input;
param1.Value = "goodbye";
cmd.Parameters.Add(param1);
NuoDbParameter param2 = new NuoDbParameter();
param2.ParameterName = "p1";
param2.Direction = ParameterDirection.Input;
param2.Value = "hello";
cmd.Parameters.Add(param2);
try
{
cmd.ExecuteNonQuery();
Assert.Fail();
}
catch (Exception e)
{
Assert.AreEqual("hello", e.Message);
}
}
}
internal static void CreateGameTable()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
try
{
DbCommand dropCommand = new NuoDbCommand("drop table Game", connection);
dropCommand.ExecuteNonQuery();
}
catch (Exception)
{
// table is allowed to be missing
}
DbCommand createCommand = new NuoDbCommand("create table Game" +
"(" +
" Id bigint generated always as identity not NULL primary key," +
" Date DATE" +
")", connection);
createCommand.ExecuteNonQuery();
DbCommand insertCommand = new NuoDbCommand("Insert into Game (Date) Values (?)", connection);
insertCommand.Prepare();
insertCommand.Parameters[0].Value = "1970-01-01";
insertCommand.ExecuteNonQuery();
}
}
public void TestAsynchronousReader2()
{
NuoDbConnection connection = new NuoDbConnection(connectionString);
NuoDbCommand command = new NuoDbCommand("select * from hockey", connection);
connection.Open();
AsyncCallback callback = new AsyncCallback(HandleCallback);
IAsyncResult result = command.BeginExecuteReader(callback, command);
}
public static void Init()
{
Utils.CreateHockeyTable();
using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
{
DbCommand command = new NuoDbCommand("select count(*) from hockey", connection);
connection.Open();
tableRows = (int)command.ExecuteScalar();
}
}
public void TestMultipleReturnResultSets()
{
using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
{
connection.Open();
new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery();
try
{
new NuoDbCommand("create procedure nunit_test() " +
" returns table t1(field1 string, field2 integer), t2(column1 string, column2 string, column3 integer) " +
" as " +
" insert into t1 values ('rset 1, row1', 0), ('rset 1, row2',1); " +
" insert into t2 values ('rset 2, row1', 'first', 100), ('rset 2, row2','second', 101); " +
" end_procedure", connection).ExecuteNonQuery();
}
catch (NuoDbSqlException e)
{
if (e.Code.Code == -1)
{
// the server doesn't support multiple result sets as return value for procedures
return;
}
else
throw;
}
NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection);
cmd.CommandType = CommandType.StoredProcedure;
using (DbDataReader reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
Assert.AreEqual("rset 1, row1", reader["field1"]);
Assert.AreEqual(0, reader["field2"]);
Assert.IsTrue(reader.Read());
Assert.AreEqual("rset 1, row2", reader["field1"]);
Assert.AreEqual(1, reader["field2"]);
Assert.IsFalse(reader.Read());
Assert.IsTrue(reader.NextResult());
Assert.IsTrue(reader.Read());
Assert.AreEqual("rset 2, row1", reader["column1"]);
Assert.AreEqual("first", reader["column2"]);
Assert.AreEqual(100, reader["column3"]);
Assert.IsTrue(reader.Read());
Assert.AreEqual("rset 2, row2", reader["column1"]);
Assert.AreEqual("second", reader["column2"]);
Assert.AreEqual(101, reader["column3"]);
Assert.IsFalse(reader.Read());
Assert.IsFalse(reader.NextResult());
}
}
}
public void TestBulkLoadPerformance()
{
using (NuoDbConnection cnn = new NuoDbConnection(connectionString))
{
cnn.Open();
Utils.DropTable(cnn, "temp");
DbCommand createCommand = new NuoDbCommand("create table temp (col1 integer, col2 integer)", cnn);
int result = createCommand.ExecuteNonQuery();
DbCommand cmm = cnn.CreateCommand();
cmm.CommandText = "insert into temp(col1, col2) values(?, ?)";
cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col1" });
cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col2" });
cmm.Prepare();
const int ROW_NUMBER = 40000;
DateTime start = DateTime.Now;
for (var i = 1; i <= ROW_NUMBER; i++)
{
cmm.Parameters["col1"].Value = i;
cmm.Parameters["col2"].Value = 2 * i;
cmm.ExecuteNonQuery();
}
DateTime end = DateTime.Now;
double insertTime = (end - start).TotalMilliseconds;
Utils.DropTable(cnn, "temp2");
createCommand = new NuoDbCommand("create table temp2 (col1 integer, col2 integer)", cnn);
createCommand.ExecuteNonQuery();
NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString);
loader.DestinationTableName = "TEMP2";
DbCommand command = new NuoDbCommand("select * from temp", cnn);
DbDataReader reader = command.ExecuteReader();
loader.BatchProcessed += new BatchProcessedEventHandler(loader_BatchProcessed);
start = DateTime.Now;
loader.WriteToServer(reader);
end = DateTime.Now;
double loadTime = (end - start).TotalMilliseconds;
reader.Close();
Console.WriteLine("{0} insert = {1}\n{0} bulk load = {2}\n", ROW_NUMBER, insertTime, loadTime);
Assert.IsTrue(loadTime < insertTime, "BulkLoad takes more time than manual insertion");
}
}
public void TestNoPrepareAnonymousParamOut()
{
using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
{
connection.Open();
new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery();
new NuoDbCommand("create procedure nunit_test(out p1 string) as p1='hello'; end_procedure", connection).ExecuteNonQuery();
NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
Assert.AreEqual("hello", cmd.Parameters["p1"].Value);
}
}
public void TestAsynchronousScalar1()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
NuoDbCommand countCommand = (NuoDbCommand)connection.CreateCommand();
countCommand.CommandText = "select count(*) from hockey";
connection.Open();
IAsyncResult result = countCommand.BeginExecuteScalar();
int count = (int)countCommand.EndExecuteScalar(result);
}
}
protected bool NuoDbDatabaseExists(string connectionString)
{
try
{
//just try to connect
using (var conn = new NuoDbConnection(connectionString))
{
conn.Open();
}
return true;
}
catch
{
return false;
}
}
public void DB4329()
{
using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
{
connection.Open();
Utils.DropTable(connection, "ExpenseTest");
DbCommand createCommand = new NuoDbCommand("Create table ExpenseTest" +
"(" +
"SourceExpenseId int," +
"ExpenseAmount numeric(15,2)" +
")", connection);
createCommand.ExecuteNonQuery();
DbCommand insertCommand = new NuoDbCommand("Insert Into ExpenseTest(SourceExpenseId, ExpenseAmount) Values (?,?)", connection);
insertCommand.Prepare();
insertCommand.Parameters[0].Value = -1254524;
insertCommand.Parameters[1].Value = -135.35;
insertCommand.ExecuteNonQuery();
insertCommand.Parameters[0].Value = 100100100;
insertCommand.Parameters[1].Value = -1325465.35;
insertCommand.ExecuteNonQuery();
insertCommand.Parameters[0].Value = 100100101;
insertCommand.Parameters[1].Value = 200000.35;
insertCommand.ExecuteNonQuery();
DbCommand selectCommand = new NuoDbCommand("select SourceExpenseId, ExpenseAmount from ExpenseTest", connection);
using (DbDataReader reader = selectCommand.ExecuteReader())
{
bool hasNext=reader.Read();
Assert.IsTrue(hasNext);
Assert.AreEqual(-1254524, reader[0]);
Assert.AreEqual(-135.35, reader[1]);
hasNext = reader.Read();
Assert.IsTrue(hasNext);
Assert.AreEqual(100100100, reader[0]);
Assert.AreEqual(-1325465.35, reader[1]);
hasNext = reader.Read();
Assert.IsTrue(hasNext);
Assert.AreEqual(100100101, reader[0]);
Assert.AreEqual(200000.35, reader[1]);
}
}
}
public void TestAsynchronousReader1()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
NuoDbCommand command = new NuoDbCommand("select * from hockey", connection);
connection.Open();
IAsyncResult result = command.BeginExecuteReader();
using (DbDataReader reader = command.EndExecuteReader(result))
{
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}\t{2}\t{3}", reader[0], reader[1], reader[2], reader["id"]);
}
}
}
}
public void TestNoPrepareAnonymousParamIn()
{
using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
{
connection.Open();
new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery();
new NuoDbCommand("create procedure nunit_test(in p1 string) as throw p1; end_procedure", connection).ExecuteNonQuery();
NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("hello");
try
{
cmd.ExecuteNonQuery();
Assert.Fail();
}
catch (Exception e)
{
Assert.AreEqual("hello", e.Message);
}
}
}
public void TestBulkLoadOnStoredProcedureCommand()
{
using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
{
connection.Open();
new NuoDbCommand("drop table temp if exists", connection).ExecuteNonQuery();
new NuoDbCommand("create table temp (col string)", connection).ExecuteNonQuery();
new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery();
new NuoDbCommand("create procedure nunit_test(input_data string) " +
" as " +
" insert into temp values (input_data); " +
" end_procedure", connection).ExecuteNonQuery();
DataTable metadata = new DataTable("dummy");
metadata.Columns.Add("xyz", typeof(string));
DataRow[] rows = new DataRow[10];
for (int i = 0; i < rows.Length; i++)
{
rows[i] = metadata.NewRow();
rows[i][0] = Convert.ToString(i);
}
NuoDbCommand loader = new NuoDbCommand(connection);
loader.CommandType = CommandType.StoredProcedure;
loader.CommandText = "nunit_test";
loader.ExecuteBatch(rows);
DbCommand command = new NuoDbCommand("select count(*) from temp", connection);
object val = command.ExecuteScalar();
Assert.AreEqual(10, val);
command = new NuoDbCommand("select col from temp", connection);
val = command.ExecuteScalar();
Assert.AreEqual("0", val);
}
}
public void TestPreparedInsertWithGeneratedKeys2()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
DbCommand maxIdCmd = connection.CreateCommand();
maxIdCmd.CommandText = "select max(id) from hockey";
int maxId = (int)maxIdCmd.ExecuteScalar();
DbCommand updateCommand = connection.CreateCommand();
updateCommand.CommandText = "insert into hockey (number, name) values (?, ?)";
updateCommand.Prepare();
updateCommand.Parameters[0].Value = 99;
updateCommand.Parameters[1].Value = "xxx";
DbDataReader reader = updateCommand.ExecuteReader();
Assert.IsNotNull(reader, "The command should return a generated keys recordset");
Assert.IsTrue(reader.Read(), "There must be at least one ID in the generated keys recordset");
int lastId = (int)reader.GetValue(0);
Assert.IsTrue(lastId > maxId, "The generated ID must be greater than the existing ones");
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandText = "select name from hockey where id = ?";
selectCommand.Parameters.Add(lastId);
string value = (string)selectCommand.ExecuteScalar();
Assert.AreEqual("xxx", value);
transaction.Rollback();
}
}
public void TestBulkLoad_DataReaderWithMappingOrdinal2Ordinal()
{
CreateTargetForBulkLoad();
NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString);
loader.BatchSize = 2;
loader.DestinationTableName = "TEMP";
loader.ColumnMappings.Add(1, 0);
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
DbCommand command = new NuoDbCommand("select number, position as xyz2 from hockey order by number", connection);
connection.Open();
DbDataReader reader = command.ExecuteReader();
loader.WriteToServer(reader);
reader.Close();
command = new NuoDbCommand("select count(*) from hockey", connection);
object val = command.ExecuteScalar();
VerifyBulkLoad((int)val, "Fan");
}
}
private static void VerifyBulkLoad(int expectedCount, string expectedFirstRow)
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
DbCommand command = new NuoDbCommand("select count(*) from temp", connection);
object val = command.ExecuteScalar();
Assert.AreEqual(expectedCount, val);
command = new NuoDbCommand("select col from temp", connection);
val = command.ExecuteScalar();
Assert.AreEqual(expectedFirstRow, val);
}
}
private static void CreateTargetForBulkLoad()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
Utils.DropTable(connection, "temp");
DbCommand createCommand = new NuoDbCommand("create table temp (col string)", connection);
int result = createCommand.ExecuteNonQuery();
}
}
public void TestUpdateWithGeneratedKeys()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
DbCommand updateCommand = connection.CreateCommand();
updateCommand.CommandText = "update hockey set number = 99 where team = 'Bruins'";
DbDataReader reader = updateCommand.ExecuteReader();
Assert.IsNotNull(reader, "The command should return a generated keys recordset");
Assert.IsFalse(reader.Read(), "The generated keys recordset should be empty");
transaction.Rollback();
}
}
public void TestTransactions()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
DbCommand countCommand = connection.CreateCommand();
countCommand.CommandText = "select count(*) from hockey";
DbCommand updateCommand = connection.CreateCommand();
updateCommand.CommandText = "insert into hockey (number, name) values (99, 'xxxx')";
int count1 = (int)countCommand.ExecuteScalar();
updateCommand.ExecuteNonQuery();
int count2 = (int)countCommand.ExecuteScalar();
Assert.AreEqual(count2, count1 + 1);
transaction.Rollback();
int count3 = (int)countCommand.ExecuteScalar();
Assert.AreEqual(count3, count1);
}
}
public void TestDataType(string sqlType, object value, object expected)
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
//DbTransaction transaction = connection.BeginTransaction();
Utils.DropTable(connection, "temp");
DbCommand createCommand = new NuoDbCommand("create table temp (col " + sqlType + ")", connection);
int result = createCommand.ExecuteNonQuery();
DbCommand insertCommand = new NuoDbCommand("insert into temp (col) values (?)", connection);
insertCommand.Parameters.Add(value);
int inserted = insertCommand.ExecuteNonQuery();
DbCommand command = new NuoDbCommand("select col from temp", connection);
object val = command.ExecuteScalar();
// compare dates using the string representation
if (val.GetType() == expected.GetType())
Assert.AreEqual(expected, val);
else if (val is DateTime)
Assert.AreEqual(DateTime.Parse(expected.ToString()), val);
else if (val is TimeSpan)
Assert.AreEqual(TimeSpan.Parse(expected.ToString()), val);
else if (expected is ICollection)
CollectionAssert.AreEqual((ICollection)expected, (ICollection)val);
else
Assert.AreEqual(expected, val);
//transaction.Rollback();
}
}
public void TestCommand2()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
DbCommand command = connection.CreateCommand();
command.CommandText = "select * from hockey";
connection.Open();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (i > 0)
Console.Out.Write(", ");
Console.Out.Write(reader[i]);
}
Console.WriteLine();
}
reader.Close();
}
}
public void TestPrepareDDLNoParameter()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
DbCommand command = connection.CreateCommand();
connection.Open();
command.CommandText = "create table xyz (col int)";
command.Prepare();
try
{
int value = command.ExecuteNonQuery();
}
catch (Exception e)
{
Assert.Fail("Executing a prepared DDL that doesn't use parameters reports an error: {0}", e.Message);
}
finally
{
Utils.DropTable(connection, "xyz");
}
}
}
public void TestCommand1()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
DbCommand command = new NuoDbCommand("select * from hockey", connection);
connection.Open();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}\t{2}\t{3}", reader[0], reader[1], reader[2], reader["id"]);
}
reader.Close();
}
}
public void TestAsynchronousUpdate2()
{
NuoDbConnection connection = new NuoDbConnection(connectionString);
connection.Open();
Utils.DropTable(connection, "temp");
NuoDbCommand countCommand = (NuoDbCommand)connection.CreateCommand();
countCommand.CommandText = "create table temp (col string)";
AsyncCallback callback = new AsyncCallback(HandleCallback3);
IAsyncResult result = countCommand.BeginExecuteNonQuery(callback, countCommand);
}
public void TestConnectionPooling()
{
NuoDbConnectionStringBuilder builder = new NuoDbConnectionStringBuilder(connectionString);
builder.Pooling = true;
builder.ConnectionLifetime = 2;
String newConnString = builder.ConnectionString;
int pooledItems = 0;
NuoDbConnection.ClearAllPools();
using (NuoDbConnection cnn = new NuoDbConnection(newConnString))
{
cnn.Open();
// 1 busy
pooledItems = NuoDbConnection.GetPooledConnectionCount(cnn);
Assert.AreEqual(1, pooledItems);
using (NuoDbConnection cnn2 = new NuoDbConnection(newConnString))
{
cnn2.Open();
// 2 busy
pooledItems = NuoDbConnection.GetPooledConnectionCount(cnn);
Assert.AreEqual(2, pooledItems);
}
// 1 available, 1 busy
pooledItems = NuoDbConnection.GetPooledConnectionCount(cnn);
Assert.AreEqual(2, pooledItems);
Thread.Sleep(3000);
// 1 busy
pooledItems = NuoDbConnection.GetPooledConnectionCount(cnn);
Assert.AreEqual(1, pooledItems);
}
// 1 available
pooledItems = NuoDbConnection.GetPooledConnectionCount(newConnString);
Assert.AreEqual(1, pooledItems);
using (NuoDbConnection cnn = new NuoDbConnection(newConnString))
{
cnn.Open();
// 1 busy
pooledItems = NuoDbConnection.GetPooledConnectionCount(cnn);
Assert.AreEqual(1, pooledItems);
}
// 1 available
pooledItems = NuoDbConnection.GetPooledConnectionCount(newConnString);
Assert.AreEqual(1, pooledItems);
Thread.Sleep(3000);
// empty pool
pooledItems = NuoDbConnection.GetPooledConnectionCount(newConnString);
Assert.AreEqual(0, pooledItems);
}
public void TestPrepareNoParameter()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
DbCommand command = connection.CreateCommand();
connection.Open();
command.CommandText = "select * from hockey where id = 2";
command.Prepare();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}\t{2}\t{3}", reader[0], reader[1], reader[2], reader["id"]);
}
reader.Close();
}
}
public void TestConnectionPoolingMaxAge()
{
NuoDbConnectionStringBuilder builder = new NuoDbConnectionStringBuilder(connectionString);
builder.Pooling = true;
builder.ConnectionLifetime = 2;
builder.MaxLifetime = 3;
String newConnString = builder.ConnectionString;
int pooledItems = 0;
NuoDbConnection.ClearAllPools();
using (NuoDbConnection cnn = new NuoDbConnection(newConnString))
{
cnn.Open();
// 1 busy
pooledItems = NuoDbConnection.GetPooledConnectionCount(cnn);
Assert.AreEqual(1, pooledItems);
Thread.Sleep(2000);
}
// 1 available
pooledItems = NuoDbConnection.GetPooledConnectionCount(newConnString);
Assert.AreEqual(1, pooledItems);
using (NuoDbConnection cnn = new NuoDbConnection(newConnString))
{
cnn.Open();
// 1 busy
pooledItems = NuoDbConnection.GetPooledConnectionCount(cnn);
Assert.AreEqual(1, pooledItems);
Thread.Sleep(2000);
}
// 0 available, the connection is too old to be recycled
pooledItems = NuoDbConnection.GetPooledConnectionCount(newConnString);
Assert.AreEqual(0, pooledItems);
}
public void TestScalability()
{
using (NuoDbConnection cnn = new NuoDbConnection(connectionString))
{
cnn.Open();
Utils.DropTable(cnn, "temp");
DbCommand createCommand = new NuoDbCommand("create table temp (col1 integer, col2 integer)", cnn);
int result = createCommand.ExecuteNonQuery();
DbCommand cmm = cnn.CreateCommand();
cmm.CommandText = "insert into temp(col1, col2) values(?, ?)";
cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col1" });
cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col2" });
cmm.Prepare();
int[] count = new int[] { 1000, 5000, 10000, 20000, 40000 };
double[] times = new double[count.Length];
for (var k = 0; k < count.Length; k++)
{
DateTime start = DateTime.Now;
for (var i = 1; i <= count[k]; i++)
{
cmm.Parameters["col1"].Value = i;
cmm.Parameters["col2"].Value = 2 * i;
cmm.ExecuteNonQuery();
}
DateTime end = DateTime.Now;
times[k] = (end - start).TotalMilliseconds;
if (k == 0)
Console.WriteLine("{0} runs = {1} msec", count[k], times[k]);
else
{
double countRatio = (count[k] / count[0]);
double timeRatio = (times[k] / times[0]);
Console.WriteLine("{0} runs = {1} msec => {2} {3}", count[k], times[k], countRatio, timeRatio);
Assert.IsTrue(timeRatio < (countRatio * 1.50), "Scalability at {2} rows is not linear! (time for {0} rows = {1}; time for {2} rows = {3} => ratio = {4} is greater than {5}",
new object[] { count[0], times[0], count[k], times[k], timeRatio, countRatio });
}
}
}
}
public void TestAsynchronousScalar3()
{
NuoDbConnection connection = new NuoDbConnection(connectionString);
NuoDbCommand countCommand = (NuoDbCommand)connection.CreateCommand();
countCommand.CommandText = "select count(*) from hockey";
connection.Open();
AsyncCallback callback = new AsyncCallback(HandleCallback2);
for(int i=0;i<20;i++)
countCommand.BeginExecuteScalar(callback, countCommand);
}
public void TestTimeZone()
{
// Use a time in the UTC time zone; otherwise, it would be treated as if it were in the local timezone even
// if we are telling NuoDB that we are in a different timezone
DateTime dstReferenceDate = DateTime.SpecifyKind(new DateTime(1999, 10, 1, 2, 30, 58), DateTimeKind.Utc);
DateTime nonDstReferenceDate = DateTime.SpecifyKind(new DateTime(1999, 12, 1, 2, 30, 58), DateTimeKind.Utc);
DateTime dtDate;
string strDate;
bool hasNext;
// GMT-5, or GMT-4 if DST is active
using (NuoDbConnection connection = new NuoDbConnection(connectionString + ";TimeZone=America/New_York"))
{
connection.Open();
Utils.DropTable(connection, "timezone");
DbCommand createCommand = new NuoDbCommand("create table timezone (asTimestamp timestamp, asDate date, asTime time, asString string)", connection);
int result = createCommand.ExecuteNonQuery();
DbCommand insertCommand = new NuoDbCommand("insert into timezone (asTimestamp, asDate, asTime, asString) values (?,?,?,?)", connection);
insertCommand.Parameters.Add(dstReferenceDate);
insertCommand.Parameters.Add(dstReferenceDate);
insertCommand.Parameters.Add(dstReferenceDate);
insertCommand.Parameters.Add(dstReferenceDate);
insertCommand.ExecuteNonQuery();
insertCommand.Parameters.Clear();
insertCommand.Parameters.Add(nonDstReferenceDate);
insertCommand.Parameters.Add(nonDstReferenceDate);
insertCommand.Parameters.Add(nonDstReferenceDate);
insertCommand.Parameters.Add(nonDstReferenceDate);
insertCommand.ExecuteNonQuery();
DbCommand command = new NuoDbCommand("select asTimestamp, asDate, asTime, asString from timezone", connection);
DbDataReader reader = command.ExecuteReader();
hasNext = reader.Read();
Assert.IsTrue(hasNext);
dtDate = reader.GetDateTime(0);
Assert.AreEqual("1999-09-30 22:30:58", dtDate.ToString("yyyy-MM-dd HH:mm:ss"));
strDate = reader.GetString(0);
Assert.AreEqual("1999-09-30 22:30:58", strDate);
dtDate = reader.GetDateTime(1);
Assert.AreEqual("1999-09-30", dtDate.ToString("yyyy-MM-dd"));
strDate = reader.GetString(1);
Assert.AreEqual("1999-09-30", strDate);
dtDate = reader.GetDateTime(2);
Assert.AreEqual("22:30:58", dtDate.ToString("HH:mm:ss"));
strDate = reader.GetString(2);
Assert.AreEqual("22:30:58", strDate);
dtDate = reader.GetDateTime(3);
Assert.AreEqual("1999-09-30 22:30:58", dtDate.ToString("yyyy-MM-dd HH:mm:ss"));
strDate = reader.GetString(3);
Assert.AreEqual("1999-09-30 22:30:58", strDate);
hasNext = reader.Read();
Assert.IsTrue(hasNext);
dtDate = reader.GetDateTime(0);
Assert.AreEqual("1999-11-30 21:30:58", dtDate.ToString("yyyy-MM-dd HH:mm:ss"));
strDate = reader.GetString(0);
Assert.AreEqual("1999-11-30 21:30:58", strDate);
dtDate = reader.GetDateTime(1);
Assert.AreEqual("1999-11-30", dtDate.ToString("yyyy-MM-dd"));
strDate = reader.GetString(1);
Assert.AreEqual("1999-11-30", strDate);
dtDate = reader.GetDateTime(2);
Assert.AreEqual("21:30:58", dtDate.ToString("HH:mm:ss"));
strDate = reader.GetString(2);
Assert.AreEqual("21:30:58", strDate);
dtDate = reader.GetDateTime(3);
Assert.AreEqual("1999-11-30 21:30:58", dtDate.ToString("yyyy-MM-dd HH:mm:ss"));
strDate = reader.GetString(3);
Assert.AreEqual("1999-11-30 21:30:58", strDate);
}
// all the date-based columns should magically move one hour back when we change timezone
// GMT-6, or GMT-5 if DST is active
using (NuoDbConnection connection = new NuoDbConnection(connectionString + ";TimeZone=America/Chicago"))
{
connection.Open();
DbCommand command = new NuoDbCommand("select asTimestamp, asDate, asTime, asString from timezone", connection);
DbDataReader reader = command.ExecuteReader();
hasNext = reader.Read();
Assert.IsTrue(hasNext);
dtDate = reader.GetDateTime(0);
Assert.AreEqual("1999-09-30 21:30:58", dtDate.ToString("yyyy-MM-dd HH:mm:ss"));
strDate = reader.GetString(0);
Assert.AreEqual("1999-09-30 21:30:58", strDate);
dtDate = reader.GetDateTime(1);
Assert.AreEqual("1999-09-30", dtDate.ToString("yyyy-MM-dd"));
strDate = reader.GetString(1);
Assert.AreEqual("1999-09-30", strDate);
dtDate = reader.GetDateTime(2);
Assert.AreEqual("21:30:58", dtDate.ToString("HH:mm:ss"));
strDate = reader.GetString(2);
Assert.AreEqual("21:30:58", strDate);
dtDate = reader.GetDateTime(3);
Assert.AreEqual("1999-09-30 22:30:58", dtDate.ToString("yyyy-MM-dd HH:mm:ss"));
strDate = reader.GetString(3);
Assert.AreEqual("1999-09-30 22:30:58", strDate);
hasNext = reader.Read();
Assert.IsTrue(hasNext);
dtDate = reader.GetDateTime(0);
Assert.AreEqual("1999-11-30 20:30:58", dtDate.ToString("yyyy-MM-dd HH:mm:ss"));
strDate = reader.GetString(0);
Assert.AreEqual("1999-11-30 20:30:58", strDate);
dtDate = reader.GetDateTime(1);
Assert.AreEqual("1999-11-30", dtDate.ToString("yyyy-MM-dd"));
strDate = reader.GetString(1);
Assert.AreEqual("1999-11-30", strDate);
dtDate = reader.GetDateTime(2);
Assert.AreEqual("20:30:58", dtDate.ToString("HH:mm:ss"));
strDate = reader.GetString(2);
Assert.AreEqual("20:30:58", strDate);
dtDate = reader.GetDateTime(3);
Assert.AreEqual("1999-11-30 21:30:58", dtDate.ToString("yyyy-MM-dd HH:mm:ss"));
strDate = reader.GetString(3);
Assert.AreEqual("1999-11-30 21:30:58", strDate);
}
}