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 NuoDbDataReader(NuoDbConnection connection, int handle, EncodedDataStream dataStream, NuoDbCommand statement, bool readColumnNames)
{
this.connection = connection;
this.handle = handle;
this.pendingRows = dataStream;
this.statement = statement;
if (this.handle != -1)
this.connection.InternalConnection.RegisterResultSet(this.handle);
this.numberColumns = this.pendingRows != null ? this.pendingRows.getInt() : 0;
this.values = new Value[numberColumns];
if (readColumnNames)
{
this.columnNames = new string[numberColumns];
for (int n = 0; n < numberColumns; ++n)
{
columnNames[n] = dataStream.getString();
}
}
else
{
//RemPreparedStatement ps = (RemPreparedStatement)statement;
//columnNames = ps.columnNames;
}
}
public NuoDbDataReader(NuoDbConnection connection, int handle, EncodedDataStream dataStream, NuoDbCommand statement, bool readColumnNames)
{
this.connection = connection;
this.handle = handle;
this.pendingRows = dataStream;
this.statement = statement;
if (this.handle != -1)
{
this.connection.InternalConnection.RegisterResultSet(this.handle);
}
this.numberColumns = this.pendingRows != null?this.pendingRows.getInt() : 0;
this.values = new Value[numberColumns];
if (readColumnNames)
{
this.columnNames = new string[numberColumns];
for (int n = 0; n < numberColumns; ++n)
{
columnNames[n] = dataStream.getString();
}
}
else
{
//RemPreparedStatement ps = (RemPreparedStatement)statement;
//columnNames = ps.columnNames;
}
}
public NuoDbDataAdapter(NuoDbDataAdapter other)
: base()
{
this.SelectCommand = other.SelectCommand is ICloneable ? (NuoDbCommand)other.SelectCommand.Clone() : null;
this.InsertCommand = other.InsertCommand is ICloneable ? (NuoDbCommand)other.InsertCommand.Clone() : null;
this.DeleteCommand = other.DeleteCommand is ICloneable ? (NuoDbCommand)other.DeleteCommand.Clone() : null;
this.UpdateCommand = other.UpdateCommand is ICloneable ? (NuoDbCommand)other.UpdateCommand.Clone() : null;
}
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 override void Close()
{
if (closed || handle == -1 || connection == null || (connection as IDbConnection).State == ConnectionState.Closed ||
!connection.InternalConnection.IsResultSetRegistered(handle))
{
return;
}
connection.InternalConnection.CloseResultSet(handle);
statement = null;
closed = true;
}
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 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 object Clone()
{
NuoDbCommand command = new NuoDbCommand();
command.CommandText = this.CommandText;
command.Connection = this.Connection;
command.Transaction = this.Transaction;
command.CommandType = this.CommandType;
command.CommandTimeout = this.CommandTimeout;
command.UpdatedRowSource = this.UpdatedRowSource;
if (this.ExpectedColumnTypes != null)
{
command.ExpectedColumnTypes = (Type[])this.ExpectedColumnTypes.Clone();
}
foreach (NuoDbParameter p in this.Parameters)
{
command.Parameters.Add(((ICloneable)p).Clone());
}
return(command);
}
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();
}
}
private void WriteToServer(DataFeeder feeder)
{
if (this.tableName.Length == 0)
{
throw new ArgumentException("The name of the destination table hasn't been specified", "DestinationTableName");
}
StringBuilder builder = new StringBuilder();
builder.Append("INSERT INTO ");
if (this.tableName.Contains("."))
{
string[] parts = this.tableName.Split(new char[] { '.' });
bool first = true;
foreach (string part in parts)
{
if (first)
{
first = false;
}
else
{
builder.Append(".");
}
builder.Append("`");
builder.Append(part.Replace("`", "``"));
builder.Append("`");
}
}
else
{
builder.Append("`");
builder.Append(this.tableName.Replace("`", "``"));
builder.Append("`");
}
builder.Append(" ");
if (mappings.Count == 0)
{
// the target table has the same number and names of the columns as in the specified input rows
builder.Append("VALUES (");
for (int i = 0; i < feeder.FieldCount; i++)
{
if (i != 0)
{
builder.Append(", ");
}
builder.Append("?");
}
builder.Append(")");
}
else
{
DataRowCollection targetColumns = null;
builder.Append(" (");
for (int i = 0; i < mappings.Count; i++)
{
NuoDbBulkLoaderColumnMapping mapping = mappings[i];
if (i != 0)
{
builder.Append(", ");
}
builder.Append("`");
if (mapping.DestinationColumn == null)
{
// we are requested to map to a target column that is identified with its ordinal number, so
// fetch the schema of the target table to find out what is its name
if (targetColumns == null)
{
// split the destination table into its different parts
string[] parts = this.tableName.Split(new char[] { '.' });
DataTable targetSchema = this.connection.GetSchema("Columns", new string[] { null, // catalog
parts.Length == 2 ? parts[0] : null, // schema
parts.Length == 2 ? parts[1] : parts[0] // table
});
targetColumns = targetSchema.Rows;
}
if (mapping.DestinationOrdinal < 0 || mapping.DestinationOrdinal > targetColumns.Count)
{
throw new IndexOutOfRangeException(String.Format("The specified ordinal of the target column ({0}) is outside the range of the column count ({1}) of table {2}",
new object[] { mapping.DestinationOrdinal, targetColumns.Count, this.tableName }));
}
string columnName = (string)(targetColumns[mapping.DestinationOrdinal]["COLUMN_NAME"]);
builder.Append(columnName.Replace("`", "``"));
}
else
{
builder.Append(mapping.DestinationColumn.Replace("`", "``"));
}
builder.Append("`");
}
builder.Append(") VALUES (");
for (int i = 0; i < mappings.Count; i++)
{
if (i != 0)
{
builder.Append(", ");
}
builder.Append("?");
}
builder.Append(")");
}
string sqlString = builder.ToString();
#if DEBUG
System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: " + sqlString);
#endif
if (this.connection.State != ConnectionState.Open)
{
this.connection.Open();
}
using (NuoDbCommand command = new NuoDbCommand(sqlString, this.connection))
{
if (mappings.Count > 0)
{
// do the check for out-of-range values just once
foreach (NuoDbBulkLoaderColumnMapping mapping in mappings)
{
if (mapping.SourceColumn == null && mapping.SourceOrdinal < 0 || mapping.SourceOrdinal > feeder.FieldCount)
{
throw new IndexOutOfRangeException(String.Format("The specified ordinal of the source column ({0}) is outside the range of the column count ({1})",
mapping.SourceOrdinal, feeder.FieldCount));
}
}
feeder = new FeederOrderer(feeder, mappings);
}
int batchCount = 0;
int totalSize = 0;
while ((batchCount = command.ExecuteBatch(feeder, this.batchSize)) > 0)
{
totalSize += batchCount;
#if DEBUG
System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: sent a batch of " + batchCount + " rows");
#endif
if (handlers.Count != 0)
{
BatchProcessedEventHandler[] tmpArray = new BatchProcessedEventHandler[handlers.Count];
handlers.CopyTo(tmpArray);
BatchProcessedEventArgs args = new BatchProcessedEventArgs();
args.BatchSize = batchCount;
args.TotalSize = totalSize;
args.HasErrors = false;
foreach (BatchProcessedEventHandler h in tmpArray)
{
h.Invoke(this, args);
}
}
}
}
}
public override void Close()
{
if (closed || handle == -1 || connection == null || (connection as IDbConnection).State == ConnectionState.Closed ||
!connection.InternalConnection.IsResultSetRegistered(handle))
{
return;
}
connection.InternalConnection.CloseResultSet(handle);
statement = null;
closed = true;
}
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 NuoDbDataReader(NuoDbConnection connection, int handle, EncodedDataStream dataStream, NuoDbCommand statement, bool readColumnNames)
{
this.connection = connection;
this.statement = statement;
InitResultSet(handle, dataStream, readColumnNames);
}
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
}
}
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 NuoDbDataAdapter(NuoDbCommand selectCommand)
: base()
{
this.SelectCommand = selectCommand;
}
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();
}
}
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);
}
}
public override void Close()
{
closeCurrentResultSet();
statement = null;
closed = true;
}
internal static void DropTable(NuoDbConnection cnn, string tableName)
{
DbCommand dropCommand = new NuoDbCommand("drop table if exists " + tableName, cnn);
dropCommand.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();
}
}
public void TestAsynchronousUpdate1()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
connection.Open();
Utils.DropTable(connection, "temp");
NuoDbCommand createCommand = new NuoDbCommand("create table temp (col string)", connection);
IAsyncResult result = createCommand.BeginExecuteNonQuery();
int count = createCommand.EndExecuteNonQuery(result);
}
}
internal static int GetTableRows()
{
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
DbCommand command = new NuoDbCommand("select count(*) from hockey", connection);
connection.Open();
return (int)command.ExecuteScalar();
}
}
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 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);
}
}
}
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();
}
}
private void WriteToServer(Feeder feeder)
{
if (this.tableName.Length == 0)
{
throw new ArgumentException("The name of the destination table hasn't been specified", "DestinationTableName");
}
StringBuilder builder = new StringBuilder();
builder.Append("INSERT INTO `");
builder.Append(this.tableName.Replace("`", "``"));
builder.Append("` ");
if (mappings.Count == 0)
{
// the target table has the same number and names of the columns as in the specified input rows
builder.Append("VALUES (");
for (int i = 0; i < feeder.FieldCount; i++)
{
if (i != 0)
{
builder.Append(", ");
}
builder.Append("?");
}
builder.Append(")");
}
else
{
DataRowCollection targetColumns = null;
builder.Append(" (");
for (int i = 0; i < mappings.Count; i++)
{
NuoDbBulkLoaderColumnMapping mapping = mappings[i];
if (i != 0)
{
builder.Append(", ");
}
builder.Append("`");
if (mapping.DestinationColumn == null)
{
// we are requested to map to a target column that is identified with its ordinal number, so
// fetch the schema of the target table to find out what is its name
if (targetColumns == null)
{
// split the destination table into its different parts
string[] parts = this.tableName.Split(new char[] { '.' });
DataTable targetSchema = this.connection.GetSchema("Columns", new string[] { null, // catalog
parts.Length == 2 ? parts[0] : null, // schema
parts.Length == 2 ? parts[1] : parts[0] // table
});
targetColumns = targetSchema.Rows;
}
if (mapping.DestinationOrdinal < 0 || mapping.DestinationOrdinal > targetColumns.Count)
{
throw new IndexOutOfRangeException(String.Format("The specified ordinal of the target column ({0}) is outside the range of the column count ({1}) of table {2}",
new object[] { mapping.DestinationOrdinal, targetColumns.Count, this.tableName }));
}
string columnName = (string)(targetColumns[mapping.DestinationOrdinal]["COLUMN_NAME"]);
builder.Append(columnName.Replace("`", "``"));
}
else
{
builder.Append(mapping.DestinationColumn.Replace("`", "``"));
}
builder.Append("`");
}
builder.Append(") VALUES (");
for (int i = 0; i < mappings.Count; i++)
{
if (i != 0)
{
builder.Append(", ");
}
builder.Append("?");
}
builder.Append(")");
}
string sqlString = builder.ToString();
#if DEBUG
System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: " + sqlString);
#endif
if (this.connection.State != ConnectionState.Open)
{
this.connection.Open();
}
using (NuoDbCommand command = new NuoDbCommand(sqlString, this.connection))
{
command.Prepare();
int totalSize = 0;
// do the check for out-of-range values just once
foreach (NuoDbBulkLoaderColumnMapping mapping in mappings)
{
if (mapping.SourceColumn == null && mapping.SourceOrdinal < 0 || mapping.SourceOrdinal > feeder.FieldCount)
{
throw new IndexOutOfRangeException(String.Format("The specified ordinal of the source column ({0}) is outside the range of the column count ({1})",
new object[] { mapping.SourceOrdinal, feeder.FieldCount }));
}
}
while (true)
{
EncodedDataStream dataStream = new RemEncodedStream(connection.InternalConnection.protocolVersion);
dataStream.startMessage(Protocol.ExecuteBatchPreparedStatement);
dataStream.encodeInt(command.handle);
int batchCount = 0;
for (; batchCount < this.batchSize && feeder.MoveNext(); batchCount++)
{
dataStream.encodeInt(command.Parameters.Count);
if (mappings.Count == 0)
{
for (int i = 0; i < feeder.FieldCount; i++)
{
dataStream.encodeDotNetObject(feeder[i]);
}
}
else
{
foreach (NuoDbBulkLoaderColumnMapping mapping in mappings)
{
if (mapping.SourceColumn == null)
{
dataStream.encodeDotNetObject(feeder[mapping.SourceOrdinal]);
}
else
{
dataStream.encodeDotNetObject(feeder[mapping.SourceColumn]);
}
}
}
}
// the iterator hasn't found any more data to import, let's break out
if (batchCount == 0)
{
break;
}
dataStream.encodeInt(-1);
dataStream.encodeInt(batchCount);
totalSize += batchCount;
#if DEBUG
System.Diagnostics.Trace.WriteLine("NuoDbBulkLoader::WriteToServer: sending a batch of " + batchCount + " rows");
#endif
this.connection.InternalConnection.sendAndReceive(dataStream);
bool hasErrors = false;
string errorMessage = string.Empty;
for (int i = 0; i < batchCount; i++)
{
int result = dataStream.getInt();
if (result == EXECUTE_FAILED)
{
if (this.connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION6)
{
int sqlCode = dataStream.getInt();
string message = dataStream.getString();
errorMessage = AppendError(errorMessage, message, i);
}
hasErrors = true;
}
}
if (this.connection.InternalConnection.protocolVersion >= Protocol.PROTOCOL_VERSION3)
{
long txnId = dataStream.getLong();
int nodeId = dataStream.getInt();
long commitSequence = dataStream.getLong();
this.connection.InternalConnection.setLastTransaction(txnId, nodeId, commitSequence);
}
if (handlers.Count != 0)
{
BatchProcessedEventHandler[] tmpArray = new BatchProcessedEventHandler[handlers.Count];
handlers.CopyTo(tmpArray);
BatchProcessedEventArgs args = new BatchProcessedEventArgs();
args.BatchSize = batchCount;
args.TotalSize = totalSize;
args.HasErrors = hasErrors;
foreach (BatchProcessedEventHandler h in tmpArray)
{
h.Invoke(this, args);
}
}
if (hasErrors)
{
throw new NuoDbSqlException(errorMessage, NuoDbSqlCode.FindError("BATCH_UPDATE_ERROR"));
}
}
}
}
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 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);
}
}
}
public object Clone()
{
NuoDbCommand command = new NuoDbCommand();
command.CommandText = this.CommandText;
command.Connection = this.Connection;
command.Transaction = this.Transaction;
command.CommandType = this.CommandType;
command.CommandTimeout = this.CommandTimeout;
command.UpdatedRowSource = this.UpdatedRowSource;
if (this.ExpectedColumnTypes != null)
command.ExpectedColumnTypes = (Type[])this.ExpectedColumnTypes.Clone();
foreach (NuoDbParameter p in this.Parameters)
{
command.Parameters.Add(((ICloneable)p).Clone());
}
return command;
}
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 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 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);
}
}
