correct way to get count of recs in a sqlsrv tbl?

[Steve]

Hello,

I have a table in a sql server db that contains thousands
of records. So, I don't want to fill a dataset with
thousands of records. But I would like to get the count
of records in the table. One way I can think of would be
to use an ADO recordset object and set that to a command
object with command.Text = "Select Count(*) From tbl1"

But I understand ADO.Net is (or has) phased/ing out the
recordset object. So what is the correct way to get the
count of records from the table in the sql server db?

Thanks,
Steve

 

[Steve]

I think I found it (vb.net)
Dim cmd as New SqlCommand
....
cmd.CommandText = "Select Count(*) From tbl1"
Dim i As Integer = (Integer)cmd.ExecuteScalar()

 

[William \(Bill\) Vaughn]

When you query the database for the number of rows in a table (which all
data access interfaces support one way or another), it's like going to the
barn to count the number of eggs so you can go back to the house to get
enough cartons. By the time you get back the rowcount is likely to be
different.
Let's assume that this is not an issue. Executing the SELECT Count(*) FROM
MyTable can be done in ADO.NET and a lot more quickly than in COM-based ADO.
You don't need (should not use) a Recordset in either case. The best
approach is to return an integer counter instead of a rowset. Recordsets
(rowsets) are expensive to build and transmit in any data access
interface--they should be avoided if possible. While the "Recordset" object
is not used in ADO.NET, you can still pass back rowsets, but the mechanisms
are a bit different.

hth

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