Skip to main content
All docs
V25.1
  • .NET 8.0+

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

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