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

• Aug 20, 2024
• 8 minutes to read

This example demonstrates how to show a List View for data fetched from a stored procedure that accepts a parameter. This example uses Non-Persistent Objects to temporally store data from the stored procedure and the Northwind database.

The Northwind database has the CustOrderHist stored procedure that returns the number of products a customer purchased. In this example, a PopupWindowShowAction from the Customers List View invokes a pop-up window that shows data from the stored procedure.

Create the Customers Persistent Class in the Platform-Agnostic Module

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

C# (EF Core)

using DevExpress.Persistent.Base;
using System.ComponentMode.DataAnnotations;

namespace YourSolutionName.Module.BusinessObjects {
    [DefaultClassOptions]
    public class Customers {
        [Key]
        public virtual string CustomerID { get; set; }
        // other properties
    }
}

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

C# (XPO)

using DevExpress.Persistent.Base;
using DevExpress.Xpo;

namespace YourSolutionName.Module.BusinessObjects {
    [DefaultClassOptions]
    public class Customers : XPLiteObject {
        public Customers(Session session) : base(session) { }
        string fCustomerID;
        [DevExpress.Xpo.Key]
        public string CustomerID {
            get => fCustomerID;
            set => SetPropertyValue(nameof(CustomerID), ref fCustomerID, value);
        }
        // other properties
    }
}

VB.NET (XPO)

Imports DevExpress.Persistent.Base
Imports DevExpress.Xpo

Namespace YourSolutionName.Module.BusinessObjects
    <DefaultClassOptions>
    Public Class Customers
        Inherits XPLiteObject
        Public Sub New(ByVal session As Session)
            MyBase.New(session)
        End Sub
        Private fCustomerID As String
        <DevExpress.Xpo.Key>
        Public Property CustomerID As String
            Get
                Return fCustomerID
            End Get
            Set(ByVal value As String)
                SetPropertyValue(NameOf(CustomerID), fCustomerID, value)
            End Set
        End Property
        ' other properties
    End Class
End Namespace

File: YourSolutionName.Module\BusinessObjects\YourSolutionNameDbContext.cs(vb).

C#

using Microsoft.EntityFrameworkCore;

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

Create Non-Persistent Objects in the Platform-Agnostic Module

1. The CustOrderHist stored procedure returns records with two fields: ProductName (string) and Total (integer). Create a non-persistent class with corresponding properties in the platform-agnostic module (MySolution.Module).

   C#

   using DevExpress.ExpressApp.DC;
   
   namespace YourSolutionName.Module.BusinessObjects {
       [DomainComponent]
       public class OrderHist {
           [DevExpress.ExpressApp.Data.Key]
           public string ProductName { get; internal set; }
           public int Total { get; internal set; }
       }
   }
   

   VB.NET

   Imports DevExpress.ExpressApp.DC
   
   Namespace YourSolutionName.Module.BusinessObjects
       <DomainComponent>
       Public Class OrderHist
           Dim _productName As String
           <DevExpress.ExpressApp.Data.Key>
           Property ProductName As String
               Get
                   Return _productName
               End Get
               Friend Set
                   _productName = Value
               End Set
           End Property
   
           Dim _total As Integer
           Property Total As Integer
               Get
                   Return _total
               End Get
               Friend Set
                   _total = 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.

   In the Model Editor, add the ProductName column to OrderHist_ListView as described in List View Columns Customization.

