public void RefreshSchemaTest()
{
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
Console.WriteLine();
Console.WriteLine("CommandBuilder - RefreshSchema Method Test - Commands for original SQL statement: ");
Console.WriteLine(builder.GetInsertCommand().CommandText);
Console.WriteLine(builder.GetUpdateCommand().CommandText);
Console.WriteLine(builder.GetDeleteCommand().CommandText);
adapter.SelectCommand.CommandText = "select int_field, char_Field from TEST where BIGINT_FIELD = ?";
adapter.SelectCommand.Parameters[0].ParameterName = "@bigint_field";
builder.RefreshSchema();
Console.WriteLine();
Console.WriteLine("CommandBuilder - RefreshSchema Method Test - Commands for new SQL statement: ");
Console.WriteLine(builder.GetInsertCommand().CommandText);
Console.WriteLine(builder.GetUpdateCommand().CommandText);
Console.WriteLine(builder.GetDeleteCommand().CommandText);
builder.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 GetDeleteCommandTest()
{
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
Console.WriteLine();
Console.WriteLine("CommandBuilder - GetDeleteCommand Method Test");
Console.WriteLine(builder.GetDeleteCommand().CommandText);
builder.Dispose();
}
public void GetDeleteCommandTest()
{
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
Console.WriteLine();
Console.WriteLine("CommandBuilder - GetDeleteCommand Method Test");
Console.WriteLine( builder.GetDeleteCommand().CommandText );
builder.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 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 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 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 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 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 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();
}
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 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 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 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 RefreshSchemaTest()
{
BdpCommandBuilder builder = new BdpCommandBuilder(adapter);
Console.WriteLine();
Console.WriteLine("CommandBuilder - RefreshSchema Method Test - Commands for original SQL statement: ");
Console.WriteLine( builder.GetInsertCommand().CommandText );
Console.WriteLine( builder.GetUpdateCommand().CommandText );
Console.WriteLine( builder.GetDeleteCommand().CommandText );
adapter.SelectCommand.CommandText = "select int_field, char_Field from TEST where BIGINT_FIELD = ?";
adapter.SelectCommand.Parameters[0].ParameterName = "@bigint_field";
builder.RefreshSchema();
Console.WriteLine();
Console.WriteLine("CommandBuilder - RefreshSchema Method Test - Commands for new SQL statement: ");
Console.WriteLine( builder.GetInsertCommand().CommandText );
Console.WriteLine( builder.GetUpdateCommand().CommandText );
Console.WriteLine( builder.GetDeleteCommand().CommandText );
builder.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");
}
