Ticket K18098
Visible to All Users

How to create a custom XPO connection provider and then use it in an XAF application

created 17 years ago (modified 2 years ago)

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

Show previous comments (11)
Andrey K (DevExpress Support) 4 years ago

    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.

      Anatol (DevExpress) 2 years ago

        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.

        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.