Ticket Q439032
Visible to All Users

How do I map persistent classes to another schema, e.g. other than the default "dbo" in MS SQL Server?

created 12 years ago (modified 11 years ago)

I want to change the ObjectsOwner 'dbo' to another. I've tried the articles Q147573 and CQ47539, but I could not do it.

Answers

created 12 years ago (modified 2 years ago)

Hello Alziro,

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")]. You can also add this attribute dynamically as described at How to separate database tables from built-in system tables via a prefix or custom schema.

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 feature as of now, e.g. SQL Anywhere). For instance, in our example below we used MSSqlConnectionProvider for Microsoft SQL Server, but you can use PostgreSqlConnectionProvider if you are using PostreSQL.

To do this in an application that uses DataStorePool (XpoDefault.GetConnectionPoolString in non-XAF and XPObjectSpaceProviderOptions.EnablePoolingInConnectionString in XAF), create a connection provider's descendant as described in the following article: How to create a custom XPO connection provider and then use it in an XAF application. Set the ObjectsOwner property in the provider's constructor.

If your XAF application does not use XPO's data store provider pooling, the aforementioned solution is also suitable. Another solution is to create a ConnectionDataStoreProvider or ConnectionStringDataStoreProvider instance (an IXpoDataStoreProvider implementer) and subscribe to its DataStoreCreated event (17.2+). Then, pass the data store provider as an argument of an object space provider (this is usually done within the YourSolutionName.Wxx/WxxApplication.xx file).

C#
using System; using DevExpress.ExpressApp; using DevExpress.ExpressApp.Security; using DevExpress.ExpressApp.Security.ClientServer; 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) { IXpoDataStoreProvider provider = XPObjectSpaceProvider.GetDataStoreProvider(args.ConnectionString, args.Connection, false); if (provider is ConnectionStringDataStoreProvider) { ((ConnectionStringDataStoreProvider)provider).DataStoreCreated += provider_DataStoreCreated; } if (provider is ConnectionDataStoreProvider) { ((ConnectionDataStoreProvider)provider).DataStoreCreated += provider_DataStoreCreated; } args.ObjectSpaceProviders.Add(new XPObjectSpaceProvider(provider, false)); // OR // if you are using our security module with integrated mode. //args.ObjectSpaceProviders.Add(new SecuredObjectSpaceProvider((ISelectDataSecurityProvider)Security, provider, false)); args.ObjectSpaceProviders.Add(new NonPersistentObjectSpaceProvider(TypesInfo, null)); } private void provider_DataStoreCreated(object sender, DataStoreCreatedEventArgs e) { MSSqlConnectionProvider underlyingXpoProvider = e.DataStore as MSSqlConnectionProvider; if(underlyingXpoProvider != null) { underlyingXpoProvider.ObjectsOwner = "MySchema"; // This schema must be created manually using your database server means before starting the program. } } //... } }

Blazor and WinForms .NET 6+ applications use the Application Builder and do not call the CreateDefaultObjectSpaceProvider method. They initialize XPO database providers using the builder.ObjectSpaceProviders.AddXpo and builder.ObjectSpaceProviders.AddSecuredXpo methods. Use the XPObjectSpaceProviderOptions.UseCustomDataStoreProvider method to initialize a custom IXpoDataStoreProvider in this case.

C#
public class Startup { public void ConfigureServices(IServiceCollection services) { //... services.AddSingleton<SharedDataStoreProviderManager>(); services.AddXaf(Configuration, builder => { builder.ObjectSpaceProviders .AddSecuredXpo((serviceProvider, options) => { options.ConnectionString = GetConnectionString(Configuration); options.EnablePoolingInConnectionString = false; options.ThreadSafe = true; options.UseSharedDataStoreProvider = true; var providerManager = serviceProvider.GetService<SharedDataStoreProviderManager>(); var provider = providerManager.GetSharedDataStoreProvider(options.ConnectionString); options.UseCustomDataStoreProvider(provider); }) //... public class SharedDataStoreProviderManager { private ConcurrentDictionary<string, IXpoDataStoreProvider> dataStoreProviders = new ConcurrentDictionary<string, IXpoDataStoreProvider>(); public IXpoDataStoreProvider GetSharedDataStoreProvider(string connectionString) => dataStoreProviders.GetOrAdd(connectionString, connectionString => GetDataStoreProvider(connectionString)); private IXpoDataStoreProvider GetDataStoreProvider(string connectionString) { var provider = XPObjectSpaceProvider.GetDataStoreProvider(connectionString, null, false); if(provider is ConnectionStringDataStoreProvider connectionStringDataStoreProvider) { connectionStringDataStoreProvider.DataStoreCreated += (dataStore, destination) => { if(destination.DataStore is MSSqlConnectionProvider) { ((MSSqlConnectionProvider)destination.DataStore).ObjectsOwner = "schema1"; } }; } return provider; } }

This example implements SharedDataStoreProviderManager to use a single shared XPObjectSpaceProvider in a Blazor application. You can skip this step in WinForms and initialize an XPObjectSpaceProvider directly in the AddSecuredXpo method's delegate.

Older versions
You need to create a custom IXpoDataStoreProvider implementer. For example:

C#
public class MyXpoDataStoreProvider : ConnectionStringDataStoreProvider, IXpoDataStoreProvider { public MyXpoDataStoreProvider(string connectionString) : base(connectionString) { } private IDataStore CustomizeDataStore(IDataStore dataStore) { if(dataStore is MSSqlConnectionProvider) { ((MSSqlConnectionProvider)dataStore).ObjectsOwner = "CustomSchema"; } 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(bool allowUpdateSchema, out IDisposable[] disposableObjects) { return CustomizeDataStore(base.CreateUpdatingStore(allowUpdateSchema, out disposableObjects)); } IDataStore IXpoDataStoreProvider.CreateWorkingStore(out IDisposable[] disposableObjects) { return CustomizeDataStore(base.CreateWorkingStore(out disposableObjects)); } IDataStore IXpoDataStoreProvider.CreateSchemaCheckingStore(out IDisposable[] disposableObjects) { return CustomizeDataStore(base.CreateSchemaCheckingStore(out disposableObjects)); } }

See also the How to customize the underlying database provider options and data access behavior in XAF article for more details.

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.

    Show previous comments (12)
    Dennis Garavsky (DevExpress) 7 years ago

      Thanks for your feedback, Martin. I've fixed the typo.

        Hello,

        I tried both options to map my persistent classes to a non-default schema as attached. But, when I login the system, it still go to select dbo.PermissionPolicyUser. Do I missing something?

        Thanks,
        Gary

        DevExpress Support Team 6 years ago

          @Gary, I've created a separate ticket on your behalf (T708164: Map persistent classes to a schema other than the default "dbo"). It has been placed in our processing queue and will be answered shortly.

          Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

          Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.