ChangeDatabase, EntityFramework and ASP.NET Providers

ChangeDatabase, EntityFramework and ASP.NET Providers

Postby Remco Blok » Thu 17 May 2012 09:31

Hello,

I use dotConnect for Oracle for my EntityFramework DbContext and I also use the ASP.NET providers. I like to connect to Oracle as one user and then work with the objects in another schema. It was great that Devart implemented the ChangeDatabase method on the OracleConnection class.

When using EntityFramework, I can change schema by writing a custom IDbConnectionFactory as follows:

Code: Select all
    public class OracleConnectionFactory : IDbConnectionFactory
    {
        private readonly string baseConnectionString;
        private readonly IDictionary<DbConnection, string> connections = new Dictionary<DbConnection, string>();
        private Func<string, DbProviderFactory> providerFactoryCreator;

        public OracleConnectionFactory()
        {
            this.baseConnectionString = "Server=localhost;Sid=XE";
        }

        public OracleConnectionFactory(string baseConnectionString)
        {
            Contract.Requires(baseConnectionString != null);
            this.baseConnectionString = baseConnectionString;
        }

        public string BaseConnectionString
        {
            get
            {
                return this.baseConnectionString;
            }
        }

        internal Func<string, DbProviderFactory> ProviderFactory
        {
            get
            {
                if (this.providerFactoryCreator == null)
                {
                    this.providerFactoryCreator = DbProviderFactories.GetFactory;
                }

                return this.providerFactoryCreator;
            }

            set
            {
                this.providerFactoryCreator = value;
            }
        }

        public DbConnection CreateConnection(string nameOrConnectionString)
        {
            Contract.Assume(!string.IsNullOrWhiteSpace(nameOrConnectionString));

            var connectionString = nameOrConnectionString;
            var name = string.Empty;

            if (nameOrConnectionString.IndexOf('=') == -1)
            {
                connectionString = new OracleConnectionStringBuilder(this.baseConnectionString).ConnectionString;
                name = nameOrConnectionString.ToUpperInvariant();
                var index = name.LastIndexOf('.');
                if (index != -1)
                {
                    var startIndex = index + 1;
                    name = name.Substring(startIndex, name.Length - startIndex);
                }
            }

            DbConnection connection;
            try
            {
                connection = this.ProviderFactory("Devart.Data.Oracle").CreateConnection();
                connection.ConnectionString = connectionString;
            }
            catch
            {
                connection = new OracleConnection(connectionString);
            }

            if (!string.IsNullOrEmpty(name))
            {
                connection.Disposed += this.OnConnectionDisposed;
                connection.StateChange += this.OnConnectionStateChange;

                this.connections.Add(connection, name);
            }

            return connection;
        }

        private void OnConnectionDisposed(object sender, EventArgs e)
        {
            var connection = (DbConnection)sender;
            connection.Disposed -= this.OnConnectionDisposed;
            connection.StateChange -= this.OnConnectionStateChange;
            this.connections.Remove(connection);
        }

        private void OnConnectionStateChange(object sender, StateChangeEventArgs e)
        {
            if (e.CurrentState == ConnectionState.Open)
            {
                var connection = (DbConnection)sender;
                string name;
                if (this.connections.TryGetValue(connection, out name))
                {
                    connection.ChangeDatabase(name);
                }
            }
        }
    }


This connection factory will only change schema if the nameOrConnectionString parameter represents a name, not a connection string. So, in my web/app.config I configure this connection factory with a base connection string in the entityframework/defaultConnectionFactory element, but I do not add a connection string to the connectionStrings element, otherwise the nameOrConnectionString parameter represents a connection string, not a name.

I choose to use a connection factory, because I did not want to polute every place where I create a DbContext with additional code to explicitly create an OracleConnection and handle the StateChange event as per Devart's blog post here: http://blogs.devart.com/dotconnect/entity-framework-code-first-support-for-oracle-mysql-postgresql-and-sqlite.html.

More important though is that my code must be database agnostic. Simply by changing the connection string users must be able to use Microsoft SQL Server, or Oracle.

So using a connection factory works fine. But I can imagine a scenario where you might have two EntityFramework DbContexts for different databases. Using this connection factory only works if both databases are Oracle and share the same base connection string.

I also use Devart's ASP.NET providers for Membership and Profile. Again, I would like to connect to Oracle as one user and then work with the objects in another schema.

I think to get that to work I would have to derive from Devart's OracleProviderFactory and override its CreateConnection method so that I can handle the StateChange event to change to another schema. But how can I specify which schema? I think the only way to do this is to add a custom connection string property. I will then have to derive from OracleConnectionStringBuilder and derive from OracleConnection. Could Devart provide guidance on how I go about that? I see the OracleConnection class exposes an internal ConnectionOptions property. Being internal I cannot change these ConnectionOptions. So it would be best if Devart could add support for this schema property in the connection string. I will then no longer need my EntityFramework connection factory either.

Remco
Remco Blok
 
Posts: 25
Joined: Tue 14 Dec 2010 12:34

Re: ChangeDatabase, EntityFramework and ASP.NET Providers

Postby Shalex » Thu 24 May 2012 12:50

Remco Blok wrote:I also use Devart's ASP.NET providers for Membership and Profile. Again, I would like to connect to Oracle as one user and then work with the objects in another schema.

Remco Blok wrote:So it would be best if Devart could add support for this schema property in the connection string.

We will post here when the corresponding connection parameter is implemented. Thank you for your suggestion.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: ChangeDatabase, EntityFramework and ASP.NET Providers

Postby Shalex » Wed 30 May 2012 11:27

The Initialization Command connection string parameter is implemented. It specifies a database-specific command that is executed immediately after opening the connection. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: ChangeDatabase, EntityFramework and ASP.NET Providers

Postby Shalex » Fri 08 Jun 2012 15:52

New build of dotConnect for Oracle 7.0.17 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=24290 .
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: ChangeDatabase, EntityFramework and ASP.NET Providers

Postby Remco Blok » Mon 11 Jun 2012 14:13

Hi Shalex,

Thanks for the quick response. Could you clarify how to use this initialization command in the connection string. The command I would like to execute is like ALTER SESSION SET CURRENT_SCHEMA = MYSCHEMA.

many thanks

Remco
Remco Blok
 
Posts: 25
Joined: Tue 14 Dec 2010 12:34

Re: ChangeDatabase, EntityFramework and ASP.NET Providers

Postby Remco Blok » Mon 11 Jun 2012 14:32

never mind. The following connection string works for me (using OS Authentication):

Server=localhost;Sid=XE;Initialization Command=ALTER SESSION SET CURRENT_SCHEMA=MYSCHEMA

Thanks very much. Works very well!
Remco Blok
 
Posts: 25
Joined: Tue 14 Dec 2010 12:34


Return to dotConnect for Oracle