Getting Number Of Rows returned from query

Discussion in 'ASP .Net' started by Chris Tremblay, Sep 9, 2004.

  1. I am trying to figure out how to go about retrieving the number of results
    returned from my queries in SQL server from VB.NET without using a the
    Select Count(*) query. The method that I was using was the following:

    Take the query that I am executing, copy the query and turn it into a count
    query, run the count query, then execute the original query.

    The reason for this is so that I can implememt public paging on my website.
    The problem with this method is that it is putting to much stress on my SQL
    Server. Is there a way that I can find the number of rows returned. The way
    I figure, if you use an SQLdatareader, it knows when it has reached the end
    of the result list, therefore, you should be able to have some access to the
    number of results without reading through each record.

    --

    Thanks,
    Chris Tremblay
    www.mtgfanatic.com, Inc.
     
    Chris Tremblay, Sep 9, 2004
    #1
    1. Advertising

  2. Chris Tremblay

    Karl Guest

    Chris,
    You won't be able to do it via the SQLDataReader without reading through the
    records first (in which case you can simply increment a counter, or use the
    solution I briefly outline in #2)

    1 - Consider using a datatable. You'll easily be able to access the number
    of rows via datatable.Rows.Count. Additionally, if your SQL Server is
    struggling, you'll be able to cache the datatable and reduce the load (I
    realize that you are probably doing a search in which case the results
    aren't very cache friendly, but hey , I thought I'd throw it out there
    anyways).

    2 - You can access an output parameter after doing a dr.close, which could
    be the @@RowCount SQL server automatically generates. I'd go into more
    details, but since you need to have the datareader closed, I'll assume that
    you'll have already read through it and could simply have used a
    counter...so this probably is a no go.

    Karl

    --
    MY ASP.Net tutorials
    http://www.openmymind.net/


    "Chris Tremblay" <> wrote in message
    news:...
    > I am trying to figure out how to go about retrieving the number of results
    > returned from my queries in SQL server from VB.NET without using a the
    > Select Count(*) query. The method that I was using was the following:
    >
    > Take the query that I am executing, copy the query and turn it into a

    count
    > query, run the count query, then execute the original query.
    >
    > The reason for this is so that I can implememt public paging on my

    website.
    > The problem with this method is that it is putting to much stress on my

    SQL
    > Server. Is there a way that I can find the number of rows returned. The

    way
    > I figure, if you use an SQLdatareader, it knows when it has reached the

    end
    > of the result list, therefore, you should be able to have some access to

    the
    > number of results without reading through each record.
    >
    > --
    >
    > Thanks,
    > Chris Tremblay
    > www.mtgfanatic.com, Inc.
    >
    >
     
    Karl, Sep 9, 2004
    #2
    1. Advertising

  3. You could use a counter variable like so...

    SQLDataReader dr = new SQLDataReader();
    int counter = 0;
    // other database code goes here
    while ( dr.Read() )
    {
    counter +=;
    }
    Response.Write ( "Number of records is: " + counter );

    Hope this helps

    "Chris Tremblay" wrote:

    > I am trying to figure out how to go about retrieving the number of results
    > returned from my queries in SQL server from VB.NET without using a the
    > Select Count(*) query. The method that I was using was the following:
    >
    > Take the query that I am executing, copy the query and turn it into a count
    > query, run the count query, then execute the original query.
    >
    > The reason for this is so that I can implememt public paging on my website.
    > The problem with this method is that it is putting to much stress on my SQL
    > Server. Is there a way that I can find the number of rows returned. The way
    > I figure, if you use an SQLdatareader, it knows when it has reached the end
    > of the result list, therefore, you should be able to have some access to the
    > number of results without reading through each record.
    >
    > --
    >
    > Thanks,
    > Chris Tremblay
    > www.mtgfanatic.com, Inc.
    >
    >
    >
     
    =?Utf-8?B?LS1kd2Vlemls?=, Sep 9, 2004
    #3
  4. Chris Tremblay

    Greg Burns Guest

    Unless I missing something this won't work.

    If he is doing paging, he is probably only returning in the datareader a
    subset (1 page) of the total records.

    Greg


    "--dweezil" <> wrote in message
    news:...
    > You could use a counter variable like so...
    >
    > SQLDataReader dr = new SQLDataReader();
    > int counter = 0;
    > // other database code goes here
    > while ( dr.Read() )
    > {
    > counter +=;
    > }
    > Response.Write ( "Number of records is: " + counter );
    >
    > Hope this helps
    >
    > "Chris Tremblay" wrote:
    >
    >> I am trying to figure out how to go about retrieving the number of
    >> results
    >> returned from my queries in SQL server from VB.NET without using a the
    >> Select Count(*) query. The method that I was using was the following:
    >>
    >> Take the query that I am executing, copy the query and turn it into a
    >> count
    >> query, run the count query, then execute the original query.
    >>
    >> The reason for this is so that I can implememt public paging on my
    >> website.
    >> The problem with this method is that it is putting to much stress on my
    >> SQL
    >> Server. Is there a way that I can find the number of rows returned. The
    >> way
    >> I figure, if you use an SQLdatareader, it knows when it has reached the
    >> end
    >> of the result list, therefore, you should be able to have some access to
    >> the
    >> number of results without reading through each record.
    >>
    >> --
    >>
    >> Thanks,
    >> Chris Tremblay
    >> www.mtgfanatic.com, Inc.
    >>
    >>
    >>
     
    Greg Burns, Sep 9, 2004
    #4
  5. After you run the query, run the statement SELECT @@ROWCOUNT

    --
    data mining and .net team
    http://www.visual-basic-data-mining.net/forum


    "Chris Tremblay" <> wrote in message
    news:...
    > I am trying to figure out how to go about retrieving the number of results
    > returned from my queries in SQL server from VB.NET without using a the
    > Select Count(*) query. The method that I was using was the following:
    >
    > Take the query that I am executing, copy the query and turn it into a

    count
    > query, run the count query, then execute the original query.
    >
    > The reason for this is so that I can implememt public paging on my

    website.
    > The problem with this method is that it is putting to much stress on my

    SQL
    > Server. Is there a way that I can find the number of rows returned. The

    way
    > I figure, if you use an SQLdatareader, it knows when it has reached the

    end
    > of the result list, therefore, you should be able to have some access to

    the
    > number of results without reading through each record.
    >
    > --
    >
    > Thanks,
    > Chris Tremblay
    > www.mtgfanatic.com, Inc.
    >
    >
     
    http://www.visual-basic-data-mining.net/forum, Sep 9, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Pikarde
    Replies:
    2
    Views:
    1,054
    Danny Bloodworth
    Nov 24, 2003
  2. John A Grandy

    2.0 ObjectDataSource number rows returned

    John A Grandy, Mar 16, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    549
    John A Grandy
    Mar 16, 2006
  3. John
    Replies:
    2
    Views:
    2,992
  4. middletree
    Replies:
    8
    Views:
    147
    dlbjr
    Nov 28, 2003
  5. Oystein@Norway

    Change the returned rows in a gridview

    Oystein@Norway, Oct 24, 2006, in forum: ASP General
    Replies:
    2
    Views:
    163
    Oystein@Norway
    Oct 24, 2006
Loading...

Share This Page