|
 |
aspx thread: Record Count
Message #1 by "Hugh McLaughlin" <hugh@k...> on Sat, 8 Dec 2001 13:43:24
|
|
Hello everyone and thanks for your help in advance. I am using a SQL
Server datareader to create a datagrid. I am trying to get a record count
that is returned from the SQL command. I tried:
strRecordCount=myReader.RecordsAffected
but this always returns -1 (not sure why) regardless of record count. How
do I get the number of records returned. As a related question, I want to
return the record count to test if it is =0 and turn off the datagrid
display if there are no records. Is there a more efficient way of doing
this. Thanks.
Message #2 by "Albert Davis" <albertdavis@h...> on Sat, 08 Dec 2001 09:56:00 -0500
|
|
The DataReader.RecordsAffected property can only be accessed when you close
it. Remember this is forward-only firehose approach in retrieving data, so
this field will only show you a count if your cmdType did an Change, Insert,
or Delete and a 0 for "no records affected". If it was a select statement
then you will always get a -1 no matter when you access it. If you want a
recordcount then you'll want to use an adapter that then fills a DataSet
exposing a DataTable which has a Rows property retrieving a
DataRowCollection. The DataRowCollection is derived from
InternalDataCollectionBase. InternalDataCollectionBase has a Count property
that exposes this functionality. So if you are using VS.NET you can see
this through intellisense by typing the following in any class:
...
(new DataTable()).Rows.Count;
...
For practically you would achieve this by doing the following,
I'll use the SqlClient namespace but you can use OleDbDataAdapter to do the
same...
...
DataSet dbDataSet = new DataSet();
SqlConnection dbConn = new SqlConnection(connectionString);
SqlDataAdapter dbAdapter = new SqlDataAdapter("SELECT * FROM
Master",dbConn);
dbAdapter.Fill(dbDataSet);
int recordCount = dbDataSet.Tables[0].Rows.Count
...
Remember a DataSet can hold many tables so I used "0" as the indexer due to
my select statement only returning 1 and positioning itself "0" by default.
But you could surely get around all of this and still get a RecordCount from
a DataReader, like for instance your DataReader executes a cmdType that is a
stored proc, well in that stored proc instead of just returning the
recordset that your select statements were wanting, return 2 recordsets, the
first one being a one column single recordset with the count and the second
being the actual recordset that your select statement needs to be returned.
You then get to this recordset by using DataReader.NextResult().
In the stored proc:
SELECT Count(*) RecordCount FROM Master
SELECT * FROM Master
Then in your code you will need to do:
...
... //We'll assume that you already executed this proc and got back a
... //DataReader.
...
DataReader.Read();
int recordCount = (int)DataReader["RecordCount"];
if(recordCount>0){
DataReader.NextResult(); //This returns a bool, but I won't capture.
while(DataReader.Read()){
... //Business Logic.
}
}
Hope this helps,
Al
>From: "Hugh McLaughlin" <hugh@k...>
>Reply-To: "ASP+" <aspx@p...>
>To: "ASP+" <aspx@p...>
>Subject: [aspx] Record Count
>Date: Sat, 8 Dec 2001 13:43:24
>
>Hello everyone and thanks for your help in advance. I am using a SQL
>Server datareader to create a datagrid. I am trying to get a record count
>that is returned from the SQL command. I tried:
>
>strRecordCount=myReader.RecordsAffected
>
>but this always returns -1 (not sure why) regardless of record count. How
>do I get the number of records returned. As a related question, I want to
>return the record count to test if it is =0 and turn off the datagrid
>display if there are no records. Is there a more efficient way of doing
>this. Thanks.
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
Message #3 by "GfWeis" <gfw@y...> on Sat, 8 Dec 2001 09:02:36 -0600
|
|
Another approach is to merely execute a function to count the records
and then execute the data reader - a sample function to count records in
an SQL data base can be found at
http://dn.yyyz.net/DnYzArticleShow.aspx?WA=2
Gfw
-----Original Message-----
From: Albert Davis [mailto:albertdavis@h...]
Sent: Saturday, December 08, 2001 8:56 AM
To: ASP+
Subject: [aspx] Re: Record Count
The DataReader.RecordsAffected property can only be accessed when you
close
it. Remember this is forward-only firehose approach in retrieving data,
so
this field will only show you a count if your cmdType did an Change,
Insert,
or Delete and a 0 for "no records affected". If it was a select
statement
then you will always get a -1 no matter when you access it. If you want
a
recordcount then you'll want to use an adapter that then fills a DataSet
exposing a DataTable which has a Rows property retrieving a
DataRowCollection. The DataRowCollection is derived from
InternalDataCollectionBase. InternalDataCollectionBase has a Count
property
that exposes this functionality. So if you are using VS.NET you can see
this through intellisense by typing the following in any class: ... (new
DataTable()).Rows.Count; ...
For practically you would achieve this by doing the following, I'll use
the SqlClient namespace but you can use OleDbDataAdapter to do the
same...
...
DataSet dbDataSet = new DataSet();
SqlConnection dbConn = new SqlConnection(connectionString);
SqlDataAdapter dbAdapter = new SqlDataAdapter("SELECT * FROM
Master",dbConn);
dbAdapter.Fill(dbDataSet);
int recordCount = dbDataSet.Tables[0].Rows.Count
...
Remember a DataSet can hold many tables so I used "0" as the indexer due
to
my select statement only returning 1 and positioning itself "0" by
default.
But you could surely get around all of this and still get a RecordCount
from
a DataReader, like for instance your DataReader executes a cmdType that
is a
stored proc, well in that stored proc instead of just returning the
recordset that your select statements were wanting, return 2 recordsets,
the
first one being a one column single recordset with the count and the
second
being the actual recordset that your select statement needs to be
returned.
You then get to this recordset by using DataReader.NextResult().
In the stored proc:
SELECT Count(*) RecordCount FROM Master
SELECT * FROM Master
Then in your code you will need to do:
...
... //We'll assume that you already executed this proc and got back a
... //DataReader. ...
DataReader.Read();
int recordCount = (int)DataReader["RecordCount"];
if(recordCount>0){
DataReader.NextResult(); //This returns a bool, but I won't capture.
while(DataReader.Read()){
... //Business Logic.
}
}
Hope this helps,
Al
>From: "Hugh McLaughlin" <hugh@k...>
>Reply-To: "ASP+" <aspx@p...>
>To: "ASP+" <aspx@p...>
>Subject: [aspx] Record Count
>Date: Sat, 8 Dec 2001 13:43:24
>
>Hello everyone and thanks for your help in advance. I am using a SQL
>Server datareader to create a datagrid. I am trying to get a record
>count that is returned from the SQL command. I tried:
>
>strRecordCount=myReader.RecordsAffected
>
>but this always returns -1 (not sure why) regardless of record count.
>How do I get the number of records returned. As a related question, I
>want to return the record count to test if it is =0 and turn off the
>datagrid display if there are no records. Is there a more efficient
>way of doing this. Thanks.
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to $subst('Email.Unsub').
_________________________________________________________________
Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp
Message #4 by "Albert Davis" <albertdavis@h...> on Sat, 08 Dec 2001 10:18:48 -0500
|
|
Certainly another approach, I guess it just really comes down to what tier
it resides, data or middle(which may or may not be decoupled by middleware),
and the type of speed your looking for...
Cheers!
>From: "GfWeis" <gfw@y...>
>Reply-To: "ASP+" <aspx@p...>
>To: "ASP+" <aspx@p...>
>Subject: [aspx] Re: Record Count
>Date: Sat, 8 Dec 2001 09:02:36 -0600
>
>Another approach is to merely execute a function to count the records
>and then execute the data reader - a sample function to count records in
>an SQL data base can be found at
>http://dn.yyyz.net/DnYzArticleShow.aspx?WA=2
>
>Gfw
>
>
>-----Original Message-----
>From: Albert Davis [mailto:albertdavis@h...]
>Sent: Saturday, December 08, 2001 8:56 AM
>To: ASP+
>Subject: [aspx] Re: Record Count
>
>
>The DataReader.RecordsAffected property can only be accessed when you
>close
>it. Remember this is forward-only firehose approach in retrieving data,
>so
>this field will only show you a count if your cmdType did an Change,
>Insert,
>or Delete and a 0 for "no records affected". If it was a select
>statement
>then you will always get a -1 no matter when you access it. If you want
>a
>recordcount then you'll want to use an adapter that then fills a DataSet
>
>exposing a DataTable which has a Rows property retrieving a
>DataRowCollection. The DataRowCollection is derived from
>InternalDataCollectionBase. InternalDataCollectionBase has a Count
>property
>that exposes this functionality. So if you are using VS.NET you can see
>
>this through intellisense by typing the following in any class: ... (new
>DataTable()).Rows.Count; ...
>
>For practically you would achieve this by doing the following, I'll use
>the SqlClient namespace but you can use OleDbDataAdapter to do the
>same...
>...
>DataSet dbDataSet = new DataSet();
>SqlConnection dbConn = new SqlConnection(connectionString);
>SqlDataAdapter dbAdapter = new SqlDataAdapter("SELECT * FROM
>Master",dbConn);
>dbAdapter.Fill(dbDataSet);
>
>int recordCount = dbDataSet.Tables[0].Rows.Count
>...
>
>Remember a DataSet can hold many tables so I used "0" as the indexer due
>to
>my select statement only returning 1 and positioning itself "0" by
>default.
>
>But you could surely get around all of this and still get a RecordCount
>from
>a DataReader, like for instance your DataReader executes a cmdType that
>is a
>stored proc, well in that stored proc instead of just returning the
>recordset that your select statements were wanting, return 2 recordsets,
>the
>first one being a one column single recordset with the count and the
>second
>being the actual recordset that your select statement needs to be
>returned.
>You then get to this recordset by using DataReader.NextResult().
>
>In the stored proc:
>
>SELECT Count(*) RecordCount FROM Master
>SELECT * FROM Master
>
>Then in your code you will need to do:
>...
>... //We'll assume that you already executed this proc and got back a
>... //DataReader. ...
>
>DataReader.Read();
>int recordCount = (int)DataReader["RecordCount"];
>
>if(recordCount>0){
> DataReader.NextResult(); //This returns a bool, but I won't capture.
>
> while(DataReader.Read()){
> ... //Business Logic.
> }
>}
>
>Hope this helps,
>Al
>
> >From: "Hugh McLaughlin" <hugh@k...>
> >Reply-To: "ASP+" <aspx@p...>
> >To: "ASP+" <aspx@p...>
> >Subject: [aspx] Record Count
> >Date: Sat, 8 Dec 2001 13:43:24
> >
> >Hello everyone and thanks for your help in advance. I am using a SQL
> >Server datareader to create a datagrid. I am trying to get a record
> >count that is returned from the SQL command. I tried:
> >
> >strRecordCount=myReader.RecordsAffected
> >
> >but this always returns -1 (not sure why) regardless of record count.
> >How do I get the number of records returned. As a related question, I
> >want to return the record count to test if it is =0 and turn off the
> >datagrid display if there are no records. Is there a more efficient
> >way of doing this. Thanks.
> >---
> >Change your mail options at http://p2p.wrox.com/manager.asp or to
> >unsubscribe send a blank email to $subst('Email.Unsub').
>
>
>_________________________________________________________________
>Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp
>
>
>
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
Message #5 by "GfWeis" <gfw@y...> on Sat, 8 Dec 2001 10:02:25 -0600
|
|
ExecuteScalar is faster than ExecuteReader is faster than
SqlDataAdapter. It would be interesting to time test executing the
ExecuteScalar/ExecuteReader vs the SqlDataAdapter.
Actually, I usually just use your method (SqlDataAdapter) when I need a
record count.
Gfw :~}
-----Original Message-----
From: Albert Davis [mailto:albertdavis@h...]
Sent: Saturday, December 08, 2001 9:19 AM
To: ASP+
Subject: [aspx] Re: Record Count
Certainly another approach, I guess it just really comes down to what
tier
it resides, data or middle(which may or may not be decoupled by
middleware),
and the type of speed your looking for...
Cheers!
>From: "GfWeis" <gfw@y...>
>Reply-To: "ASP+" <aspx@p...>
>To: "ASP+" <aspx@p...>
>Subject: [aspx] Re: Record Count
>Date: Sat, 8 Dec 2001 09:02:36 -0600
>
>Another approach is to merely execute a function to count the records
>and then execute the data reader - a sample function to count records
>in an SQL data base can be found at
>http://dn.yyyz.net/DnYzArticleShow.aspx?WA=2
>
>Gfw
>
>
>-----Original Message-----
>From: Albert Davis [mailto:albertdavis@h...]
>Sent: Saturday, December 08, 2001 8:56 AM
>To: ASP+
>Subject: [aspx] Re: Record Count
>
>
>The DataReader.RecordsAffected property can only be accessed when you
>close it. Remember this is forward-only firehose approach in
>retrieving data, so
>this field will only show you a count if your cmdType did an Change,
>Insert,
>or Delete and a 0 for "no records affected". If it was a select
>statement
>then you will always get a -1 no matter when you access it. If you
want
>a
>recordcount then you'll want to use an adapter that then fills a
DataSet
>
>exposing a DataTable which has a Rows property retrieving a
>DataRowCollection. The DataRowCollection is derived from
>InternalDataCollectionBase. InternalDataCollectionBase has a Count
>property that exposes this functionality. So if you are using VS.NET
>you can see
>
>this through intellisense by typing the following in any class: ...
>(new DataTable()).Rows.Count; ...
>
>For practically you would achieve this by doing the following, I'll use
>the SqlClient namespace but you can use OleDbDataAdapter to do the
>same... ...
>DataSet dbDataSet = new DataSet();
>SqlConnection dbConn = new SqlConnection(connectionString);
>SqlDataAdapter dbAdapter = new SqlDataAdapter("SELECT * FROM
>Master",dbConn);
>dbAdapter.Fill(dbDataSet);
>
>int recordCount = dbDataSet.Tables[0].Rows.Count
>...
>
>Remember a DataSet can hold many tables so I used "0" as the indexer
>due to my select statement only returning 1 and positioning itself "0"
>by default.
>
>But you could surely get around all of this and still get a RecordCount
>from a DataReader, like for instance your DataReader executes a cmdType
>that is a
>stored proc, well in that stored proc instead of just returning the
>recordset that your select statements were wanting, return 2
recordsets,
>the
>first one being a one column single recordset with the count and the
>second
>being the actual recordset that your select statement needs to be
>returned.
>You then get to this recordset by using DataReader.NextResult().
>
>In the stored proc:
>
>SELECT Count(*) RecordCount FROM Master
>SELECT * FROM Master
>
>Then in your code you will need to do:
>...
>... //We'll assume that you already executed this proc and got back a
>... //DataReader. ...
>
>DataReader.Read();
>int recordCount = (int)DataReader["RecordCount"];
>
>if(recordCount>0){
> DataReader.NextResult(); //This returns a bool, but I won't
>capture.
>
> while(DataReader.Read()){
> ... //Business Logic.
> }
>}
>
>Hope this helps,
>Al
>
> >From: "Hugh McLaughlin" <hugh@k...>
> >Reply-To: "ASP+" <aspx@p...>
> >To: "ASP+" <aspx@p...>
> >Subject: [aspx] Record Count
> >Date: Sat, 8 Dec 2001 13:43:24
> >
> >Hello everyone and thanks for your help in advance. I am using a SQL
> >Server datareader to create a datagrid. I am trying to get a record
> >count that is returned from the SQL command. I tried:
> >
> >strRecordCount=myReader.RecordsAffected
> >
> >but this always returns -1 (not sure why) regardless of record count.
> >How do I get the number of records returned. As a related question,
> >I want to return the record count to test if it is =0 and turn off
> >the datagrid display if there are no records. Is there a more
> >efficient way of doing this. Thanks.
> >---
> >Change your mail options at http://p2p.wrox.com/manager.asp or to
> >unsubscribe send a blank email to $subst('Email.Unsub').
>
>
>_________________________________________________________________
>Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to $subst('Email.Unsub').
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to $subst('Email.Unsub').
_________________________________________________________________
Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp
Message #6 by "Albert Davis" <albertdavis@h...> on Sat, 08 Dec 2001 12:57:25 -0500
|
|
When accessing a DB I primary want to get in and out as quick as possible,
but within .NET I don't want to do COM Interop with an Unmanaged C++ object
that reads and writes data -- I still want to get good speed but with less
hassle, thus more the reason to use the .NET Framework. So to get the speed
I'm looking for using the most simplistic approach I use a combination route
like I described below. No matter how you look at it I have 2 requirements
when touching an RDBMS -- 1)Marshalling of calls to an RDBMS to do something
and 2)After the Marshalling has taken place, tell me what was affected
(DataReader.RecordsAffected & DataRowsCollection.Count).
We all know up front there are 2 basic ways of working with Data within
.NET, Disconnected and Connected. I really think the Disconnected route is
a great feature and with the previous version of ADO, achieving Disconnected
functionality wasn't native nor easy. Disconnected and Connected paths all
have there place that really depend on what needs are. Considering this I
am just talking about getting in and out as fast as possible and not
worrying about keeping the data around (in cache) once I get it as well as
holding true to my 2 requirements. Using a DataSet can serve my 2
requirments going down the Disconnected path but this is where the lack of
speed comes into play. DataReaders have much better response time for
retrieving data unarguabully, but what about sticking with my 2 reqs? Well
the RecordCount isn't a built in to the DataReader (or is it? Hidden
Funtionality by M$? M$ please do tell!) but the speed for getting in and out
as fast as possible is. Well we could first use ExecuteScalar to get the
count, but then if satisfied we still got to make another call using
ExecuteReader with a different query get the results -- 1 connection obj, 2
command objs, 2 db calls. This route is extremely well suited for serving
my 2 reqs (although I have never timed this route) and is probably the route
to take if needing to centralize this logic.
Centralizing this approach is not as important to me, so for fast reading
and writing (remembering what my initial consideration was -- not worrying
about hanging on to the data) I like to spread this task to the sp's as
well. All of the sp's that support fast IO for business tasks having the
need for speed, I have choosen to always return 2 recordset instead of one,
where the first is always a one column recordset holding the record count of
the query to be executed. I then go a step further and create const's
(these are named the same as the columns in the table or query alias's) that
hold the column positions of the returned recordset that then allow me to
use the Type Safe Methods provided within the DataReader class
(GetInt32(const),GetFloat(const),etc.). At this level you get a whopping
speed increase between 3-8 fold (depending how many accesses were done) than
using text as your indexer e.g. DataReader["myColumn"] and about .1 sec
increase than a numeric indexer e.g. DataReader[0] due to the fact that text
indexers have to look up the column number, look up the type, etc. -- 1
connection obj, 1 command obj, 1 db call + better speed..
This couldn't be used in every case nor would I want it to but when I have a
process that needs to talk speed (and the data doesn't need to hang around)
the above route has always served.
:) Al
>From: "GfWeis" <gfw@y...>
>Reply-To: "ASP+" <aspx@p...>
>To: "ASP+" <aspx@p...>
>Subject: [aspx] Re: Record Count
>Date: Sat, 8 Dec 2001 10:02:25 -0600
>
>ExecuteScalar is faster than ExecuteReader is faster than
>SqlDataAdapter. It would be interesting to time test executing the
>ExecuteScalar/ExecuteReader vs the SqlDataAdapter.
>Actually, I usually just use your method (SqlDataAdapter) when I need a
>record count.
>
>Gfw :~}
>
>
>-----Original Message-----
>From: Albert Davis [mailto:albertdavis@h...]
>Sent: Saturday, December 08, 2001 9:19 AM
>To: ASP+
>Subject: [aspx] Re: Record Count
>
>
>Certainly another approach, I guess it just really comes down to what
>tier
>it resides, data or middle(which may or may not be decoupled by
>middleware),
>and the type of speed your looking for...
>
>Cheers!
>
> >From: "GfWeis" <gfw@y...>
> >Reply-To: "ASP+" <aspx@p...>
> >To: "ASP+" <aspx@p...>
> >Subject: [aspx] Re: Record Count
> >Date: Sat, 8 Dec 2001 09:02:36 -0600
> >
> >Another approach is to merely execute a function to count the records
> >and then execute the data reader - a sample function to count records
> >in an SQL data base can be found at
> >http://dn.yyyz.net/DnYzArticleShow.aspx?WA=2
> >
> >Gfw
> >
> >
> >-----Original Message-----
> >From: Albert Davis [mailto:albertdavis@h...]
> >Sent: Saturday, December 08, 2001 8:56 AM
> >To: ASP+
> >Subject: [aspx] Re: Record Count
> >
> >
> >The DataReader.RecordsAffected property can only be accessed when you
> >close it. Remember this is forward-only firehose approach in
> >retrieving data, so
> >this field will only show you a count if your cmdType did an Change,
> >Insert,
> >or Delete and a 0 for "no records affected". If it was a select
> >statement
> >then you will always get a -1 no matter when you access it. If you
>want
> >a
> >recordcount then you'll want to use an adapter that then fills a
>DataSet
> >
> >exposing a DataTable which has a Rows property retrieving a
> >DataRowCollection. The DataRowCollection is derived from
> >InternalDataCollectionBase. InternalDataCollectionBase has a Count
> >property that exposes this functionality. So if you are using VS.NET
> >you can see
> >
> >this through intellisense by typing the following in any class: ...
> >(new DataTable()).Rows.Count; ...
> >
> >For practically you would achieve this by doing the following, I'll use
>
> >the SqlClient namespace but you can use OleDbDataAdapter to do the
> >same... ...
> >DataSet dbDataSet = new DataSet();
> >SqlConnection dbConn = new SqlConnection(connectionString);
> >SqlDataAdapter dbAdapter = new SqlDataAdapter("SELECT * FROM
> >Master",dbConn);
> >dbAdapter.Fill(dbDataSet);
> >
> >int recordCount = dbDataSet.Tables[0].Rows.Count
> >...
> >
> >Remember a DataSet can hold many tables so I used "0" as the indexer
> >due to my select statement only returning 1 and positioning itself "0"
> >by default.
> >
> >But you could surely get around all of this and still get a RecordCount
>
> >from a DataReader, like for instance your DataReader executes a cmdType
>
> >that is a
> >stored proc, well in that stored proc instead of just returning the
> >recordset that your select statements were wanting, return 2
>recordsets,
> >the
> >first one being a one column single recordset with the count and the
> >second
> >being the actual recordset that your select statement needs to be
> >returned.
> >You then get to this recordset by using DataReader.NextResult().
> >
> >In the stored proc:
> >
> >SELECT Count(*) RecordCount FROM Master
> >SELECT * FROM Master
> >
> >Then in your code you will need to do:
> >...
> >... //We'll assume that you already executed this proc and got back a
> >... //DataReader. ...
> >
> >DataReader.Read();
> >int recordCount = (int)DataReader["RecordCount"];
> >
> >if(recordCount>0){
> > DataReader.NextResult(); //This returns a bool, but I won't
> >capture.
> >
> > while(DataReader.Read()){
> > ... //Business Logic.
> > }
> >}
> >
> >Hope this helps,
> >Al
> >
> > >From: "Hugh McLaughlin" <hugh@k...>
> > >Reply-To: "ASP+" <aspx@p...>
> > >To: "ASP+" <aspx@p...>
> > >Subject: [aspx] Record Count
> > >Date: Sat, 8 Dec 2001 13:43:24
> > >
> > >Hello everyone and thanks for your help in advance. I am using a SQL
>
> > >Server datareader to create a datagrid. I am trying to get a record
> > >count that is returned from the SQL command. I tried:
> > >
> > >strRecordCount=myReader.RecordsAffected
> > >
> > >but this always returns -1 (not sure why) regardless of record count.
>
> > >How do I get the number of records returned. As a related question,
> > >I want to return the record count to test if it is =0 and turn off
> > >the datagrid display if there are no records. Is there a more
> > >efficient way of doing this. Thanks.
> > >---
> > >Change your mail options at http://p2p.wrox.com/manager.asp or to
> > >unsubscribe send a blank email to $subst('Email.Unsub').
> >
> >
> >_________________________________________________________________
> >Get your FREE download of MSN Explorer at
> >http://explorer.msn.com/intl.asp
> >
> >
> >---
> >Change your mail options at http://p2p.wrox.com/manager.asp or to
> >unsubscribe send a blank email to $subst('Email.Unsub').
> >
> >
> >---
> >Change your mail options at http://p2p.wrox.com/manager.asp or to
> >unsubscribe send a blank email to $subst('Email.Unsub').
>
>
>_________________________________________________________________
>Get your FREE download of MSN Explorer at
>http://explorer.msn.com/intl.asp
>
>
>
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
Message #7 by "Hugh McLaughlin" <hugh@k...> on Sun, 9 Dec 2001 01:01:09
|
|
> The DataReader.RecordsAffected property can only be accessed when you
close
> it. Remember this is forward-only firehose approach in retrieving data,
so
> this field will only show you a count if your cmdType did an Change,
Insert,
> or Delete and a 0 for "no records affected". If it was a select
statement
> then you will always get a -1 no matter when you access it. If you want
a
> recordcount then you'll want to use an adapter that then fills a DataSet
> exposing a DataTable which has a Rows property retrieving a
> DataRowCollection. The DataRowCollection is derived from
> InternalDataCollectionBase. InternalDataCollectionBase has a Count
property
> that exposes this functionality. So if you are using VS.NET you can see
> this through intellisense by typing the following in any class:
> ...
> (new DataTable()).Rows.Count;
> ...
>
> For practically you would achieve this by doing the following,
> I'll use the SqlClient namespace but you can use OleDbDataAdapter to do
the
> same...
> ...
> DataSet dbDataSet = new DataSet();
> SqlConnection dbConn = new SqlConnection(connectionString);
> SqlDataAdapter dbAdapter = new SqlDataAdapter("SELECT * FROM
> Master",dbConn);
> dbAdapter.Fill(dbDataSet);
>
> int recordCount = dbDataSet.Tables[0].Rows.Count
> ...
>
> Remember a DataSet can hold many tables so I used "0" as the indexer due
to
> my select statement only returning 1 and positioning itself "0" by
default.
>
> But you could surely get around all of this and still get a RecordCount
from
> a DataReader, like for instance your DataReader executes a cmdType that
is a
> stored proc, well in that stored proc instead of just returning the
> recordset that your select statements were wanting, return 2 recordsets,
the
> first one being a one column single recordset with the count and the
second
> being the actual recordset that your select statement needs to be
returned.
> You then get to this recordset by using DataReader.NextResult().
>
> In the stored proc:
>
> SELECT Count(*) RecordCount FROM Master
> SELECT * FROM Master
>
> Then in your code you will need to do:
> ...
> ... //We'll assume that you already executed this proc and got back a
> ... //DataReader.
> ...
>
> DataReader.Read();
> int recordCount = (int)DataReader["RecordCount"];
>
> if(recordCount>0){
> DataReader.NextResult(); //This returns a bool, but I won't capture.
>
> while(DataReader.Read()){
> ... //Business Logic.
> }
> }
>
> Hope this helps,
> Al
>
> >From: "Hugh McLaughlin" <hugh@k...>
> >Reply-To: "ASP+" <aspx@p...>
> >To: "ASP+" <aspx@p...>
> >Subject: [aspx] Record Count
> >Date: Sat, 8 Dec 2001 13:43:24
> >
> >Hello everyone and thanks for your help in advance. I am using a SQL
> >Server datareader to create a datagrid. I am trying to get a record
count
> >that is returned from the SQL command. I tried:
> >
> >strRecordCount=myReader.RecordsAffected
> >
> >but this always returns -1 (not sure why) regardless of record count.
How
> >do I get the number of records returned. As a related question, I want
to
> >return the record count to test if it is =0 and turn off the datagrid
> >display if there are no records. Is there a more efficient way of doing
> >this. Thanks.
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp
>
Thanks for your response. I understand now why the datareader doesn't
work, but I do not understand and cannot get to work the final statement:
int recordCount = dbDataSet.Tables[0].Rows.Count
This gives me a compiler error:
BC30800: Argument lists in all call statements must now be enclosed in
parentheses
Your help is greatly appreciated. Thanks.
|
|
|
View Cart
