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 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 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 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 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); } } }
public void TestPrepareParamInOut() { 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(inout p1 string) as if(p1='goodbye') p1='hello'; end_if; end_procedure", connection).ExecuteNonQuery(); NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Prepare(); Assert.IsTrue(cmd.Parameters.Contains("p1")); Assert.AreEqual(ParameterDirection.InputOutput, cmd.Parameters["p1"].Direction); cmd.Parameters["p1"].Value = "goodbye"; cmd.ExecuteNonQuery(); Assert.AreEqual("hello", cmd.Parameters["p1"].Value); } }
public void TestPrepareParamIn() { 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.Prepare(); Assert.IsTrue(cmd.Parameters.Contains("p1")); Assert.AreEqual(ParameterDirection.Input, cmd.Parameters["p1"].Direction); cmd.Parameters["p1"].Value = "hello"; try { cmd.ExecuteNonQuery(); Assert.Fail(); } catch (Exception e) { Assert.AreEqual("hello", e.Message); } } }
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(); } }
internal static void CreateHockeyTable() { using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString)) { connection.Open(); DropTable(connection, "hockey"); DbCommand createCommand = new NuoDbCommand("create table Hockey" + "(" + " Id bigint not NULL generated always as identity primary key," + " Number Integer," + " Name String," + " Position String," + " Team String" + ")", connection); createCommand.ExecuteNonQuery(); DbCommand insertCommand = new NuoDbCommand("Insert into Hockey (Number, Name, Position, Team) Values (?,?,?,?)", connection); insertCommand.Prepare(); insertCommand.Parameters[0].Value = 37; insertCommand.Parameters[1].Value = "PATRICE BERGERON"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 48; insertCommand.Parameters[1].Value = "CHRIS BOURQUE"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 11; insertCommand.Parameters[1].Value = "GREGORY CAMPBELL"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 18; insertCommand.Parameters[1].Value = "NATHAN HORTON"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 23; insertCommand.Parameters[1].Value = "CHRIS KELLY"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 46; insertCommand.Parameters[1].Value = "DAVID KREJCI"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 17; insertCommand.Parameters[1].Value = "MILAN LUCIC"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 64; insertCommand.Parameters[1].Value = "LANE MACDERMID"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 63; insertCommand.Parameters[1].Value = "BRAD MARCHAND"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 20; insertCommand.Parameters[1].Value = "DANIEL PAILLE"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 49; insertCommand.Parameters[1].Value = "RICH PEVERLEY"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 91; insertCommand.Parameters[1].Value = "MARC SAVARD"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 19; insertCommand.Parameters[1].Value = "TYLER SEGUIN"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 22; insertCommand.Parameters[1].Value = "SHAWN THORNTON"; insertCommand.Parameters[2].Value = "Forward"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 55; insertCommand.Parameters[1].Value = "JOHNNY BOYCHUK"; insertCommand.Parameters[2].Value = "Defense"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 33; insertCommand.Parameters[1].Value = "ZDENO CHARA"; insertCommand.Parameters[2].Value = "Defense"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 21; insertCommand.Parameters[1].Value = "ANDREW FERENCE"; insertCommand.Parameters[2].Value = "Defense"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 27; insertCommand.Parameters[1].Value = "DOUGIE HAMILTON"; insertCommand.Parameters[2].Value = "Defense"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 45; insertCommand.Parameters[1].Value = "AARON JOHNSON"; insertCommand.Parameters[2].Value = "Defense"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 54; insertCommand.Parameters[1].Value = "ADAM MCQUAID"; insertCommand.Parameters[2].Value = "Defense"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 44; insertCommand.Parameters[1].Value = "DENNIS SEIDENBERG"; insertCommand.Parameters[2].Value = "Defense"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 35; insertCommand.Parameters[1].Value = "ANTON KHUDOBIN"; insertCommand.Parameters[2].Value = "Goalie"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 40; insertCommand.Parameters[1].Value = "TUUKKA RASK"; insertCommand.Parameters[2].Value = "Goalie"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); insertCommand.Parameters[0].Value = 1; insertCommand.Parameters[1].Value = "MAX SUMMIT"; insertCommand.Parameters[2].Value = "Fan"; insertCommand.Parameters[3].Value = "Bruins"; insertCommand.ExecuteNonQuery(); } }
internal static void DropTable(NuoDbConnection cnn, string tableName) { try { DbCommand dropCommand = new NuoDbCommand("drop table " + tableName, cnn); dropCommand.ExecuteNonQuery(); } catch (Exception) { // table is allowed to be missing } }
internal static void CreatePersonTable() { using (NuoDbConnection connection = new NuoDbConnection(connectionString)) { connection.Open(); try { DbCommand dropCommand = new NuoDbCommand("drop table Person", connection); dropCommand.ExecuteNonQuery(); } catch (Exception) { // table is allowed to be missing } DbCommand createCommand = new NuoDbCommand("create table Person" + "(" + " Id char(38) not NULL primary key," + " Name String" + ")", connection); createCommand.ExecuteNonQuery(); DbCommand insertCommand = new NuoDbCommand("Insert into Person (Id, Name) Values (?,?)", connection); insertCommand.Prepare(); insertCommand.Parameters[0].Value = new Guid("{F571197E-7A4F-4961-9363-7411EACCA841}"); insertCommand.Parameters[1].Value = "Klaus Müller"; insertCommand.ExecuteNonQuery(); } }
public void TestTableValuedArgument() { 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(input_data(field1 string, field2 integer), out output_data string) " + " as " + " output_data = ''; " + " for select field1 from input_data; " + " output_data = output_data || field1 || ' '; " + " end_for; " + " end_procedure", connection).ExecuteNonQuery(); } catch (NuoDbSqlException e) { if (e.Code.Code == -1) { // the server doesn't support table valued arguments for procedures return; } else throw; } NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Prepare(); DataTable table = new DataTable(); table.Columns.Add("f1", typeof(string)); table.Columns.Add("f2", typeof(int)); DataRow row1 = table.NewRow(); row1[0] = "hello"; row1[1] = 0; table.Rows.Add(row1); DataRow row2 = table.NewRow(); row2[0] = "world!"; row2[1] = 0; table.Rows.Add(row2); cmd.Parameters[0].Value = table; cmd.ExecuteNonQuery(); Assert.AreEqual("hello world! ", cmd.Parameters[1].Value); } }
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 }); } } } }
internal static void DropTable(NuoDbConnection cnn, string tableName) { DbCommand dropCommand = new NuoDbCommand("drop table if exists " + tableName, cnn); dropCommand.ExecuteNonQuery(); }
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 TestNoPrepareNamedParamOut() { 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, out p2 string) as p1='hello'; 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.Output; cmd.Parameters.Add(param1); NuoDbParameter param2 = new NuoDbParameter(); param2.ParameterName = "p1"; param2.Direction = ParameterDirection.Output; cmd.Parameters.Add(param2); cmd.ExecuteNonQuery(); Assert.AreEqual("hello", cmd.Parameters["p1"].Value); } }
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 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)); } } }