2. Create a controller for Customers Views and add a PopupWindowShowAction in the controller. The scenario in this example requires that a single Customers object is selected. To ensure this, set the Action SelectionDependencyType property to SelectionDependencyType.RequireSingleObject.

   C#

   using DevExpress.ExpressApp;
   using DevExpress.ExpressApp.Actions;
   using YourSolutionName.Module.BusinessObjects;
   
   namespace YourSolutionName.Module.Controllers {
       public class CustomersViewController : ObjectViewController<ObjectView, Customers> {
           public CustomersViewController() {
               PopupWindowShowAction action = new PopupWindowShowAction(this, "Order Hist", DevExpress.Persistent.Base.PredefinedCategory.View);
               action.SelectionDependencyType = SelectionDependencyType.RequireSingleObject;
               action.CustomizePopupWindowParams += Action_CustomizePopupWindowParams;
           }
   
           private void Action_CustomizePopupWindowParams(object sender, CustomizePopupWindowParamsEventArgs e) {
               // ...
           }
       }
   }
   

   VB.NET

   Imports DevExpress.ExpressApp
   Imports DevExpress.ExpressApp.Actions
   Imports YourSolutionName.Module.BusinessObjects
   
   Namespace YourSolutionName.Module.Controllers
       Public Class CustomersViewController
           Inherits ObjectViewController(Of ObjectView, Customers)
   
           Public Sub New()
               Dim action As PopupWindowShowAction = New PopupWindowShowAction(Me, "Order Hist", DevExpress.Persistent.Base.PredefinedCategory.View)
               action.SelectionDependencyType = SelectionDependencyType.RequireSingleObject
               AddHandler action.CustomizePopupWindowParams, AddressOf Action_CustomizePopupWindowParams
           End Sub
   
           Private Sub Action_CustomizePopupWindowParams(ByVal sender As Object, ByVal e As CustomizePopupWindowParamsEventArgs)
               ' ...
           End Sub
       End Class
   End Namespace
   

3. In the CustomizePopupWindowParams event handler, call the XafApplication.CreateObjectSpace(Type) method to create a NonPersistentObjectSpace from the OrderHist class and handle the NonPersistentObjectSpace.ObjectsGetting event. Call the XafApplication.CreateListView(IObjectSpace, Type, Boolean) method to create a List View from the OrderHist and pass this List View to the e.View parameter.

   C#

   private void Action_CustomizePopupWindowParams(object sender, CustomizePopupWindowParamsEventArgs e) {
       NonPersistentObjectSpace objectSpace = (NonPersistentObjectSpace)Application.CreateObjectSpace(typeof(OrderHist));
       objectSpace.ObjectsGetting += ObjectSpace_ObjectsGetting;
       e.View = Application.CreateListView(objectSpace, typeof(OrderHist), true);
   }
   
   private void ObjectSpace_ObjectsGetting(object sender, ObjectsGettingEventArgs e) {
       // ...
   }
   

   VB.NET

   Private Sub Action_CustomizePopupWindowParams(ByVal sender As Object, ByVal e As CustomizePopupWindowParamsEventArgs)
       Dim objectSpace As NonPersistentObjectSpace = CType(Application.CreateObjectSpace(GetType(OrderHist)), NonPersistentObjectSpace)
       AddHandler objectSpace.ObjectsGetting, AddressOf ObjectSpace_ObjectsGetting
       e.View = Application.CreateListView(objectSpace, GetType(OrderHist), True)
   End Sub
   
   Private Sub ObjectSpace_ObjectsGetting(ByVal sender As Object, ByVal e As ObjectsGettingEventArgs)
       ' ...
   End Sub
   

