How to Tell if a SqlConnection has a Open SqlDataReader

  • Thread starter Thread starter Gary van der Merwe
  • Start date Start date
G

Gary van der Merwe

Hi

How do I tell if a SqlConnection has a open datareader. If I try to do a new
ExecuteReader, a exception gets thrown if there is a open reader. I want to
be able to tell if there is an open datareader before I do the
executeReader.

If I break and do a watch on the SqlConnection , there is a private field
(_reader) witch is !=null when there is an open reader. Only problem is that
it is private.

Gary
 
You probably wouldn't like to do it.
use instead a safer method like:

SqlConnection connection="your connection string";
connection.Open();
SqlCommand command=new SqlCommand("",connection);
SqlDataReader reader;

try
{ // 1st reader
command.CommandText="your SQL string";
reader=command.ExecuteReader();
try
{ while (reader.Read()) { ...do whatever...; }
}
catch
{ ...handle any error...}
finally
{ reader.Close(); }
 
Hi Fratnik

Thats what I am currently doing. I want to avoid an exception been thrown.

Gary
 
Doesn't make much sense to me. With this routine you'll never get an
exception due to open reader because it is always closed in the finally
block before you open the next one. The catch block will eventually return
another type of exception, for example an invalid select statement, but
NEVER an exception related to reader already open.
 
Hi Fratnik

Oh. Sorry I did not understand what you were trying to point out in your
first reply. I do now. That is normaly the way that I would do it.

The case where I am having this problem, is a windows service. The
ExecuteReader() is called from the elapsed event of a timer. Some times the
elapsed fires, while the other is still processing the results of the
DataReader.

I tried disabling the beginig of the elapsed event and enabling it at the
end. That was a bit bugy because there are other things that enable and
disable the timer.

Right now I am just catching the exception thrown. I want to avoid that with
a if statment.

Thanks for your replys.

Gary
 
Hi,

sounds like maybe a multithreading problem?
Your code is executing while a second thread executes the same code using
the same connection object for a (in this case: second) reader (?)

You could lock the connection while the reader is executing making it
impossible for other threads to use the connection object for a second
reader until you unlock it (after closing your first reader).

Take a look at "lock statement" for c# in MSDN.

something like (pseudocode):

public void timerEvent()
{
lock(myconnection)
{
myconnection.executeReader.
//close reader
}
}

hth
Uwe
 
Gary van der Merwe said:
Hi Fratnik

Oh. Sorry I did not understand what you were trying to point out in your
first reply. I do now. That is normaly the way that I would do it.

The case where I am having this problem, is a windows service. The
ExecuteReader() is called from the elapsed event of a timer. Some times the
elapsed fires, while the other is still processing the results of the
DataReader.

I tried disabling the beginig of the elapsed event and enabling it at the
end. That was a bit bugy because there are other things that enable and
disable the timer.

Right now I am just catching the exception thrown. I want to avoid that with
a if statment.
Click to expand...

This is really a syncronization problem.

In your timer callback method, put a lock (Synclock in vb) on some object
(the connection will do), to serialize use of the connection.

like

lock (con)
{
SqlCommand cmd = new SqlCommand("...",con);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
//read data
}
}

or

SyncLock con
dim cmd as new SqlCommand("...",con)
dim rdr as SqlDataReader = cmd.ExecteReader()
try
'read data

finally
rdr.close
end try
end Synclock

David
 
Uwe Hafner said:
You could lock the connection while the reader is executing making it
impossible for other threads to use the connection object for a second
reader until you unlock it (after closing your first reader).
Click to expand...

Or maybe another idea, if you don't need something to occur EXACTLY
every n seconds, but rather n seconds since the last one finished, try
this... instead of using a Timer, start a new thread (ThreadPool would
be a good choice) that continually runs, and uses an
AutoResetEvent.WaitOne() that specifies a timeout. After the timeout
expires, you run your datareader... something like this (code not
validated)

public AutoResetEvent areDoDataReader = new AutoResetEvent(true);
bool bServiceThread = true;
public static void RunDataReader()
{
while (bServiceThread)
{
areDoDataReader.WaitOne(3000, true); // 3 seconds
if (bServiceThread) myConnection.ExecuteReader(...);
}
Trace.Writeline("Exiting RunDataReader");
}

you would start this thread by calling:

ThreadPool.QueueUserWorkItem(new WaitCallback(Class.RunDataReader));

If you don't need this reader to be timed exactly to the millisecond,
but still have a delay in between reads, this is a great way to do it.
You can even signal to the thread to bypass the delay and execute the
reader early by calling areDoDataReader.Set()...

-mbray
 
Ado.net is not thread safe by design, implementing your own locks would work
but the recommended way to deal with these issues is to open a new
connection/command when the Event fires. Making sure that every thread you
run has its own connection object and that you open the connection as late
as possible and close it as soon as possible will make your code stable and
performant. If you share the connection object across threads you will
evetually run into serious stress issues.

Thanks
 
Back
Top