Dynamic LINQ Database Selection for Oracle .NET

posted by Bryan on 21 Jun 2011

Using an Oracle database backend in conjunction with LINQ required use of a 3rd party tool called DevArt dotConnect for Oracle. However, when the database model is configured, it's hard coded to a specific database instance (Dev, Test, etc). I wanted to dynamically select which database instance to run against relative to the lane my tests were running against. It's actually easier than I thought.

LINQ is an awesome querying language that you can integrate right into your .NET code base. I use it quite a bit to query collections of all types. Querying the database is simple.

var q = from e in _imsData.Emails
   where e.Id.ToString() == messageId
   select e;

if (q.Count().Equals(0))
   throw new Exception("Could not find Message by this id : " + messageId);

return q.First();

While developing these tests and running them against a TEST environment, we had no issues. When we pointed our tests to run against our STAGE environment, tests started to fail left and right. After some research, I realized the Oracle LINQ configuration was still pointed to the TEST database.

In Visual Studio, I had added a new item (Data item > Devart LinqConnect Model). This template existed because I installed the Devart dotConnect tools. This file contains 4 sub files (.cs, .Designer.cs, .edps and .lqml.view).

In the .Designer.cs file, the constructor loads the ConnectionString from the App.Config file (that was placed there automatically by creating the data model mappings).

[System.Data.Linq.Mapping.DatabaseAttribute(Name = "IMS")]
[ProviderAttribute(typeof(Devart.Data.Oracle.Linq.Provider.OracleDataProvider))]
public partial class ImsDataContext : Devart.Data.Linq.DataContext
{
    private static System.Data.Linq.Mapping.MappingSource mappingSource = new Devart.Data.Linq.Mapping.AttributeMappingSource();
    public static Devart.Data.Linq.CompiledQueryCache compiledQueryCache = Devart.Data.Linq.CompiledQueryCache.RegisterDataContext(typeof(ImsDataContext));

        
    public ImsDataContext() :
    base(GetConnectionString("MyConnectionString"), mappingSource)
    {
        OnCreated();
    }

Notice how the class is partial. This is where you can hijack the constructor implementation and not load the default connection string.

First:

You should have another class file (.cs) that is the other partial class. My example is ImsDataContext.

namespace ImsContext
{
    partial class ImsDataContext
    {

        // Place your implementation of partial extension methods here
    }
}

Create a new class called ImsDataContextMaster that extends to that partial class.

namespace ImsContext
{
    public class ImsDataContextMaster : ImsDataContext
    {
        private static readonly LinqConnection _connectionInfo = new LinqConnection();

        public ImsDataContextMaster()
            : base(_connectionInfo.ConnectionString)
        {
        }
    }
}

Second:

Create another class called LinqConnection. You will see it is instantiated automatically in the ImsDataContextMaster class. This is where the magic is.

public class LinqConnection
    {
        public string ConnectionString { get; set; }

        public LinqConnection()
        {
            //Somehow get which environment you are pointing to.
            string currentEnv = EnvironmentUnderTest.GetEnvironment().Id;

            if (currentEnv.Equals("TEST"))
            {
                ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["MyConnectionString"];
                ConnectionString = connectionStringSettings.ConnectionString;
            }
            else if (currentEnv.Equals("STAGE"))
            {
                ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["MyConnectionStageString"];
                ConnectionString = connectionStringSettings.ConnectionString;
            }
            else if (currentEnv.Equals("LOCAL"))
            {
                ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["MyConnectionLocalString"];
                ConnectionString = connectionStringSettings.ConnectionString;
            }
            else
            {
                throw new Exception("Could not determine which Environment to use with LINQ.");
            }
        }
    }

When this code is called:

ImsDataContextMaster _imsData = new ImsDataContextMaster();

The class will first set up the LinqConnection object, which determines which connection string to use. Then it sets that value in the constructor's base call. (_connectionInfo.ConnectionString)

Thus, giving you the ability to dynamically select which database instance you want to point to.

RockyCode

Dynamic LINQ Database Selection for Oracle .NET

Leave a comment