Getting the row count from a SQLDataReader
-
Ok so I am using a SqlDataReader in ADO.NET because I do not want or need the overhead of a DataSet. But now I have just realised I cannot figure out how to get a row count from the reader without actually looping through it, which bites because I need the rowcount before I loop so that I can initialise a collection array. Doh. DOTNET247 seems to say that there is no way other than either looping through or using horrid SQL
countstatements. So anybody got a magic bullet? Or should I just bite the bullet and do that SQL count?Paul Watson
Bluegrass
Cape Town, South AfricaYou can't, because it's a forward-only cursor. Paul Watson wrote: But now I have just realised I cannot figure out how to get a row count from the reader without actually looping through it, which bites because I need the rowcount before I loop so that I can initialise a collection array. Doh. You can create an ArrayList and, at the end, use the ArrayList.CopyTo method for copying it to typed array. Since this will only copy object references, not the actual values, it's very fast. Normally much, much faster than a SQL count. Q261186 - Computer Randomly Plays Classical Music
-
You can't, because it's a forward-only cursor. Paul Watson wrote: But now I have just realised I cannot figure out how to get a row count from the reader without actually looping through it, which bites because I need the rowcount before I loop so that I can initialise a collection array. Doh. You can create an ArrayList and, at the end, use the ArrayList.CopyTo method for copying it to typed array. Since this will only copy object references, not the actual values, it's very fast. Normally much, much faster than a SQL count. Q261186 - Computer Randomly Plays Classical Music
Daniel Turini wrote: You can't, because it's a forward-only cursor. I know, was hoping someone had some trick up their sleave :) Daniel Turini wrote: Since this will only copy object references, not the actual values, it's very fast. Normally much, much faster than a SQL count. Great idea, thanks Daniel.
Paul Watson
Bluegrass
Cape Town, South Africa -
You can't, because it's a forward-only cursor. Paul Watson wrote: But now I have just realised I cannot figure out how to get a row count from the reader without actually looping through it, which bites because I need the rowcount before I loop so that I can initialise a collection array. Doh. You can create an ArrayList and, at the end, use the ArrayList.CopyTo method for copying it to typed array. Since this will only copy object references, not the actual values, it's very fast. Normally much, much faster than a SQL count. Q261186 - Computer Randomly Plays Classical Music
If you had used an SQL COUNT() function before you retrieved the it probably wouldn't be accurate either (atleast not in a multiuser enviornment), unless it's run in a serializable transaction. The SqlDataReader streams the data and will reflect all the changes in the database until the data has passed through the reader. So records could easily be added or deleted after the count was issued and while the reader is processing. :) Morty
-
If you had used an SQL COUNT() function before you retrieved the it probably wouldn't be accurate either (atleast not in a multiuser enviornment), unless it's run in a serializable transaction. The SqlDataReader streams the data and will reflect all the changes in the database until the data has passed through the reader. So records could easily be added or deleted after the count was issued and while the reader is processing. :) Morty
Morten Abrahamsen wrote: If you had used an SQL COUNT() function before you retrieved the it probably wouldn't be accurate either (atleast not in a multiuser enviornment), unless it's run in a serializable transaction. Nice point Morten Abrahamsen wrote: The SqlDataReader streams the data and will reflect all the changes in the database until the data has passed through the reader. So records could easily be added or deleted after the count was issued and while the reader is processing. This is not true: The .NET framework does not provides support for server-side cursors, only client-side cursors. This way, only deleted records would be noticed by SqlDataReader. Q261186 - Computer Randomly Plays Classical Music
-
Morten Abrahamsen wrote: If you had used an SQL COUNT() function before you retrieved the it probably wouldn't be accurate either (atleast not in a multiuser enviornment), unless it's run in a serializable transaction. Nice point Morten Abrahamsen wrote: The SqlDataReader streams the data and will reflect all the changes in the database until the data has passed through the reader. So records could easily be added or deleted after the count was issued and while the reader is processing. This is not true: The .NET framework does not provides support for server-side cursors, only client-side cursors. This way, only deleted records would be noticed by SqlDataReader. Q261186 - Computer Randomly Plays Classical Music
What do you base this on ? AFAIK, the data is streamed directly from the server, and even though it doesn't utilize server-side cursors it doesn't precalculate the entire dataset (it's streamed ... standard SQL Server dataset processing...). So if records are appended during the read it should be reflected. However I could be wrong, so if you have any docs / references please post them :) .NET SDK: Changes made to a resultset by another process or thread while data is being read may be visible to the user of the SqlDataReader. However, the precise behavior is timing dependent. Morty
-
What do you base this on ? AFAIK, the data is streamed directly from the server, and even though it doesn't utilize server-side cursors it doesn't precalculate the entire dataset (it's streamed ... standard SQL Server dataset processing...). So if records are appended during the read it should be reflected. However I could be wrong, so if you have any docs / references please post them :) .NET SDK: Changes made to a resultset by another process or thread while data is being read may be visible to the user of the SqlDataReader. However, the precise behavior is timing dependent. Morty
You may be right: I'm sure the SqlDataReader is a client-side cursor (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/dvconChoosingRightDataAccessTechnology.asp[^]) but the rest was a conclusion of mine... Q261186 - Computer Randomly Plays Classical Music
-
You may be right: I'm sure the SqlDataReader is a client-side cursor (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/dvconChoosingRightDataAccessTechnology.asp[^]) but the rest was a conclusion of mine... Q261186 - Computer Randomly Plays Classical Music
Well... the SqlDataReader is by no means a client side cursor... it's simply a TDS parser. That's what the dataset is for ;) Would be cool to check out the theory though... (how much locking does the TDS generator really support...) but as usual, I would probably never find the time. Morty
-
Well... the SqlDataReader is by no means a client side cursor... it's simply a TDS parser. That's what the dataset is for ;) Would be cool to check out the theory though... (how much locking does the TDS generator really support...) but as usual, I would probably never find the time. Morty
Morten Abrahamsen wrote: Well... the SqlDataReader is by no means a client side cursor... it's simply a TDS parser. That's what the dataset is for Well, if it is a TDS parser it should be pretty fast! I think you're right, but some Anakrino hacking may solve this issue :) Q261186 - Computer Randomly Plays Classical Music
-
Morten Abrahamsen wrote: Well... the SqlDataReader is by no means a client side cursor... it's simply a TDS parser. That's what the dataset is for Well, if it is a TDS parser it should be pretty fast! I think you're right, but some Anakrino hacking may solve this issue :) Q261186 - Computer Randomly Plays Classical Music
Hehe... been there ;) However, this is not an issue dependant on the .NET SQL Client layer. It's a question of how the SQL Server handles standard query processing. If you issue a select query with a 100.000 record resultset, it would be returned as a datastream (TDS) to the client. I would think that if there is no isolation (tx) there would just be a read lock on the current row (or index key) and not on the table, so records could easily be added or deleted. So if I'm correct the SQL Server would just read the data page by page and never precalculate the total amount and lock it. Hence the "imagined resultset" could be radically (add/delete) changed during the processing (which could take time), and a prior Count would be useless. Just my 2c :)
-
Hehe... been there ;) However, this is not an issue dependant on the .NET SQL Client layer. It's a question of how the SQL Server handles standard query processing. If you issue a select query with a 100.000 record resultset, it would be returned as a datastream (TDS) to the client. I would think that if there is no isolation (tx) there would just be a read lock on the current row (or index key) and not on the table, so records could easily be added or deleted. So if I'm correct the SQL Server would just read the data page by page and never precalculate the total amount and lock it. Hence the "imagined resultset" could be radically (add/delete) changed during the processing (which could take time), and a prior Count would be useless. Just my 2c :)
I deduced the inserts would not be visible and deletes would by this: what would happen with a forward only cursor if I do a SELECT ... ORDER BY table_field and someone inserts data before my current cursor position? Q261186 - Computer Randomly Plays Classical Music
-
I deduced the inserts would not be visible and deletes would by this: what would happen with a forward only cursor if I do a SELECT ... ORDER BY table_field and someone inserts data before my current cursor position? Q261186 - Computer Randomly Plays Classical Music
I agree. If you do an order by and that order by is not the clustered index SQL Server would have to preprocess the entire dataset. (meaning the keys .. not necessarily the data). However, it would be interesting to see what happens if you don't use a postprocessing instruction. (ORDER BY, GROUP BY, UNION, DISTINCT etc) :) Morty