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 List View With Data From a Stored Procedure With a Parameter

  • 9 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 the 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, create the following Customers class:

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

EF Core

using DevExpress.Persistent.Base;

namespace YourSolutionName.Module.BusinessObjects {
    [DefaultClassOptions]
    public class Customers {
        string fCustomerID;
        [System.ComponentModel.DataAnnotations.Key]
        public string CustomerID {
            get => fCustomerID;
            set => fCustomerID = value;
        }
        // other properties
    }
}

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

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.

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

    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.

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

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

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

    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.

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

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

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