GridView for ASP.NET MVC - How to use Custom Data Binding and XPO to bind a grid to a table with an unknown schema

The approach demonstrated in this example can be used when you need to take advantage of the partial data loading feature, but cannot use statically declared business models mapped to the database. This approach allows you to add and remove database columns without modifying the application code, to implement a generic View consuming data from an arbitrary table (or even database) selected by the user, or to implement a SaaS application.

Prerequisites

This example is based on the following technologies:

1. eXpress Persistent Objects (XPO) are used as a data access layer. Refer to tutorials in our online documentation to get started with XPO: Getting Started.
2. XPO supports dynamic mapping to the database tables without declaring any classes. Refer to the following article for more information about this functionality: How to create persistent metadata on the fly and load data from an arbitrary table.
3. Our GridView component for ASP.NET MVC platform supports Custom Data Binding that allows you to populate a grid with data while taking into account the current grid state (filtering, sorting, grouping).
4. The XPCollection object can be used to load objects with unknown type at compile time. The XPView object allows you to build complex queries with grouping, filtering, sorting, and data aggregation.

Refer to our online documentation for more information about the concepts used in this example.

Implementation details

1. To change the persistent classes schema at run time, you need to create a separate Data Access Layer for each user instead of sharing a single ThreadSafeDataLayer instance between all users.
   Refer to the XpoHelper.cs (VB: XpoHelper.vb) file for implementation details.

2. To handle the concurrent access to a data store, this example uses the DataStorePool component. XPO automatically creates DataStorePool when the connection string contains the special parameter. The XpoDefault.GetConnectionPoolString method is used to prepare such connection string.

3. GetNewSession and GetNewUnitOfWork methods implemented in the extended XpoHelper class accept an XPDictionary instance as the last parameter. XPDictionary provides metadata information used to map persistent objects to database tables. In the example, the XPDictionary instance is prepared in the DatabaseSchemaHelper.cs (VB: DatabaseSchemaHelper.vb) file. This implementation is intended for demonstration purposes only. In real projects, it should be replaced with a custom implementation integrated with the application architecture and business requirements.

4. The XpoBindingHandlers.cs (VB: XpoBindingHandlers.vb) file contains a universal class that can be used in real projects without modifications. It provides the implementation of typed method delegates required to calling to a grid view model's GridViewModel.ProcessCustomBinding method. The usage is demonstrated in the OrdersController.cs (VB: OrdersController.vb) file.

How to use

To add this functionality to an existing ASP.NET MVC project, do the following:

1. Copy the XpoHelper.cs (VB: XpoHelper.vb) file to your project. If the project already contains a similar helper class, you can replace it or use both implementations together.

2. Add required connection strings to your Web.config file. Refer to the Microsoft documentation to learn more about the <connectionStrings> configuration section:

   • Creating a Connection String and Working with SQL Server LocalDB
   • SQL Server Connection Strings for ASP.NET Web Applications.

   If the application uses a database other than MS SQL Server, it is necessary to add a special parameter to a connection string. Refer to the following article for details: How To Create a Correct Connection String.

3. Copy the XpoBindingHandlers.cs (VB: XpoBindingHandlers.vb) file to your project.

4. Implement a helper class that builds metadata to map persistent objects to specific tables according to your business requirements. For more information, refer to the following resources:

   • KB Article: How to create persistent metadata on the fly and load data from an arbitrary table
   • DatabaseSchemaHelper.cs (VB: DatabaseSchemaHelper.vb)

Use the XpoBindingHandler class in your Controllers the same way as it is demonstrated in the OrdersController.cs (VB: OrdersController.vb) file. This class is independent from other parts of the project and can be re-used without modifications.

Files to Review

• OrdersController.cs (VB: OrdersController.vb)
• DatabaseSchemaHelper.cs (VB: DatabaseSchemaHelper.vb)
• XpoBindingHandlers.cs (VB: XpoBindingHandlers.vb)
• XpoHelper.cs (VB: XpoHelper.vb)

Documentation

• How to bind editors to XPO objects in an ASP.NET MVC application
• DevExpress XPO ORM for .NET Framework / .NET Core / .NET Standard 2.0

More Examples

• DevExpress XPO ORM for .NET Framework / .NET Core / .NET Standard 2.0