Calling a stored procedure

LLBLGen Pro supports stored procedures by offering the ability to define calls to those stored procedures. There are two types of stored procedures: procedures which do not return a resultset, called Action Stored Procedures, and procedures which return one or more resultsets, which are called Retrieval Stored Procedures.

This section illustrates how call definitions to these stored procedures in your project are generated in code and how you can use them in your code. Classes with stored procedure calls are stored in the database specific Visual Studio project.

To fetch a TypedView mapped onto a resultset of a stored procedure please see using the TypedView classes, Adapter.

Retrieval Stored Procedure Calls

When you add a call definition for a retrieval stored procedure to the project in the LLBLGen Pro designer, a static/shared method that will call that stored procedure will be added to a class called RetrievalProcedures.

If the stored procedure returns a single resultset, the return value of the generated method will be a DataTable. When the stored procedure returns more than one resultset, the return value of the generated method will be a DataSet, containing each resultset in a separate DataTable.

For example, if we add a call definition to our LLBLGen Pro project to the procedure in Northwind called CustOrderDetail, taking one parameter, an OrderID, a static method called CustOrderDetail is created, returning a DataTable (because the procedure returns a single resultset) and accepting a single parameter, orderID, which is of type int/Integer because the parameter itself is of type integer. To utilize this method in your own code, you can call as shown below. For the orderID, the value 10254 is passed as the parameter value:

DataTable resultSet = RetrievalProcedures.CustOrderDetail(10254);

Because the stored procedure call methods are located in the database specific project, they will create a new DataAccessAdapter object if not such an object is supplied, which is the case in our example above. If you want to use an existing DataAccessAdapter, for example because you want the stored procedure to run inside an existing transaction, you can specify that existing adapter in the method call as an extra parameter.

Output parameters are also supported. When a stored procedure has an output parameter, a parameter representing the output parameter in the stored procedure is added to the method heading and is defined as 'ref' (C#) or ByRef (VB.NET). Illustrated below is the call to an imaginary stored procedure which returns a datatable, takes 4 input parameters and returns a value in an output parameter:

int outputValue;
DataTable resultSet = RetrievalProcedures.MyStoredProcedure(1, 2, 3, 4, ref outputValue);

Dim outputValue as Integer
Dim resultSet As DataTable = RetrievalProcedures.MyStoredProcedure(1, 2, 3, 4, ByRef outputValue)

Action Stored Procedure Calls

If you have added a call to a procedure to your project and the stored procedure doesn't return a resultset, the static/shared method is added to the class ActionProcedures. Instead of returning a DataTable or DataSet, a method in this class returns an int/Integer, which represents the return value of the ExecuteNonQuery() method, which is the number of rows affected if the database has row counting enabled (and the stored procedure doesn't switch it off).

Otherwise the action stored procedure methods work the same as the retrieval stored procedures mentioned above: input parameters are defined as normal parameters for the method and output parameters are defined as ref/ByRef parameters.

Async calls to Action Stored Procedures

You can also call the action procedures asynchronically: for each action stored procedure, besides a normal call method, also an async overload has been generated, suffixed with Async. Stored procedures which return cursors don't have an async variant.

If the stored procedure has output parameters, the Async variant will return an instance of a generated struct with the name StoredProcedureNameResult. This struct has for every output parameter a property and a property called *_ProcReturnValue*, which is the return value of the stored procedure.

If the stored procedure doesn't have output parameters the method will return an int, which is the return value.

InputOutput parameters and async method calls

If the output parameters are also input parameters, in normal stored procedure methods the arguments are ref arguments in the method signature. This isn't possible for async methods and therefore they're specified as normal arguments: the input value for the parameters has to be specified through the method arguments, and the output value is returned in the instance of the generated struct.

Example:

var result = await ActionProcedures.MyProcWithOutputParameterAsync(
                                        string.Empty, CancellationToken.None);
var outputParamValue result.TheOutputParameter);

Here the 'TheOutputParameter' parameter for the procedure is an input/output parameter and therefore has a method argument, which receives in the call above as value String.Empty. The output value is returned in the returned struct instance

Wrap call in IRetrievalQuery object

LLBLGen Pro offers you to get the call to a retrieval stored procedure as an IRetrievalQuery object. An IRetrievalQuery object is the query object generated by a Dynamic Query Engine (DQE) and which is executed by the low level fetch logic of LLBLGen Pro's O/R mapper core. The IRetrievalQuery object allows you to fetch a query as a datareader or to project the results of the stored procedure call onto a data-structure of your choice, for example an entity collection.

You retrieve an IRetrievalQuery object which wraps the call to a given stored procedure by calling the following generated method (each retrieval stored procedure has such a method generated):

IRetrievalQuery procCall = RetrievalProcedures.GetStoredProcedureCallNameCallAsQuery(parameters);

You can then pass the IRetrievalQuery object to the methods for fetching a datareader or fetch a projection. See for more information about fetching a datareader or fetching a projection: Fetching DataReaders and projections.