LightSpeed supports the use of stored procedures and user-defined functions for selecting entities. This support is database-dependent.
Selecting Entities Using a Stored Procedure
To select entities, call IUnitOfWork.Find, passing a ProcedureQuery. The ProcedureQuery must specify the name of the stored procedure in the database, and provide any input or output parameters expected by the stored procedure in the form of ProcedureParameter objects. The Find method returns the list of entities returned by the stored procedure.
Saving Changes Using Stored Procedures
If you wish to use stored procedures for all database access, including inserts, updates and deletes, you must declare your entities as procedure-backed, and specify the stored procedures for each CRUD operation (select, insert, update and delete), plus for select by ID. To do this:
- In code, specify CrudProcedureAttribute on the entity type, providing the procedure names.
- In the designer, set the Access Method to StoredProcedures. This enables the Select Procedure, Select By Id Procedure, Insert Procedure, Update Procedure and Delete Procedure properties. Configure these as required.
In either case, the Select and Select By Id procedures must adhere to the standard stored procedure requirements and conventions described below. In addition, Select By Id must take a single parameter named Id (the parameter name can be overridden using Identity Column Name or TableAttribute.IdColumnName).
Stored Procedure Requirements and Conventions
- The stored procedure must return a record set.
- Oracle does not support record sets as the return value of a procedure, so on Oracle the record set must be returned through a out parameter of ref cursor type, named “results.”
The returned record set is treated exactly the same as if it had been returned by performing a table SELECT. Therefore:
- The set of columns in the record set must correspond in name and type to the fields of the entity. The ColumnAttribute (in the designer, the Column Name setting) is respected as normal.
- The returned record set must also contain an Id column. If the entity type being returned has the TableAttribute and the IdColumnName is set (equivalently, in the designer, the Identity Column Name is set for the entity), then the returned record set must contain a column with this name instead.
Stored Procedures in the Designer
If you model stored procedures in the designer, the designer generates
methods on the strong-typed unit of work which create the ProcedureQuery
and ProcedureParameter objects for you, and present a conventional .NET
method interface such as IList<Product> GetCheapItems(decimal maxPrice).
