Thursday, January 08, 2009

Best way to get record count using ADO.Net?

I think this may apply to the entity framework, but I'm not sure.

I was modifying a stored procedure which needed to run a query and also return the row count of that query. So the same (fairly long) query was pasted again using select count instead of select by which it set the value of an out parameter. I didn't like this for a few reasons:
  • Dual maintenance of the query.
  • The stored procedure is twice as long.
  • Inefficiency of calling the query twice. The count is already known by SqlServer after running the first query.
  • Having another parameter to configure and hard-code the name of in the calling code.
At first I was thinking that the count of any query is explicitly returned to any ADO call (unless the stored procedure includes "set nocount on"). The count I'm thinking of is the count of records affected that shows in SqlServer's management studio after running any query (N row(s) affected). Rows affected in this case includes rows merely selected.
So I looked at the properties of the SqlDataReader. It has a "RecordsAffected" property. Sounds a lot like "rows affected" no? Well, in order to get that count you must iterate through all the rows in the reader and close it. Obviously, not what I'm looking for.

Next I looked at the SqlCommand object. When you call ExecuteNonQuery, you get records affected! Cool, must be what I'm looking for. Nope - when this object says "records affected" it means just that (unlike what SqlServer means by "rows affected"). It will only return the count of rows updated, deleted, inserted.

So what seems to be the best way is to include a secondary recordset in the stored procedure: "select @@rowcount." My coworker verified that this count is connection specific (you don't get someone else's count). It's the count that management studio displays.
Accessing this count from a reader goes like yay:

_reader.NextResult();
_reader.Read();
_reader[0];


The call to NextResult advances the reader to the next result set (the set that only includes the result of "select @@rowcount"). We know that result set contains only the count so we don't need any explicit column names.

No comments: