InterBase and Firebird Developer’s manual for Visual Studio Net. Part 1. Introduction to ADO.NET

OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
con.Close();

OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();
cb.Provider = «LCPI.IBProvider»;
cb.Add(«Location»,@»localhost:d:\Program Files\Firebird\examples\EMPLOYEE.FDB»);
cb.Add(«User ID», «sysdba»);
cb.Add(«Password», «masterkey»);
cb.Add(«ctype», «win1251»);
Console.WriteLine(cb.ToString())

OleDbConnectionStringBuilder cb = new OleDbConnectionStringBuilder();
cb.FileName = AppDomain.CurrentDomain.BaseDirectory + @»\employee.udl»;
OleDbConnection con = new OleDbConnection(cb.ToString());
con.Open();

Properties.Settings s = new Properties.Settings();
//read ConnectionString from app.Settings
Console.WriteLine(s.ConnectionString);

public void DataProtectionAPITest()
{
    // Description: DATA PROTECTION API, encrypting connection string, ADO .Net provider
    try
    {
    //open App.config connectionStrings section
    Configuration config = ConfigurationManager.OpenExeConfiguration(
    System.Reflection.Assembly.GetExecutingAssembly().Location);

    ConnectionStringsSection section =
    config.GetSection(«connectionStrings») as ConnectionStringsSection;

    if (!section.SectionInformation.IsProtected)
       {
         // perform encription
         section.SectionInformation.ProtectSection(
         «DataProtectionConfigurationProvider»);
         // save configuration
         config.Save();
       }

    }
    catch (Exception ex)
    {
           Console.WriteLine(ex.Message);
    }

    // read connection string from encrypted section
    Console.WriteLine(Properties.Settings.Default.ConnectionString);
}

public void ExecuteScalarTest()
{
   //Description: ExecuteScalar — gets a single value. Firebird, InterBase .Net provider (c#)
   OleDbConnection con = ConnectionProvider.CreateConnection();
   con.Open();
   OleDbTransaction trans = con.BeginTransaction();

   OleDbCommand cmd = new OleDbCommand(«select count(*) from employee», con, trans);
   Console.WriteLine(«Record count:» + cmd.ExecuteScalar().ToString());

   trans.Commit();
   con.Close();
}

public void ExecuteReaderTest()
{
   //Example: ExecuteReader using Firebird an InterBase oledb driver; connect InterBase c#
   OleDbConnection con = ConnectionProvider.CreateConnection();
   con.Open();

   //Create command
   OleDbCommand cmd = con.CreateCommand();
   cmd.Transaction = con.BeginTransaction();
   cmd.CommandText = «select * from employee»;
   OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

   //read data
   while (rdr.Read())
   {
      string tmp =«»;
      for(int i=0; i<rdr.FieldCount -1;i++)
      {
         if (tmp != «») tmp += «\t»;
         tmp += rdr[i].ToString();
      }

      Console.WriteLine(tmp);
   }

   rdr.Close();
   //after calling OleDbDataReader.Close() connection will be closed too
   Assert.AreEqual(ConnectionState.Closed,con.State);
}

public void ExecuteNonQueryTest()
{
    //Example: Run ExecuteNonQuery using Firebird OLE DB driver and .Net provider (c#)
    OleDbConnection con = ConnectionProvider.CreateConnection();
    con.Open();
    OleDbTransaction trans = con.BeginTransaction();

    //INSERT         
    OleDbCommand cmd = new OleDbCommand(
    «insert into country (country,currency) values(:country,:currency) «,
    con, trans);

    cmd.Parameters.AddWithValue(«country», «Russia»);
    cmd.Parameters.AddWithValue(«currency», «Kopec»);

    //records affected
    Assert.AreEqual(1, cmd.ExecuteNonQuery());

    //UPDATE
    cmd = new OleDbCommand(
    «update country set currency=:currency where country =:country», con, trans);

    cmd.Parameters.AddWithValue(«currency», «Rouble»);
    cmd.Parameters.AddWithValue(«country», «Russia»);

    //records affected
    Assert.AreEqual(1, cmd.ExecuteNonQuery());

    //DELETE
    cmd = new OleDbCommand(
    «delete from country where country =:country», con, trans);

    cmd.Parameters.AddWithValue(«country», «Russia»);

    //records affected
    Assert.AreEqual(1, cmd.ExecuteNonQuery());

    trans.Commit();
    con.Close();
}

