最佳实践?打开并关闭多个连接，或为ado.net打开一个大型连接

I am using ADO.Net for some database connectivity and I was just hoping I was doing it the right way. I am opening and closing multiple connections for each stored procedure. Or should I be wrapping this up in just one open connection (less resource on the database maybe?) thanks, and if there is anything odd or something I could be doing better let me know thanks!

我使用ADO。我只是希望我的方法是正确的。我正在为每个存储过程打开和关闭多个连接。或者我应该用一个打开的连接(数据库上的资源可能更少)来完成这个操作吗?

Example: this but I have like 6 of them...

这个，但是我有六个……

using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    SqlCommand cmd = new SqlCommand("spSelectAllTrip", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    conn.Open();
    ddlTripTypeA.DataSource = cmd.ExecuteReader();
    ddlTripTypeA.DataTextField = "TripType";
    ddlTripTypeA.DataValueField = "TripTypeAID";
    ddlTripTypeA.DataBind();
}
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    SqlCommand cmd = new SqlCommand("spSelectAllTripB", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    conn.Open();
    ddlTripTypeB.DataSource = cmd.ExecuteReader();
    ddlTripTypeB.DataTextField = "TripType";
    ddlTripTypeB.DataValueField = "TripTypeBID";
    ddlTripTypeB.DataBind();
}

4 个解决方案

#1

.Net has connection pooling already managed for you so you don't need to worry about reusing connections like you might have with old asp. I always go with several small quick calls vs keeping one open the whole time because not all the time it's open is generally used for calls. You have your site code running doing some things between as well.

. net已经为您管理了连接池，所以您不必像使用旧的asp那样担心重用连接。我总是用几个简短的电话，而不是一直开着一个，因为并不是所有的时间都是用来打电话的。你的网站代码也在运行。

Now if you are going to make 6 consecutive calls one after another then it might make sense to open one and reuse. But other than that I say just stick with what you are doing.

如果你要一个接一个地进行6个连续调用，那么打开一个并重用它可能是有意义的。但除此之外，我要说的是坚持你正在做的。

The only thing you might want to look into is a connection manager so that you aren't having to create the connection object in .net over and over again. But that doesn't have anything to do with db connections vs just object creation.

您可能只想查看一个连接管理器，这样您就不必在。net中反复创建连接对象。但这与db连接和对象创建没有任何关系。

#2

You should keep connections open as short a time as possible. Thus, you want to open a connection, execute a query or stored procedure and then close the connection. Although this sounds expensive, it leverages ADO.NET's built-in connection pooling. When you close a connection, it is returned to a pool and reused so you do not suffer a performance hit.

你应该尽可能短的时间保持联系。因此，您希望打开一个连接，执行查询或存储过程，然后关闭连接。虽然这听起来很贵，但它却利用了ADO。净的内置连接池。当您关闭一个连接时，它被返回到一个池中并被重用，这样您就不会受到性能影响。

#3

A better way to do this would be to prep both of the commands and then open the connection and execute them both in quick succession:

更好的方法是准备好这两个命令，然后打开连接并快速执行它们:

conn.Open();
comm1.ExecuteReader();
comm2.ExecuteReader();

Always keep them open for as short a time as possible.

尽可能短的时间让它们打开。

#4

ADO.Net uses connection pooling, so that should lower the cost of opening new connections avoiding any need to have one connection open throughout your whole application.

ADO。Net使用连接池，这样可以降低打开新连接的成本，避免在整个应用程序中需要打开一个连接。

However, there is probably still some overhead to shuffling the connections in the pool, so if you have code that is run sequentially and immediately, within the same portion of your code, then you are probably better off using a single connection for that short span. Check Adam's answer for a bit more on that - you want to get everything else set up first so that the connection is open for as short a time as possible.

但是，转移池中的连接可能仍然存在一些开销，因此，如果您的代码是在代码的相同部分中按顺序和立即运行的，那么您最好在短时间内使用单个连接。检查一下Adam的答案——你想先把所有其他的东西都设置好，以便连接尽可能短的时间内打开。

If any ADO.Net programmers can confirm or correct this, please do.

如果有的话麻烦。Net程序员可以确认或更正这一点，请做。

#1