4. To allow users to filter and sort a List View, use the DynamicCollection class in the ObjectsGetting event handler to populate the e.Objects collection. 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 have 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;
   
   // ...
   private void ObjectSpace_ObjectsGetting(object sender, ObjectsGettingEventArgs e) {
       NonPersistentObjectSpace objectSpace = (NonPersistentObjectSpace)sender;
       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) {
       Customers customer = (Customers)View.SelectedObjects[0];
       e.Objects = GetDataFromSproc(customer.CustomerID);
       e.ShapeData = true;
   }
   List<OrderHist> GetDataFromSproc(string key) {
       // ...
   }
   

   VB.NET

   Imports DevExpress.ExpressApp
   Imports System.Collections.Generic
   
   ' ...
   Private Sub ObjectSpace_ObjectsGetting(ByVal sender As Object, ByVal e As ObjectsGettingEventArgs)
       Dim objectSpace As NonPersistentObjectSpace = CType(sender, NonPersistentObjectSpace)
       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)
       Dim customer As Customers = CType(View.SelectedObjects(0), Customers)
       e.Objects = GetDataFromSproc(customer.CustomerID)
       e.ShapeData = True
   End Sub
   
   Private Function GetDataFromSproc(ByVal key As String) As List(Of OrderHist)
       ' ...
   End Function
   

   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

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 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. The created controller is created for a persistent class. Cast the ViewController.ObjectSpace property to XPObjectSpace to get an XPObjectSpace instance in the GetDataFromSproc method. Use the ObjectViewController<ViewType, ObjectType>.ViewCurrentObject property to get a selected Customers object.

C#

using DevExpress.ExpressApp.Xpo;
using DevExpress.Xpo.DB;
using DevExpress.Xpo;

// ...
List<OrderHist> GetDataFromSproc(string key) {
    XPObjectSpace persistentObjectSpace = (XPObjectSpace)ObjectSpace;
    Session session = persistentObjectSpace.Session;
    SelectedData results = session.ExecuteQueryWithMetadata($"CustOrderHist @CustomerID={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);
    }
    List<OrderHist> objects = new List<OrderHist>();
    foreach (SelectStatementResultRow row in results.ResultSet[1].Rows) {
        OrderHist obj = new OrderHist();
        obj.ProductName = row.Values[columnNames["ProductName"]] as string;
        obj.Total = (int)row.Values[columnNames["Total"]];
        objects.Add(obj);
    }
    return objects;
}

VB.NET

Imports DevExpress.ExpressApp.Xpo
Imports DevExpress.Xpo.DB
Imports DevExpress.Xpo

' ...
Private Function GetDataFromSproc(ByVal key As String) As List(Of OrderHist)
    Dim persistentObjectSpace As XPObjectSpace = CType(ObjectSpace, XPObjectSpace)
    Dim session As Session = persistentObjectSpace.Session
    Dim results As SelectedData = session.ExecuteQueryWithMetadata($"CustOrderHist @CustomerID={key}")
    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 OrderHist) = New List(Of OrderHist)()

    For Each row As SelectStatementResultRow In results.ResultSet(1).Rows
        Dim obj As OrderHist = New OrderHist()
        obj.ProductName = TryCast(row.Values(columnNames("ProductName")), String)
        obj.Total = CInt(row.Values(columnNames("Total")))
        objects.Add(obj)
    Next

    Return objects
End Function

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 CustOrderHist {
        [System.ComponentModel.DataAnnotations.Key]
        public virtual string ProductName { get; set; }
        public virtual int Total { get; set; }
    }
}

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<CustOrderHist> CustOrderHists { get; set; }
}

The created controller is created for a persistent class. Cast the ViewController.ObjectSpace property to EFCoreObjectSpace to get an EFCoreObjectSpace instance 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. Use the ObjectViewController<ViewType, ObjectType>.ViewCurrentObject property to get a selected Customers object.

C#

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

// ...
List<OrderHist> GetDataFromSproc(string key) {
    EFCoreObjectSpace persistentObjectSpace = (EFCoreObjectSpace)ObjectSpace;
    YourSolutionNameEFCoreDbContext dbContext = (YourSolutionNameEFCoreDbContext)persistentObjectSpace.DbContext;
    IQueryable<CustOrderHist> results = dbContext.CustOrderHists.FromSqlRaw($"CustOrderHist @CustomerID={key}");
    List<OrderHist> objects = new List<OrderHist>();
    foreach (CustOrderHist coh in results) {               
        OrderHist obj = new OrderHist();
        obj.ProductName = coh.ProductName;
        obj.Total = coh.Total;
        objects.Add(obj);
    }
    return objects;
}