Office Test - Tutorial 5

The 5th page of the OfficeTest General Tests tutorial covers stored procedures and briefly looks at using triggers. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example. The 3rd tutorial (Employee: Simple) is similar to this one except that stored procedures and triggers are not used. I won't be covering the basic setup in this tutorial since it's already covered in the earlier one.

NOTE: You may find the test application gives insert errors when using the trigger example. This will occur if you have performed inserts using the other web pages and provided your own keys. The gEmployeeID generator may be out of sync. Just hit the insert several times and ignore the error. The generator will eventually more forward past your other keys values. Otherwise go back and delete those additional rows.

5. Using stored procedures and triggers.

    Again we will look at the Employee table. This time we are using a single SqlDataSource for retrieving the Employees data and one for JobPosition data. A GridView is used for display and deleting. A DetailsView is used for inserting. There is another separate SqlDataSource for the Stored Procedure DetailsView.

    Employee: Stored Procedure/Trigger

    The related triggers and stored procedures can be found in the test database or you can view them with IBDataWorks if you own it. I will show them here as well so its not necessary to go look them up.

    Insert Employee Trigger

    CREATE TRIGGER Employee_INSERT FOR "Employee" ACTIVE BEFORE INSERT
    AS
    BEGIN
      IF (NEW."EmployeeID" is NULL) THEN
        NEW."EmployeeID" = GEN_ID("gEmployeeID", 1);
    END
       

    This trigger fires automatically on every insert into the Employee table. It checks to see if the primary key (EmployeeID) is NULL. If its NULL then it uses the generator (gEmployeeID) to generate the next primary key before the insert happens. If an EmployeeID is provided then it does nothing.

    Insert Employee stored procedure

    CREATE PROCEDURE insertEmployee (FirstName varchar(50), LastName varchar(50), HireDT date, JobPositionID integer)
    RETURNS ( EmployeeID INTEGER )
    AS
    BEGIN
      EmployeeID = GEN_ID("gEmployeeID",1);

      INSERT INTO "Employee"("EmployeeID", "FirstName", "LastName", "HireDT", "JobPositionID")
        VALUES(:EmployeeID, :FirstName, :LastName, :HireDT, :JobPositionID);
    END
       

    This stored procedure accepts all Employee table fields except the EmployeeID as parameters. It generates the EmployeeID using the gEmployeeID generator and then does an insert into the Employee table. The EmployeeID is also a RETURN parameter so the code calling this stored procedure can get access to the primary key used for the insert.

    The C# code: EmployeeTriggerStoredProc.aspx.cs
    There are 4 functions in the C# code. The first 3 are the same standard ones used in the other pages we've looked at so far. The last function is not necessary but I added it to retrieve the values of the stored procedure parameters after an insert. The dsInsertWithStoredProc_Inserted just helps demonstrate that we can get the primary key value generated inside the stored procedure back after the insert.

    The HTML code: EmployeeTriggerStoredProc.aspx
    Let's concentrate on the HTML part of the code. I'll go over any properties for the objects that may not be obvious.

    The dsAllEmployees SqlDataSource is straight forward as it works exactly as it would if no trigger was involved. This is because it doesn't need to know about the trigger at all. The trigger fires automatically during any insert.

    The dsJobPosition SqlDataSource is also just a standard select of all the JobPosition records.

    SqlDataSource: dsInsertWithStoredProc

    <asp:SqlDataSource ID="dsInsertWithStoredProc" runat="server"
      InsertCommandType="StoredProcedure" InsertCommand='INSERTEMPLOYEE'>
      <InsertParameters>
        <asp:Parameter Name="FirstName" />
        <asp:Parameter Name="LastName" />
        <asp:Parameter Name="HireDT" />
        <asp:Parameter Name="JobPositionID" />
        <asp:Parameter Direction="ReturnValue" Name="EmployeeID" />
      </InsertParameters>
    </asp:SqlDataSource>
       

    I've only listed the properties that need our attention. Since this is an example of setting up an insert through a stored procedure we will focus on the InsertCommand.

    • InsertCommandType="StoredProcedure": We need to set the insert command type to stored procedure.
    • InsertCommand='INSERTEMPLOYEE': The command itself is the name of the stored procedure. Notice the full capitals and single quotes (') around the name. This is because the database is dialect 3 and the stored procedure was created in full uppercase.
    • asp:Parameter Name="FirstName": This is just a standard input parameter to the stored procedure. Notice that FirstName, LastName, HireDT and JobPosition are all input parameters. Check the syntax of the stored procedure above to see that this matches our stored procedure parameter list.
    • asp:Parameter Direction="ReturnValue" Name="EmployeeID": The INSERTEMPLOYEE stored procedure also has 1 return parameter so we can get back the primary key. This parameter must be set to Direction="ReturnValue" in order to retrieve that value.

    The ReturnValue is the most common mistake many people make. If you open the InsertQuery property dialog and click the "Show advanced properties" link:

    You can see here how to setup the parameter. Remember you must use Direction="ReturnValue" not Output or InputOutput.

    The dsJobPosition SqlDataSource is also just a standard select of all the JobPosition records.

    The dvEmployees and DetailsView1 DetailsViews are also straight forward setups. dvEmployees uses the dsAllEmployees SqlDataSource and DetailsView1 uses the dsInsertWithStoredProc SqlDataSource. You should notice that for both DetailsViews the EmployeeID is not defined as a field. In the case of dvEmployees the EmployeeID is passed to the insert as NULL which fires the trigger and allows it to generate the primary key. In the case of DetailsView1 the stored procedure doesn't require the EmployeeID so there is no need for it at all.

    In this particular example either case works just fine. The trigger example is marginally easier. In more advanced situations the stored procedure will likely prove the better option since it gives the most control and also makes it easier to retrieve values (such as the primary key) back without additional scripting.

See next Tutorial...Employee: DataSet web page which manually creates a dataset with multiple tables and then links the tables in memory.