How to: Display a Detail View With Data From a Stored Procedure From the Navigation
- 7 minutes to read
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:
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:
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
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.
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.
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) { // ... } } }
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.
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.
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 for instructions on how to access data from the ExecuteQueryWithMetadata method How to: Access Data in SQL Query Results.
Use the XPObjectSpace.Session property to access a Session instance. You can access an XPObjectSpace instance from the CompositeObjectSpace.AdditionalObjectSpaces collection.
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
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 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(vb) file.
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.
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;
}
}