|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Data reader / Serverside cursorHello,
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" <RBrowning1***@gmail.com> wrote in message I understand this but have one questtion. Inews:1194797600.941785.317030@c30g2000hsa.googlegroups.com... > 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. > use a SQL Command object to execute a Select statement, then a data For example if my> 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. Show quote > select says something like "Select * from customers" - what happens if All of this detail boils down to the approach you take toward the design and > 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. implementation of your shared data model. I discuss this in detail in my book. Show quote > > Ta > > Ray >  |
|||||||||||||||||||||||
Other interesting topics