All docs
V21.1
21.2 (EAP/Beta)
21.1
20.2
The page you are viewing does not exist in version 20.2. This link will take you to the root page.
20.1
The page you are viewing does not exist in version 20.1. This link will take you to the root page.
19.2
The page you are viewing does not exist in version 19.2. This link will take you to the root page.
19.1
The page you are viewing does not exist in version 19.1. This link will take you to the root page.
18.2
The page you are viewing does not exist in version 18.2. This link will take you to the root page.
18.1
The page you are viewing does not exist in version 18.1. This link will take you to the root page.
17.2
The page you are viewing does not exist in version 17.2. This link will take you to the root page.
.NET Framework 4.5.2+
.NET Framework 4.5.2+
.NET Standard 2.0+
.NET Core 3.0+

How to: Display a Detail View With Data From a Stored Procedure From the Navigation

  • 8 minutes to read

This example demonstrates how to show a Detail View for data fetched from a stored procedure from the Navigation.

This example uses the Northwind database and Non-Persistent Objects to store data from the stored procedure. The stored procedure is defined as follows:

CREATE PROCEDURE GetEmployee
    @ID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Employees
    WHERE EmployeeID = @ID
END
GO

Create Non-Persistent Objects in the Platform-Agnostic Module

  1. In the platform-agnostic module, create the following non-persistent class:

    using DevExpress.ExpressApp.DC;
    using DevExpress.Persistent.Base;
    
    namespace YourSolutionName.Module.BusinessObjects {
        [DomainComponent, DefaultClassOptions]
        public class MyNonPersistentObject {
            [DevExpress.ExpressApp.Data.Key]
            public int EmployeeID { get; internal set; }
            public string FirstName { get; internal set; }
            public string LastName { get; internal set; }
            public string Title { get; internal set; }
        }
    }
    

    Note

    Internal/Friend setters are used in the non-persistent class to disable editing the properties because editing is not implemented in this example.

  2. Handle the XafApplication.ObjectSpaceCreated event to subscribe to the NonPersistentObjectSpace events as described in How to: Display a Non-Persistent Object’s Detail View

    using DevExpress.ExpressApp;
    
    namespace YourSolutionName.Module {    
        public sealed partial class YourSolutionNameModule : ModuleBase {
            // ...
            public override void Setup(XafApplication application) {
                base.Setup(application);
                application.SetupComplete += Application_SetupComplete;
            }
            private void Application_SetupComplete(object sender, EventArgs e) {
                Application.ObjectSpaceCreated += Application_ObjectSpaceCreated;
            }
            private void Application_ObjectSpaceCreated(object sender, ObjectSpaceCreatedEventArgs e) {
                // ...
                NonPersistentObjectSpace npos = e.ObjectSpace as NonPersistentObjectSpace;
                if (npos != null) {
                    // ...
                }
            }
        }
    }
    
  3. An ORM-dependent code executes a stored procedure (it uses Session in XPO and DbContext in EF Core). A corresponding persistent ObjectSpace has access to them. To allow the NonPersistentObjectSpace to access persistent ObjectSpaces, populate the CompositeObjectSpace.AdditionalObjectSpaces collection in the ObjectSpaceCreated event handler.

    private void Application_ObjectSpaceCreated(object sender, ObjectSpaceCreatedEventArgs e) {
        CompositeObjectSpace os = e.ObjectSpace as CompositeObjectSpace;
        if (os != null && !(os.Owner is CompositeObjectSpace)) {
            os.PopulateAdditionalObjectSpaces((XafApplication)sender);
        }
        NonPersistentObjectSpace npos = e.ObjectSpace as NonPersistentObjectSpace;
        if (npos != null) {
            // ...
        }
    }
    
  4. To separate the Module code from business logic to fetch data, create an adapter class to handle the NonPersistentObjectSpace events. Handle the NonPersistentObjectSpace.ObjectByKeyGetting event to return a displayed object.

    using DevExpress.ExpressApp;
    
    namespace YourSolutionName.Module.BusinessObjects {
        class MyNonPersistentObjectAdapter {
            NonPersistentObjectSpace objectSpace;
            public MyNonPersistentObjectAdapter(NonPersistentObjectSpace npos) {
                objectSpace = npos;
                objectSpace.ObjectByKeyGetting += ObjectSpace_ObjectByKeyGetting;
            }
    
            private void ObjectSpace_ObjectByKeyGetting(object sender, ObjectByKeyGettingEventArgs e) {
                if (e.ObjectType != typeof(MyNonPersistentObject)) {
                    return;
                }
                e.Object = GetObjectFromSproc(e.Key);
            }
            MyNonPersistentObject GetObjectFromSproc(object key) {
                // ...
            }
        }
    }
    
    private void Application_ObjectSpaceCreated(object sender, ObjectSpaceCreatedEventArgs e) {
        CompositeObjectSpace os = e.ObjectSpace as CompositeObjectSpace;
        if (os != null && !(os.Owner is CompositeObjectSpace)) {
            os.PopulateAdditionalObjectSpaces((XafApplication)sender);
        }
        NonPersistentObjectSpace npos = e.ObjectSpace as NonPersistentObjectSpace;
        if (npos != null) {
            new MyNonPersistentObjectAdapter(npos);
        }
    }
    

    The GetObjectFromSproc method should contain ORM-dependent code to get data from a stored procedure.

  5. Add a navigation item for the MyNonPersistentObject Detail View with the object key as described in How to: Display a Non-Persistent Object’s Detail View.

