Problem
XAF can setup a database (DB) from scratch or connect to an existing DB and scaffold CRUD UI for it automatically. We often get questions on how to use XAF to create an application for existing data stores in an enterprise. Often, such backend systems are old and badly documented, have specific denormalized structures with composite keys, used by other non-XAF apps and thus cannot be changed. Since database schema is frozen and there is often a ready business logic layer based on stored procedures (SP), potential XAF developers want to re-use as much of that legacy instead of creating a new ORM-based layer.
In general, we always recommend that you run through our getting started tutorials and try to accomplish the most important business tasks for your future app using XAF approaches. Contact support in case of any technical questions or general problems. This article will also provide guidance for the most popular requirements.
Solutions
1. Map to existing database tables.
XAF supports two ORMs for a data exchange with the database: eXpress Persistent Objects (XPO) and Entity Framework (EF 6). If you give XAF a chance with ORM and its persistent objects mapped to your database tables, you can get started with this article: eXpressApp Framework > Task-Based Help > How to: Generate XPO Business Classes for Existing Data Tables. Take special note that you can easily establish relationships between your objects and ORM will manage all the data retrieval tasks for you (like joining multiple tables). I suggest you check out the Object Relationships help article to get started with this. Most likely, it will be easier for you if XPO creates all the associated objects using the ORM wizards. As for filtering data, the object-oriented criteria language or LINQ is always at your service with our frameworks: Querying a Data Store.
For XAF and almost any ORM library (XPO or EF) composite keys are not welcomed (How to create a persistent object for a database table with a compound key). If you have a non-standard table schema that cannot be changed, you may want to consider these approaches:
- Map your persistent classes to an SQL view;
- Use non-persistent objects;
- Integrate custom controls that access data bypassing XAF/ORM methods.
Still, if you have a majority of such non-standard tables, the use of XAF or any ORM may be not justified with it. The same goes if you have very custom requirements for the UI and need to customize each and every UI screen a lot. I mean when your UI generally differs a lot from what you see in XAF demos and documentation.
2. Reuse existing business logic layer based on SP or external services.
With XAF, the ORM concept is very important: XAFers should NOT normally think of database tables, SP, SQL statements, but rather operate data in an object-oriented manner. Refer to the following articles for more information:
Applying Domain-Driven Design and Patterns
XAF - Best practices for domain objects
Business Classes vs Database Tables
Ways to Implement Business Logic
Create, Read, Update and Delete Data
Since XAF does not have any built-in support for SP, there is no simple way to use SP to commit changes to the database either. The object space concept used by XAF expects that the database queries are generated dynamically based on business objects and filters defined in code and UI. Various filters can be created at runtime and you cannot know beforehand which SP might be needed to perform all possible data operations.
While it is technically possible to inject direct SQL calls into the standard XAF CRUD processing due to XAF flexibility, using raw SQL widely is NOT a primary and recommended usage scenario for XAF. Exceptions are scenarios where data from SP or external services is just retrieved for view/read purposes (no modification).
Getting CRUD work with SP or external services will be difficult, especially without good XAF/XPO/EF knowledge, because you will need to use their low level APIs. As a result, chances are high that XAF may be not the best choice for this particular project and requirements - evaluate them carefully. In addition, it may also be difficult to effectively use XAF if you do not have previous experience programming WinForms, ASP.NET apps. Please check out this article to see how XAF's development approach compares to others.
Tips to re-use existing business logic layer only in a few application places
If you are fine with the ORM and its object-oriented approach for the bigger application part (= the use of XAF is justified), you may find the tips below helpful:
- Allow SP and other direct SQL calls in your XAF application when the XPO-based features of the security system are enabled: How to: Call Direct SQL Queries in Integrated Mode or through the Middle Tier Application Server;
- To call direct SQL queries with XPO, use the XPObjectSpace.Session property rather than creating a new session connected to the default data layer. With EF, refer to the How to access EF's DbContext from EFObjectSpace article.
- If you just need to read data from SP and present it in XAF views (for instance, in a ListView or report) consider using non-persistent objects as shown below:
How to show data from a stored procedure in a ListView with XPO
How to show data from a stored procedure in a ListView with EF
Certain underlying components (for instance, DevExpress Dashboards and XtraReports) provide specialized APIs for binding to SQL results: How to bind dashboards, reports, and analysis objects to external SQL Database Connections.
- Although XPO allows you to call SP through special methods, it does not support direct mapping of business objects to them for CRUD. If this design is mandatory, consider using EF instead of XPO in your application, because it supports it: Code First - Insert, Update, Delete Stored Procedure Mapping. We have not extensively tested this approach with EF in XAF and it may require other modifications.
- XAF object spaces are committed within separate transactions, and you cannot include your SP call into them. If you call your direct SQL before committing the object space, make sure to rollback changes if the subsequent commit fails. With XPO, additionally check that the object's session is not NestedUnitOfWork, because the object is not actually saved to the database in this case. And if you call your procedure after committing the object space, your procedure should not fail. Since XAF knows nothing about your procedures and changes they make to data, you need to manually reload modified objects in Views.
- The following XAF APIs may be helpful to integrate SP into the standard XAF CRUD processing: IObjectSpace > Committing, Committed, CustomCommitChanges, CustomDeleteObjects, Refresh. Refer to the following Support Center tickets for examples on their usage: Q438763 | Q423413 | Q578113.
- Although XPO allows you to call SP through special methods, it does not support direct mapping business objects to them for CRUD. If this design is mandatory, consider using EF instead of XPO in your application, because it supports it: Code First - Insert, Update, Delete Stored Procedure Mapping. We have not extensively tested this approach with EF in XAF and it may require other modifications.
See Also
Concepts>Extend Functionality>Customize Controllers and ActionsCan I connect an XAF application to a custom data source (Web service, OData service, NoSQL database, etc.)?
How to reuse XAF Views and other standard module functionality in non-XAF apps, forms or controls
How much of XAF's default UI is customizable.
Search keywords
legacy, unchanged, frozen, schema, database, data store, reuse, external, logic, BLL, stored procedures, SP, SQL, triggers, custom CRUD, custom save, delete objects, manipulate data after data insertion, OnSaving, OnSaved, ExecuteSproc, ExecuteQuery, result set, override, replace, standard INSERT, UPDATE, DELETE, SELECT, inject, plug in.