CSharp/C#, Getting Record Count in SqlDataReader

Getting Record Count in SqlDataReader

Author	Message
Raphael Ilo #1 / 5	Getting Record Count in SqlDataReader Hi all, this sounds pretty trivial but I'd like to know how to obtain the record count in an SqlDataReader object used in a SELECT statement. The SqlDataReader class has a property called RecordsAffected which return an int data type; this property would have suffix but it returns -1 when used with a SELECT statement. Using a DataSet object; ds; I could have something like this - ds.Tables[0].Rows.Count - return the record count. I'm not opting for a DataSet object 'cos it's very slow and we are looking at over 12,000 records at any successful SELECT query and over 8,000 possible queries at any time. I need to get the record count before running the Read() method in the SqlDataReader object or else the exception handler will be called if no record is found. Is there a way of getting the record count of an SqlDataReader object in a SELECT query before proceeding to the Read() while loop? Regards, Raphael.
Mon, 28 Mar 2005 18:32:37 GMT

fréd #2 / 5	Getting Record Count in SqlDataReader a datareader read sequentially records. So it can't knows the number of record to get. So you only have indirect solutions. 1) using a counter in the Read() loop 2) using a complex command. You add a count(*) SQL request that you first execute and then you execute your select request after a NextResult() command Quote: >-----Original Message----- >Hi all, this sounds pretty trivial but I'd like to know >how to obtain the record count in an SqlDataReader object >used in a SELECT statement. The SqlDataReader class has a >property called RecordsAffected which return an int data >type; this property would have suffix but it returns -1 >when used with a SELECT statement. >Using a DataSet object; ds; I could have something like >this - ds.Tables[0].Rows.Count - return the record count. >I'm not opting for a DataSet object 'cos it's very slow >and we are looking at over 12,000 records at any >successful SELECT query and over 8,000 possible queries at >any time. >I need to get the record count before running the Read() >method in the SqlDataReader object or else the exception >handler will be called if no record is found. >Is there a way of getting the record count of an >SqlDataReader object in a SELECT query before proceeding >to the Read() while loop? >Regards, Raphael. >.
Mon, 28 Mar 2005 19:39:40 GMT

Raphael Ilo #3 / 5	Getting Record Count in SqlDataReader ...thanks frdo, but even using a ReturnValue or an Output parameter, I'd still need to run the Read() method. If you attempt to manipulate data within the Read() method when no data exsits, it will throw an exception for sure. The other alternative I could probably think of is to assign the value of a non-null column0 to a variable and test the value before reading any further. Quote: >-----Original Message----- >a datareader read sequentially records. So it can't knows >the number of record to get. >So you only have indirect solutions. >1) using a counter in the Read() loop >2) using a complex command. You add a count(*) SQL >request that you first execute and then you execute your >select request after a NextResult() command >>-----Original Message----- >>Hi all, this sounds pretty trivial but I'd like to know >>how to obtain the record count in an SqlDataReader >object >>used in a SELECT statement. The SqlDataReader class has >a >>property called RecordsAffected which return an int data >>type; this property would have suffix but it returns -1 >>when used with a SELECT statement. >>Using a DataSet object; ds; I could have something like >>this - ds.Tables[0].Rows.Count - return the record >count. >>I'm not opting for a DataSet object 'cos it's very slow >>and we are looking at over 12,000 records at any >>successful SELECT query and over 8,000 possible queries >at >>any time. >>I need to get the record count before running the Read() >>method in the SqlDataReader object or else the exception >>handler will be called if no record is found. >>Is there a way of getting the record count of an >>SqlDataReader object in a SELECT query before proceeding >>to the Read() while loop? >>Regards, Raphael. >>. >.
Mon, 28 Mar 2005 20:06:23 GMT

Jose Joy #4 / 5	Getting Record Count in SqlDataReader I do not see your point regarding the Read() manipulation when NO data exist. The Read() method, returns a bool. By checking this value you will see if you have a row or not: while (myDataReader.Read()) ... Jos ...thanks frdo, but even using a ReturnValue or an Output parameter, I'd still need to run the Read() method. If you attempt to manipulate data within the Read() method when no data exsits, it will throw an exception for sure. The other alternative I could probably think of is to assign the value of a non-null column0 to a variable and test the value before reading any further. Quote: >-----Original Message----- >a datareader read sequentially records. So it can't knows >the number of record to get. >So you only have indirect solutions. >1) using a counter in the Read() loop >2) using a complex command. You add a count(*) SQL >request that you first execute and then you execute your >select request after a NextResult() command >>-----Original Message----- >>Hi all, this sounds pretty trivial but I'd like to know >>how to obtain the record count in an SqlDataReader >object >>used in a SELECT statement. The SqlDataReader class has >a >>property called RecordsAffected which return an int data >>type; this property would have suffix but it returns -1 >>when used with a SELECT statement. >>Using a DataSet object; ds; I could have something like >>this - ds.Tables[0].Rows.Count - return the record >count. >>I'm not opting for a DataSet object 'cos it's very slow >>and we are looking at over 12,000 records at any >>successful SELECT query and over 8,000 possible queries >at >>any time. >>I need to get the record count before running the Read() >>method in the SqlDataReader object or else the exception >>handler will be called if no record is found. >>Is there a way of getting the record count of an >>SqlDataReader object in a SELECT query before proceeding >>to the Read() while loop? >>Regards, Raphael. >>. >.
Mon, 28 Mar 2005 20:18:50 GMT

Don Dumitr #5 / 5	Getting Record Count in SqlDataReader When data is returned from SQL Server, it doesn't know ahead of time how many records are going to be returned. In the case of a large result set, records will start being returned before the entire query is complete - your client will received the first record before the server know the total record count. So the format of the on-the-wire protocol does not send a "number of rows" count until after the very last record is sent. The normal pattern for reading from a SqlDataReader is... while (reader.Read()) { // is there another record? // use reader to get the fields you want from this particular record Quote: } --Don -- This posting is provided "AS IS" with no warranties, and confers no rights. Quote: > Hi all, this sounds pretty trivial but I'd like to know > how to obtain the record count in an SqlDataReader object > used in a SELECT statement. The SqlDataReader class has a > property called RecordsAffected which return an int data > type; this property would have suffix but it returns -1 > when used with a SELECT statement. > Using a DataSet object; ds; I could have something like > this - ds.Tables[0].Rows.Count - return the record count. > I'm not opting for a DataSet object 'cos it's very slow > and we are looking at over 12,000 records at any > successful SELECT query and over 8,000 possible queries at > any time. > I need to get the record count before running the Read() > method in the SqlDataReader object or else the exception > handler will be called if no record is found. > Is there a way of getting the record count of an > SqlDataReader object in a SELECT query before proceeding > to the Read() while loop? > Regards, Raphael.
Tue, 29 Mar 2005 02:38:01 GMT

Page 1 of 1

[ 5 post ]