insert into country (country,currency) values(?,?)

insert into country (country,currency) values(:country,:currency)

update country set currency=:currency where country =:country

cmd.Parameters.AddWithValue(«currency», «Rouble»);
cmd.Parameters.AddWithValue(«country», «Russia»);

cmd.Parameters[«country»].Value = «Latvia»;
cmd.Parameters[«currency»].Value = «Lat»;

 select * from stored_procedure_name(…)

public void StoredProcedureResultSetTest()
{
    //Description: SELECT * FROM STORED PROCEDURE, ExecuteReader, ResultSets, Firebird c#, InterBase .Net provider
    OleDbConnection con = ConnectionProvider.CreateConnection();
    con.Open();
    OleDbTransaction trans = con.BeginTransaction();

    //select stored procedure in params 
    OleDbCommand cmd_in_params = new OleDbCommand(«select cust_no from CUSTOMER», con, trans);

    //select mail label through the stored procedure
    OleDbCommand cmd_stored_proc = new OleDbCommand(«select * from mail_label(:cust_no)», con, trans);

    //add one IN parameter                     
    cmd_stored_proc.Parameters.Add(«cust_no», OleDbType.Integer);

    //execure reader
    using (OleDbDataReader rdr = cmd_in_params.ExecuteReader(CommandBehavior.CloseConnection))
    {
      //for each customer No  
      while (rdr.Read())
      {
          cmd_stored_proc.Parameters[«cust_no»].Value = rdr[«cust_no»];
          using (OleDbDataReader rdr_out = cmd_stored_proc.ExecuteReader())
          {
             Console.WriteLine(«Customer №» + rdr[«cust_no»]);
             while (rdr_out.Read())
                    for (int i = 0; i < rdr_out.FieldCount; i++)
                      Console.WriteLine(rdr_out.GetName(i) + «=» + rdr_out[i]);

            Console.WriteLine();
          }
      }

    }
}

 execute procedure stored_procedure_name

public void StoredProcedureOUTParamsTest()
{
   // Description: EXECUTE PROCEDURE, out parameters, InterBase c#, Firebird .Net provider, IBProvider
   OleDbConnection con = ConnectionProvider.CreateConnection();
   con.Open();
   OleDbTransaction trans = con.BeginTransaction();

   //select in params 
   OleDbCommand cmd_in_params =
     new OleDbCommand(«select cust_no from CUSTOMER», con, trans);

   //STORED PROCEDURE
   OleDbCommand cmd_stored_proc =
     new OleDbCommand(«execute procedure mail_label(:cust_no)», con, trans);

   //IN parameter
   cmd_stored_proc.Parameters.Add(«cust_no», OleDbType.BSTR);
   //OUT parameters
   cmd_stored_proc.Parameters.Add(«line1», OleDbType.BSTR).Direction = ParameterDirection.Output;
   cmd_stored_proc.Parameters.Add(«line2», OleDbType.BSTR).Direction = ParameterDirection.Output;
   cmd_stored_proc.Parameters.Add(«line3», OleDbType.BSTR).Direction = ParameterDirection.Output;
   cmd_stored_proc.Parameters.Add(«line4», OleDbType.BSTR).Direction = ParameterDirection.Output;
   cmd_stored_proc.Parameters.Add(«line5», OleDbType.BSTR).Direction = ParameterDirection.Output;
   cmd_stored_proc.Parameters.Add(«line6», OleDbType.BSTR).Direction = ParameterDirection.Output;

   //execure reader
   using (OleDbDataReader rdr = cmd_in_params.ExecuteReader())
   {
       //for each customer No  
       while (rdr.Read())
       {
         cmd_stored_proc.Parameters[«cust_no»].Value = rdr[«cust_no»];
         cmd_stored_proc.ExecuteNonQuery();

         Console.WriteLine(«Customer №» + rdr[«cust_no»]);
         Console.WriteLine(cmd_stored_proc.Parameters[«line1»].Value);
         Console.WriteLine(cmd_stored_proc.Parameters[«line2»].Value);
         Console.WriteLine(cmd_stored_proc.Parameters[«line3»].Value);
         Console.WriteLine(cmd_stored_proc.Parameters[«line4»].Value);
         Console.WriteLine(cmd_stored_proc.Parameters[«line5»].Value);
         Console.WriteLine(cmd_stored_proc.Parameters[«line6»].Value);
         Console.WriteLine(«»);
       }
   }

   trans.Commit();
   con.Close();
}

