在ONE数据访问层处理多个连接字符串 [英] Handling multiple connection strings in ONE DataAccess Layer

问题描述

我有一个有趣的两难境地。我现在有一个与多个域工作的一个数据访问层和这些领域中的每一个都有不同的存储过程调用多个数据库存储库。截至目前，我只是使用switch语句来确定应用程序正在运行的机器，并从Web.config返回适当的连接字符串。现在我有处理在同一个SQL服务器和ID多个数据库存储库的艰巨的任务，要动态地确定基于存储过程调用的连接字符串。也许我在想这一点，但我不能换我的头围绕如何，我去处理这个问题。

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

推荐答案

这的确是一个有趣challange。从视图的设计点这可以以各种方式来解决，但它们似乎都有点破

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.

这样的：

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

这种方式可以保留现有的code。

This way you can keep your existing code.

您可以使用code一代创建SQL语句的界面的程序。

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

地址：

这只是poped我的心......为什么不使用的LINQ到SQL的DataContext为你的事？

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

这篇关于在ONE数据访问层处理多个连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！