public void UpdateTimeStampTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(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 = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).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 override DataTable ExecuteQueryAsDataTable (string sql)
{
if (String.IsNullOrEmpty ("sql"))
throw new ArgumentException ("sql");
DataTable table = new DataTable ();
using (IDbCommand command = CreateCommand (sql)) {
using (FbDataAdapter adapter = new FbDataAdapter (command as FbCommand)) {
try {
adapter.Fill (table);
} catch {
} finally {
command.Connection.Close ();
}
}
}
return table;
public void FillTest()
{
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
FbCommandBuilder builder = new FbCommandBuilder(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();
transaction.Commit();
}
/// <include file='Doc/en_EN/FbCommandBuilder.xml' path='doc/class[@name="FbCommandBuilder"]/method[@name="Dispose(System.Boolean)"]/*'/>
protected override void Dispose(bool disposing)
{
if (!this.disposed)
{
try
{
if (disposing)
{
// Clear generated commands
this.RefreshSchema();
// Clear FbDataAdapter handler if needed
if (this.adapterHandler != null)
{
this.dataAdapter.RowUpdating -= adapterHandler;
}
// Clear fields
this.sqlInsert = null;
this.sqlUpdate = null;
this.sqlDelete = null;
this.whereClausule1 = null;
this.whereClausule2 = null;
this.setClausule = null;
this.separator = null;
this.quotePrefix = null;
this.quoteSuffix = null;
this.dataAdapter = null;
this.timestampColumnName = null;
}
// release any unmanaged resources
this.disposed = true;
}
finally
{
base.Dispose(disposing);
}
}
}
public void FillMultipleTest()
{
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
FbCommandBuilder builder = new FbCommandBuilder(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 DataAdapterFillTest()
{
FbCommand command = new FbCommand("select * from TEST where DATE_FIELD = ?", Connection);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@DATE_FIELD", FbDbType.Date, 4, "DATE_FIELD").Value = new DateTime(2003, 1, 5);
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Console.WriteLine();
Console.WriteLine("Implicit transactions - 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();
}
private void OpenDbConnection(string db, string uid, string pwd)
{
try
{
FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.DataSource = "localhost";
cs.Database = database;
cs.UserID = userID;
cs.Password = password;
cs.Dialect = 3;
dbSource = cs.ToString();
dbConnection = new FbConnection (dbSource);
dbConnection.Open ();
dbCommand = new FbCommand (null, dbConnection);
dbDataAdapter = new FbDataAdapter (dbCommand);
}
catch
{
dbConnection = null;
IO.Error ("Unable to open database connection:\n{0}", dbSource);
}
}
public void MultipleResultsetTest()
{
FbCommand command = new FbCommand("", this.Connection);
command.CommandText = "select * from test;";
command.CommandText += "select int_field from test;";
command.CommandText += "select int_field, char_field from test;";
FbDataAdapter adapter = new FbDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
Assert.AreEqual(3, ds.Tables.Count, "Incorrect tables count");
}
public void SubsequentDeletes()
{
string selectSql = "SELECT * FROM test";
string deleteSql = "DELETE FROM test WHERE int_field = @id";
// Set up conenction and select/delete commands
FbConnection connection = new FbConnection(this.Connection.ConnectionString);
FbCommand select = new FbCommand(selectSql, connection);
FbCommand delete = new FbCommand(deleteSql, connection);
delete.Parameters.Add("@id", FbDbType.Integer);
delete.Parameters[0].SourceColumn = "INT_FIELD";
// Set up the FbDataAdapter
FbDataAdapter adapter = new FbDataAdapter(select);
adapter.DeleteCommand = delete;
// Set up dataset
DataSet ds = new DataSet();
adapter.Fill(ds);
// Delete one row
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
// Delete another row
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
// Delete another row
ds.Tables[0].Rows[0].Delete();
adapter.Update(ds);
}
public void DeleteTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 10;
FbCommandBuilder builder = new FbCommandBuilder(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 UpdateVarCharTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(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 = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
string val = (string)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual("ONE VAR THOUSAND", val.Trim(), "varchar_field has not correct value");
}
public void InsertTest()
{
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
FbCommandBuilder builder = new FbCommandBuilder(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");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
/// <include file='Doc/en_EN/FbCommandBuilder.xml' path='doc/class[@name="FbCommandBuilder"]/method[@name="Dispose(System.Boolean)"]/*'/>
protected override void Dispose(bool disposing)
{
if (!this.disposed)
{
try
{
if (disposing)
{
// Clear generated commands
this.RefreshSchema();
// Clear FbDataAdapter handler if needed
if (this.adapterHandler != null)
{
this.dataAdapter.RowUpdating -= adapterHandler;
}
// Clear fields
this.sqlInsert = null;
this.sqlUpdate = null;
this.sqlDelete = null;
this.whereClausule1 = null;
this.whereClausule2 = null;
this.setClausule = null;
this.separator = null;
this.quotePrefix = null;
this.quoteSuffix = null;
this.dataAdapter = null;
this.timestampColumnName = null;
}
// release any unmanaged resources
this.disposed = true;
}
finally
{
base.Dispose(disposing);
}
}
}
/// <include file='Doc/en_EN/FbCommandBuilder.xml' path='doc/class[@name="FbCommandBuilder"]/property[@name="ctor(FbDataAdapter)"]/*'/>
public FbCommandBuilder(FbDataAdapter adapter) : this()
{
this.DataAdapter = adapter;
}
/// <include file='Doc/en_EN/FbCommandBuilder.xml' path='doc/class[@name="FbCommandBuilder"]/property[@name="ctor(FbDataAdapter)"]/*'/>
public FbCommandBuilder(FbDataAdapter adapter) : this()
{
this.DataAdapter = adapter;
}
public override void SetUp()
{
base.SetUp();
adapter = new FbDataAdapter(new FbCommand("select * from TEST where VARCHAR_FIELD = ?", Connection));
}
public void UpdateDecimalTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(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 = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
decimal val = (decimal)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int32.MaxValue, val, "decimal_field has not correct value");
}
/// <summary>
/// Execute a FbCommand (that returns a resultset) against the provided FbConnection.
/// </summary>
/// <example>
/// <code>
/// XmlReader r = helper.ExecuteXmlReader(command);
/// </code></example>
/// <param name="command">The IDbCommand to execute</param>
/// <returns>An XmlReader containing the resultset generated by the command</returns>
public override XmlReader ExecuteXmlReader(IDbCommand command)
{
bool mustCloseConnection = false;
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
mustCloseConnection = true;
}
CleanParameterSyntax(command);
FbDataAdapter da = new FbDataAdapter((FbCommand)command);
DataSet ds = new DataSet();
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds);
StringReader stream = new StringReader(ds.GetXml());
if (mustCloseConnection)
{
command.Connection.Close();
}
return new XmlTextReader(stream);
}
