Using Stored Procedures

On Linq to Sql LLBLGen Pro supports calls to stored procedures directly. This also means that you can't map entity insert/update/retrieve/delete actions to stored procedures.

There are two type of Stored Procedure Calls you can map in the LLBLGen Pro project: action stored procedures (calling a procedure, passing in parameters, no result set returned), and retrieval stored procedures (calling a procedure, passing in parameters, resultset is returned). The code they result into is described more in detail below.

The stored procedure calls are actually pure ADO.NET calls, and use the context's connection object but bypass all other Linq to Sql logic. You can utilize the building blocks of this code for additional code as well by adding a partial class to the context class.

Action Stored Procedure calls

When you map in the designer a Stored Procedure Call to a stored procedure which doesn't return a resultset, it's considered a call to an action procedure. For every call mapped, the designer will generate in the generated context class a method, with the name: CallStoredProcedureName.

The return value of the procedure is an integer, which represents the number of rows affected as reported by the RDBMS. The method generated has for each parameter of the stored procedure a parameter. If the parameter is an inputoutput or output parameter, the method parameter is generated as a call by reference and the value returned by the stored procedure is returned through the output parameter.

The example below calls the stored procedure pr_GetLastName. The stored procedure returns the last name in a parameter. The stored procedure is mapped as a call with the name GetLastName.

var ctxt = new MyProjectDataContext();
string lastName = string.Empty;
ctxt.CallGetLastName(ref lastName);

Dim ctxt As New MyProjectDataContext()
Dim lastName As String = String.Empty
ctxt.CallGetLastName(lastName)

Retrieval Stored Procedure calls

When you map in the designer a Stored Procedure Call to a stored procedure which returns one or more resultsets, it's considered a call to a retrieval procedure. For every call mapped, the designer will generate in the generated context class a method, with the name: ** GetStoredProcedureNameResults.

The return value of the procedure is a DataTable if the stored procedure returns 1 resultset, or a DataSet if the stored procedure returns multiple resultsets. If you want typed results, in the designer map atyped view onto the resultset.

The method generated has for each parameter of the stored procedure a parameter. If the parameter is an inputoutput or output parameter, the method parameter is generated as a call by reference and the value returned by the stored procedure is returned through the output parameter.

The example below calls the stored procedure CustOrdersHist in the Northwind database and returns a datatable, as the stored procedure returns a single resultset.

var ctxt = new NorthwindDataContext();
var results = ctxt.GetCustOrderHistResults("ALFKI");