Is it best to pass an open SqlConnection as a parameter, or call a new one in each method?

ADO.NET uses connection pooling, so it automatically reuses existing opened connections, even when you think that you are opening a new connection. Having that in mind, there is really no reason to pass a connection through your code (as a parameter). This will make your code much cleaner, with the same performance as when you were passing the connection as a parameter.

More details here

Also (and this is really important), please, use the "using" keyword. That way, you will not have to deal with closing the connection and cleanup, because your code as it is written now doesn't deal with closing the connections, so in a case of some exception you might end up with hitting connection limit on your server. Go with something like this:

using(var connection = new SqlConnection(<connection_string>))
{
  connection.Open();
  using(var command = connection.CreateCommand())
  {

  }
}

As you can see, there is no need to call connection.Close() or deal with exceptions and closing the connection in your finally block, because that is a "job" for the "using" block.

Also, one important note...transactions are not passed via connection polling, so if you want to keep your transaction across method calls, you will have to pass your connection (and that is the only reason I can think of why you should do that).


The best pattern to use is Repository+UnitOfWork patterns.

So repository is created and passed the UnitOfWork which contains the connection. After work is done UnitOfWork is disposed.

// Pseudocode
using(UnitOfWork uow = new UnitOfWork())
{
   Repository.Init(uow);
   Repository.SaveInDb(stuff);
}

And Unit of work:

// PseudoCode
class UnitOfWork : IDisposable
{
   public UnitOfWork()
   {
      conn = new SqlConnection();
      conn.Open();
   }

   public void Dispose()
   {
       conn.Close();
   }

 ....

}

This is what I always use.

Some people prefer simpler approach where Repository owns connection. This is simpler but in case you need to have a transaction across multiple repositories, it will not work.