title: Does for loop open and close a database connection on each iteration?
body: <p>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?  </p>
  
  <pre><code>for (int i = 0; i &lt; count; i++)
  {
      int num = dataTable.Rows[i]["Location_Id"]
      database.ProcessAlerts("spProcessAlerts", num)
  }
  </code></pre>
  
  <p>And from the database class, here is the relevant portion:</p>
  
  <pre><code>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();
      }
  }
  </code></pre>
  
  <p>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 <a href="https://stackoverflow.com/questions/10113255/execution-time-slower-with-each-iteration-of-the-same-sproc">Execution Time Slower with each Iteration of the same SPROC</a>, <a href="https://stackoverflow.com/questions/4801736/keep-sql-connection-open-for-iterating-many-requests-or-close-each-step">Keep Sql Connection open for iterating many requests? Or close each step?</a>, <a href="https://stackoverflow.com/questions/14950876/opening-and-closing-database-connection-for-each-query">Opening and closing database connection for each query</a> and <a href="https://stackoverflow.com/questions/4457928/c-sharp-sqlconnection-pooling">C# SQLConnection pooling</a>).  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.</p>
  
  <p>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.</p>