I want to change the ObjectsOwner 'dbo' to another. I've tried the articles Q147573 and CQ47539, but I could not do it.
How do I map persistent classes to another schema, e.g. other than the default "dbo" in MS SQL Server?
Answers
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.
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
@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.