Invoking Stored Procedures

It is sometimes necessary to encapsulate very complex queries as stored procedures.  You can invoke a stored procedure from LightSpeed either to load a set of entities, to calculate a single value such as a count or total, or just to execute it with no return value.

To add a stored procedure to the designer, drag it onto the designer surface.  LightSpeed will create a SelectProcedure, ScalarProcedure or NonQueryProcedure accordingly depending on the result schema of the procedure.  For a SelectProcedure, LightSpeed tries to identify the type of entity being returned; if no existing entity is suitable then LightSpeed creates a new entity class based on the result schema.  In any case, LightSpeed generates a method on the unit of work to invoke the procedure.

Invoking a Stored Procedure Using LINQ

To invoke a stored procedure, call the appropriate method on the unit of work.

using (StoreUnitOfWork unitOfWork = _context.CreateUnitOfWork())
{ // SelectProcedure returns a collection of entities
  var expensiveProducts = unitOfWork.GetProductsCostingMoreThan(10000);
  // ScalarProcedure returns a value
  var totalPendingOrderValue = unitOfWork.GetTotalPendingOrderValue();
  // NonQueryProcedure does not return a value
    unitOfWork.ArchiveInactiveCustomers(DateTime.Now.AddMonths(-6));
}

If the stored procedure has output or input-output parameters, these will appear as out or ref parameters to the method.

Invoking a Stored Procedure Using Query Objects

If you are not using the designer, you must use query objects to invoke a stored procedure.  (The LINQ methods shown above use query objects internally, but are generated by the designer so that you do not need to work with the query objects directly.)

The key class for using stored procedures with query objects is ProcedureQuery.  ProcedureQuery encapsulates the name of the procedure and any parameters you want to pass to it.  You can pass a ProcedureQuery to one of three methods defined on IUnitOfWork:

·         To load entities, call the Find method.

·         To get a single scalar value, call the Calculate method.

·         To execute an action (no return value), call the Execute method.

using (IUnitOfWork unitOfWork = _context.CreateUnitOfWork())
{ // Loading entities
  ProcedureQuery entityQuery = new ProcedureQuery("GetProductsCostingMoreThan",
    new ProcedureParameter("minCost", 10000));
  var expensiveProducts = unitOfWork.Find<Product>(entityQuery);
  // Getting a single value ProcedureQuery valueQuery = new ProcedureQuery("GetTotalPendingOrderValue");
  var totalPendingOrderValue = unitOfWork.Calculate(valueQuery);
  // Executing an action
  ProcedureQuery actionQuery = new ProcedureQuery("ArchiveInactiveCustomers",
    new ProcedureParameter("olderThan", DateTime.Now.AddMonths(-6)));
  unitOfWork.Execute(actionQuery);
}

The query object API uses ProcedureParameter objects to represent stored procedure parameters.  You can declare parameters with direction Output, InputOutput or ReturnValue to receive values returned by the stored procedure through parameters or as the return value.

Database Considerations for Stored Procedures

When loading entities through a stored procedure, 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 Column Name setting (or ColumnAttribute) is respected as normal.

·         The returned record set must also contain an Id column. If the Identity Column Name is set for the entity (or the entity has TableAttribute.IdColumnName), then the returned record set must contain a column with this name instead.

When using stored procedures on Oracle, you must follow a special convention for returning results.  See the chapter Working with Database Providers for more information.

Additional Support for Stored Procedures

For information about other ways of using stored procedures with LightSpeed, see the chapter Working with Legacy Databases.