ADO.Net Best Practices Validation Tool

  • Thread starter Thread starter news.microsoft.com
  • Start date Start date
N

news.microsoft.com

Does anyone know of a tool that can look at C# code and make sure that all
open datareaders are closed as well as validate other ADO.Net best practice
principles?

I have a team of 10 developers and we recently had a bug in our code that
claims that an existing connection can't be used because it is associated
with an open datareader. Unfortunately, looking through all the code is
blinding me and so I wondered if there was a tool that could do it for me.

Any and all help is appreciated.

-- Rodney
 
Hi Rodney,

I don't know of any such tool but I do know that nothing beats clean coding.
 
Miha,

I concur. The code is actually quite clean but locating the place in our
production environment where it seems to be the only place that it occurs is
what is baffling. So, I was hoping that a tool was available.

Anyway, thanks for the tip.

-- rodney

Miha Markic said:
Hi Rodney,

I don't know of any such tool but I do know that nothing beats clean
coding.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

news.microsoft.com said:
Does anyone know of a tool that can look at C# code and make sure that
all open datareaders are closed as well as validate other ADO.Net best
practice principles?

I have a team of 10 developers and we recently had a bug in our code that
claims that an existing connection can't be used because it is associated
with an open datareader. Unfortunately, looking through all the code is
blinding me and so I wondered if there was a tool that could do it for
me.

Any and all help is appreciated.

-- Rodney
Click to expand...
Click to expand...
 
news.microsoft.com said:
Miha,

I concur. The code is actually quite clean
Click to expand...

Actually it isn't clean enough :-). One of the parts of the clean coding is
that you close the connection and/or datareader using try/finally (or using)
statements.
And don't forget disposing.
And do that at the same level (i.e. in same method).
The other approach that might help you is to provide logging. At least
you'll know what sequence of actions led to your problem.

but locating the place in our
production environment where it seems to be the only place that it occurs
is what is baffling. So, I was hoping that a tool was available.
Click to expand...

IMO your best bet is logging.
 
Thanks for the advice. We have logging. All my database objects are pooled
and as such connections are closed when the object is returned to the pool.

So, ultimately, this is what occurring in my application and therefore the
connections are closed appropriately. But, thanks for the tip.

-- Rodney
 
1) How about fixing your newsgroup account so your "from" address comes out
as "Rodney xxxx"?
2) This is a very common problem with the DataReader. First, look for
routines that pass a DataReader back as a Return argument from a Function.
In this case you're skating on thin ice. If the sending routine did not
create the DataReader with CommandBehavior.CloseConnection, you're pooched.
Even if this switch is set, the receiving routine MUST close the DataReader.
Next, make sure that the routines that consume the DataReader have exception
handlers that include code to close the DataReader if something goes wrong.
Many shops have moved away from the DataReader as it's the cause of many
similar issues. Frankly, I think you should use it where it makes sense, but
with a considerable amount of caution. If you MUST pass a DataReader between
routines, you simply must have a strategy to close it and release the
Connection.

I'm lobbying the DataWorks people to add a property on the Connection object
that indicates if there is an open DataReader and a method to close it. In
ADO.NET 2.0 you might be tempted to use MARS which permits multiple
DataReaders on the same connection. However, I expect this would simply mask
the underlying issue and you would eventually overflow the Connection Pool.

hth

--
____________________________________
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.
__________________________________
 
I want to add a little bit to what Bill Vaughn has already mentioned,
specifically regarding this paragraph-
In ADO.NET 2.0 you might be tempted to use MARS which permits multiple
DataReaders on the same connection. However, I expect this would simply
mask the underlying issue and you would eventually overflow the Connection
Pool.
Click to expand...

Yes it is true you could actually have multiple data readers on one single
connection in MARS, but it is way more involved and frankly way more
unpredictable than "just having multiple resultsets". You should in fact,
never, try to maintain two simultaneously active resultsets, except the
following two situations -

a) You wish to execute a select, and DML on the same higher isolation level
in the same transaction
b) You wish to have relatively cleaner code for updating the database while
procesing SqlDataReader. I.E. create reader and start procesing the results,
use a second command to update/insert/delete as you go.

There are many other situations, some of which are beyond your control -
such as how speedily does the client consume the results etc. which can lead
to unpredictable behavior using MARS. So *never* use MARS to mask this
problem.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Okay so like everyone let me begin by saying "No substitute for clean code".

But you knew that :), so let me suggest a solution.

Create an object called SqlConnection - INSIDE your namespace, so it masks
the global::SqlConnection. Inside your Rodney.SqlConnection, put diagnostics
information about who opened the connection, who closed it, and a stackwalk
getting the method/class of who opened it. This log output should always
result in a clean Open-Close pairing. Where it doesn't form a neat Open
Close Pair - is where your problem is. Find that class, find the developer
who wrote it, and beat him on the head.

HTH :)

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Hi Sahil,

Sahil Malik said:
Okay so like everyone let me begin by saying "No substitute for clean
code".

But you knew that :), so let me suggest a solution.

Create an object called SqlConnection - INSIDE your namespace, so it masks
the global::SqlConnection. Inside your Rodney.SqlConnection, put
diagnostics information about who opened the connection, who closed it,
and a stackwalk getting the method/class of who opened it.
Click to expand...

Good suggestion.
And make sure that this is a debug build without optimizations - otherwise
stackwalk might be optimized (inlining).
 
Add a loop where the error is occuring that will output the data in your
datareader.

Knowing what data is in the reader will tell you where in the code it was
created, and therefore where it is not being closed correctly.
 
Back
Top