Description:
I want to have all database tables and field names in the upper case, without using persistent attributes for every class and property.
Answer:
Applies to:
eXpress Persistent Objects
eXpressApp Framework
One of the approaches to accomplishing this task is to create a custom XPO connection provider. This class will be a descendant of one of the standard connection providers listed in the Database Systems Supported by XPO help topic.
1. Creating a custom connection provider class
Here we will create a new MyAccessConnectionProvider class by inheriting from the AccessConnectionProvider class. I will declare this class in a separate file within our windows forms application project.
Please look at the resulting code:
C#using System;
using System.Data;
using DevExpress.Xpo.DB;
using DevExpress.Xpo.DB.Helpers;
using System.Data.OleDb;
using System.Threading;
using DevExpress.Persistent.Base;
namespace WinSolution.Win {
public class MyAccessConnectionProvider : AccessConnectionProvider {
public MyAccessConnectionProvider(IDbConnection connection, AutoCreateOption autoCreateOption) : base(connection, autoCreateOption) { }
public override string ComposeSafeColumnName(string columnName) {
return base.ComposeSafeColumnName(columnName).ToUpper(Thread.CurrentThread.CurrentCulture);
}
public override string ComposeSafeTableName(string tableName) {
return base.ComposeSafeTableName(tableName).ToUpper(Thread.CurrentThread.CurrentCulture);
}
public new static IDataStore CreateProviderFromString(string connectionString, AutoCreateOption autoCreateOption, out IDisposable[] objectsToDisposeOnDisconnect) {
IDbConnection connection = new OleDbConnection(connectionString);
objectsToDisposeOnDisconnect = new IDisposable[] { connection };
return CreateProviderFromConnection(connection, autoCreateOption);
}
public new static IDataStore CreateProviderFromConnection(IDbConnection connection, AutoCreateOption autoCreateOption) {
if (((System.Data.OleDb.OleDbConnection)connection).Provider.StartsWith("Microsoft.Jet.OLEDB")
|| ((System.Data.OleDb.OleDbConnection)connection).Provider.StartsWith("Microsoft.ACE.OLEDB"))
return new MyAccessConnectionProvider(connection, autoCreateOption);
else
return null;
}
public new static string GetConnectionString(string database, string userid, string password) {
return String.Format("{3}={4};Provider=Microsoft.Jet.OLEDB.4.0;Mode=Share Deny None;data source={0};user id={1};password={2};", database, userid, password, DataStoreBase.XpoProviderTypeParameterName, XpoProviderTypeString);
}
public new static void Register() {
DataStoreBase.RegisterDataStoreProvider(XpoProviderTypeString, CreateProviderFromString);
DataStoreBase.RegisterDataStoreProvider(typeof(System.Data.OleDb.OleDbConnection).FullName, CreateProviderFromConnection);
}
public override string ComposeSafeConstraintName(string constraintName) {
return base.ComposeSafeConstraintName(constraintName).ToUpper(Thread.CurrentThread.CurrentCulture);
}
public new const string XpoProviderTypeString = "MyMSAccess";
}
}
Please take special note of the overridden ComposeSafeColumnName and ComposeSafeTableName methods where I used the ToUpper method to accomplish our task.
The provider must be registered by associating it with a connection type and an identifier so that XPO can instantiate the correct provider. For this purpose, create new static CreateProviderFromConnection and CreateProviderFromString methods and create a static constructor. In the static constructor, call the DataStoreBase.RegisterDataStoreProvider method to register the CreateProviderFrom~ delegates. We recommend that you choose a different name identifier for your provider, which doesn't match the built-in providers' identifiers. Declare the XpoProviderTypeString constant string field to specify your own name identifier. Here this will be "MyMSAccess". This identifier will be used in the Register and other methods. Note that the static CreateProviderFromString method calls the static CreateProviderFromConnection method of our provider. Static methods of the original provider should not be used.
2. Connection string
To tell XPO that it must use our connection string to connect to our Access database we need to provide the provider's identifier in the connection string for the XpoProvider attribute. For instance, this is the code in the XAF application designer file:
C#this.ConnectionString = "XpoProvider=MyMSAccess;Provider=Microsoft.Jet.OLEDB.4.0;Mode=Share Deny None;data source=WinSolution.mdb;user id=Admin;password=;";
You can also provide the same connection string in the configuration file:
XML<connectionStrings>
<add name="ConnectionString" connectionString="XpoProvider=MyMSAccess;Provider=Microsoft.Jet.OLEDB.4.0;Mode=Share Deny None;data source=WinSolution.mdb;user id=Admin;password=;" />
</connectionStrings>
or even use the following code to specify the connection string programmatically:
C#this.ConnectionString = MyAccessConnectionProvider.GetConnectionString("WinSolution.mdb");
3. Registering our connection provider
Before using our connection provider and making it known to XPO we need to register it. To accomplish this, we need to call the static Register method in the constructor of the XafAplication descendant (defined in the WinApplication and WebApplication files):
C#namespace WinSolution.Win {
public partial class WinSolutionWindowsFormsApplication : WinApplication {
public WinSolutionWindowsFormsApplication () {
InitializeComponent();
MyAccessConnectionProvider.Register();
//...
In non-XAF applications, if your provider should replace the built-in provider (is registered with the built-in provider identifier and connection type), the provider's Register method must be called after calling any method of the DevExpress.Xpo.XpoDefault class. This is required because XpoDefault registers built-in providers on first invocation, and will overwrite previous registrations.
See Also:
How to customize the underlying database provider options and data access behavior in XAF
How to reopen a broken connection when using a PostgreSQL database
How to implement a custom XPO connection provider for AdoNetCore.AseClient
Custom XPO connection providers for MS SQL Server:
XPO, SQL Server and spatial data revisited
Persisting DateTimeOffset with XPO
Does not compile on 12.1.4
Thank you for the feedback, Walter. We will update this article as soon as we can.
Hi Dennis,
can you please provide a similar example in VB.net? (and, if possible, for Oracle)
Regards
Wieland
Hello Wieland,
I used the free C# to VB.NET converter from http://www.developerfusion.com/tools/convert/csharp-to-vb/ to convert this example to VB.NET (see in the attachment).
I hope you will find this info helpful.
I do not have an example for Oracle, but this seems to be a matter of using the appropriate connection provider class from the http://documentation.devexpress.com/#XPO/CustomDocument2114 help article.
Feel free to create a separate ticket if you experience any further difficulties with this.
Hi Dennis, thanks for your answer.
I got the Connection Provider running in VB, but still struggling with the Oracle-connection.
I apologize for not having withdrawn my question, as i did it on my own, the same way you described.
regards
@Wieland: I will be glad to assist you further if you provide me with a small VB.NET sample where you tried to implement this solution, but it did not work as expected. Not to mix everything within a single thread, please create a separate ticket in the Support Center for this.
Is it possible to register the connection provider in a module, rather than the application?
@Sam: Sure, you can do this in the module's constructor.
It seems to me that there should be one more step or the title of the article should be changed
You write, "Before using our connection provider and making it known to XPO"
And the title says you are going to show us how to use it, but the article never explains how to use it.
Maybe I'm just being dense. - extreme newbie to xpo and xaf blazor.
Thanks
Hello,
I've created a separate ticket on your behalf (T958214: How to use custom XPO connection provider). It has been placed in our processing queue and will be answered shortly.
Thanks,
Andrey
I have implemented this solution for a Postgres connection provider but I get an exception when the application starts and the database is not existing because the CreateProviderFromString autoCreateOption parameter is set to SchemaAlreadyExists.
Forcing it to DatabaseAndSchema solves the problem but I would like to be able to change it from outside, i.e. the caller should pass a different parameter.
using DevExpress.Xpo.DB; using Npgsql; using System; using System.Data; namespace PrgDig.PostGIS { /// <summary> /// <see cref="https://supportcenter.devexpress.com/ticket/details/k18098/how-to-create-a-custom-xpo-connection-provider-and-then-use-it-in-an-xaf-application"/> /// </summary> public class PostGISConnectionProvider : PostgreSqlConnectionProvider { public PostGISConnectionProvider(IDbConnection connection, AutoCreateOption autoCreateOption) : base(connection, autoCreateOption) { } public new static IDataStore CreateProviderFromString(string connectionString, AutoCreateOption autoCreateOption, out IDisposable[] objectsToDisposeOnDisconnect) { IDbConnection connection = new NpgsqlConnection(connectionString); objectsToDisposeOnDisconnect = new IDisposable[] { connection }; return CreateProviderFromConnection(connection, autoCreateOption); } public new static IDataStore CreateProviderFromConnection(IDbConnection connection, AutoCreateOption autoCreateOption) { return new PostGISConnectionProvider(connection, autoCreateOption); } public new static string GetConnectionString(string server, int port, string userId, string password, string database) { return $"{XpoProviderTypeParameterName}={XpoProviderTypeString};Server={server};Port={port};Database={database};User ID={userId};Password={password}"; } public new static string GetConnectionString(string server, string userId, string password, string database) { return $"{XpoProviderTypeParameterName}={XpoProviderTypeString};Server={server};Database={database};User ID={userId};Password={password}"; } public new static void Register() { RegisterDataStoreProvider(XpoProviderTypeString, CreateProviderFromString); RegisterDataStoreProvider(typeof(PostgreSqlConnectionProvider).FullName, CreateProviderFromConnection); } public new const string XpoProviderTypeString = "PostGIS"; /// <summary> /// <see cref=">https://supportcenter.devexpress.com/ticket/details/t439479/add-support-for-geometry-in-x"/> /// <see cref="https://community.devexpress.com/blogs/oliver/archive/2017/01/16/xpo-sql-server-and-spatial-data-revisited.aspx"/> /// </summary> /// <param name="value"></param> /// <param name="args"></param> /// <returns></returns> protected override object ReformatReadValue(object value, ReformatReadValueArgs args) { if (value != null && value is NetTopologySuite.Geometries.Geometry) { return value; } else { return base.ReformatReadValue(value, args); } } } }
Hello,
I created a separate ticket on your behalf: (T988926: How to set AutoCreateOption dynamycally). We placed it in our processing queue and will process it shortly.
Thanks,
Andrey
Hi.
How should I tell XAF/XPO to use my connection provider if I set the connection using WinApplication's designer (dragging MySqlConnection from Toolbox) and not in App.config?
I cannot specify the XpoProvider there.
Thank you.
Regards.
Hello Ivan,
I created a separate ticket for your question and will answer you there soon: T1171312: How to specify which Connection Provider an application should use when creating a MySqlConnection manually.