C# 将 DB Connection 对象传递给方法

passing DB Connection object to methods

提问by CSharpAtl

Was wondering if it is recomended to pass a database connection object around(to other modules) or let the method (in the other module) take care of setting it up. I am leaning toward letting the method set it up as to not have to check the state of the connection before using it, and just having the caller pass any needed data to the calling method that would be needed to setup the connection.

想知道是否建议将数据库连接对象传递给（到其他模块）或让方法（在其他模块中）负责设置它。我倾向于让方法设置它，以便在使用它之前不必检查连接的状态，而只需让调用者将任何需要的数据传递给设置连接所需的调用方法。

采纳答案by Marc Gravell

Personally I like to use tightly scoped connections; open them late, use them, and close them (in a "using" block, all within the local method). Connection pooling will deal with re-using the connection in most cases, so there is no real overhead in this approach.

我个人喜欢使用紧密范围的连接；打开它们，使用它们，然后关闭它们（在“使用”块中，所有这些都在本地方法中）。在大多数情况下，连接池将处理重用连接，因此这种方法没有真正的开销。

The main advantage in passing connections usedto be so that you could pass the transaction around; however, TransactionScopeis a simpler way of sharing a transaction between methods.

在通过连接的主要优点使用是如此，你可以通过周围的交易; 然而，TransactionScope是一种在方法之间共享事务的更简单的方法。

Since the classes are implementation specific, I'd write each to open it's own native transaction. Otherwise, you can use the ado.net factory methods to create the appropriate type from the config file (the provider name).

由于这些类是特定于实现的，我会编写每个类来打开它自己的本机事务。否则，您可以使用 ado.net 工厂方法从配置文件（提供程序名称）创建适当的类型。

回答by Mitchel Sellers

I personally work to centralize my data access as much as possible, however, if not possible I ALWAYS open a new connection in the other classes, as I find that there are too many other things that can get in the way when passing the actual connection object.

我个人努力尽可能地集中我的数据访问，但是，如果不可能，我总是在其他类中打开一个新连接，因为我发现在传递实际连接时有太多其他事情会妨碍目的。

回答by craigmoliver

I would use the web.config

我会使用 web.config

<configuration>
    <connectionStrings>
        <add name="conn1" providerName="System.Data.SqlClient" connectionString="string here" />
        <add name="conn2" providerName="System.Data.SqlClient" connectionString="string here" />
    </connectionStrings>
</configuration>

Then you can reference it from anywhere in the application

然后你可以从应用程序的任何地方引用它

回答by Corey Trager

Setting up the connection is potentially expensive and potentially adds a round trip. So, again, potentially, the better design is to pass the connection object.

建立连接可能很昂贵，并且可能会增加往返行程。因此，同样，更好的设计可能是传递连接对象。

I say potentially, because if you are a Microsoft ADO app, you are probably using a connection pool....

我说有可能，因为如果您是 Microsoft ADO 应用程序，您可能正在使用连接池....

回答by Micah

For automated testing purposes, it's usually easier to pass it in. This is called dependency injection.

出于自动化测试的目的，通常更容易将其传入。这称为依赖注入。

When you need to write tests, you can create a mock database connection object and pass that instead of the real one. That way, your automated tests won't rely on an actual database that needs to be repopulated with data every time.

当您需要编写测试时，您可以创建一个模拟数据库连接对象并传递它而不是真实的。这样，您的自动化测试就不会依赖于每次都需要重新填充数据的实际数据库。

回答by CSharpAtl

Here is a little more insight into this problem. I have a class that manages db connections, and have 2 classes that implement an interface. One of the classes is for SQL and the other is of OLAP. The manager is the one that knows which connection to use, so it could pass the exact connection to the type, or the type can create his own connection.

这是对这个问题的更深入的了解。我有一个管理数据库连接的类，并且有 2 个实现接口的类。其中一个类用于 SQL，另一个类用于 OLAP。管理器知道要使用哪个连接，因此它可以将确切的连接传递给类型，或者类型可以创建自己的连接。

回答by Simara

You can pass connection objects without any problem (for instance Microsoft Enterprise Library allows static method calls passing in a connection) or you could manage it externally its up to your design, there are not direct technical tradeoffs.

