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

• Nov 11, 2022
• 8 minutes to read

This example demonstrates how to show a List View with data fetched from a stored procedure from the Navigation. This example uses the Non-Persistent Objects to temporally store data from the stored procedure and the Northwind database. The stored procedure is defined as follows:

SQL

CREATE Procedure GetEmployees
AS
    SET NOCOUNT ON;
    SELECT * FROM Employees
GO

Create Non-Persistent Objects in the Platform-Agnostic Module

1. In the platform-agnostic module (MySolution.Module), create the following non-persistent class:

   C#

   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; }
       }
   }
   

   VB.NET

   Imports DevExpress.ExpressApp.DC
   Imports DevExpress.Persistent.Base
   
   Namespace YourSolutionName.Module.BusinessObjects
       <DomainComponent, DefaultClassOptions>
       Public Class MyNonPersistentObject
           Dim _employeeID As Integer
           <DevExpress.ExpressApp.Data.Key>
           Property EmployeeID As Integer
               Get
                   Return _employeeID
               End Get
               Friend Set
                   _employeeID = Value
               End Set
           End Property
   
           Dim _firstName As String
           Property FirstName As String
               Get
                   Return _firstName
               End Get
               Friend Set
                   _firstName = Value
               End Set
           End Property
   
           Dim _lastName As String
           Property LastName As String
               Get
                   Return _lastName
               End Get
               Friend Set
                   _lastName = Value
               End Set
           End Property
   
           Dim _title As String
           Property Title As String
               Get
                   Return _title
               End Get
               Friend Set
                   _title = Value
               End Set
           End Property
       End Class
   End Namespace
   

   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 List View from the Navigation.

   C#

   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) {
                   // ...
               }
           }
       }
   }
   

   VB.NET

   Imports DevExpress.ExpressApp
   
   Public NotInheritable Class YourSolutionNameModule
       Inherits ModuleBase
       ' ...
       Public Overrides Sub Setup(application As XafApplication)
           MyBase.Setup(application)
           AddHandler application.SetupComplete, AddressOf Application_SetupComplete
       End Sub
       Private Sub Application_SetupComplete(ByVal sender As Object, ByVal e As EventArgs)
           AddHandler Application.ObjectSpaceCreated, AddressOf Application_ObjectSpaceCreated
       End Sub
       Private Sub Application_ObjectSpaceCreated(ByVal sender As Object, ByVal e As ObjectSpaceCreatedEventArgs)
           ' ...
           Dim npos As NonPersistentObjectSpace = TryCast(e.ObjectSpace, NonPersistentObjectSpace)
           If npos IsNot Nothing Then
               ' ...
           End If
       End Sub
   End Class
   

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.

   C#

   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) {
           // ...
       }
   }
   

   VB.NET

   Private Sub Application_ObjectSpaceCreated(ByVal sender As Object, ByVal e As ObjectSpaceCreatedEventArgs)
       Dim os As CompositeObjectSpace = TryCast(e.ObjectSpace, CompositeObjectSpace)
       If os IsNot Nothing AndAlso Not (TypeOf os.Owner Is CompositeObjectSpace) Then
           os.PopulateAdditionalObjectSpaces(CType(sender, XafApplication))
       End If
       Dim npos As NonPersistentObjectSpace = TryCast(e.ObjectSpace, NonPersistentObjectSpace)
       If npos IsNot Nothing Then
           ' ...
       End If
   End Sub
   

4. To be able to filter and sort a List View, use the DynamicCollection class in the ObjectsGetting event handler to populate the e.Objects collection. To separate the Module code from business logic to fetch data, create an adapter class to handle the NonPersistentObjectSpace events. The following example demonstrates how to implement this: How to filter and sort Non-Persistent Objects.

   Note

   Filtering and sorting non-persistent object is supported only in the Client data access mode. In XAF Blazor, List Views use the Queryable data access mode by default. Change the non-persistent List View data access mode to Client in XAF Blazor applications as described in List View Data Access Modes.

   C#

   using DevExpress.ExpressApp;
   using System.Collections.Generic;
   
   namespace YourSolutionName.Module.BusinessObjects {
       class MyNonPersistentObjectAdapter {
           NonPersistentObjectSpace objectSpace;
           public MyNonPersistentObjectAdapter(NonPersistentObjectSpace npos) {
               objectSpace = npos;
               objectSpace.ObjectsGetting += Npos_ObjectsGetting;
           }
           private void Npos_ObjectsGetting(object sender, ObjectsGettingEventArgs e) {
               if (e.ObjectType != typeof(MyNonPersistentObject)) {
                   return;
               }
               var collection = new DynamicCollection(objectSpace, e.ObjectType, e.Criteria, e.Sorting, e.InTransaction);
               collection.FetchObjects += DynamicCollection_FetchObjects;
               e.Objects = collection;
           }
           private void DynamicCollection_FetchObjects(object sender, FetchObjectsEventArgs e) {
               if (e.ObjectType == typeof(MyNonPersistentObject)) {
                   e.Objects = GetDataFromSproc();
                   e.ShapeData = true;
               }
           }
           List<MyNonPersistentObject> GetDataFromSproc() {
               // ...
           }
       }
   }
   

   VB.NET

   Imports DevExpress.ExpressApp
   Imports System.Collections.Generic
   
   Namespace YourSolutionName.Module.BusinessObjects
       Class MyNonPersistentObjectAdapter
           Private objectSpace As NonPersistentObjectSpace
   
           Public Sub New(ByVal npos As NonPersistentObjectSpace)
               objectSpace = npos
               AddHandler objectSpace.ObjectsGetting, AddressOf Npos_ObjectsGetting
           End Sub
   
           Private Sub Npos_ObjectsGetting(ByVal sender As Object, ByVal e As ObjectsGettingEventArgs)
               If e.ObjectType <> GetType(MyNonPersistentObject) Then
                   Return
               End If
   
               Dim collection = New DynamicCollection(objectSpace, e.ObjectType, e.Criteria, e.Sorting, e.InTransaction)
               AddHandler collection.FetchObjects, AddressOf DynamicCollection_FetchObjects
               e.Objects = collection
           End Sub
   
           Private Sub DynamicCollection_FetchObjects(ByVal sender As Object, ByVal e As FetchObjectsEventArgs)
               If e.ObjectType = GetType(MyNonPersistentObject) Then
                   e.Objects = GetDataFromSproc()
                   e.ShapeData = True
               End If
           End Sub
   
           Private Function GetDataFromSproc() As List(Of MyNonPersistentObject)
               ' ...
           End Function
       End Class
   End Namespace
   

   C#

   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);
       }
   }
   

   VB.NET

   Private Sub Application_ObjectSpaceCreated(ByVal sender As Object, ByVal e As ObjectSpaceCreatedEventArgs)
       Dim os As CompositeObjectSpace = TryCast(e.ObjectSpace, CompositeObjectSpace)
       If os IsNot Nothing AndAlso Not (TypeOf os.Owner Is CompositeObjectSpace) Then
           os.PopulateAdditionalObjectSpaces(CType(sender, XafApplication))
       End If
       Dim npos As NonPersistentObjectSpace = TryCast(e.ObjectSpace, NonPersistentObjectSpace)
       If npos IsNot Nothing Then
           Dim tempVar = New MyNonPersistentObjectAdapter(npos)
       End If
   End Sub
   

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

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

