A lot of sample code around counting the records in some selection uses tricks like creating a datatable with the selection and then using the .Count property, or creating a datareader and then looping the resulting set and counting the records.

Both techniques, while giving the correct results, will give you a needless load on the sql server and system - especialy with large recordsets that will either consume considerable memory chunks, or needlessly read hundres of thounsends of records.

The elegenat solution is to offload the counting to the SQL Server, and querying only the result of the count from a datareader. That way only the simple result number is transfered from the server to the workstation.

The code sample below shows a basic count from all records in a table. By changing the sql statement a more complex query can easily be implemented.

        ' --- Count the records in the selection

        Dim db_connection As SqlConnection
        Dim db_command As SqlCommand
        Dim datareader As SqlDataReader

        ' ---- Connection based on a stored connection string
        db_connection = New SqlConnection(My.Settings.MyConnectionString)
        ' --- Or something like this if you want to use a full connection string:
        ' Data Source=MYSERVER\INSTANCE;Initial Catalog=MyDatabasr;Persist Security Info=True;User ID=myuser;Password=mypassword;Connect Timeout=60

        db_connection.Open()
        ' --- The actual query statement to count
        db_command = New SqlCommand("SELECT COUNT (id) as totalcount FROM mytable", db_connection)
        datareader = db_command.ExecuteReader
        datareader.Read()
        MsgBox("Number of records: " & datareader("totalcount").ToString)
        datareader.Close()
        db_connection.Close()


Visitor Comments