public void AbortTransaction()
{
StringBuilder b1 = new StringBuilder();
b1.AppendFormat("ALTER TABLE \"{0}\" drop \"INT_FIELD\"", "TEST");
BdpTransaction transaction = null;
BdpCommand command = null;
try
{
transaction = this.Connection.BeginTransaction();
command = new BdpCommand(b1.ToString(), this.Connection, transaction);
command.ExecuteNonQuery();
transaction.Commit();
transaction = null;
}
catch (Exception)
{
transaction.Rollback();
transaction = null;
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
public void IndexerByIndexTest()
{
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
Console.WriteLine();
Console.WriteLine("DataReader - Read Method - Test");
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader[i] + "\t");
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void GetSchemaTableWithExpressionFieldTest()
{
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select TEST.*, 0 AS VALOR from TEST", Connection, transaction);
BdpDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schema = reader.GetSchemaTable();
Console.WriteLine();
Console.WriteLine("DataReader - GetSchemaTable Method- Test");
DataRow[] currRows = schema.Select(null, null, DataViewRowState.CurrentRows);
foreach (DataColumn myCol in schema.Columns)
{
Console.Write("{0}\t\t", myCol.ColumnName);
}
Console.WriteLine();
foreach (DataRow myRow in currRows)
{
foreach (DataColumn myCol in schema.Columns)
{
Console.Write("{0}\t\t", myRow[myCol]);
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void GetValuesTest()
{
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
Console.WriteLine();
Console.WriteLine("DataReader - Read Method - Test");
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
for (int i = 0; i < values.Length; i++)
{
Console.Write(values[i] + "\t");
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void UpdatedArrayFieldTest()
{
Console.WriteLine("\r\nUpdate IARRAY field with implicit transaction.");
int[] values = new int[4];
values[0] = 10;
values[1] = 20;
values[2] = 30;
values[3] = 40;
// Add IARRAY_FIELD column
BdpCommand command = new BdpCommand("alter table TEST add IARRAY_FIELD INTEGER[4]", Connection);
command.ExecuteNonQuery();
command.Close();
// Now test the update of an array
command = new BdpCommand("update TEST set iarray_field = ? where int_field = ?", Connection);
command.Parameters.Add("@iarray_field", BdpType.Array).Value = values;
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Array field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
public void DeleteTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 10;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0].Delete();
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void AbortTransaction()
{
StringBuilder b1 = new StringBuilder();
b1.AppendFormat("ALTER TABLE \"{0}\" drop \"INT_FIELD\"", "TEST");
BdpTransaction transaction = null;
BdpCommand command = null;
try
{
transaction = this.Connection.BeginTransaction();
command = new BdpCommand(b1.ToString(), this.Connection, transaction);
command.ExecuteNonQuery();
transaction.Commit();
transaction = null;
}
catch (Exception)
{
transaction.Rollback();
transaction = null;
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
public void ExecuteScalarTest()
{
BdpCommand command = new BdpCommand("select sum(int_field) from TEST", Connection);
object actual = command.ExecuteScalar();
Console.WriteLine("\r\nExecuteScalar with implicit transaction: {0}", actual);
Assert.AreEqual(4950, actual, "Wrong sum returned.");
command.Dispose();
}
private static void CreateTables(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("DROP TABLE TEST");
BdpCommand command = null;
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
// Table for general purpouse tests
commandText.Append("CREATE TABLE TEST (");
commandText.Append("INT_FIELD INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,");
commandText.Append("CHAR_FIELD CHAR(30),");
commandText.Append("VARCHAR_FIELD VARCHAR(100),");
commandText.Append("BIGINT_FIELD BIGINT,");
commandText.Append("SMALLINT_FIELD SMALLINT,");
commandText.Append("DOUBLE_FIELD DOUBLE PRECISION,");
commandText.Append("FLOAT_FIELD FLOAT,");
commandText.Append("NUMERIC_FIELD NUMERIC(15,2),");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2),");
commandText.Append("DATE_FIELD DATE,");
commandText.Append("TIME_FIELD TIME,");
commandText.Append("TIMESTAMP_FIELD TIMESTAMP,");
commandText.Append("CLOB_FIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,");
commandText.Append("BLOB_FIELD BLOB SUB_TYPE 0 SEGMENT SIZE 80,");
commandText.Append("EXPR_FIELD COMPUTED BY (smallint_field * 1000));");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
connection.Close();
}
public void DataAdapterFillTest()
{
BdpCommand command = new BdpCommand("select * from TEST where DATE_FIELD = ?", Connection);
BdpDataAdapter adapter = new BdpDataAdapter(command);
// adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = new DateTime(2003, 1, 5);
adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = DateTime.Today;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Console.WriteLine();
Console.WriteLine("Implicit transactions - DataAdapter Fill Method - Test");
int tables = 0;
int rows = 0;
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
tables++;
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
rows++;
}
}
Assert.AreEqual(1, tables, "Wrong number of tables.");
Assert.AreEqual(100, rows, "Wrong number of rows.");
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
public void DataAdapterFillTest()
{
BdpCommand command = new BdpCommand("select * from TEST where DATE_FIELD = ?", Connection);
BdpDataAdapter adapter = new BdpDataAdapter(command);
// adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = new DateTime(2003, 1, 5);
adapter.SelectCommand.Parameters.Add("@DATE_FIELD", BdpType.Date, 4, "DATE_FIELD").Value = DateTime.Today;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Console.WriteLine();
Console.WriteLine("Implicit transactions - DataAdapter Fill Method - Test");
int tables = 0;
int rows = 0;
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
tables++;
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
rows++;
}
}
Assert.AreEqual(1, tables, "Wrong number of tables.");
Assert.AreEqual(100, rows, "Wrong number of rows.");
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
public void UpdatedClobFieldTest()
{
Console.WriteLine("\r\nUpdate CLOB field with implicit transaction.");
BdpCommand command = new BdpCommand("update TEST set clob_field = ? where int_field = ?", Connection);
command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stMemo).Value = "Clob field update with implicit transaction";
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Clob field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
public void UpdateTimeStampTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
DateTime dtValue = DateTime.Now;
ds.Tables["TEST"].Rows[0]["TIMESTAMP_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT timestamp_field FROM TEST WHERE int_field = ?";
command = new BdpCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
DateTime val = (DateTime)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(dtValue.Day, val.Day, "timestamp_field has not correct day");
Assert.AreEqual(dtValue.Month, val.Month, "timestamp_field has not correct month");
Assert.AreEqual(dtValue.Year, val.Year, "timestamp_field has not correct year");
Assert.AreEqual(dtValue.Hour, val.Hour, "timestamp_field has not correct hour");
Assert.AreEqual(dtValue.Minute, val.Minute, "timestamp_field has not correct minute");
Assert.AreEqual(dtValue.Second, val.Second, "timestamp_field has not correct second");
}
public void UpdatedBlobFieldTest()
{
Console.WriteLine("\r\nUpdate BLOB field with implicit transaction.");
BdpCommand command = new BdpCommand("update TEST set blob_field = ? where int_field = ?", Connection);
command.Parameters.Add("@blob_field", BdpType.Blob).Value =
Encoding.Default.GetBytes("Blob field update with implicit transaction");
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Blob field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
public void NextResultTest()
{
string querys = "select * from TEST order by INT_FIELD asc;" +
"select * from TEST order by INT_FIELD desc;";
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand(querys, Connection, transaction);
BdpDataReader reader = command.ExecuteReader();
Console.WriteLine();
Console.WriteLine("DataReader - NextResult Method - Test ( First Result )");
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
if (reader.NextResult())
{
Console.WriteLine("DataReader - NextResult Method - Test ( Second Result )");
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void FillMultipleWithImplicitTransactionTest()
{
BdpCommand command = new BdpCommand("select * from TEST", Connection);
BdpDataAdapter adapter = new BdpDataAdapter(command);
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
adapter.Fill(ds1, "TEST");
adapter.Fill(ds2, "TEST");
Assert.AreEqual(100, ds1.Tables["TEST"].Rows.Count, "Incorrect row count (ds1)");
Assert.AreEqual(100, ds2.Tables["TEST"].Rows.Count, "Incorrect row count (ds2)");
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
public void InsertTest()
{
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
DataRow newRow = ds.Tables["TEST"].NewRow();
newRow["INT_FIELD"] = 101;
newRow["CHAR_FIELD"] = "ONE THOUSAND";
newRow["VARCHAR_FIELD"] = ":;,.{}`+^*[]\\!|@#$%&/()?_-<>";
newRow["BIGINT_field"] = 100000;
newRow["SMALLINT_field"] = 100;
newRow["DOUBLE_FIELD"] = 100.01;
newRow["NUMERIC_FIELD"] = 100.01;
newRow["DECIMAL_FIELD"] = 100.01;
newRow["DATE_FIELD"] = new DateTime(100, 10, 10);
newRow["TIME_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10);
newRow["TIMESTAMP_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10);
newRow["CLOB_FIELD"] = "ONE THOUSAND";
ds.Tables["TEST"].Rows.Add(newRow);
adapter.Update(ds, "TEST");
Assert.AreEqual(101, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void FillTest()
{
BdpCommand command = new BdpCommand("SELECT * FROM TEST", Connection);
BdpDataAdapter adapter = new BdpDataAdapter(command);
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
Console.WriteLine();
Console.WriteLine("DataAdapter - Fill Method - Test");
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
}
}
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
public void UpdateVarCharTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["VARCHAR_FIELD"] = "ONE VAR THOUSAND";
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT varchar_field FROM TEST WHERE int_field = ?";
command = new BdpCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
string val = (string)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual("ONE VAR THOUSAND", val.Trim(), "varchar_field has an incorrect value");
}
public void UpdateDecimalTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["DECIMAL_FIELD"] = System.Int32.MaxValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT decimal_field FROM TEST WHERE int_field = ?";
command = new BdpCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
decimal val = (decimal)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int32.MaxValue, val, "decimal_field has an incorrect value");
}
public void FillTest()
{
BdpCommand command = new BdpCommand("SELECT * FROM TEST", Connection);
BdpDataAdapter adapter = new BdpDataAdapter(command);
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
Console.WriteLine();
Console.WriteLine("DataAdapter - Fill Method - Test");
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
}
}
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
public void ReadTest()
{
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
Console.WriteLine();
Console.WriteLine("DataReader - Read Method - Test");
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
for(int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
reader.Close();
command.Dispose();
transaction.Rollback();
}
private static void InsertTestData(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("DELETE FROM TEST");
BdpCommand command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
BdpTransaction transaction = connection.BeginTransaction();
command = new BdpCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field", BdpType.Int32);
command.Parameters.Add("@char_field", BdpType.String, BdpType.stFixed);
command.Parameters.Add("@varchar_field", BdpType.String);
command.Parameters.Add("@bigint_field", BdpType.Int64);
command.Parameters.Add("@smallint_field", BdpType.Int16);
command.Parameters.Add("@float_field", BdpType.Float);
command.Parameters.Add("@double_field", BdpType.Double);
command.Parameters.Add("@numeric_field", BdpType.Decimal);
command.Parameters.Add("@date_field", BdpType.Date);
command.Parameters.Add("@time_Field", BdpType.Time);
command.Parameters.Add("@timestamp_field", BdpType.DateTime);
command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stHMemo);
command.Parameters.Add("@blob_field", BdpType.Blob, BdpType.stHBinary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10) / 5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10) / 5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value = DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
command.Dispose();
connection.Close();
}
}
public void GetSchemaTableWithExpressionFieldTest()
{
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select TEST.*, 0 AS VALOR from TEST", Connection, transaction);
BdpDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable schema = reader.GetSchemaTable();
Console.WriteLine();
Console.WriteLine("DataReader - GetSchemaTable Method- Test");
DataRow[] currRows = schema.Select(null, null, DataViewRowState.CurrentRows);
foreach (DataColumn myCol in schema.Columns)
{
Console.Write("{0}\t\t", myCol.ColumnName);
}
Console.WriteLine();
foreach (DataRow myRow in currRows)
{
foreach (DataColumn myCol in schema.Columns)
{
Console.Write("{0}\t\t", myRow[myCol]);
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void NextResultTest()
{
string querys = "select * from TEST order by INT_FIELD asc;" +
"select * from TEST order by INT_FIELD desc;";
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand(querys, Connection, transaction);
BdpDataReader reader = command.ExecuteReader();
Console.WriteLine();
Console.WriteLine("DataReader - NextResult Method - Test ( First Result )");
while (reader.Read())
{
for(int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
if(reader.NextResult())
{
Console.WriteLine("DataReader - NextResult Method - Test ( Second Result )");
while (reader.Read())
{
for(int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader.GetValue(i) + "\t");
}
Console.WriteLine();
}
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void UpdateVarCharTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["VARCHAR_FIELD"] = "ONE VAR THOUSAND";
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT varchar_field FROM TEST WHERE int_field = ?";
command = new BdpCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
string val = (string)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual("ONE VAR THOUSAND", val.Trim(), "varchar_field has an incorrect value");
}
public void UpdatedClobFieldTest()
{
Console.WriteLine("\r\nUpdate CLOB field with implicit transaction.");
BdpCommand command = new BdpCommand("update TEST set clob_field = ? where int_field = ?", Connection);
command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stMemo).Value = "Clob field update with implicit transaction";
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Clob field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
public void ExecuteScalarTest()
{
BdpCommand command = new BdpCommand("select sum(int_field) from TEST", Connection);
object actual = command.ExecuteScalar();
Console.WriteLine("\r\nExecuteScalar with implicit transaction: {0}", actual);
Assert.AreEqual(4950, actual, "Wrong sum returned.");
command.Dispose();
}
public void FillMultipleTest()
{
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
adapter.Fill(ds1, "TEST");
adapter.Fill(ds2, "TEST");
Assert.AreEqual(100, ds1.Tables["TEST"].Rows.Count, "Incorrect row count (ds1)");
Assert.AreEqual(100, ds2.Tables["TEST"].Rows.Count, "Incorrect row count (ds2)");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
private static void CreateTriggers(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
BdpCommand command = null;
// new_row
commandText = new StringBuilder();
commandText.Append("DROP TRIGGER new_row");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER new_row FOR test ACTIVE\r\n");
commandText.Append("AFTER INSERT POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'new row';\r\n");
commandText.Append("END");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
// update_row
commandText = new StringBuilder();
commandText.Append("DROP TRIGGER update_row");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER update_row FOR test ACTIVE\r\n");
commandText.Append("AFTER UPDATE POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'updated row';\r\n");
commandText.Append("END");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
connection.Close();
}
private static void CreateTables(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("DROP TABLE TEST");
BdpCommand command = null;
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
// Table for general purpouse tests
commandText.Append("CREATE TABLE TEST (");
commandText.Append("INT_FIELD INTEGER DEFAULT 0 NOT NULL PRIMARY KEY,");
commandText.Append("CHAR_FIELD CHAR(30),");
commandText.Append("VARCHAR_FIELD VARCHAR(100),");
commandText.Append("BIGINT_FIELD BIGINT,");
commandText.Append("SMALLINT_FIELD SMALLINT,");
commandText.Append("DOUBLE_FIELD DOUBLE PRECISION,");
commandText.Append("FLOAT_FIELD FLOAT,");
commandText.Append("NUMERIC_FIELD NUMERIC(15,2),");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2),");
commandText.Append("DATE_FIELD DATE,");
commandText.Append("TIME_FIELD TIME,");
commandText.Append("TIMESTAMP_FIELD TIMESTAMP,");
commandText.Append("CLOB_FIELD BLOB SUB_TYPE 1 SEGMENT SIZE 80,");
commandText.Append("BLOB_FIELD BLOB SUB_TYPE 0 SEGMENT SIZE 80,");
commandText.Append("EXPR_FIELD COMPUTED BY (smallint_field * 1000));");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
connection.Close();
}
private static void CreateTriggers(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
BdpCommand command = null;
// new_row
commandText = new StringBuilder();
commandText.Append("DROP TRIGGER new_row");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER new_row FOR test ACTIVE\r\n");
commandText.Append("AFTER INSERT POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'new row';\r\n");
commandText.Append("END");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
// update_row
commandText = new StringBuilder();
commandText.Append("DROP TRIGGER update_row");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE TRIGGER update_row FOR test ACTIVE\r\n");
commandText.Append("AFTER UPDATE POSITION 0\r\n");
commandText.Append("AS\r\n");
commandText.Append("BEGIN\r\n");
commandText.Append("POST_EVENT 'updated row';\r\n");
commandText.Append("END");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
connection.Close();
}
private static void CreateProcedures(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
BdpCommand command = null;
StringBuilder commandText = new StringBuilder();
// SELECT_DATA
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE SELECT_DATA");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE SELECT_DATA \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("INT_FIELD INTEGER, \r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100), \r\n");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2)) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("FOR SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD \r\n");
commandText.Append("DO \r\n");
commandText.Append("SUSPEND; \r\n");
commandText.Append("end;");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
// GETRECORDCOUNT
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE GETRECORDCOUNT");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETRECORDCOUNT \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("RECCOUNT SMALLINT) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("for select count(*) from test into :reccount \r\n");
commandText.Append("do \r\n");
commandText.Append("suspend; \r\n");
commandText.Append("end\r\n");
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETVARCHARFIELD
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE GETVARCHARFIELD");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETVARCHARFIELD (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select varchar_field from test where int_field = :id into :varchar_field\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETASCIIBLOB
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE GETASCIIBLOB");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETASCIIBLOB (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("ASCII_BLOB BLOB SUB_TYPE 1)\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select clob_field from test where int_field = :id into :ascii_blob\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
connection.Close();
}
public void UpdatedBlobFieldTest()
{
Console.WriteLine("\r\nUpdate BLOB field with implicit transaction.");
BdpCommand command = new BdpCommand("update TEST set blob_field = ? where int_field = ?", Connection);
command.Parameters.Add("@blob_field", BdpType.Blob).Value =
Encoding.Default.GetBytes("Blob field update with implicit transaction");
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Blob field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
public void UpdatedArrayFieldTest()
{
Console.WriteLine("\r\nUpdate IARRAY field with implicit transaction.");
int[] values = new int[4];
values[0] = 10;
values[1] = 20;
values[2] = 30;
values[3] = 40;
// Add IARRAY_FIELD column
BdpCommand command = new BdpCommand("alter table TEST add IARRAY_FIELD INTEGER[4]", Connection);
command.ExecuteNonQuery();
command.Close();
// Now test the update of an array
command = new BdpCommand("update TEST set iarray_field = ? where int_field = ?", Connection);
command.Parameters.Add("@iarray_field", BdpType.Array).Value = values;
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
int i = command.ExecuteNonQuery();
Assert.AreEqual(i, 1, "Array field update with implicit transaction failed");
// Force the implicit transaction to be committed
command.Dispose();
}
public void GetValuesTest()
{
BdpTransaction transaction = Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
Console.WriteLine();
Console.WriteLine("DataReader - Read Method - Test");
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
for (int i = 0; i < values.Length; i++)
{
Console.Write(values[i] + "\t");
}
Console.WriteLine();
}
reader.Close();
transaction.Rollback();
command.Dispose();
}
public void UpdateDecimalTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["DECIMAL_FIELD"] = System.Int32.MaxValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT decimal_field FROM TEST WHERE int_field = ?";
command = new BdpCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
decimal val = (decimal)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int32.MaxValue, val, "decimal_field has an incorrect value");
}
public void InsertTest()
{
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand("select * from TEST", Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
DataRow newRow = ds.Tables["TEST"].NewRow();
newRow["INT_FIELD"] = 101;
newRow["CHAR_FIELD"] = "ONE THOUSAND";
newRow["VARCHAR_FIELD"] = ":;,.{}`+^*[]\\!|@#$%&/()?_-<>";
newRow["BIGINT_field"] = 100000;
newRow["SMALLINT_field"] = 100;
newRow["DOUBLE_FIELD"] = 100.01;
newRow["NUMERIC_FIELD"] = 100.01;
newRow["DECIMAL_FIELD"] = 100.01;
newRow["DATE_FIELD"] = new DateTime(100, 10, 10);
newRow["TIME_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10);
newRow["TIMESTAMP_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10);
newRow["CLOB_FIELD"] = "ONE THOUSAND";
ds.Tables["TEST"].Rows.Add(newRow);
adapter.Update(ds, "TEST");
Assert.AreEqual(101, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
private static void CreateProcedures(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
BdpCommand command = null;
StringBuilder commandText = new StringBuilder();
// SELECT_DATA
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE SELECT_DATA");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE SELECT_DATA \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("INT_FIELD INTEGER, \r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100), \r\n");
commandText.Append("DECIMAL_FIELD DECIMAL(15,2)) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("FOR SELECT INT_FIELD, VARCHAR_FIELD, DECIMAL_FIELD FROM TEST INTO :INT_FIELD, :VARCHAR_FIELD, :DECIMAL_FIELD \r\n");
commandText.Append("DO \r\n");
commandText.Append("SUSPEND; \r\n");
commandText.Append("end;");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
// GETRECORDCOUNT
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE GETRECORDCOUNT");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETRECORDCOUNT \r\n");
commandText.Append("RETURNS ( \r\n");
commandText.Append("RECCOUNT SMALLINT) \r\n");
commandText.Append("AS \r\n");
commandText.Append("begin \r\n");
commandText.Append("for select count(*) from test into :reccount \r\n");
commandText.Append("do \r\n");
commandText.Append("suspend; \r\n");
commandText.Append("end\r\n");
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETVARCHARFIELD
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE GETVARCHARFIELD");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETVARCHARFIELD (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("VARCHAR_FIELD VARCHAR(100))\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select varchar_field from test where int_field = :id into :varchar_field\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
// GETASCIIBLOB
commandText = new StringBuilder();
commandText.Append("DROP PROCEDURE GETASCIIBLOB");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
commandText = new StringBuilder();
commandText.Append("CREATE PROCEDURE GETASCIIBLOB (\r\n");
commandText.Append("ID INTEGER)\r\n");
commandText.Append("RETURNS (\r\n");
commandText.Append("ASCII_BLOB BLOB SUB_TYPE 1)\r\n");
commandText.Append("AS\r\n");
commandText.Append("begin\r\n");
commandText.Append("for select clob_field from test where int_field = :id into :ascii_blob\r\n");
commandText.Append("do\r\n");
commandText.Append("suspend;\r\n");
commandText.Append("end\r\n");
try
{
command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
}
catch
{
}
connection.Close();
}
public void UpdateTimeStampTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
DateTime dtValue = DateTime.Now;
ds.Tables["TEST"].Rows[0]["TIMESTAMP_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT timestamp_field FROM TEST WHERE int_field = ?";
command = new BdpCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", BdpType.Int32).Value = 1;
DateTime val = (DateTime)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(dtValue.Day, val.Day, "timestamp_field has not correct day");
Assert.AreEqual(dtValue.Month, val.Month, "timestamp_field has not correct month");
Assert.AreEqual(dtValue.Year, val.Year, "timestamp_field has not correct year");
Assert.AreEqual(dtValue.Hour, val.Hour, "timestamp_field has not correct hour");
Assert.AreEqual(dtValue.Minute, val.Minute, "timestamp_field has not correct minute");
Assert.AreEqual(dtValue.Second, val.Second, "timestamp_field has not correct second");
}
private static void InsertTestData(string connectionString)
{
BdpConnection connection = new BdpConnection(connectionString);
connection.Open();
StringBuilder commandText = new StringBuilder();
commandText.Append("DELETE FROM TEST");
BdpCommand command = new BdpCommand(commandText.ToString(), connection);
command.ExecuteNonQuery();
command.Dispose();
commandText = new StringBuilder();
commandText.Append("insert into test (int_field, char_field, varchar_field, bigint_field, smallint_field, float_field, double_field, numeric_field, date_field, time_field, timestamp_field, clob_field, blob_field)");
commandText.Append(" values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
BdpTransaction transaction = connection.BeginTransaction();
command = new BdpCommand(commandText.ToString(), connection, transaction);
try
{
// Add command parameters
command.Parameters.Add("@int_field", BdpType.Int32);
command.Parameters.Add("@char_field", BdpType.String, BdpType.stFixed);
command.Parameters.Add("@varchar_field", BdpType.String);
command.Parameters.Add("@bigint_field", BdpType.Int64);
command.Parameters.Add("@smallint_field", BdpType.Int16);
command.Parameters.Add("@float_field", BdpType.Float);
command.Parameters.Add("@double_field", BdpType.Double);
command.Parameters.Add("@numeric_field", BdpType.Decimal);
command.Parameters.Add("@date_field", BdpType.Date);
command.Parameters.Add("@time_Field", BdpType.Time);
command.Parameters.Add("@timestamp_field", BdpType.DateTime);
command.Parameters.Add("@clob_field", BdpType.Blob, BdpType.stHMemo);
command.Parameters.Add("@blob_field", BdpType.Blob, BdpType.stHBinary);
command.Prepare();
for (int i = 0; i < 100; i++)
{
command.Parameters["@int_field"].Value = i;
command.Parameters["@char_field"].Value = "IRow " + i.ToString();
command.Parameters["@varchar_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@bigint_field"].Value = i;
command.Parameters["@smallint_field"].Value = i;
command.Parameters["@float_field"].Value = (float)(i + 10) / 5;
command.Parameters["@double_field"].Value = Math.Log(i, 10);
command.Parameters["@numeric_field"].Value = (decimal)(i + 10) / 5;
command.Parameters["@date_field"].Value = DateTime.Now;
command.Parameters["@time_field"].Value = DateTime.Now;
command.Parameters["@timestamp_field"].Value = DateTime.Now;
command.Parameters["@clob_field"].Value = "IRow Number " + i.ToString();
command.Parameters["@blob_field"].Value = Encoding.Default.GetBytes("IRow Number " + i.ToString());
command.ExecuteNonQuery();
}
// Commit transaction
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
command.Dispose();
connection.Close();
}
}
public void DeleteTest()
{
string sql = "select * from TEST where int_field = ?";
BdpTransaction transaction = this.Connection.BeginTransaction();
BdpCommand command = new BdpCommand(sql, Connection, transaction);
BdpDataAdapter adapter = new BdpDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", BdpType.Int32).Value = 10;
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0].Delete();
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}