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)