Create XPO-Dependent Code to Get Data from a Stored Procedure

Use the Session.ExecuteQueryWithMetadata method to get data from a stored procedure. This method returns column names along with data. Refer to the following article for instructions on how to access data from the ExecuteQueryWithMetadata method How to: Access Data in SQL Query Results.

Use the XPObjectSpace.Session property to access a Session instance. You can access an XPObjectSpace instance from the CompositeObjectSpace.AdditionalObjectSpaces collection.

using DevExpress.ExpressApp.Xpo;
using DevExpress.Xpo;
using DevExpress.Xpo.DB;
using System.Linq;

class MyNonPersistentObjectAdapter {
    // ...
    MyNonPersistentObject GetObjectFromSproc(object key) {
        XPObjectSpace persistentObjectSpace = objectSpace.AdditionalObjectSpaces.OfType<XPObjectSpace>().First();
        Session session = persistentObjectSpace.Session;
        SelectedData results = session.ExecuteQueryWithMetadata($"GetEmployee @ID={key}");
        Dictionary<string, int> columnNames = new Dictionary<string, int>();
        for (int columnIndex = 0; columnIndex < results.ResultSet[0].Rows.Length; columnIndex++) {
            string columnName = results.ResultSet[0].Rows[columnIndex].Values[0] as string;
            columnNames.Add(columnName, columnIndex);
        }
        MyNonPersistentObject obj = new MyNonPersistentObject();
        if (results.ResultSet[1].Rows.Length > 0) {
            SelectStatementResultRow row = results.ResultSet[1].Rows[0];
            obj.EmployeeID = (int)row.Values[columnNames["EmployeeID"]];
            obj.FirstName = row.Values[columnNames["FirstName"]] as string;
            obj.LastName = row.Values[columnNames["LastName"]] as string;
            obj.Title = row.Values[columnNames["Title"]] as string;
        }
        return obj;
    }
}

Create EF Core-Dependent Code to Get Data from a Stored Procedure

In EF Core, use the DbSet object’s RelationalQueryableExtensions.FromSqlRaw extension method to get data from a stored procedure. Create an entity class that should store data fetched from a stored procedure.

namespace YourSolutionName.Module.BusinessObjects {
    public class Employees {
        [System.ComponentModel.DataAnnotations.Key]
        public int EmployeeID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Title { get; set; }
    }
}

Add the new entity class to the solution’s DbContext in the YourSolutionName.Module\BusinessObjects\YourSolutionNameDbContext.cs(vb) file.

using Microsoft.EntityFrameworkCore;

public class YourSolutionNameEFCoreDbContext : DbContext {
    // ...
    public DbSet<Employees> Employees { get; set; }
}

Get an EFCoreObjectSpace instance from the CompositeObjectSpace.AdditionalObjectSpaces collection in the GetObjectFromSproc method. Access your YourSolutionNameEFCoreDbContext instance from the EFCoreObjectSpace.DbContext property. Call the YourSolutionNameEFCoreDbContext.Employees.FromSqlRaw method to get data from a stored procedure.

using DevExpress.ExpressApp.EFCore;
using Microsoft.EntityFrameworkCore;

class MyNonPersistentObjectAdapter {
    // ...
    MyNonPersistentObject GetObjectFromSproc(object key) {
        EFCoreObjectSpace persistentObjectSpace = objectSpace.AdditionalObjectSpaces.OfType<EFCoreObjectSpace>().First();
        YourSolutionNameEFCoreDbContext dbContext = (YourSolutionNameEFCoreDbContext)persistentObjectSpace.DbContext;
        IQueryable<Employees> results = dbContext.Employees.FromSqlRaw($"GetEmployee @ID={key}");
        MyNonPersistentObject obj = new MyNonPersistentObject();
        Employees employees = results.ToList().FirstOrDefault();
        if (employees != null) {
            obj.EmployeeID = employees.EmployeeID;
            obj.FirstName = employees.FirstName;
            obj.LastName = employees.LastName;
            obj.Title = employees.Title;
        }
        return obj;
    }
}
See Also