In XPO, 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 information on how to access data returned by 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.

C#

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

class MyNonPersistentObjectAdapter {
    // ...
    List<MyNonPersistentObject> GetDataFromSproc() {
        XPObjectSpace persistentObjectSpace = objectSpace.AdditionalObjectSpaces.OfType<XPObjectSpace>().First();
        Session session = persistentObjectSpace.Session;
        SelectedData results = session.ExecuteQueryWithMetadata("GetEmployees");
        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);
        }
        List<MyNonPersistentObject> objects = new List<MyNonPersistentObject>();
        foreach (SelectStatementResultRow row in results.ResultSet[1].Rows) {
            MyNonPersistentObject obj = new MyNonPersistentObject();
            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;
            objects.Add(obj);
        }
        return objects;
    }
}

VB.NET

Imports DevExpress.ExpressApp.Xpo
Imports DevExpress.Xpo
Imports DevExpress.Xpo.DB
Imports System.Linq

Class MyNonPersistentObjectAdapter
    ' ...
    Private Function GetDataFromSproc() As List(Of MyNonPersistentObject)
        Dim persistentObjectSpace As XPObjectSpace = objectSpace.AdditionalObjectSpaces.OfType(Of XPObjectSpace)().First()
        Dim session As Session = persistentObjectSpace.Session
        Dim results As SelectedData = session.ExecuteQueryWithMetadata("GetEmployees")
        Dim columnNames As Dictionary(Of String, Integer) = New Dictionary(Of String, Integer)()

        For columnIndex As Integer = 0 To results.ResultSet(0).Rows.Length - 1
            Dim columnName As String = TryCast(results.ResultSet(0).Rows(columnIndex).Values(0), String)
            columnNames.Add(columnName, columnIndex)
        Next

        Dim objects As List(Of MyNonPersistentObject) = New List(Of MyNonPersistentObject)()

        For Each row As SelectStatementResultRow In results.ResultSet(1).Rows
            Dim obj As MyNonPersistentObject = New MyNonPersistentObject()
            obj.EmployeeID = CInt(row.Values(columnNames("EmployeeID")))
            obj.FirstName = TryCast(row.Values(columnNames("FirstName")), String)
            obj.LastName = TryCast(row.Values(columnNames("LastName")), String)
            obj.Title = TryCast(row.Values(columnNames("Title")), String)
            objects.Add(obj)
        Next

        Return objects
    End Function
End Class

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.

C#

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

// Make sure that you use options.UseChangeTrackingProxies() in your DbContext settings.

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

C#

using Microsoft.EntityFrameworkCore;

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

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

C#

using DevExpress.ExpressApp.EFCore;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

class MyNonPersistentObjectAdapter {
    // ...
    List<MyNonPersistentObject> GetDataFromSproc() {
        EFCoreObjectSpace persistentObjectSpace = objectSpace.AdditionalObjectSpaces.OfType<EFCoreObjectSpace>().First();
        YourSolutionNameEFCoreDbContext dbContext = (YourSolutionNameEFCoreDbContext)persistentObjectSpace.DbContext;
        IQueryable<Employees> results = dbContext.Employees.FromSqlRaw("GetEmployees");
        List<MyNonPersistentObject> objects = new List<MyNonPersistentObject>();
        foreach (Employees employees in results) {
            MyNonPersistentObject obj = new MyNonPersistentObject();
            obj.EmployeeID = employees.EmployeeID;
            obj.FirstName = employees.FirstName;
            obj.LastName = employees.LastName;
            obj.Title = employees.Title;
            objects.Add(obj);
        }
        return objects;
    }
}