How to: Display a Detail View With Data From a Stored Procedure From the Navigation(如何:使用来自导航中的存储过程的数据显示详细视图)
This example demonstrates how to show a Detail View for data fetched from a stored procedure from the Navigation.
此示例演示如何显示从导航的存储过程中获取的数据的详细信息视图。
This example uses the Northwind database and Non-Persistent Objects to store data from the stored procedure. The stored procedure is defined as follows:
此示例使用NorthWind数据库和非持久性对象来存储存储过程中的数据。存储过程定义如下:
SQL
CREATE PROCEDURE GetEmployee
@ID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Employees
WHERE EmployeeID = @ID
END
GO
Create Non-Persistent Objects in the Platform-Agnostic Module(在与平台无关的模块中创建非持久性对象)
In the platform-agnostic module, create the following non-persistent class:
在与平台无关的模块中,创建以下非持久类:
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 Detail View
处理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 separate the Module code from business logic to fetch data, create an adapter class to handle the NonPersistentObjectSpace events. Handle the NonPersistentObjectSpace.ObjectByKeyGetting event to return a displayed object.
要将模块代码与业务逻辑分离以获取数据,请创建一个适配器类来处理NonPersistentObjectSpace事件。处理NonPersistentObjectSpace. ObjectByKeyGet事件以返回显示的对象。
C#
using DevExpress.ExpressApp;
namespace YourSolutionName.Module.BusinessObjects {
class MyNonPersistentObjectAdapter {
NonPersistentObjectSpace objectSpace;
public MyNonPersistentObjectAdapter(NonPersistentObjectSpace npos) {
objectSpace = npos;
objectSpace.ObjectByKeyGetting += ObjectSpace_ObjectByKeyGetting;
}
private void ObjectSpace_ObjectByKeyGetting(object sender, ObjectByKeyGettingEventArgs e) {
if (e.ObjectType != typeof(MyNonPersistentObject)) {
return;
}
e.Object = GetObjectFromSproc(e.Key);
}
MyNonPersistentObject GetObjectFromSproc(object key) {
// ...
}
}
}
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 GetObjectFromSproc method should contain ORM-dependent code to get data from a stored procedure.
GetObjectFromSproc方法应该包含依赖于ORM的代码,以便从存储过程中获取数据。
Add a navigation item for the MyNonPersistentObject Detail View with the object key as described in How to: Display a Non-Persistent Object’s Detail View.
使用对象键为MyNonPersistentObject详细信息视图添加导航项,如如何:显示非持久性对象的详细信息视图中所述。
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 for instructions on how to access data from 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. 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 {
// ...
MyNonPersistentObject GetObjectFromSproc(object key) {
XPObjectSpace persistentObjectSpace = objectSpace.AdditionalObjectSpaces.OfType<XPObjectSpace>().First();
Session session = persistentObjectSpace.Session;
SelectedData results = session.ExecuteQueryWithMetadata($"GetEmployee @ID={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);
}
MyNonPersistentObject obj = new MyNonPersistentObject();
if (results.ResultSet[1].Rows.Length > 0) {
SelectStatementResultRow row = results.ResultSet[1].Rows[0];
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;
}
return obj;
}
}
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 GetObjectFromSproc method. Access your YourSolutionNameEFCoreDbContext instance from the EFCoreObjectSpace.DbContext property. Call the YourSolutionNameEFCoreDbContext.Employees.FromSqlRaw method to get data from a stored procedure.
从GetObjectFromSproc方法中的CompositeObjectSpace.AdditionalObjectSpaces集合中获取EFCoreObjectSpace实例。从EFCoreObjectSpace. DbContext属性访问YourSolutionNameEFCoreDbContext实例。调用YourSolutionNameEFCoreDbContext。FromSqlRaw方法从存储过程中获取数据。
C#
using DevExpress.ExpressApp.EFCore;
using Microsoft.EntityFrameworkCore;
class MyNonPersistentObjectAdapter {
// ...
MyNonPersistentObject GetObjectFromSproc(object key) {
EFCoreObjectSpace persistentObjectSpace = objectSpace.AdditionalObjectSpaces.OfType<EFCoreObjectSpace>().First();
YourSolutionNameEFCoreDbContext dbContext = (YourSolutionNameEFCoreDbContext)persistentObjectSpace.DbContext;
IQueryable<Employees> results = dbContext.Employees.FromSqlRaw($"GetEmployee @ID={key}");
MyNonPersistentObject obj = new MyNonPersistentObject();
Employees employees = results.ToList().FirstOrDefault();
if (employees != null) {
obj.EmployeeID = employees.EmployeeID;
obj.FirstName = employees.FirstName;
obj.LastName = employees.LastName;
obj.Title = employees.Title;
}
return obj;
}
}