[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Collapse]eXpress Persistent Objects
 [Expand]Product Information
 [Expand]Getting Started
 [Expand]Fundamentals
 [Collapse]Feature Center
  [Expand]Data Representation
  [Expand]Object Relational Mapping
  [Expand]Connecting to a Data Store
  [Expand]Data Exchange and Manipulation
  [Collapse]Querying a Data Store
    Creating Criteria
    Direct SQL Queries
    Filtering
    Free Joins
    Limitations of CriteriaOperator.Parse
    LINQ to XPO
    LINQPad Context Driver
    Sorting
    Stored Procedures
  [Expand]Using XPO in Web Applications
 [Expand]Concepts
 [Expand]Design-Time Features
 [Expand]Examples
 [Expand]Member Tables
 [Expand]API Reference
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

Stored Procedures

Support for stored procedures in XPO includes the following capabilities.

Expanded Direct Calling of Existing Stored Procedures

This can be accomplished via the Session.ExecuteSproc, Session.GetObjectsFromSproc, and Session.GetObjectsFromSproc 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.

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:

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.

Expanded 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 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 18.2\Components\WinForms\Bin\XpoTutorials.exe).

Expanded See Also

Is this topic helpful?​​​​​​​