public void ArrayReadWriteTest()
{
    //Description: Firebird and InterBase arrays, reading and writing arrays via c#, ADO Net
    OleDbConnection con = ConnectionProvider.CreateConnection();
    con.Open();
    OleDbTransaction trans = con.BeginTransaction();

    OleDbCommand cmd = new OleDbCommand(
    «select job_code, job_grade, job_country, job_title, language_req from job»,
    con, trans);

    OleDbCommand cmd_upd = new OleDbCommand(
    «update job set language_req=:language_reg where \n» +
    «job_code=:job_code and job_grade=:job_grade and job_country=:job_country»,
    con, trans);

    cmd_upd.Parameters.Add(«language_req», OleDbType.Variant);
    cmd_upd.Parameters.Add(«job_code», OleDbType.BSTR);
    cmd_upd.Parameters.Add(«job_grade», OleDbType.BSTR);
    cmd_upd.Parameters.Add(«job_country», OleDbType.BSTR);

    using (OleDbDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
        Console.WriteLine(«JOB TITLE:» + rdr[«job_title»].ToString());

        //read arrays
        object lang_obj_arr = rdr[«language_req»];

        if (lang_obj_arr != DBNull.Value)
        {
            //using Array.CreateInstance for create new array
            //arrays bound = 5 elements and first index is 1, not zero
            short arr_lower_bound = 1;

            Array lang_str_arr = Array.CreateInstance(typeof(string), new int[] {5}, new int[] { arr_lower_bound });

            //copy elements into the array
            ((Array)lang_obj_arr).CopyTo(lang_str_arr, arr_lower_bound);

            for (int i = arr_lower_bound; i < lang_str_arr.Length + arr_lower_bound; i++)
            {
              //trim \n for each array value
  	          string trimmed_value = lang_str_arr.GetValue(i).ToString().Replace(«\n», «»);
              lang_str_arr.SetValue(trimmed_value, i);

              //print values
              if (lang_str_arr.GetValue(i).ToString() != «»)
                  Console.WriteLine(lang_str_arr.GetValue(i));
            }

            //write updated arrays values 
            cmd_upd.Parameters[«language_req»].Value = lang_str_arr;
            cmd_upd.Parameters[«job_code»].Value = rdr[«job_code»];
            cmd_upd.Parameters[«job_grade»].Value = rdr[«job_grade»];
            cmd_upd.Parameters[«job_country»].Value = rdr[«job_country»];

            //store changes into the database
            Assert.IsTrue(cmd_upd.ExecuteNonQuery() == 1);
         }
         else
            Console.WriteLine(«No language specified»);

         Console.WriteLine(«»);
       }
   }

   //rollback changes
   trans.Rollback();
   con.Close();
}

public void BLOBReadWriteTest()
{
    //Description: working with BLOB fields, read and write blob fields, InterBase c#, Firebird ADO Net provider
    OleDbConnection con = ConnectionProvider.CreateConnection();
    con.Open();
    OleDbTransaction trans = con.BeginTransaction();

    //BLOB Read command
    OleDbCommand cmd = new OleDbCommand(
    «select proj_id, proj_name,proj_desc from project», con, trans);

    //BLOB write command			
    OleDbCommand cmd_update = new OleDbCommand(
    «update project set proj_desc=:proj_desc where proj_id=:proj_id», con, trans);

    //create parameters with BSTR type
    cmd_update.Parameters.Add(«proj_desc», OleDbType.BSTR);
    cmd_update.Parameters.Add(«proj_id», OleDbType.BSTR);

    using (OleDbDataReader rdr = cmd.ExecuteReader())
    {
       while (rdr.Read())
       {
             //read BLOB 
             Console.WriteLine(«PROJECT: « + rdr[«proj_name»].ToString());
             Console.WriteLine(rdr[«proj_desc»].ToString());

             //write BLOB
             cmd_update.Parameters[«proj_id»].Value = rdr[«proj_id»];

             //change case for BLOB value for each iteration
             string new_project_description = rdr[«proj_desc»].ToString();
             if (new_project_description.ToUpper() != new_project_description)
                 new_project_description = new_project_description.ToUpper();
             else
                 new_project_description = new_project_description.ToLower();

             cmd_update.Parameters[«proj_desc»].Value = new_project_description;
             Assert.AreEqual(1, cmd_update.ExecuteNonQuery());
  	    }
    }

    trans.Commit();
    con.Close();
}

