Detect If SqlConnection Is In Use?

[JonOfAllTrades]

I'm sure this has been answered before, but I couldn't find the right search
terms.
We've all seen how a single SqlConnection can only serve one SqlDataReader
at a time. However, often one needs to query within a queried loop.
Typically, I'd create a second SqlConnection called "innerLoopConnection" or
similar. Rather than handling this manually, I'd like to modify my
ExecuteReader wrapper fn to check to see if the SqlConnection is in use, and
if it is, create a new SqlConnection on the spot. Something like this:

SqlConnection reusedConnection;
....
SqlDataReader ReadQuery(string query)
{
if (reusedConnection.State != System.Data.ConnectionState.Open) return
ReadQueryTempConnection(query);
//else
SqlCommand command = new SqlCommand(query, reusedConnection);
return command.ExecuteReader();
}

However, SqlConnection.State seems to be largely NYI. Is there any other
way to check to see if a connection is in a useable state? At the moment,
I'm catching InvalidOperationException and using that as a cue to use
ReadQueryTempConnection(), but it doesn't quite work, it seems that the
pre-existing SqlDataReader is killed by the _attempt_ to reuse its
SqlConnection.
Any ideas? Thanks!

 

[Miha Markic]

Always create a new instance of SqlConnection for a linear operation.
Connection pool will take care of the details.

 

[JonOfAllTrades]

Always create a new instance of SqlConnection for a linear operation.
Connection pool will take care of the details.

If I don't call Close() on these Connections, won't they accumulate until
they time out and die, or the SQL server reaches its connection limit? Or is
Close() pretty much obsolete and unnecessary with the current pooling system?

 

[William \(Bill\) Vaughn]

Ah no.
See my whitepaper on connecting. You cannot depend on the GC to clean up
connections.
http://betav.com/blog/billva/2007/05/managing_and_monitoring_net_co.html

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[William \(Bill\) Vaughn]

Architecturally, this is a bad idea. First, consider that you should not use
the DataReader as a portal to a server-side cursor--because it's not. Until
the DataReader has read all of the rows returned by the SELECT, the SQL
engine is holding resources and (potentially) blocking other users. This is
bad.
Use the DataReader to open a channel to the rowset. Fetch the rows as
quickly into RAM -- as with the DataTable Load method. Process the rows in
memory. BUT in a general sense if you are bringing rows to the client for
processing you're (again) missing the point. Rowset processing should be
done ON THE SERVER--not the client. To make bulk changes to rows, you need
to write a stored procedure that runs on the server to massage the data.



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Miha Markic]

You should create/open a SqlConnection instance as late as possible and
close/dispose it asap.

 

[JonOfAllTrades]

Architecturally, this is a bad idea. First, consider that you should not use
the DataReader as a portal to a server-side cursor--because it's not. Until
the DataReader has read all of the rows returned by the SELECT, the SQL
engine is holding resources and (potentially) blocking other users. This is
bad.
Use the DataReader to open a channel to the rowset. Fetch the rows as
quickly into RAM -- as with the DataTable Load method. Process the rows in
memory. BUT in a general sense if you are bringing rows to the client for
processing you're (again) missing the point. Rowset processing should be
done ON THE SERVER--not the client. To make bulk changes to rows, you need
to write a stored procedure that runs on the server to massage the data.

I think you may be jumping to conclusions a bit regarding how I'm using the
data. It's not for row processing, I absolutely do that with sprocs, and
always with set operations rather than cursors, with a very few exceptions.
I'm using DataReaders to read data for browsing, or for very complex loading
that can't practically be done with sprocs or table functions.

You do raise a good point in that populating a DataTable with raw data, and
then looping the DataTable to create objects, would allow me to release the
lock and connection sooner. I would say that it's simpler and more intuitive
for the program to create objects as it reads, all in one step, though. None
of my load operations take more than a few seconds, but for very large data
sets I definitely see the advantage.

Forgive me for not reading your paper in detail, its Saturday and "I'm not
even supposed to BE here today," but it looks like the gist of it, for my
purposes, is that "it's OK to create and destroy lots of SqlConnections,
because it's really going to reuse them." If so, it makes sense to create a
new SqlConnection for every ExecuteReader operation, and perhaps return it as
an out parameter along with the opened DataReader... but then the calling
code has to Close() the connection, which is what I was hoping to avoid all
along.

For now, I'm using two shared SqlConnections; if they're both in use (i.e.,
I'm reading three loops deep), it creates a temporary connection on the spot,
logs a warning, and then the connection dangles until it dies of natural
causes. Ugly, but better than refusing to answer the ReadQuery(), and as
long as it happens rarely, better than rewriting all the code to close its
own connections.