学习011-04-07 How to: Display a List View With Data From a Stored Procedure With a Parameter（如何：使用来自带有

How to: Display a List View With Data From a Stored Procedure With a Parameter（如何：使用来自带有参数的存储过程的数据显示列表视图）

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.
此示例演示如何显示从接受参数的存储过程中获取的数据的列表视图。此示例使用非持久性对象临时存储来自存储过程和NorthWind数据库的数据。

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.
NorthWind数据库具有CustOrderHist存储过程，该存储过程返回客户购买的产品数量。

Create the Customers Persistent Class in the Platform-Agnostic Module（在与平台无关的模块中创建客户持久类）

In the platform-agnostic 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
    }
}

File: YourSolutionName.Module\BusinessObjects\YourSolutionNameDbContext.cs.

C# 
using Microsoft.EntityFrameworkCore;


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

Create Non-Persistent Objects in the Platform-Agnostic Module（在与平台无关的模块中创建非持久性对象）

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.
CustOrderHist存储过程返回包含两个字段的记录：ProductName（string）和Total（integer）。在与平台无关的模块中创建一个具有相应属性的非持久类。

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

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.
在模型编辑器中，按照列表视图列自定义中的说明将ProductName列添加到OrderHist_ListView。

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.
为客户视图创建控制器并在控制器中添加PopupWindowShowAction。此示例中的场景要求选择单个客户对象。为确保这一点，请将ActionSelectionDependencyType属性设置为SelectionDependencyType。必需单对象。

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

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.
在CustomizePopupWindowParams事件处理程序中，调用XafApplication. CreateObjectSpace（Type）方法从OrderHist类创建NonPersistentObjectSpace并处理NonPersistentObjectSpaceObjectsGet事件。调用XafApplication.CreateListView（IObjectSpace，Type，Boolean）方法从OrderHist创建列表视图并将此列表视图传递给e.View参数。

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

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.
要允许用户过滤和排序列表视图，请使用ObjectsGet事件处理程序中的DynamicCollection类来填充e. 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.
仅在客户端数据访问模式下支持过滤和排序非持久性对象。在XAF Blazor中，列表视图默认具有可查询数据访问模式。如列表视图数据访问模式中所述，将XAF Blazor应用程序中的非持久性列表视图数据访问模式更改为客户端。

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

The GetDataFromSproc method should contain ORM-dependent code to get data from a stored procedure.
GetDataFromSproc方法应该包含依赖于ORM的代码，以便从存储过程中获取数据。

Create XPO-Dependent Code to Get Data from a Stored Procedure（创建依赖于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 to access data returned by the ExecuteQueryWithMetadata method How to: Access Data in SQL Query Results.
使用Session.ExecuteQueryWithMetadata方法从存储过程中获取数据。此方法返回列名以及数据。请参阅以下文章访问ExecuteQueryWithMetadata方法返回的数据如何：访问SQL查询结果中的数据。

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.
使用XPObjectSpace. Session属性访问Session实例。创建的控制器是为持久类创建的。将ViewController.ObjectSpace属性强制转换为XPObjectSpace以获取GetDataFromSproc方法中的XPObjectSpace实例。使用ObjectViewController<ViewType，ObjectType>.View货币对象属性获取选定的客户对象。

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

Create EF Core-Dependent Code to Get Data from a Stored Procedure（创建EF核心相关代码以从存储过程中获取数据）

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.
在EF Core中，使用DbSet对象的RelationalQueryableExtensions. FromSqlRaw扩展方法从存储过程中获取数据。创建一个实体类，用于存储从存储过程中获取的数据。

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 file.
在YourSolutionName. Module\BusinessObjects\YourSolutionNameDbContext.cs文件中将新的实体类添加到解决方案的DbContext。

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.
创建的控制器是为持久类创建的。将ViewController. ObjectSpace属性转换为EFCoreObjectSpace以获取GetDataFromSproc方法中的EFCoreObjectSpace实例。从EFCoreObjectSpace.DbContext属性访问YourSolutionNameEFCoreDbContext实例。调用YourSolutionNameEFCoreDbContext.FromSqlRaw方法从存储过程中获取数据。使用ObjectViewController<ViewType，ObjectType>.View货币对象属性获取选定的客户对象。

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