Handling multiple connection strings in ONE DataAccess Layer

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 procedu开发者_JAVA技巧re 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 dynamically 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.

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

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.

If you can truly map a single connection string based on stored procedure name uniqueness, an interesting thing to try would be to dynamically discover the stored procedures available from a list of connection strings at application startup and cache a mapping.

It does seem like you can use the normal ConnectionStrings configuration that allows you to name (classify) your connection strings. At some point in your code, you are specifically calling a stored procedure. Could that call also specify the key of the connection string to use? ie: Reporting, Member, etc.

<connectionStrings>
    <add name="Reporting" connectionString="<your connection string>" ... />
    <add name="Member" connectionString="<another connection string>" ... />
</connectionStrings>

A call to your DAL could look something like this:

var result = ExecStoredProcedure("Reporting", "up_SomeProcedure");

Assuming that your DAL is only one Assembly that is trying to manipulate data for several different databases. I would suggest, splitting up the code into separate assemblies for each database. Then you can have specific configuration information for each assembly, and the code will just look at the specific configuration information for the executing assembly.

For example, assuming you have a Movies database, and a Books database. You would create a library that knows how to manipulate data in your Movies Database and all of the code and stored procedures necessary for creating,editing, and querying movies would be in that library. This library would have it's own configuration information including the connection string to the database.

Similarly for interacting with your books database you would have a separate library that contains all the stored procedures for creating,editing, and querying information about your books. Again this would have it's own configuration information including the connection string.

This approach will allow for flexibility for adding more data sources, and configuring them independently of each other.

If you so desired you could of course have another library that would deal with transactions that might require access to both databases and would reference the code in the libraries previously mentioned.

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;
    }
}