public void AutoCommitSessionTest()
{
    // Description: AutoCommit, AutoCommitLevel, autocommit transaction mode, InterBase c#, Firebird ADO Net
    OleDbConnectionStringBuilder builder =
    ConnectionProvider.GetConnectionStringBuilder();
    builder.Add(«auto_commit», true);
    builder.Add(«auto_commit_level», Convert.ToInt32(System.Data.IsolationLevel.RepeatableRead));
 
    OleDbConnection con = new OleDbConnection(builder.ToString());
    con.Open();
 
    OleDbCommand cmd = new OleDbCommand(«select count(*) from employee», con);
    Assert.IsTrue((int)cmd.ExecuteScalar() > 0);
 
    con.Close();
}

public void InternalTransactionTest()
{
    //Description: NESTED TRANSACTIONS, Firebird ADO .Net, C#, Firebird Net proovider
    OleDbConnectionStringBuilder builder = ConnectionProvider.GetConnectionStringBuilderFromUDL();

    //enable nested transactions
    builder.Add(«nested_trans», true);

    OleDbConnection con1 = new OleDbConnection(builder.ToString());
    con1.Open();

    //main transaction
    OleDbTransaction trans = con1.BeginTransaction();

    //add new recordь
    OleDbCommand cmd_insert = new OleDbCommand(
    «insert into country (country,currency) values (:country,:currency)», con1, trans);

    cmd_insert.Parameters.AddWithValue(«:country», «Russia»);
    cmd_insert.Parameters.AddWithValue(«:currency», «Ruble»);
    Assert.AreEqual(1, cmd_insert.ExecuteNonQuery());

    //begin nested transaction
    OleDbTransaction internal_transaction = trans.Begin();

    //begin 3rd level nested transaction
    OleDbTransaction internal_transaction2 = internal_transaction.Begin();

    //delete record using most nested transaction
    OleDbCommand cmd_delete = new OleDbCommand(
        «delete from country where country=?», con1, internal_transaction2);
    cmd_delete.Parameters.AddWithValue(«?», «Russia»);
    Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

    //rollback most nested transaction
    internal_transaction2.Rollback();

    //check what record was not deleted using 2nd level nested transaction
    OleDbCommand cmd_check = new OleDbCommand(
        «select count(*) from country where country=?», con1, internal_transaction);

    cmd_check.Parameters.AddWithValue(«?», «Russia»);
    Assert.AreEqual (1, cmd_check.ExecuteScalar());

    //delete record
    cmd_delete.Transaction = internal_transaction;
    Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

    //commit changes
    internal_transaction.Commit();

    //check waht record was deleted
    cmd_check.Transaction = trans;
    Assert.AreEqual(0, cmd_check.ExecuteScalar());

    trans.Commit();
    con1.Close();
}

