The requested page is not available for the requested platform. You are viewing the content for .NET Framework 4.5.2+ platform.

Stored Procedures

  • 2 min to read

Support for stored procedures in XPO includes the following capabilities.

Direct Calling of Existing Stored Procedures

This can be accomplished via the Session.ExecuteSproc, Session.GetObjectsFromSproc, and Session.GetObjectsFromSproc<T> methods.

To simplify implementation, the Data Model Wizard has an option to enable generating all the required persistent classes and DDL (Data Description Language) code for views, triggers and stored procedures.

StoredProcedures_WizardOption

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

StoredProcedures_WizardPage

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.

Mapping of Persistent Classes to MS 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 19.1\Components\WinForms\Bin\XpoTutorials.exe).

See Also