Skip to main content
All docs
V22.2
.NET Framework 4.5.2+

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

  • 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 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:

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.

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

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