public void TransactionScopeTest()
{
  //TransactionScrope link all local transaction with distributed transaction
  //In the following context we will use 2 local transaction for separated connections
  //распределенная.
  using (TransactionScope scope = new TransactionScope())
  {
     //local transaction will be created automaticly
     OleDbConnection con1 = ConnectionProvider.CreateConnection();
     con1.Open();

     //insert command
     OleDbCommand cmd_insert = new OleDbCommand(
     «insert into country (country,currency) values (:country,:currency)»,con1);
     cmd_insert.Parameters.AddWithValue(«country», «Russia»);
     cmd_insert.Parameters.AddWithValue(«currency», «Rouble»);
     Assert.AreEqual(1, cmd_insert.ExecuteNonQuery());

     //2nd local transaction will be created automaticly too
     OleDbConnection con2 = ConnectionProvider.CreateConnection();
     con2.Open();

     cmd_insert.Connection = con2;
     cmd_insert.Parameters[«country»].Value = «Latvia»;
     cmd_insert.Parameters[«currency»].Value = «Lat»;
     Assert.AreEqual(1, cmd_insert.ExecuteNonQuery());

     //Commit distributed transaction
     //For each local transaction Commit() method will be called
     scope.Complete();

  //if distributed transaction was not completed after end of the using block then all local transactions will be rolled back
  //by calling IDispose.Dispose()
  }

  //TransactionScope will use local transaction because all commands will executed in a single context
  using (TransactionScope scope = new TransactionScope())
  {
      OleDbConnection con1 = ConnectionProvider.CreateConnection();
      con1.Open();

      //check records addition
      OleDbCommand cmd_select = new OleDbCommand(
      «select count(*) from country where country=:country», con1);
      cmd_select.Parameters.Add(«country», OleDbType.BSTR);

      //delete records 
      OleDbCommand cmd_delete = new OleDbCommand(
      «delete from country where country=:country», con1);
      cmd_delete.Parameters.Add(«country», OleDbType.BSTR);

      cmd_select.Parameters[«country»].Value = «Russia»;
      cmd_delete.Parameters[«country»].Value = «Russia»;
      Assert.AreEqual(1, cmd_select.ExecuteScalar());
      Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

      cmd_select.Parameters[«country»].Value = «Latvia»;
      cmd_delete.Parameters[«country»].Value = «Latvia»;
      Assert.AreEqual(1, cmd_select.ExecuteScalar());
      Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

      scope.Complete(); //commit   

   } //rollback           
}

public void SQLTransactionTest()
{
    //Description: SET TRANSACTION READ ONLY, COMMIT RETAIN, Firebird ADO Net, C#
    OleDbConnection con1 = ConnectionProvider.CreateConnection();
    con1.Open();

    OleDbCommand cmd = new OleDbCommand(
    «SET TRANSACTION READ ONLY WAIT ISOLATION LEVEL READ COMMITTED», con1);
    cmd.ExecuteNonQuery();

    cmd.CommandText = «select count(*) from employee»;
    Assert.AreNotEqual(0, cmd.ExecuteScalar());

    //commit changes without closing a transaction context
    cmd.CommandText = «COMMIT RETAIN»;
    cmd.ExecuteNonQuery();

    cmd.CommandText = «select count(*) from employee»;
    Assert.AreNotEqual(0, cmd.ExecuteScalar());

    cmd.CommandText = «COMMIT»;
    cmd.ExecuteNonQuery();

    con1.Close();
}

SAVEPOINT save_point_name

ROLLBACK TO SAVEPOINT save_point_name или COMMIT TO SAVEPOINT save_point_name

public void SavePointTest()
{
    //Description: SAVEPOINT, ROLLBACK TO SAVEPOINT, Firebird Net provider, InterBase c#
    OleDbConnection con1 = ConnectionProvider.CreateConnection();
    con1.Open();

    OleDbTransaction transaction = con1.BeginTransaction();

    OleDbCommand cmd_insert = new OleDbCommand(
    «insert into country (country,currency) values (:country,:currency)», con1, transaction);
    cmd_insert.Parameters.AddWithValue(«:country», «Russia»);
    cmd_insert.Parameters.AddWithValue(«:currency», «Ruble»);
    Assert.AreEqual(1, cmd_insert.ExecuteNonQuery());

    new OleDbCommand(«SAVEPOINT AFTER_INSERT_POINT», con1, transaction).ExecuteNonQuery();

    //delete country in using 3-level internal transaction context
    OleDbCommand cmd_delete = new OleDbCommand(
        «delete from country where country=?», con1, transaction);
    cmd_delete.Parameters.AddWithValue(«?», «Russia»);
    Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

    new OleDbCommand(«ROLLBACK TO SAVEPOINT AFTER_INSERT_POINT», con1, transaction).ExecuteNonQuery();

    //check what record was not deleted from Firebird database
    OleDbCommand cmd_check = new OleDbCommand(
        «select count(*) from country where country=?», con1, transaction);

    cmd_check.Parameters.AddWithValue(«?», «Russia»);
    Assert.AreEqual(1, cmd_check.ExecuteScalar());

    transaction.Rollback();
    con1.Close();
}

