Office Test - Tutorial 8

The 8th page of the OfficeTest General Tests tutorial looks at using a simple stored procedure to add 2 numbers and return the sum. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

8. Calling a Stored Procedure.

The web page for this tutorial just provides a very simple and basic example of using a stored procedure for some sort of computation (not a result set) and retieving a result from it. In this case the page allows the user to provide 2 integers and calls a stored procedure to add the values and return the sum.

Stored Procedure Page


I won't spend any time on the HTML since it's very straight forward and not the focus of this tutorial.

AddTwoValues


CREATE PROCEDURE "AddTwoValues" ( "Value1"  INTEGER, "Value2"  INTEGER )
RETURNS ( "TheSum"  INTEGER )
AS
BEGIN
  "TheSum" = "Value1"+"Value2";
END
   

This is about as simple as we can get for a stored procedure. "TheSum" is a RETURNS paramter for the procedure.
protected void cmdAddTwoValues_Click(object sender, EventArgs e) {
  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  try {
    con.Open();
    FbCommand cmd = new FbCommand("\"AddTwoValues\"", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@Value1", Int32.Parse(txtNum1.Text));
    cmd.Parameters.Add("@Value2", Int32.Parse(txtNum2.Text));
    FbParameter param = new FbParameter("@TheSum", TypeCode.Int32);
    param.Direction = ParameterDirection.ReturnValue;
    cmd.Parameters.Add(param);
    cmd.ExecuteScalar();
    txtAddTwoValues.Text = param.Value.ToString();
  } finally {
    con.Close();
  }
}
   

As we have shown in previous examples CommandType must be set to StoredProcedure. An FbParameter is created separately so we can assign its DataType to Int32 and the Direction to ReturnValue.

Whats important here and new is how we execute the stored procedure. ExecuteScalar must be called on the FbCommand because we do not want or expect a result set. Once we call ExecuteScalar we can now go back and get the Value of the FbParameter we created for the return value.

That is really all there is to know about calling a stored procedure. Just remember ExecuteScalar is used for non dataset procedures.

See next Tutorial...BillingType: Strongly Typed DataSets web page which uses Visual Studio 2005's built-in dataset tool and ObjectDataSources.