how many instances of SqlConnection should I use

Question

Background:
I have an application that I have nicely separated my interface logic from my middle tier logic which handles the queries to the database. I do a lot of custom sorting and narrowing so I'm not using many SqlDataSources and instead calling a lot of stored procedures with SqlCommands.

I am using Forms Authentication to create protected sub-directories. In the web.config files in the protected directories I have more connection strings that link to users with higher privileged roles.

Question:
Should I share a SqlConnection object in my middle tier to cut out repetitive code, or should I create a new instance for each operation? A shared SqlConnection I could re-instantiate if I need to change connection strings to get access to protected stored procedures. Is there a best practice here?

Answer 1

Don't worry about sharing to conserve resources. .NET will manage this for you, it does connection pooling by default. Write the code to be clear and understandable and let .NET take care of these details.

Answer 2

Create a new one (and dispose properly) and utilize connection pooling.

Answer 3

Create as many SqlConnections as you need, as shortly lived as possible, through the using statement:

using (var connection = new SqlConnection(...)) {
  connection.Open();
  ...
}

Sql connections are taken from a connection pool, which will automatically manage contention for you.

See: http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx