Retrieving actual rowcount from SQLDataAdapter when using StartRec

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anybody know how to get the actual number or rows returned when using
the StartRecord and MaxRecords parameters with the SQLDataAdapter.Fill()?

Since the whole resultset is returned from SQL Server it would be nice to
access this information. And appending SELECT @@ROWCOUNT to the original
query doesn't seem to work since Fill() doesn't seem to support multiple
tables when using StartRecord/MaxRecords.

I know I could restrict the number of rows returned using SELECT TOP, but
the actual number of rows is requested information. Also, I'd like to avoid
using a separate SELECT COUNT(*), since the query is dynamically generated
and might be fairly heavy.

Btw, this is using VS 2005 beta 2 with SQL Server 2000.

Grateful for any ideas!

Arne
 
Once Fill completes, the number of rows fetched is reflected in the
Tables.Rows.Count property. Each rowset is saved to a separte table--each
with its own count. If you execute Fill again (with a different
StartRecord), ADO.NET simply skips over the rows before the starting point
and concatenates the new rows to the end of the DataTable. No, ADO.NET is
not executing any fancy TOP or ROWCOUNT commands behind the scenes. It
executes the exact same query each time. I expect that if you don't use a
TOP or WHERE clause to limit the rows returned--even though you use MaxRows,
all of the rows are fetched whether you use them or not. When you close a
DataReader (and that's what Fill uses behind the scenes), ADO.NET simply
walks through the remaining rows in the resultset and throws them away. This
permits it to step to the next resultset if any...
Here are the commands executed against SQL Server with two Fill calls (with
start and max rows arguments set).

Dim ds As New DataSet

Dim intStart As Integer = 0

Dim intMax As Integer = 10

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

SqlDataAdapter1.Fill(ds, intStart, intMax, "Customers")

Debug.WriteLine(ds.Tables(0).Rows.Count)

SqlDataAdapter1.Fill(ds, intStart + 10, intMax, "Customers")

End Sub




SQL:BatchCompleted SELECT CustID, TID, CustName, Discount, Photo, Notes,
DateAdded, TimeStamp FROM Customers .Net SqlClient Data Provider billva
BETAVLOCAL\billva 110 546 0 186 0 55 2005-06-15 22:14:55.770

RPC:Completed exec sp_reset_connection .Net SqlClient Data Provider billva
BETAVLOCAL\billva 0 0 0 0 0 55 2005-06-15 22:15:23.770

SQL:BatchCompleted SELECT CustID, TID, CustName, Discount, Photo, Notes,
DateAdded, TimeStamp FROM Customers .Net SqlClient Data Provider billva
BETAVLOCAL\billva 94 546 0 123 0 55 2005-06-15 22:15:23.770


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top