I can't remember, but is there a way to get number of records when 
using a data reader?

I'm using C# talking to an SQL 2k database using the SQLClient.

Cheers,

Julian Voelcker
The Virtual World (UK) Limited
Cirencester, United Kingdom

This may help!

SqlConnection connection =3D new SqlConnection
    ("server=3Dlocalhost;database=3Dweblogin;uid=3Dsa;pwd=3D");

try {
    connection.Open ();

    StringBuilder builder =3D new StringBuilder ();
    builder.Append ("select count (*) from users " +
        "where username =3D \'");
    builder.Append (username);
    builder.Append ("\' and cast (rtrim (password) as " +
        "varbinary) =3D cast (\'");
    builder.Append (password);
    builder.Append ("\' as varbinary)");

    SqlCommand command =3D new SqlCommand (builder.ToString (),
        connection);

    int count =3D (int) command.ExecuteScalar ();
    return (count > 0);
}
catch (SqlException) {
    return false;
}
finally {
    connection.Close ();
}



-----Original Message-----
From: Julian Voelcker [mailto:asp@t...]
Sent: Tuesday, December 10, 2002 6:43 PM
To: aspx_beginners
Subject: [aspx_beginners] Record Count using Data Readers


I can't remember, but is there a way to get number of records when
using a data reader?

I'm using C# talking to an SQL 2k database using the SQLClient.

Cheers,

Julian Voelcker
The Virtual World (UK) Limited
Cirencester, United Kingdom

Either Execute a SELECT COUNT(*) query (or SET @OutputParam = @@ROWCOUNT) in
your sproc, or increment a counter variable as your read in the data. The
DataReader itself does not know how many records you have since it doesn't
assemble this meta-data when you create it (which is one thing that makes
this object fast)

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Julian Voelcker" <asp@t...>
Subject: [aspx_beginners] Record Count using Data Readers


: I can't remember, but is there a way to get number of records when
: using a data reader?
:
: I'm using C# talking to an SQL 2k database using the SQLClient.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hi,

Thanks for that, but I don't need the count from the SQL, I need to 
know the count before handling the results of the reader query.

I need to do a query along the lines of, if there are records loop 
through them else do something else.

On Wed, 11 Dec 2002 08:28:37 +0800, Peter Zaps wrote:
> SqlConnection connection = new SqlConnection
>     ("server=localhost;database=weblogin;uid=sa;pwd=");
> 
> try {
>     connection.Open ();
> 
>     StringBuilder builder = new StringBuilder ();
>     builder.Append ("select count (*) from users " +
>         "where username = \'");
>     builder.Append (username);
>     builder.Append ("\' and cast (rtrim (password) as " +
>         "varbinary) = cast (\'");
>     builder.Append (password);
>     builder.Append ("\' as varbinary)");
> 
>     SqlCommand command = new SqlCommand (builder.ToString (),
>         connection);
> 
>     int count = (int) command.ExecuteScalar ();
>     return (count > 0);
> }
> catch (SqlException) {
>     return false;
> }
> finally {
>     connection.Close ();
> }
>


Cheers,

Julian Voelcker
The Virtual World (UK) Limited
Cirencester, United Kingdom

OK, thanks for that.

The problem I have is that I read the reader and then populate it with a 
query.

I then want to see if there are records and if there are loop through them, 
else do something else.

To date I have been using 'if(drReader.Read())' to test to see if there are 
records and 'while(drReader.Read())' to loop through the data.

The problem I have had is doing the test and then doing the loop through, 
because (from memory) the 'if(drReader.Read())' was moving me to the first 
record (if there were some) and then the 'while(drReader.Read())' was starting 
at the second record.

Anyway, I have been thinking about it the wrong way - it occurs to me that I 
can just start with the while loop, but put a counter in it and then testing 
to see if the counter has incremented, which will give me a the 'find data and 
process it or do something else' scenario that I want.



On Wed, 11 Dec 2002 12:01:41 +1100, Ken Schaefer wrote:
> Either Execute a SELECT COUNT(*) query (or SET @OutputParam = @@ROWCOUNT) in
> your sproc, or increment a counter variable as your read in the data. The
> DataReader itself does not know how many records you have since it doesn't
> assemble this meta-data when you create it (which is one thing that makes
> this object fast)
>


Cheers,

Julian Voelcker
The Virtual World (UK) Limited
Cirencester, United Kingdom

Yes, you can use a counter (I think I suggested that in another recent
thread), or you can keep your current code, and do:

If dr.Read() then

    ' Code Add the first element

    While dr.Read()

        ' Code to add subsequent elements (if any)

    Wend

Else

End If

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Julian Voelcker" <asp@t...>
Subject: [aspx_beginners] Re: Record Count using Data Readers


: OK, thanks for that.
:
: The problem I have is that I read the reader and then populate it with a
: query.
:
: I then want to see if there are records and if there are loop through
them,
: else do something else.
:
: To date I have been using 'if(drReader.Read())' to test to see if there
are
: records and 'while(drReader.Read())' to loop through the data.
:
: The problem I have had is doing the test and then doing the loop through,
: because (from memory) the 'if(drReader.Read())' was moving me to the first
: record (if there were some) and then the 'while(drReader.Read())' was
starting
: at the second record.
:
: Anyway, I have been thinking about it the wrong way - it occurs to me that
I
: can just start with the while loop, but put a counter in it and then
testing
: to see if the counter has incremented, which will give me a the 'find data
and
: process it or do something else' scenario that I want.
:
:
:
: On Wed, 11 Dec 2002 12:01:41 +1100, Ken Schaefer wrote:
: > Either Execute a SELECT COUNT(*) query (or SET @OutputParam 
@@ROWCOUNT) in
: > your sproc, or increment a counter variable as your read in the data.
The
: > DataReader itself does not know how many records you have since it
doesn't
: > assemble this meta-data when you create it (which is one thing that
makes
: > this object fast)
: >

Hi Ken,

Thanks for that.

The problem with your suggestion is that if the code for processing the 
data is quite complex it becomes a pain to maintain.

On Fri, 13 Dec 2002 12:46:40 +1100, Ken Schaefer wrote:
> If dr.Read() then
> 
>     ' Code Add the first element
> 
>     While dr.Read()
> 
>         ' Code to add subsequent elements (if any)
> 
>     Wend
> 
> Else
> 
> End If
> 
> Cheers
> Ken
>


Cheers,

Julian Voelcker
The Virtual World (UK) Limited
Cirencester, United Kingdom

Create your own class to do the processing if it's that complex. As I can
see it, you're only adding a single step...

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Julian Voelcker" <asp@t...>
Subject: [aspx_beginners] Re: Record Count using Data Readers


: Hi Ken,
:
: Thanks for that.
:
: The problem with your suggestion is that if the code for processing the
: data is quite complex it becomes a pain to maintain.
:
: On Fri, 13 Dec 2002 12:46:40 +1100, Ken Schaefer wrote:
: > If dr.Read() then
: >
: >     ' Code Add the first element
: >
: >     While dr.Read()
: >
: >         ' Code to add subsequent elements (if any)
: >
: >     Wend
: >
: > Else
: >
: > End If
: >
: > Cheers
: > Ken
: >
:
:
: Cheers,
:
: Julian Voelcker
: The Virtual World (UK) Limited
: Cirencester, United Kingdom
:
:
: