title: How to avoid .NET Connection Pool timeouts when inserting 37k rows
body: <p>I'm trying to figure out the best way to batch insert about 37k rows into my Sql Server using DAPPER.</p>
  
  <p>My problem is that when I use <code>Parallel.ForEach</code> - the number of connections to the database increases over a short period of time - finally hitting nearly or about 100 ... which gives connection pool errors. If I force the max degree of parall then it's hit that max number and stays there. </p>
  
  <p>Setting the maxdegree feels wrong.</p>
  
  <p>It currently is doing about 10-20 inserts a second. This is also in a simple Console App - so there's <em>no</em> other database activity besides what's happening in my <code>Parallel.ForEach</code> loop.</p>
  
  <p>Is using <code>Parallel.ForEach</code> the incorrect thing in this case because this is not-CPU bound? </p>
  
  <p>Should I be using <code>async/await</code> ? If so, what stopping this from doing hundreds of db calls in one go?</p>
  
  <p>Sample code which is basically what I'm doing.</p>
  
  <pre><code>var items = GetItemsFromSomewhere(); // Returns 37K items.
  
  Parallel.ForEach(items =&gt; item)
  {
      using (var sqlConnection = new SqlConnection(_connectionString))
      {
          var result = sqlConnection.Execute(myQuery, new { ... } );
      }
  }
  </code></pre>
  
  <p>My (incorrect) understanding of this was that there should on be about 8 or so connections at any time to the db. The Connection Pool will release the connection (which remains instantiated in the Connection Pool, waiting to be used). And if the <code>Execute</code> takes .. i donno .. lets say even a 1 second (the longest running time for an insert was about 500ms .. and that's 1 in every 100 or so) ... that's ok .. that thread is blocked and chills until the <code>Execute</code> completes. Then the scope completes (and <code>Dispose</code> is auto called) and the connection closed. With the connection closed, the <code>Parallel.ForEach</code> then grabs the next item in the collection, goes to the connection pool and then grabs a spare connection (remember - we just closed one, a split second ago) ... rinse.repeat.</p>
  
  <p>Is this wrong?</p>
  
  <p>Notes:</p>
  
  <ul>
  <li>.NET 4.5</li>
  <li>Sql 2012</li>
  <li>Console app.</li>
  <li>Using Dapper.NET for sql code.</li>
  </ul>