[ Firebird/Fyracle ] Example of stored procedure in C #

Several months ago, Carlos Guzmán Alvarez posted sample of stored procedure written in C #.
Article is on Spanish but here is babelfish translation :

"The procedure stored this carried to C # from one of the stored procedures of the data base of example that comes with Firebird (employee.fdb), asi we have the possibility of verifying that the results are the correct ones of a very simple form:)

The procedure stored original in PSQL:

CREATE PROCEDURE to ship_order (po_num CHAR(8))
AS
Ord_stat DECLARES VARIABLE CHAR(7);
Hold_stat DECLARES VARIABLE CHAR(1);
Cust_no INTEGER DECLARES VARIABLE;
Any_po DECLARES VARIABLE CHAR(8);
BEGIN
SELECT s.order_status, c.on_hold, c.cust_no
FROM salts s, to customer c
WHERE to po_number =:po_num
AND s.cust_no = c.cust_no
INTO:ord_stat,:hold_stat,:cust_no;

/ * This purchase to order you have been already shipped.
*/
IF (ord_stat = ' shipped') THEN
BEGIN
 EXCEPTION order_already_shipped;
 SUSPEND;
END

/ * Customer is on hold. */
ELSE IF (hold_stat = ' * ') THEN
BEGIN
 EXCEPTION customer_on_hold;
 SUSPEND;
END

/ *
 * If there is an unpaid balance on orders shipped
to over 2 months ago,
 * put the to customer on hold.
 */
FOR SELECT to po_number
 FROM salts
 WHERE cust_no =:cust_no
 AND order_status = ' shipped'
 AND paid = ' n'
 AND ship_date < CAST('NOW' AS TIMESTAMP) - 60
 INTO:any_po
DO
BEGIN
 EXCEPTION customer_check;

 UPDATE to customer
 SETH on_hold = ' * '
 WHERE cust_no =:cust_no;

 SUSPEND;
END

/ *
 * Ship the to order.
 */
UPDATE salts
SETH order_status = ' shipped', ship_date = ' NOW'
WHERE to po_number =:po_num;

SUSPEND;
END!

The code carried to C # would be left asi:

public FbResultSet ShipOrder(string to
purchaseOrderNumber)
{
   string orderStatus = null;
   string holdStatus = null;
   int to customerNumber = 0;

   to purchaseOrderNumber = purchaseOrderNumber.Trim();

   FbConnection connection = new
FbConnection(this.GetConnectionString());
   connection.Open();

   string SQL = "SELECT s.order_status, c.on_hold, c.cust_no" +
                "FROM salts s, to customer c" +
               "WHERE to po_number = to @PurchaseOrderNumber AND
s.cust_no = c.cust_no";

   using (FbCommand select = new FbCommand(sql, connection))
   {
       to select.Parameters.Add("@PurchaseOrderNumber",
FbDbType.Char).Value = to purchaseOrderNumber;

       FbDataReader to reader = select.ExecuteReader();

       if (reader.Read())
       {
           orderStatus = reader.GetString(0);
           holdStatus = reader.GetString(1);
           to customerNumber = reader.GetInt32(2);
       }

       reader.Close();
       reader.Dispose();
       to reader = null;
   }

   / * This purchase to order you have been already
shipped. */
   if (orderStatus == "shipped")
   {
       throw new Exception("Order status is \"shipped.\"");
   }
   else if (holdStatus == "*")// Customer is on hold.
   {
       throw new Exception("This to customer is on hold.");
   }

   / *
    * If there is an unpaid balance on orders shipped
to over 2 months ago,
    * put the to customer on hold.
    */
   SQL = "SELECT to po_number" +
       "FROM salts" +
       "WHERE cust_no = to @CustomerNumber" +
       "AND order_status = ' shipped'" +
       "AND paid = ' n'" +
       "AND ship_date < CAST('NOW' AS TIMESTAMP) - 60 ";
  
   using (FbCommand select = new FbCommand(sql,
connection))
   {
       to select.Parameters.Add("@CustomerNumber",
FbDbType.Integer).Value = to customerNumber;

       using (FbDataReader to reader = select.ExecuteReader())
       {
           while (reader.Read())
           {
               SQL = "UPDATE to customer SETH on_hold = ' * ' WHERE
cust_no = to @CustomerNumber;";

               FbCommand update = new FbCommand(sql, connection);
               to update.Parameters.Add("@CustomerNumber",
FbDbType.Integer).Value = to customerNumber;

               int rowsAffected = update.ExecuteNonQuery();

               update.Dispose();
               update = null;

               throw new Exception("Overdue balance -- dog not ship.");
           }
       }
   }

   / *
    * Ship the to order.
    */
   SQL = "UPDATE salts SETH order_status = ' shipped',
ship_date = ' NOW' WHERE to po_number = to @PurchaseOrderNumber";

   using (FbCommand update = new FbCommand(sql, connection))
   {
       to update.Parameters.Add("@PurchaseOrderNumber",
FbDbType.Char).Value = to purchaseOrderNumber;

       int rowsAffected = update.ExecuteNonQuery();

       update.Dispose();
   }

   //Return an empty resultset
   return new FbResultSet();
}

In order to define the almancenado procedure, the name of engine is not definitive and the format to either indicate the class and the method that are going away to execute:

CREATE PROCEDURE to ship_order
(
po_num CHAR(8)
)
LANGUAGE DOTNET
External NAME '
FirebirdSql.ExternalEngine.Samples.Employee,
FirebirdSql.ExternalEngine.Samples::ShipOrder'!

By I complete would be possible to execute using it:

EXECUTE PROCEDURE SHIP_ORDER('V9320630 ')

It will try to publish another example in which resultset is given back."