for 循环是否在每次迭代时打开和关闭数据库连接? [英] Does for loop open and close a database connection on each iteration?

问题描述

我正在帮助调试一些代码并有以下 for 循环.它是否在每次迭代时打开和关闭与数据库的连接?

I'm helping to debug some code and have the following for loop. Does it open and close a connection to the database on each iteration?

for (int i = 0; i < count; i++)
{
    int num = dataTable.Rows[i]["Location_Id"]
    database.ProcessAlerts("spProcessAlerts", num)
}

从数据库类中，这里是相关部分:

And from the database class, here is the relevant portion:

public bool GetConnection()
{
    try
    {
        GeneralLib.GetIniSettings();
        string connectionStrings = GeneralLib.Settings.ConnectionStrings;
        Database.conn = new SqlConnection(connectionStrings);
        Database.conn.Open();
    }
...
public void ProcessAlerts(string ProcedureName, int nByLocationId)
{
    try
{
    if (this.GetConnection())
    {
        SqlCommand sqlCommand = new SqlCommand(ProcedureName, Database.conn);
        sqlCommand.CommandType = CommandType.StoredProcedure;
        SqlParameter sqlParameter = sqlCommand.Parameters.Add("@ByLocation_Id", SqlDbType.Int);
        sqlParameter.Value = nByLocationId;
        sqlCommand.ExecuteNonQuery();
        this.CloseConnection();
    }
}

我的直觉让我相信 for 循环会在每次迭代中打开/关闭，但由于我根本不使用 .net，所以我真的不知道.我对此进行了大量搜索，但没有找到满意的答案(例如 同一个 SPROC 的每次迭代执行时间变慢，保持 Sql 连接打开以迭代多个请求?还是关闭每个步骤?，为每个查询打开和关闭数据库连接 和 C# SQLConnection 池).一些帖子说在您需要时打开连接并立即关闭它，而其他帖子则说如果您使用连接池，则连接并没有真正关闭，它只是不可用.我不完全理解这一点.

My intuition leads me to believe the for loop is opening/closing on each iteration but since I don't work with .net at all, I don't really know. I've searched a good bit about this and haven't found a satisfactory answer (like Execution Time Slower with each Iteration of the same SPROC, Keep Sql Connection open for iterating many requests? Or close each step?, Opening and closing database connection for each query and C# SQLConnection pooling). Some posts have said to open the connection when you need it and close it right away while others have said that if you are using connection pooling, the connection isn't really closed, it's just not usable. I don't completely understand this.

基本上这段代码应该处理信息以便可以发送警报，而我们最近在此过程中遇到了很大的时间延迟.从日志中，我可以看到 for 循环何时开始/停止，有时需要数小时才能遍历几千条记录.也可能是 spProcessAlerts 花费了大量时间来运行某些行，因此我也在调查那里发生了什么.

Basically this piece of code is supposed to process information so that alerts can be dispatched, and we have recently been experiencing a large time delay in this process. From a log I can see when the for loop starts/stops, and it sometimes takes hours to loop through a couple thousand records. It could also be that spProcessAlerts is taking a lot time to run for certain rows so I am looking into what goes on in there as well.

推荐答案

是... 也不是.

ADO.Net 使用 连接池. 所以，当您反复调用 Connection.Open() 和 Connection.Close()，ADO.Net 可能只是将相同的现有的、已经打开的连接交还给您.

ADO.Net uses Connection Pooling. So, while you are repeatedly calling Connection.Open() and Connection.Close(), ADO.Net is probably just handing you back the same existing, already open connection.

不过，我的偏好仍然是将那种东西抽象到循环之外.

My preference, though, is still to abstract that kind of thing outside of the loop.

这篇关于for 循环是否在每次迭代时打开和关闭数据库连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！