- 2 minutes to read
Support for stored procedures in XPO includes the following capabilities.
Direct Calling of Existing Stored Procedures
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.
Wnen 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:
|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.
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.
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 20.1\Components\WinForms\Bin\XpoTutorials.exe).