CODEWITHSUNDEEP
c#.netsql-serverperformance
Snake_Plissken
Snake_Plissken

Reputation: 400

Does for loop open and close a database connection on each iteration?

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

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.

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.

Upvotes: 2

Views: 3761

Answers (3)

Ersin Tarhan
Ersin Tarhan

Reputation: 361

if you want single connection like pipeline, you can change your code like this :

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

 public SqlServerConnection GetConnectionX()
 {

    GeneralLib.GetIniSettings();
    string connectionStrings = GeneralLib.Settings.ConnectionStrings;
    return  new SqlConnection(connectionStrings);

 }



  public void ProcessAlerts(string ProcedureName, int nByLocationId , SqlServerConnection connection)
 {
   try
   {

    SqlCommand sqlCommand = new SqlCommand(ProcedureName, connection);
    sqlCommand.CommandType = CommandType.StoredProcedure;
    SqlParameter sqlParameter = sqlCommand.Parameters.Add("@ByLocation_Id", SqlDbType.Int);
    sqlParameter.Value = nByLocationId;
    sqlCommand.ExecuteNonQuery();
}
}

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416039

Yes... and no.

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.

Upvotes: 5

Aaron
Aaron

Reputation: 435

The way you have your code, yes it looks like you are opening a new connection every time, however you don't have to do it that way.

I'd rework your GetConnection into OpenConnection and CheckConnection, where OpenConnection sets a boolean, and CheckConnection only calls OpenConnection if that boolean is false. Then I would call OpenConnection above your for loop, (and I'd also make a close connection under it)

Upvotes: 0

Related Questions