Office Test - Tutorial 7

The 7th page of the OfficeTest General Tests tutorial looks at using select stored procedures. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

7. Select Stored Procedures.

    One nice feature of Firebird is the ability to create select stored procedures. A select procedure can be used in 2 different ways. You can either simply call the procedure and retrieve the records returned or you can select from the procedure as if it was a table. The 2nd part is what makes it very handy. This allows you to easily filter and order the results and even decide to return only the columns you require. Most databases support the first part - returning a result set - but not the second the part.

    This is a very powerfull feature because the stored procedure ends up working like a view that supports an entire programming langauge.

    The example web page is very simple. It provides a dropdown list containing all the possible JobPositions. You can select any single JobPosition and then the GridView below it is filled with the results of all Employees for that JobPosition. If the checkbox is checked then the stored procedure is called directly and all columns are returned. If the checkbox is not checked then a select from the stored procedure is called which selects only the firstname.

    Employee: Stored Procedure/Trigger

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

    GetEmployeesByJob

    CREATE PROCEDURE "GetEmployeesByJob" ( "JobPositionID"  INTEGER )
    RETURNS ( "EmployeeID"  INTEGER, "FirstName"  VARCHAR(50), "LastName"  VARCHAR(50) )
    AS
    BEGIN
      FOR select "EmployeeID", "FirstName", "LastName"
          from "Employee"
          where "JobPositionID" = :"JobPositionID"
          into :"EmployeeID", :"FirstName", :"LastName" DO
        SUSPEND;
    END
       

    This stored procedure simply takes 1 parameter for the JobPositionID and does a select from the Employee table using it as a filter. Notice that SUSPEND is called on each pass of the loop to send that row of data out as return record. The RETURNS must also be defined. Here we have EmployeeID, FirstName and LastName.

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) {
      string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
      FbConnection con = new FbConnection(connectionString);
      try {
        con.Open();
        FbCommand cmd;
        if (cbGetAllColumns.Checked) {
          cmd = new FbCommand("\"GetEmployeesByJob\"", con);
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.Clear();
          cmd.Parameters.Add("@JobPositionID", Int32.Parse(lstJob.SelectedValue));
        } else {
          cmd = new FbCommand("SELECT \"FirstName\" FROM \"GetEmployeesByJob\"(@JobPositionID)", con);
          cmd.CommandType = CommandType.Text;
          cmd.Parameters.Clear();
          cmd.Parameters.Add("@JobPositionID", Int32.Parse(lstJob.SelectedValue));
        }
        FbDataReader reader = cmd.ExecuteReader();
        try {
          gvEmployees.DataSource = reader;
          gvEmployees.DataBind();
        } finally {
          reader.Close();
        }
      } finally {
        con.Close();
      }
    }
       

    Whether the stored procedure is used directly or a select is performed we still setup the FbCommand and FbDataReader the same way. When the reader is created is can be assigned to the GridViews (gvEmployees) DataSource property. Calling DataBind will then force a refresh of the GridView with the readers data.

    Stored Procedure

    cmd = new FbCommand("\"GetEmployeesByJob\"", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@JobPositionID", Int32.Parse(lstJob.SelectedValue));
       

    In this case the command is just the name of the stored procedure. Keep in mind I need to add \" around the name to maintain case because the database is dialect 3. The CommandType is of course StoredProcedure. Then we simply add one parameter to the stored procedure and set it to the SelectedValue property of the dropdown list for JobPosition. The command will automatically create a reader with the rows returned by the stored procedure. The result of this call will have 3 columns - EmployeeID, FirstName and LastName.

    Selecting from a procedure

    cmd = new FbCommand("SELECT \"FirstName\" FROM \"GetEmployeesByJob\"(@JobPositionID)", con);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@JobPositionID", Int32.Parse(lstJob.SelectedValue));
       

    Here we switch CommandType to Text since we are really performing a select, not executing a stored procedure. The SQL for the select must contain the parameter for the stored procedure as well. The same row results will be return as in the previous case, since we didn't add a WHERE clause, but with only the FirstName column.

    Well thats really all there is to it. There are times when putting additional logic in the stored procedure will provide better performance by reducing the data returned to the calling application. However the application may still want to tailor the information it receives. This mechanism provides an elegant solution to that problem.

See next Tutorial...Stored Procedure web page which calls a stored procedure with no result set and retrieves a computed value.