Sahil
Thu Oct 27 22:51:40 CDT 2005
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.
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
----------------------------------------------------------------------------
"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:%23JwoE2x2FHA.1420@TK2MSFTNGP09.phx.gbl...
> 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.
> __________________________________
>
> "news.microsoft.com" <rwoodruf@umich.edu> wrote in message
> news:eZ3flPn2FHA.1980@TK2MSFTNGP15.phx.gbl...
>> 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
>>
>
>