Application Block - Data - SQL Count Function 1

[colttaylor]

I'm using Microsoft's data-access application block and I would like a faster (more resource friendly) method of getting the count of active records in a table.
Here is a rough sketch of what I am currently doing...

Public Function get_company_count() As Integer
get_company_count = 0
Dim cmd As String = "count from tbl_companies where cmb_status = A"
Dim loc_companies As DataSet = SqlHelper.ExecuteDataset(Settings.SQLConnectStrUser, System.Data.CommandType.Text, cmd)
get_company_count = companies.Tables("tbl_companies").Rows.Count
End Function

What I would like to do is use Microsoft SQL's COUNT function, i.e. SELECT COUNT(CMP_ID)... but I don't know how to get just an integer value out of the sqlhelper class. Anybody have any ideas?

thanks in advance!
Colt

If it's stupid but it works, it isn't stupid

 

[colttaylor]

Oops,
There is a typo in my get_company_count source above.
The second line should be...

Dim cmd as string = "select cmp_id from tbl_compaines where cmb_status = A"

Sorry for the confusion.
Thanks again,
Colt

If it's stupid but it works, it isn't stupid

 

[PankajBanga]

You can use DataTable.Compute Method. It Computes the given expression on the current rows that pass the filter criteria.

Dim Count As Integer

Count = DataSet.Tables("TableName").Compute(("COUNT(ColumnName)", ""))

http://home.iprimus.com.au/pankajbanga

 

[colttaylor]

THANKS FOR THE QUICK AND DETAILED RESPONSE!


If it's stupid but it works, it isn't stupid