Shared SqlConnection

Question

Is it good practice to have one shared SqlConnection object in a .NET application to use for all of your database connections or should you have a separate one each time you access the database?

I currently have a shared one and seem to be running into issues all of a sudden. It seems like I can't use a shared one when I need to use SQL authentication mode instead of Windows authentication mode. I just tried for the first time using SQL authentication and it gave me this error when I tried using the same connection a second time:

There is already an open DataReader associated with this Command which must be closed first.

Answer 1

You should have a separate one really. Reusing connections is handled with connection pooling. The 2nd issue like others have said can probably be resolved by enabling MARS.

Answer 2

That error has absolutely nothing to do with authentication. You are reusing a connection from the middle before you closed your SqlDataReader returned from an ExecuteReader(). This is forbidden. You must check your code and eliminate your problem. There are alternatives to use MARS (multiple active record sets) but I would strongly discourage that.

Using multiple connections in your application will probably be even worse because apparently you don't know what connection are in use and when, so when you'll use separate connections you will run into transaction consistency problems.