Hello,

New to ADO.NET so apologies in advance for repeating what I am sure
has been asked been before. I have read that a data reader is much
like a Server Side cursor. I understand this but have one questtion. I
use a SQL Command object to execute a Select statement, then a data
reader to retrieve the results one at a time. When is the result set
generated? When the command's executeReader is run? For example if my
select says something like "Select * from customers" - what happens if
other users change the customers table before my datareader has read
the results? I assume they are not part of my result set and that the
result was actually generated by the executeReader and not
incremnetally as I use the dataReader. Can someone confirm this,
please? If this is implementation dependent I'm talkign SQL Server.

Ta

Ray

See >>>

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
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)
-----------------------------------------------------------------------------------------------------------------------

"rbrowning1958" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> New to ADO.NET so apologies in advance for repeating what I am sure
> has been asked been before. I have read that a data reader is much
> like a Server Side cursor.

>>> Well, sort of. It's a read-only, forward-only "cursor" if it's anything.
>>> It's really a data stream. A "cursor" implies scrollability.

I understand this but have one questtion. I
> use a SQL Command object to execute a Select statement, then a data
> reader to retrieve the results one at a time. When is the result set
> generated? When the command's executeReader is run?

>>> Again, sort of. When the SQL is executed SQL Server starts a
>>> connection-dedicated "agent" who runs off and starts finding rows to
>>> return to the client. When the first few rows (the CacheSize) are found,
>>> the agent tells the client that there is a resultset available to
>>> process. At that point the server-side process (the agent) blocks
>>> waiting for the client to start fetching the rows. As rows are processed
>>> on the client, the server agent goes back to work finding more rows.

For example if my
> select says something like "Select * from customers" - what happens if
> other users change the customers table before my datareader has read
> the results? I assume they are not part of my result set and that the
> result was actually generated by the executeReader and not
> incremnetally as I use the dataReader. Can someone confirm this,
> please? If this is implementation dependent I'm talkign SQL Server.

>>> So, in this case, suppose you fetch the first 5 rows of the rowset (that
>>> could have several hundred rows). The rest of the potential rowset has a
>>> "share" lock on it and can be changed by other clients. Any SELECT gets
>>> a snapshot of the data as it was when the agent is ready to retrieve
>>> that row. It can change an instant later and any number of times
>>> thereafter--or before the agent gets to that row. To "lock down" the
>>> rows, you're going to need a Transaction with a "repeatable read" option
>>> set. This creates a "pessimistic" lock to prevent these changes.

All of this detail boils down to the approach you take toward the design and
implementation of your shared data model. I discuss this in detail in my
book.

>
> Ta
>
> Ray
>