Current filter:
                                You should refresh the page.
                                  • 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 all comments
                                • Walter Gumirato 07.02.2012

                                  Does not compile on 12.1.4

                                • Dennis (DevExpress) 07.04.2012

                                  Thank you for the feedback, Walter. We will update this article as soon as we can.

                                • Wieland Voß 06.26.2013

                                  Hi Dennis,
                                  can you please provide a similar example in VB.net? (and, if possible, for Oracle)
                                  Regards
                                  Wieland

                                • Dennis (DevExpress) 06.27.2013

                                  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.

                                • Wieland Voß 07.02.2013

                                  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

                                • Dennis (DevExpress) 07.03.2013

                                  @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.

                                • Sam Curtis 07.03.2018

                                  Is it possible to register the connection provider in a module, rather than the application?

                                • Dennis (DevExpress) 07.04.2018

                                  @Sam: Sure, you can do this in the module's constructor.

                                0 Solutions

                                Creation Date Importance Sort by