title: Managing SQL database connections accross many transactions
body: <p>we have a DAL that needs to wrap many database inserts in a single transaction that we can rollback or commit.  </p>
  
  <p>What is the best practice for handling that?  </p>
  
  <p>We are currently doing the following:</p>
  
  <ol>
  <li>Create DB Connection and Transaction</li>
  <li>Fill a collection with all the classes that represent an action to perform.  Pass in the connection via constructor.</li>
  <li>In a try/catch, loop through all the action classes and call thier Publish() method</li>
  <li>Commit if successful (closes connection) or rollback if errors (closes connections).</li>
  </ol>
  
  <p>The process can take sometime and we seem to be running out of pooled database connections.  Is there a better way to manage the transactions?</p>
  
  <p>This is using SQL 2008, .net 3.5 and the 4.1 version of enterprise library data access.</p>