try
{
   OleDbConnection con = new OleDbConnection(«Provider=LCPI.IBProvider;Data Source=Empty;»);
   con.Open();
   con.Close();
}
catch (OleDbException ole_ex)
{
   foreach (OleDbError err in ole_ex.Errors)
   {
        Console.WriteLine(«Message:      « + err.Message);
        Console.WriteLine(«Native Error: « + err.NativeError);
        Console.WriteLine(«Source:       « + err.Source);
        Console.WriteLine(«SQL State   : « + err.SQLState);
   }
}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}

Message:      Required initialization property «Password».
Native Error: 0
Source:       LCPI.IBProvider.2
SQL State   :
Message:      Required initialization property «User ID».
Native Error: 0
Source:       LCPI.IBProvider.2
SQL State   :

public void ReadPropertiesTest()
{
   // Description: using OleDbProperties in IBProvider, Firebird (InterBase) c# or VB .Net
   OleDbConnection con = ConnectionProvider.CreateConnection();
   con.Open();

   //OleDbConnection properties
   OleDbProperties properties = OleDbProperties.GetPropertiesFor(con);
   Assert.AreNotEqual(0, properties.Count);
   PrintProperties(properties);

   //OleDbCommand properties
   OleDbCommand cmd = new OleDbCommand(«select * from country», con, con.BeginTransaction());

   properties = OleDbProperties.GetPropertiesFor(cmd);
   Assert.AreNotEqual(0, properties.Count);
   PrintProperties(properties);

   //Property from Rowset ole db group can be changed
   properties[«Use Bookmarks»].Value = true;
   Assert.IsTrue(Convert.ToBoolean(properties[«Use Bookmarks»].Value));

   //OleDbDataReader properties
   using (OleDbDataReader rdr = cmd.ExecuteReader())
   {
       properties = OleDbProperties.GetPropertiesFor(rdr);
       PrintProperties(properties);
   }

   con.Close();
}

private void PrintProperties(OleDbProperties properties)
{
   foreach (OleDbProperty  prop in properties.Values)
            Console.WriteLine((prop.Required ==true ? «[r] « : «») +
                               prop.Name + «=» + prop.ValueString);
}

public void OleDbServicesTest()
{
    const int connection_count = 50;

    //storage for tests results
    Dictionary<string, double> timeResults = new Dictionary<string, double>();

    OleDbConnectionStringBuilder builder =
    ConnectionProvider.GetConnectionStringBuilderFromUDL();

    //OLE DB SERVISES = alss services are enabled
    builder.OleDbServices = OleDbServicesValues.EnableAll;
    timeResults.Add(
    String.Format(«OLE DB Services=EnableAll ({0})», builder.OleDbServices),
    DoConnections(builder, connection_count));

    //OLE DB SERVISES = all services are disabled 
    builder.OleDbServices = OleDbServicesValues.DisableAll;
    timeResults.Add(
    String.Format(«OLE DB Services=DisableAll ({0})», builder.OleDbServices),
 	    DoConnections(builder, connection_count));

    //OLE DB SERVICES = all services are enabled exept client cursors
    //and supporting the operation outside the session’s boundaries 
    builder.OleDbServices = (OleDbServicesValues.EnableAll &
                           ~ OleDbServicesValues.ClientCursor &
                           ~ OleDbServicesValues.AggregationAfterSession);
    timeResults.Add(
    String.Format(«OLE DB Services=\n»+
                  «\tEnableAll & \n» +
                  «\t~ClientCursor & \n» +
                  «\t~AggregationAfterSession ({0})», builder.OleDbServices),
          DoConnections(builder, connection_count));

    foreach (string key in timeResults.Keys)
          Console.WriteLine(key + «. Seconds elapsed: « + timeResults[key]);
}

