Handling multiple connection strings in ONE DataAccess Layer

Question

I have an interesting dilemma. I currently have a DataAccess layer that has to work with multiple Domains and each one of those domains has multiple Database repositories depending on the stored procedure called. AS of right now I simply use a SWITCH statement to determine the Machine the application is running on and return the appropriate connection string from the Web.config. Now I have the daunting task of dealing with multiple database repositories in the same SQL server and id like to dynamjically determine the connection string based on the stored procedure called. Maybe I'm over thinking this, but I just cannot wrap my head around how I'm going to deal with this. Thanks in advance to anyone.

Answer 1

First thing I'd say is there's likely a flaw in your design, as the need to invoke multiple stored procedures across multiple databases is uncommon.

However, there are many options available, for example:

1. Store a list of the stored procedures along with each one's corresponding connection string in the web.config and retrieve them at runtime.
2. Have a centralised table listing stored procedures and connection strings (so you only have one connection string in the web.config, just the one pointing to the central table of stored procedure connection strings).

Answer 2

This is indeed an interesting challange. From the Design-Point of view this can be solved in various ways, but they all seem a little broken.

Well the only thing that really worked for me in this situation was like:

Create a procedure database on each server and have "interface" procedures on this database that call the real procedures.

like: CREATE PROC pr_GetAddress ( @addressId int ) AS BEGIN exec pubs.dbo.pr_GetAddress @addressId END

This way you can keep your existing code.

You might use code generation to create the sql statement for the "interface" procedures.

ADD:

This just poped my mind... Why not using the Linq-to-Sql DataContext for your thing?

I just played around with this snippet here:

public class ExperimentalDataContext: DataContext
{
    public ExperimentalDataContext(string connectionString) : base(connectionString) { }

    public IExecuteResult ExecuteMethod(object instance, MethodInfo methodInfo, params object[] parameters)
    {
        return this.ExecuteMethodCall(instance, methodInfo, parameters);
    }

    [Function(Name = "dbo.fx_Levenstein", IsComposable = true)]
    public static System.Nullable<double> fx_Levenstein([Parameter(DbType = "NVarChar(255)")] string firstword, [Parameter(DbType = "NVarChar(255)")] string secondword)
    {
        using (ExperimentalDataContext context = new ExperimentalDataContext(GetConnectionString("your-connectionstring")))
        {
            return ((System.Nullable<double>)(context.ExecuteMethod(context, ((MethodInfo)(MethodInfo.GetCurrentMethod())), firstword, secondword).ReturnValue));
        }
    }

    private static string GetConnectionString(string key)
    {
        return ConfigurationManager.ConnectionStrings[key].ConnectionString;
    }
}

Answer 3

If all you have to go on to determine the database is a stored procedure call, then in a configuration file, define your own configuration section that maps stored procedure names to connection strings. That would be the most clear-cut, but would then need maintaining.

You could alternatively centralise this into a smaller catalog database that stores connection strings against stored procedure names - then you can create a SQL script that keeps the information up to date and correct (no spelling errors or typos) automatically, by quering the other database objects.

I would then read this into memory (cache it) so that you can avoid calling this catalog database more than once for the lifetime of the application. Going on what little information I can surmise about the overall structure of your database (and ignoring the question of how this structure came to pass), I'd say this is a managable solution.

You can improve on this solution by having a base DAL class responsible for returning connections suitable for a given procedure name - so any code changes will only need to occur in one place to return different connections. The rest of the code calls GetConnection("getCustomers") and blissfully ignores the details.