它是最好通过一个开放的SqlConnection作为参数，或拨打每个方法一个新的？ [英] Is it best to pass an open SqlConnection as a parameter, or call a new one in each method?

问题描述

如果方法/功能，我会打电话给涉及一个开放的SqlConnection的需要，我会在其中调用函数的方法打开这个了。例如：

If methods/functions I'm going to call involve the need of an open SqlConnection, I will open this up in the method which is calling the function. For example:

protected static void btnSubmit(){
   conn.Open();
   myMethod(someParam, conn);
   conn.Close();
}

protected static void myMethod(object someParam, SqlConnection conn){
   //Some SQL commands etc here..
}

我这样做，这样我：

I do this so that I:

• 只有不断打开和关闭1每个进程的SqlConnection

不过，这将是更好地构建我的code，像这样：

However, would it be better to structure my code like so:

protected static void btnSubmit(){
   myMethod(someParam);
}

protected static void myMethod(object someParam){
   SqlConnection conn = New SqlConnection(".....");
   conn.Open();
   //Some SQL commands etc here..
   conn.Close();
}

好处我看到它构造这种方式是：

The advantage I see of structuring it this way is:

• 我没有通过一个额外的参数为每个方法


• 如果以后上下行的方法不再有SQL命令，没有每次都被称为是一个未使用的参数

缺点我看到这一点，是：

The disadvantage I see to this, is:

• 如果 myMethod的是一个递归方法，那么当它自称其将要打开另一个的SqlConnection ，和等等，等等..


• 如果 btnSubmit按钮呼吁多种方法，这都需要一个SqlConnection，每一个将要打开和关闭一个新的连接。

• If myMethod is a recursive method, then when it calls itself its going to be opening another SqlConnection, and so on, and so on..
• If btnSubmit is calling multiple methods which all require a SqlConnection, each one is going to open and close a new connection.

什么是这样做的最佳方式，并且是最常见练？

What is the best way of doing this, and which is most commonly practised?

推荐答案

ADO.NET使用连接池，所以它会自动重用已经打开的连接，即使你认为你打开一个新的连接。有了这样的心态，实在没有理由通过您的code连接（如参数）。这将使你的code更清洁的具有相同的性能，当你路过的连接作为参数作为。

ADO.NET uses connection pooling, so it automatically reuses already opened connection, even when you think that you are opening a new connection. Having that on mind, there is really no reason to pass connection through your code (as parameter). That will make your code much cleaner with the same performance as when you are passing the connection as parameter.

更多细节这里

同时，（这是非常重要的），请使用使用关键字。这样一来，你不会有关闭连接和清理，以deail，因为像现在写你的code不能与一些例外的情况下关闭连接，所以应对你可能最终与打在服务器上连接限制。像这样的东西去：

Also (and this is really important), please, use "using" keyword. In that way, you will not have to deail with closing the connection and cleanup, because your code as it is written now doesn't deal with closing the connections, so in a case of some exception you might end up with hitting connection limit on your server. Go with something like this:

using(var connection = new SqlConnection(<connection_string>))
{
  connection.Open();
  using(var command = connection.CreateCommand())
  {

  }
}

正如你所看到的，没有必要调用connection.close（）时，或处理异常和关闭在最后块的连接，因为这是一个工作，为使用块。

As you can see, there is no need to call connection.Close() or deal with exceptions and closing the connection in your finally block, because that is a "job" for the "using" block.

此外，还有一个重要的注意事项...的交易不是通过连接投票通过，所以，如果你想保持你的事务在方法调用，你必须通过你的连接（这是我能想到的原因的唯一原因你应该做的）。

Also, one important note...transactions are not passed via connection polling, so if you want to keep your transaction across method calls, you will have to pass your connection (and that is the only reason I can think of why you should do that).

这篇关于它是最好通过一个开放的SqlConnection作为参数，或拨打每个方法一个新的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！