Search This Blog

Thursday, March 12, 2015

How to map ORM data model to another schema, e.g. other than the default "dbo" in MS SQL Server?

I just wanted to bring your attention to the recent update of one of the articles in our support knowledge, which is devoted to advanced data layer customizations - telling XAF business objects to use a different owner schema in the database.



Here we go:


If you use Entity Framework for data access, then you can use the DbModelBuilder API or TableAttribute as described at http://devproconnections.com/entity-framework/working-schema-names-entity-framework-code-first-design

If you use XPO for data access, you can map your persistent classes to a non-default schema using one of the two good options below:
1. Decorate your persistent class with DevExpress.Xpo.PersistentAttribute and specify the schema name followed by a dot and the table name, e.g.:[Persistent("MySchema.MyTable")]. When using the ORM Data Model Wizard to generate an XPO data model from an existing database, this mapping is done automatically. Be careful when using dots within the Persistent attribute as it is an indicator for using a custom schema name (dots in table names are not currently supported).

2. Specify the ObjectsOwner property of the required XPO database connection provider (not all providers support this as of now, e.g. SQL Anywhere).
To do this customization in XAF, create a custom IXpoDataStoreProvider implementer and pass its instance to the XPObjectSpaceProvider constructor (this is usually done within YourSolutionName.Wxx/WxxApplication.xx file). Refer to the example code below:


using System;
using DevExpress.ExpressApp;
using DevExpress.ExpressApp.Security;
using DevExpress.ExpressApp.Win;
using DevExpress.ExpressApp.Xpo;
using DevExpress.Xpo.DB;

namespace MainDemo.Win {
    public partial class MainDemoWinApplication : WinApplication {
        ...
        protected override void CreateDefaultObjectSpaceProvider(CreateCustomObjectSpaceProviderEventArgs args) {
            //args.ObjectSpaceProvider = new XPObjectSpaceProvider(args.ConnectionString, args.Connection);
            args.ObjectSpaceProvider = new XPObjectSpaceProvider(
                new MyXpoDataStoreProvider(args.ConnectionString), false
            );
        }
    }
    public class MyXpoDataStoreProvider : ConnectionStringDataStoreProvider, IXpoDataStoreProvider {
        public MyXpoDataStoreProvider(string connectionString) : base(connectionString) { }
        private IDataStore CustomizeDataStore(IDataStore dataStore) {
            if (dataStore is MSSqlConnectionProvider) {
                ((MSSqlConnectionProvider)dataStore).ObjectsOwner = "MySchema";
            }
            return dataStore;
            //Use this if you also need a data layer caching in the client app.
            //DataCacheRoot cacheRoot = new DataCacheRoot(dataStore);
            //return new DataCacheNode(cacheRoot);
        }
        IDataStore IXpoDataStoreProvider.CreateUpdatingStore(out IDisposable[] disposableObjects) {
            return CustomizeDataStore(base.CreateUpdatingStore(out disposableObjects));
        }
        IDataStore IXpoDataStoreProvider.CreateWorkingStore(out IDisposable[] disposableObjects) {
            return CustomizeDataStore(base.CreateWorkingStore(out disposableObjects));
        }
    }
}

 
Take special note that XPO does not currently create a custom schema - it must already be present in the database. Refer to the documentation for your database for more details on how to create custom schemas (e.g. check this article for MS SQL Server).


No comments:

Post a Comment