Database connection object in shared variable

[Daniel Fernandes]

Hi there

Let's say due to several reasons I have an ASP.Net application that
has a database access class that stores a database connection in a
shared member :


public class dataaccess

private shared DB as System.Data.SqlClient.SqlConnection

public shared function GetItems as dataset
...
end function

public shared sub InsertItems as dataset
...
end sub

end class


I understand that I shouldn't be doing this and instead do the "open a
connection as late as possible and close it as soon as possible" thing
but in that case I can't.

I also undestand that putting database connection object in a shared
variable can lead to problems because access to shared members is not
thread safe.

Now...Only two times I had such a "predictable" issue happening and
the exception thrown was "There is already an open DataReader
associated with this Connection which must be closed first.".

I tried to programmatically replicate this by either running calling
multiple pages that will execute a stored procedure containing a
WAITFOR command or that execute a stored procedure that returns
thousands of records.
In both cases I didn't manage to get the exception thrown.

Which lead to my question which is: under which circumstances the fact
of using a database connection object in a shared member will lead to
predictable problems ?

PS: I believe that in the two times where I had the exception "There
is already an open..." thrown was when the execution of the stored
procedure couldn't be performed and therefore resulted into an
exception due to other issues (execute permission for instance).


Thanks for your help

Daniel

 

[Kevin Spencer]

Hi Daniel,

I'm going to have to disagree with your premise:

I understand that I shouldn't be doing this and instead do the "open a
connection as late as possible and close it as soon as possible" thing
but in that case I can't.

"Can't" is not a word that should be in any programmer's vocabulary. Of
course you can. You just don't know how to.

Based upon the corrected premise, the entire question is moot. I would
suggest asking instead, how you CAN work with a Connection in the way it was
designed to be used.

In other words, what is it that seems to prevent you from using a Connection
in the way it was designed to be used?

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

 

[Guest]

eIt will happen any time a connection still has an open object appended to
it. This could be a long running command, or the issue you are talking about
(open Reader).

You can test the type of Exception thrown, however, and determine if it is a
execution permission. Check the exception objects in System.Data. In
addition, you can pull the errors collection when you are using SqlClient and
query the errors returned (SqlErrorCollection class).


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Scott Allen]

I understand that I shouldn't be doing this and instead do the "open a
connection as late as possible and close it as soon possible" thing
but in that case I can't.

Hi Daniel:

By any chance are you trying to restrict the number of connections to
the database? If so, you could adjust the connection pool size in the
connection string.

Perhaps if you tell us why you must use a shaed connection we could
think of alternatives. I would try to avoid the current implementation
at all costs. Threading bugs tend to be subtle and sometimes remain
latent until the worst possible time. Just ask GE Energy!
http://www.securityfocus.com/news/8412

 

[Patrice]

When having more than one DataReader on an open connection (though it looks
like you don't see this, it's likely because of something wrong in your test
such as IIS6 new application isolation mode perhaps). Though you could
workaround this is IMO really looking for trouble.

What is the reason behind having a single shared connection ?

Patrice

 

[Daniel Fernandes]

I do know how to create a data access class that uses connection
pooling.

The problem is that I have to use an internal library that provides
helper classes for data access and in particular there is a
storedprocedure object that has two constructors :

1. Where you pass a connection (not a standard sql connection though)
and you would expect the connection to be opened, the stored procedure
to be executed and then the connection to be closed. Sadly the
connection is never closed which means after 100s or so calls SQL Server
will stop responding. I didn't look at the implementation but my guess
is the developer uses IDisposable for disposing the connection object
and this is obviously a mistake because the connection should be closed
and disposed explicitely after the stored procedure execution.

2. The second constructor is very similar to 1. and it's expected that
the connection is kept alive at the end of the stored procedure
execution. And that is the only one I am using but I would think such a
mechanism is only safe when in a single thread type application.

 

[Daniel Fernandes]

I finally managed to replicate the problem by having a stored procedure
returning a large number of rows (5000+) and running multiple ASPX pages
that did the very same thing.

I had again the "There is already a datareader associated with this
connection which must be closed first." exception.

Although I am not using datareaders I know they are used internally used
as a dataset is filled by a dataadapter that makes use of datareaders.

 

[Kevin Spencer]

Hi Daniel,

I'm not sure what you mean by "an internal library." Internal to what? Do
you mean that somebody else in your company developed the library? If so,
that person is the one who should be posting here, as their design is
fatally flawed. IOW, the problem lies with the library you're using. I can't
imagine why anyone would be forcing you to use defective software. To build
good software, you need to USE good software.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

 

[Kevin Spencer]

Well, there is actually one other possibility. I seem to recall you saying
that you had problems with an opened connection associated with a
DataReader. A DataReader is a connected object. It must maintain an opened
connection to the database for the duration of the time you use it. It can
only fetch one record from the database at a time. I have developed a Data
Access class for our company, and it includes methods for getting
DataReaders. You have to pass a Connection and Command reference to it, so
that you can close them afterwards. And when you use the DataReader, you'd
darned well better close the Connection yourself afterwards. Which is why I
also created a method to which you pass a Connection, and it closes and
disposes the Connection.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

 

[Daniel Fernandes]

Kevin

Thanks for your answer.
By "Internal Library" I meant a framework that have been developed for
internal use and that indeed has some issues when dealing with
connections.

The reason why I am getting an exception related to a Datareader object
is because the Fill method of a DataAdapter I believe uses a Datareader
hence the problem.

I am doing some tests using ACT and I hope I will be able to convince
the maintainer of the library to update it.

Cheers
Daniel

 

[Kevin Spencer]

Hi Daniel,

Interesting. Our in-house data class has methods for working with DataSets
as well. However, these methods open their own Connection, create the
DataSet, and then close the Connection. The user of the method doesn't even
have to think about the Connection. In fact, I have found that it is best to
use Connections internally (inside a method) whenever possible, rather than
exposing them to the developer, who may or may not be good enough to know
what to do with them. The only methods we have that expose Connection
objects are ones that return DataReaders.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
I get paid good money to
solve puzzles for a living

 

[elizas]

How to close a Connection object automatically when we close the associated Reader ob

OleDbDataReader oReader = oCommand.ExecuteReader(CommandBehavior.CloseConnection);

The argument "CommandBehavior.CloseConnection" will specify that the connection object will close automatically when we close the Reader object.

The sample code is given below:

OleDbConnection connectionObj = new OleDbConnection(connectionString);
OleDbCommand commandObj = new OleDbCommand();
OleDbDataReader readerObj = null;
commandObj = new OleDbCommand(sqlQuery, connectionObj);
connectionObj.Open();
readerObj = commandObj.ExecuteReader(CommandBehavior.CloseConnection);


Now when we close the Reader object then the Connection object will close automatically.