您可以毫无问题地传递连接对象（例如 Microsoft 企业库允许静态方法调用传入连接），或者您可以根据您的设计在外部管理它，没有直接的技术权衡。

Be careful for portability not to pass an specific connection if your solution will be ported to other databases (meaning don′t pass a SqlConnection it you plan to work with other databases)

如果您的解决方案将被移植到其他数据库，请注意可移植性不要传递特定的连接（意思是不要传递 SqlConnection 计划与其他数据库一起使用）

回答by Sam Saffron

Personally, I like storing a stack of my current open connection and transactions on top of the Thread Local Storageusing SetData and GetData. I define a class that manages my connections to the database and allow it to use the dispose pattern. This saves me the need to pass connections and transactions around, which is something that I think clutters and complicates the code.

就我个人而言，我喜欢使用 SetData 和 GetData在线程本地存储之上存储我当前打开的连接和事务的堆栈。我定义了一个类来管理我与数据库的连接并允许它使用处置模式。这使我无需传递连接和事务，我认为这会使代码变得混乱和复杂。

I would strongly recommend againstleaving it up to the methods to open connections every time they need data. It will leads to a really bad situation where it is both hard to manage transactions throughout the application and too many connections are opened and closed (I know about connection pooling, it is still more expensive to look up a connection from the pool than it is to reuse an object)

我强烈建议不要将其留给每次需要数据时打开连接的方法。这将导致一个非常糟糕的情况，即很难在整个应用程序中管理事务，而且打开和关闭的连接太多（我知道连接池，从池中查找连接的成本仍然高于实际情况）重用一个对象）

So I end up having something along these lines (totally untested):

所以我最终得到了一些类似的东西（完全未经测试）：

class DatabaseContext : IDisposable {

    List<DatabaseContext> currentContexts;
    SqlConnection connection;
    bool first = false; 

    DatabaseContext (List<DatabaseContext> contexts)
    {
        currentContexts = contexts;
        if (contexts.Count == 0)
        {
            connection = new SqlConnection(); // fill in info 
            connection.Open();
            first = true;
        }
        else
        {
            connection = contexts.First().connection;
        }

        contexts.Add(this);
    }

   static List<DatabaseContext> DatabaseContexts {
        get
        {
            var contexts = CallContext.GetData("contexts") as List<DatabaseContext>;
            if (contexts == null)
            {
                contexts = new List<DatabaseContext>();
                CallContext.SetData("contexts", contexts);
            }
            return contexts;
        }
    }

    public static DatabaseContext GetOpenConnection() 
    {
        return new DatabaseContext(DatabaseContexts);
    }


    public SqlCommand CreateCommand(string sql)
    {
        var cmd = new SqlCommand(sql);
        cmd.Connection = connection;
        return cmd;
    }

    public void Dispose()
    {
        if (first)
        {
            connection.Close();
        }
        currentContexts.Remove(this);
    }
}



void Test()
{
    // connection is opened here
    using (var ctx = DatabaseContext.GetOpenConnection())
    {
        using (var cmd = ctx.CreateCommand("select 1"))
        {
            cmd.ExecuteNonQuery(); 
        }

        Test2(); 
    }
    // closed after dispose
}

void Test2()
{
    // reuse existing connection 
    using (var ctx = DatabaseContext.GetOpenConnection())
    {
        using (var cmd = ctx.CreateCommand("select 2"))
        {
            cmd.ExecuteNonQuery();
        }
    }
    // leaves connection open
}

回答by DOK

I would suggest that you distinguish between the connection object and its state (open, closed).

我建议您区分连接对象及其状态（打开、关闭）。

You can have a single method (or property) that reads the connection string from web.config. Using the same version of the connection string every time ensures that you will benefit from connection pooling.

您可以有一个从 web.config 读取连接字符串的方法（或属性）。每次使用相同版本的连接字符串可确保您将从连接池中受益。

Call that method when you need to open a connection. At the very last moment, after setting up all of the SqlCommand properties, open the connection, use it, and then close it. In C#, you can use the using statement to make sure the connection is closed. If not, be sure to close the connection in a finally block.

当您需要打开连接时调用该方法。在最后一刻，在设置所有 SqlCommand 属性后，打开连接，使用它，然后关闭它。在 C# 中，您可以使用 using 语句来确保连接已关闭。如果没有，请确保在 finally 块中关闭连接。