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);
}
}
}
}
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/enti ... qlite.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