New features of Firebird 2.0. Examples for ADO.NET (c#)

public void ExecuteBlockSQLTest()
{
    // Description: EXECUTE BLOCK — lets performing a block of instructions on
    //the server side of Firebird 2.0. or above
    OleDbConnection con = OpenFB2Connection();
    OleDbTransaction trans = con.BeginTransaction();

    //server side command text
    string execute_block_data =
    «EXECUTE BLOCK (X INTEGER = :X) \n» +
    «RETURNS (Y INTEGER)           \n» +
    «AS                            \n» +
    «BEGIN                         \n» +
    »    Y = X * 2;                \n» +
    «SUSPEND;                      \n» +
    «END                           \n»;

    //in parameter
    int in_parameter_X = 2;

    OleDbCommand cmd = new OleDbCommand(execute_block_data, con, trans);
    cmd.Parameters.AddWithValue(«X», in_parameter_X);

    //run EXECUTE BLOCK query
    Assert.AreEqual((int)cmd.ExecuteScalar(), in_parameter_X * 2);
    trans.Commit();

    con.Close();
}

public void InsertReturning()
{
    // Description: INSERT RETURNING — it lets reading values that were added while performing the operation,
    //connect to Firebird 2.0 or above
    OleDbConnection con = OpenFB2Connection();
    OleDbTransaction trans = con.BeginTransaction();

    //INSERT RETURNING command
    OleDbCommand cmd = new OleDbCommand(
    «insert into customer (cust_no, customer)     \n» +
    «values(GEN_ID(CUST_NO_GEN,1),:customer_name) \n» +
    «RETURNING cust_no»,con,trans);

    cmd.Parameters.AddWithValue(«customer_name», «New customer»);
    //add output parameter
    cmd.Parameters.Add(«customer_no», OleDbType.Integer)
 				.Direction =ParameterDirection.Output;

    Assert.AreEqual(1, cmd.ExecuteNonQuery());

    //delete record
    OleDbCommand cmd_delete = new OleDbCommand(
    «delete from customer where cust_no=?», con, trans);

    cmd_delete.Parameters.AddWithValue(«?»,cmd.Parameters[«customer_no»].Value);
    Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

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

public void RollbackRetainTest()
{
    // Description: ROLLBACK RETAIN — пlets roll back transaction at the starting moment,
    //Firebird 2.0 and above feature
    OleDbConnection con = OpenFB2Connection();
    OleDbTransaction trans = con.BeginTransaction();

    //insert new record 
    OleDbCommand cmd = new OleDbCommand(
    «insert into customer (cust_no, customer)    « +
    «values(GEN_ID(CUST_NO_GEN,1),’New customer’)», con, trans);
    Assert.AreEqual(1, cmd.ExecuteNonQuery());

    //ROLLBACK RETAIN 
    new OleDbCommand(«ROLLBACK RETAIN», con, trans).ExecuteNonQuery();

    // transaction is active
    cmd = new OleDbCommand(
        «select count(*) from customer», con, trans);

    Assert.IsTrue((int)cmd.ExecuteScalar() > 0 );

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

public void RowsKeywordTest()
{
      // Description: ROWS — lets specifying the processed rows number in Firebird 2.0
      OleDbConnection con = OpenFB2Connection();
      OleDbTransaction trans = con.BeginTransaction();

      //command will return 3 records
      OleDbCommand cmd = new OleDbCommand(
      «select * from customer rows 1 to 3», con, trans);

      short rec_count = 0;

      using (OleDbDataReader reader = cmd.ExecuteReader())
          while (reader.Read()) { rec_count++; }

      Assert.AreEqual(3, rec_count);

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

select * from
  (select EMP_NO, FIRST_NAME || ‘ ‘ || LAST_NAME from employee)
      as EMPLOYEE_DERIVED_TABLE (ID, FULL_NAME)