containing the employee’s new salary.
In the Model Browser under HRModel
.Store, click the Stored Procedures node, and
then right-click UPDATE_AND_RETURN_
SALARY_BINDV. From the menu, choose Add
Function Import. In the Add Function Import
dialog box, select None for Returns a Collection
Of (because the Oracle stored procedure did
not include a SYS_REFCURSOR, this function
cannot return a value), and click OK.
In your project’s program file, uncomment the next block of code—// CALLING
A STORED PROCEDURE VIA IMPORT
FUNCTION (USING OUTPUT BIND
VARIABLE)—shown in Listing 3. Note that
the code in Listing 3 includes the following
line to set up the output bind variable:
Code Listing 3: Code for calling stored procedure in Program.cs
// CALLING A STORED PROCEDURE VIA IMPORT FUNCTION (RETURNING VALUES)
int empid = 100;
int raise = 9000;
foreach (var result in ctx.UPDATE_AND_RETURN_SALARY(empid, raise))
{
Console.WriteLine("Name: " + result.FIRST_NAME + " Updated Salary: " +
result.SALARY);
}
Console.WriteLine();
Console.ReadLine();
// CALLING A STORED PROCEDURE VIA IMPORT FUNCTION (USING OUTPUT BIND VARIABLE)
int empid = 100;
int raise = 1111;
ObjectParameter newsal = new ObjectParameter("NEWSAL", typeof(decimal));
ctx.UPDATE_AND_RETURN_SALARY_BINDV(empid, raise, newsal);
Console.WriteLine( newsal.Value);
ObjectParameter newsal = new
ObjectParameter("NEWSAL",
typeof(decimal));
The first argument to this particular
ObjectParameter constructor must be set to
the name of the PL/SQL output parameter.
Build the code and step into it. Take a look
at the output window, and you can see that
the employee’s updated salary is displayed
there and no configuration change to the
App.config file was required.
S TORED PROCEDURE MAPPINGS
The entity model also supports “mapping”
stored procedures to insert, update, and
delete operations on Entities. When these
operations occur, the mapped stored procedure will fire. For this article, the INSERT_
EMPLO YEE procedure will fire every time a
new Employee entity is created. This stored
procedure was included in the HR_SP. SQL
script you ran earlier, and because it returns
an output value, it must include an output
SYS_REFCURSOR in its parameter list. (The
metadata for that REF CURSOR was included
when you earlier copied the contents of the
App.Config.txt file to the App.Config file. )
To enable this stored procedure mapping,
first open up Entity Designer, right-click
Employee, and select Stored Procedure
Mapping. This will open the Mapping Details
pane at the bottom of the designer (as
shown in Figure 3).
In Mapping Details, select <Select
Insert Function> and select the INSERT_
EMPLOYEE stored procedure. By default,
mappings will be created between the entity
and the procedure. Notice that this Oracle
stored procedure accepts as its parameters
all of the data for the Employee entity except
for the EMPLOYEE_ID. The stored procedure
uses a sequence to generate an EMPLOYEE_
ID, inserts a row into Oracle Database, and
then returns the generated EMPLOYEE_ID
inside of a SYS_REFCURSOR. This ensures
that the Entity is consistent.
TRIGGERS AND SEQUENCES
In addition to working with stored procedures that generate sequences, developers also work with triggers that generate sequences. Developers using Entity
Framework will need to make sure that their
entities are able to receive the new values
being generated by triggers.
To try out triggers and sequences with
entities, first remove the Stored Procedure
Mapping you just created so that there is
no special processing occurring when a new
Employee entity is created.
