学习011-04-09 How to: Display a List View With Data From a Stored Procedure From the Navigation

How to: Display a List View With Data From a Stored Procedure From the Navigation（如何：使用导航中存储过程中的数据显示列表视图）

This example demonstrates how to show a List View with data fetched from a stored procedure from the Navigation. This example uses the Non-Persistent Objects to temporally store data from the stored procedure and the Northwind database. The stored procedure is defined as follows:
此示例演示如何显示列表视图，其中包含从导航中的存储过程中获取的数据。此示例使用非持久性对象临时存储存储过程和北风数据库中的数据。存储过程定义如下：

SQL
CREATE Procedure GetEmployees
AS
    SET NOCOUNT ON;
    SELECT * FROM Employees
GO

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

In the platform-agnostic module (MySolution.Module), create the following non-persistent class:
在与平台无关的模块（MySolutions. Module）中，创建以下非持久类：

C#
using DevExpress.ExpressApp.DC;
using DevExpress.Persistent.Base;


namespace YourSolutionName.Module.BusinessObjects {
    [DomainComponent, DefaultClassOptions]
    public class MyNonPersistentObject {
        [DevExpress.ExpressApp.Data.Key]
        public int EmployeeID { get; internal set; }
        public string FirstName { get; internal set; }
        public string LastName { get; internal set; }
        public string Title { 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.
内部/朋友设置器在非持久类中用于禁用编辑属性，因为在此示例中未实现编辑。

Handle the XafApplication.ObjectSpaceCreated event to subscribe to the NonPersistentObjectSpace events as described in How to: Display a Non-Persistent Object’s List View from the Navigation.
处理XafApplication. ObjectSpaceCreated事件以订阅NonPersistentObjectSpace事件，如如何：从导航显示非持久性对象的列表视图中所述。

C#
using DevExpress.ExpressApp;


namespace YourSolutionName.Module {    
    public sealed partial class YourSolutionNameModule : ModuleBase {
        // ...
        public override void Setup(XafApplication application) {
            base.Setup(application);
            application.SetupComplete += Application_SetupComplete;
        }
        private void Application_SetupComplete(object sender, EventArgs e) {
            Application.ObjectSpaceCreated += Application_ObjectSpaceCreated;
        }
        private void Application_ObjectSpaceCreated(object sender, ObjectSpaceCreatedEventArgs e) {
            // ...
            NonPersistentObjectSpace npos = e.ObjectSpace as NonPersistentObjectSpace;
            if (npos != null) {
                // ...
            }
        }
    }
}

An ORM-dependent code executes a stored procedure (it uses Session in XPO and DbContext in EF Core). A corresponding persistent ObjectSpace has access to them. To allow the NonPersistentObjectSpace to access persistent ObjectSpaces, populate the CompositeObjectSpace.AdditionalObjectSpaces collection in the ObjectSpaceCreated event handler.
依赖于ORM的代码执行存储过程（它在XPO中使用Session，在EF Core中使用DbContext）。相应的持久ObjectSpace可以访问它们。要允许NonPersistentObjectSpace访问持久ObjectSpaces，请在ObjectSpaceCreated事件处理程序中填充CompositeObjectSpaceAdditionalObjectSpaces集合。

C#
private void Application_ObjectSpaceCreated(object sender, ObjectSpaceCreatedEventArgs e) {
    CompositeObjectSpace os = e.ObjectSpace as CompositeObjectSpace;
    if (os != null && !(os.Owner is CompositeObjectSpace)) {
        os.PopulateAdditionalObjectSpaces((XafApplication)sender);
    }
    NonPersistentObjectSpace npos = e.ObjectSpace as NonPersistentObjectSpace;
    if (npos != null) {
        // ...
    }
}

To be able to filter and sort a List View, use the DynamicCollection class in the ObjectsGetting event handler to populate the e.Objects collection. To separate the Module code from business logic to fetch data, create an adapter class to handle the NonPersistentObjectSpace events. The following example demonstrates how to implement this: How to filter and sort Non-Persistent Objects.
为了能够过滤和排序列表视图，请使用ObjectsGet事件处理程序中的DynamicCollection类来填充e. Objects集合。要将模块代码与业务逻辑分开以获取数据，请创建一个适配器类来处理NonPersistentObjectSpace事件。以下示例演示了如何实现这一点：如何过滤和排序非持久性对象。

Note
Filtering and sorting non-persistent object is supported only in the Client data access mode. In XAF Blazor, List Views use 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;


namespace YourSolutionName.Module.BusinessObjects {
    class MyNonPersistentObjectAdapter {
        NonPersistentObjectSpace objectSpace;
        public MyNonPersistentObjectAdapter(NonPersistentObjectSpace npos) {
            objectSpace = npos;
            objectSpace.ObjectsGetting += Npos_ObjectsGetting;
        }
        private void Npos_ObjectsGetting(object sender, ObjectsGettingEventArgs e) {
            if (e.ObjectType != typeof(MyNonPersistentObject)) {
                return;
            }
            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) {
            if (e.ObjectType == typeof(MyNonPersistentObject)) {
                e.Objects = GetDataFromSproc();
                e.ShapeData = true;
            }
        }
        List<MyNonPersistentObject> GetDataFromSproc() {
            // ...
        }
    }
}

C#
private void Application_ObjectSpaceCreated(object sender, ObjectSpaceCreatedEventArgs e) {
    CompositeObjectSpace os = e.ObjectSpace as CompositeObjectSpace;
    if (os != null && !(os.Owner is CompositeObjectSpace)) {
        os.PopulateAdditionalObjectSpaces((XafApplication)sender);
    }
    NonPersistentObjectSpace npos = e.ObjectSpace as NonPersistentObjectSpace;
    if (npos != null) {
        new MyNonPersistentObjectAdapter(npos);
    }
}

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的代码以从存储过程中获取数据）

In 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 for information on how to access data returned by the ExecuteQueryWithMetadata method How to: Access Data in SQL Query Results.
在XPO中，使用Session.ExecuteQueryWithMetadata方法从存储过程中获取数据。此方法返回列名和数据。有关如何访问ExecuteQueryWithMetadata方法返回的数据的信息，请参阅以下文章如何：访问SQL查询结果中的数据。

Use the XPObjectSpace.Session property to access a Session instance. You can access an XPObjectSpace instance from the CompositeObjectSpace.AdditionalObjectSpaces collection.
使用XPObjectSpace. Session属性访问Session实例。您可以从CompositeObjectSpace访问XPObjectSpace实例。AdditionalObjectSpaces集合。

C#
using DevExpress.ExpressApp.Xpo;
using DevExpress.Xpo;
using DevExpress.Xpo.DB;
using System.Linq;


class MyNonPersistentObjectAdapter {
    // ...
    List<MyNonPersistentObject> GetDataFromSproc() {
        XPObjectSpace persistentObjectSpace = objectSpace.AdditionalObjectSpaces.OfType<XPObjectSpace>().First();
        Session session = persistentObjectSpace.Session;
        SelectedData results = session.ExecuteQueryWithMetadata("GetEmployees");
        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<MyNonPersistentObject> objects = new List<MyNonPersistentObject>();
        foreach (SelectStatementResultRow row in results.ResultSet[1].Rows) {
            MyNonPersistentObject obj = new MyNonPersistentObject();
            obj.EmployeeID = (int)row.Values[columnNames["EmployeeID"]];
            obj.FirstName = row.Values[columnNames["FirstName"]] as string;
            obj.LastName = row.Values[columnNames["LastName"]] as string;
            obj.Title = row.Values[columnNames["Title"]] as string;
            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 Employees {
        [System.ComponentModel.DataAnnotations.Key]
        public virtual int EmployeeID { get; set; }
        public virtual string FirstName { get; set; }
        public virtual string LastName { get; set; }
        public virtual string Title { get; set; }
    }
}


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

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

Get an EFCoreObjectSpace instance from the CompositeObjectSpace.AdditionalObjectSpaces collection 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.
从GetDataFromSproc方法中的CompositeObjectSpace.AdditionalObjectSpaces集合中获取EFCoreObjectSpace实例。从EFCoreObjectSpace. DbContext属性访问YourSolutionNameEFCoreDbContext实例。调用YourSolutionNameEFCoreDbContext.FromSqlRaw方法从存储过程中获取数据。

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


class MyNonPersistentObjectAdapter {
    // ...
    List<MyNonPersistentObject> GetDataFromSproc() {
        EFCoreObjectSpace persistentObjectSpace = objectSpace.AdditionalObjectSpaces.OfType<EFCoreObjectSpace>().First();
        YourSolutionNameEFCoreDbContext dbContext = (YourSolutionNameEFCoreDbContext)persistentObjectSpace.DbContext;
        IQueryable<Employees> results = dbContext.Employees.FromSqlRaw("GetEmployees");
        List<MyNonPersistentObject> objects = new List<MyNonPersistentObject>();
        foreach (Employees employees in results) {
            MyNonPersistentObject obj = new MyNonPersistentObject();
            obj.EmployeeID = employees.EmployeeID;
            obj.FirstName = employees.FirstName;
            obj.LastName = employees.LastName;
            obj.Title = employees.Title;
            objects.Add(obj);
        }
        return objects;
    }
}