Stored Procedures

• Dec 28, 2022
• 3 minutes to read

Support for stored procedures in XPO includes the following capabilities.

Direct Calling of Existing Stored Procedures

You can use the following methods to call stored procedures:

• Session.ExecuteSproc
• Session.GetObjectsFromSproc
• Session.GetObjectsFromSproc<T>.

To simplify implementation, the Data Model Wizard can optionally generate all the required persistent classes and DDL (Data Description Language) code for views, triggers, and stored procedures.

When the Import stored procedures option is enabled, the following wizard page is activated.

The wizard generates the static SprocHelper class located in the StoredSprocHelper.cs (StoredSprocHelper.vb) file. This class exposes the following static methods that wrap the Session.ExecuteSproc and Session.GetObjectsFromSproc methods:

Method Name	Description
Exec<StoredProcedureName>	Calls the <StoredProcedureName> stored procedure via the Session.ExecuteSproc method and returns a result set.
Exec<StoredProcedureName>IntoObjects	Calls the <StoredProcedureName> stored procedure via the Session.GetObjectsFromSproc methods and returns a collection of <StoredProcedureName>Result objects.
Exec<StoredProcedureName>IntoDataView	Calls the <StoredProcedureName> stored procedure via the Session.ExecuteSproc method and returns a XPDataView class instance containing the <StoredProcedureName>Result objects that are results from the stored procedure execution. This method also has an overload that also calls the stored procedure but fills the XPDataView object passed to it as a parameter.

The <StoredProcedureName>Result is a non-persistent class generated by the wizard which represents the stored procedure result. Properties of this class correspond to result set columns selected in the wizard pare illustrated above.

The code sample below demonstrates how to use the generated static method to call a stored procedure:

C#

public IList<CountEmployeesByClassificationResult> GetEmployeesByClassification(Session session) {
    // The ExecHR_CountEmployeesByClassificationIntoObjects method was generated by Data Model Wizard.
    return SprocHelper.ExecHR_CountEmployeesByClassificationIntoObjects(session).ToList();
}

VB.NET

Public Function GetEmployeesByClassification(ByVal session As Session) As IList(Of CountEmployeesByClassificationResult)
    ' The ExecHR_CountEmployeesByClassificationIntoObjects method was generated by Data Model Wizard.
    Return SprocHelper.ExecHR_CountEmployeesByClassificationIntoObjects(session).ToList()
End Function

Mapping of Persistent Classes to Microsoft SQL Server and Oracle Database Views

This mapping is performed with the help of INSTEAD-OF triggers and stored procedures, requires the following tasks to be performed:

• Create a database view.
• Map a persistent class to this view.
• Create stored procedures for INSERT, UPDATE and DELETE functionalities.
• Create INSTEAD-OF triggers, routing this functionality to the created stored procedures.

Note

The Advantage, DB2, Firebird, MySql, and Pervasive databases have some known restrictions for generating result set columns, while the MSAccess database does not support getting the list of stored procedures. These issues may affect the Data Model Wizard, which works just fine with other databases supported by XPO. In any case, you can use the Session.ExecuteSproc, Session.GetObjectsFromSproc, and Session.GetObjectsFromSproc<T> methods to call stored procedures for any database, except for the MSSqlServerCE and SQLite databases, which do not support stored procedures.

Note

You can try the functionality described here in the Querying a Data Store | Stored Procedures section of the XPO Tutorials demo (C:\Users\Public\Documents\DevExpress Demos 23.2\Components\WinForms\Bin\XpoTutorials.exe).