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.

score 6 · Accepted Answer · edited Jul 21 '10 at 19:52

6

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.

edited Jul 21 '10 at 19:52

Tom H

46,766
14
87
128

answered May 22 '09 at 21:13

RichardOD

28,883
9
61
81

score 3 · Answer 2 · answered May 22 '09 at 21:13

3

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.

answered May 22 '09 at 21:13

Remus Rusanu

288,378
40
442
569

If you are saying "don't use MARS" - do you have any specific reasons? Out of curiosity... – Marc Gravell May 22 '09 at 21:14
http://blogs.msdn.com/sqlnativeclient/archive/2006/09/27/774290.aspx discusses some of the negatives to using MARS – RichardOD May 22 '09 at 21:28
2

The transaction isolation model of MARS is incomplete, as in not completely defined theoretically. The result is that the server may get 'confused', as the link posted by Richard admits. What that 'confusion' usually means is that the number of possible interactions is too big and not all code paths in the server are tested and validated. – Remus Rusanu May 22 '09 at 21:42
2

Just to add on how does this error happen: you either explicitly call a new batch into execution inside an SqlDataReader loop (unlikely, since you'd have seen this before) or your skipping a SqlDataReader.Close call, likely due to an exception (possibly related to the change of security). Always wrap your SqlDataReader in an using statement to enforce its closure in case of exception. – Remus Rusanu May 22 '09 at 21:45

score 2 · Answer 3 · edited May 23 '17 at 12:08

2

A lot of this may be a dup from here; in short, keep connections short lived and local under most circumstances. Re the data reader; perhaps enable MARS?

edited May 23 '17 at 12:08

Community

1
1

answered May 22 '09 at 21:11

Marc Gravell

1,026,079
266
2,566
2,900

score 2 · Answer 4 · answered May 24 '09 at 21:33

Your problem is that you are trying to use a connection that is currently being used by a SqlDataReader. You can't do that. When you use a SqlDataReader you must close it before reusing the connection that's being consumed. I suggest you to create a different connection every time you access the DB (anytime, not only with SqlDataReaders). If you have the pooling enabled, then the framework itself (or is it SqlServer?) will reuse the connections when possible.

If you need to access the DB sequentially, let's say you do a select, then another select, and then an update, you may reuse the connection (I mean, the same SqlConnection instance), but if you need to access the DB while reading from a SqlDataReader, then you'll need 2 different connections.

Of course you have to have in mind concurrency issues. If you use transactions then some records may be locked during certain operations, and when you use the SqlDataReader in parallel with other queries, you should set the isolation level to a level that doesn't mess with the rest of the queries.

score 1 · Answer 5 · answered May 22 '09 at 21:11

While for a single thread it may perform better to use a single SqlConnection object, it puts demands on the precious resource that is your database server by hanging onto a connection, and thus db server resources, longer than it needs to.

It is better to instantiate the SqlConnection for as short a period of time as possible. Connection pooling mitigates most of the expense of setting up the connection, and ensures the most efficient use of the database resources.

score 0 · Answer 6 · answered May 22 '09 at 21:11

If you're using SQL Server 2000, this may answer your question.

The winning answer there appears to be "This is due to a change in the default setting for MARs. It used to be on by default and we changed it to off by default post RC1. So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string)."

Shared SqlConnection

6 Answers6