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 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 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 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");
}
}
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);
}
}
public void TestHighAvailability()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString.Replace("Server=", "Server=localhost:8,")))
{
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 TestReaderNoStoredProcedureWithParam()
{
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 integer) returns table(id integer, value string) as var i = 0; while(i<p1) insert into table values (i, 'xx'); i = i+1; end_while; end_procedure", connection).ExecuteNonQuery();
NuoDbCommand cmd = new NuoDbCommand("call nunit_test(?)", connection);
cmd.Parameters.Add(10);
int n = 0;
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Assert.AreEqual(n++, reader[0]);
Assert.AreEqual("xx", reader[1]);
}
Assert.AreEqual(10, n);
}
}
}
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 TestClientInfo()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString + ";ClientInfo=hello;ClientHost=localhost.localdomain;ClientProcessId=101"))
{
DbCommand cmd = new NuoDbCommand("select * from system.connections where connid=GetConnectionID()", connection);
connection.Open();
using (DbDataReader reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
try
{
Assert.AreEqual("hello", reader["clientinfo"]);
Assert.AreEqual("localhost.localdomain", reader["clienthost"]);
Assert.AreEqual("101", reader["clientprocessid"]);
}
catch (IndexOutOfRangeException)
{
// the version of NuoDB doesn't expose the client info columns
}
Assert.IsFalse(reader.Read());
}
}
}
public void TestUTFParams()
{
NuoDbConnection connection = new NuoDbConnection(connectionString);
connection.Open();
Utils.DropTable(connection, "temp");
string utf8String = "z a \u0306 \u01FD \u03B2";
new NuoDbCommand("create table temp (col1 string)", connection).ExecuteNonQuery();
using (NuoDbCommand cmd = new NuoDbCommand("insert into temp values (?)", connection))
{
cmd.Prepare();
cmd.Parameters[0].Value = utf8String;
Assert.AreEqual(1, cmd.ExecuteNonQuery());
}
using (NuoDbCommand cmd = new NuoDbCommand("select * from temp", connection))
{
using (DbDataReader reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
Assert.AreEqual(utf8String, reader.GetString(0));
}
}
}
