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