/// <summary>
/// Opens many connections and start transactions
/// </summary>
/// <param name=»builder»></param>
/// <param name=»cnt_connection»></param>
/// <returns>execution time</returns> 
private double DoConnections(OleDbConnectionStringBuilder builder,
     int cnt_connection)
{
    DateTime startTime = DateTime.Now;

    for (int i = 1; i <= cnt_connection; i++)
    {
        OleDbConnection con = new OleDbConnection(builder.ToString());
        con.Open();
        OleDbTransaction trans = con.BeginTransaction();
        trans.Commit();
        con.Close();
    }

    return DateTime.Now.Subtract(startTime).TotalSeconds;
}

public void FillDataSetFromDataAdapter()
{
   // Description: fill DataSet via OleDbDataAdapter, connect Firebird, InterBase
   DataSet ds = new DataSet();

   using (System.Transactions.TransactionScope scope =
 		new System.Transactions.TransactionScope())
   {
      OleDbConnection con = ConnectionProvider.CreateConnection();
      con.Open();

      OleDbDataAdapter adapter =
           new OleDbDataAdapter(«select * from EMPLOYEE», con);
      adapter.Fill(ds);

      Assert.IsTrue(ds.Tables[0].Rows.Count > 0);
      scope.Complete();
   }
}

public void FillDataSetFromDBReaderTest()
{
    // Description: filling DataSet using OleDbDataReader, ADO .Net Firebird and InterBase components
    OleDbConnection con = ConnectionProvider.CreateConnection();
    con.Open();
    OleDbCommand cmd =
      new OleDbCommand(«select * from EMPLOYEE»,con,   con.BeginTransaction());

    DataSet ds = new DataSet();
    DataTable tbl = ds.Tables.Add(«EMPLOYEE»);

    using (OleDbDataReader reader =
                           cmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
          ds.Load(reader, LoadOption.OverwriteChanges, tbl);
    }
}

public void GetDBReaderFromDataTable()
{
   // Description: DataTableReader — forwaronly reards virtual table, ADO Net, InterBase and Firebird components
   DataSet ds = new DataSet();
   DataTable tbl = ds.Tables.Add(«EMPLOYEE»);

   //loading dataSet 
   OleDbConnection con = ConnectionProvider.CreateConnection();
   con.Open();
   OleDbTransaction trans = con.BeginTransaction();

   ds.Load(new OleDbCommand(
           «select * from EMPLOYEE»,con,trans).ExecuteReader(),
           LoadOption.OverwriteChanges, tbl);

   //using forward only reading for DataTable
   this.PrintDBDataReader(new DataTableReader(tbl));

   //OleDbDataReader and connected mode
   this.PrintDBDataReader(
        new OleDbCommand(«select * from EMPLOYEE», con,  trans).ExecuteReader());

   trans.Commit();
   con.Close();
}

/// <summary>
/// Prints DBDataReader
/// </summary>
/// <param name=»reader»></param>
public void PrintDBDataReader(System.Data.Common.DbDataReader reader)
{
     while (reader.Read())
     {
        Console.WriteLine(«*********************************»);
        for (int i = 0; i < reader.FieldCount; i++)
           Console.WriteLine(reader.GetName(i) + «=» + reader[i].ToString());
     }

     reader.Close();
}

public void UpdateDataSet()
{
   // Description: OleDbDataAdapter.Update(DataTable) — store changes into Firebird and InterBase DBMS, c#
   DataSet ds = new DataSet();
   DataTable tbl = ds.Tables.Add(«EMPLOYEE»);

   OleDbConnection con = ConnectionProvider.CreateConnection();
   con.Open();
   OleDbDataAdapter adapter = new OleDbDataAdapter(«select * from EMPLOYEE», con);
   adapter.SelectCommand.Transaction = con.BeginTransaction();
   adapter.Fill(tbl);

   //change values in DataSet
   foreach (DataRow row in tbl.Rows)
      row[«FIRST_NAME»] = row[«FIRST_NAME»].ToString().ToUpper();

   //build commands for update, insert и delete
   OleDbCommandBuilder cmd_builder = new OleDbCommandBuilder(adapter);
   adapter.DeleteCommand = cmd_builder.GetDeleteCommand();
   adapter.UpdateCommand = cmd_builder.GetUpdateCommand();
   adapter.InsertCommand = cmd_builder.GetInsertCommand();

   //perform update
   adapter.Update(tbl);

   //rollback
   adapter.SelectCommand.Transaction.Rollback();
   con.Close();
}