number of records in Datareader

pavlospa

I'm using an oledbcommand to select some records from a database. Then I populate a datareader. All the records are retrived correctly. The problem that i have is that i can't find the number of the returned records (making a loop counting them isn't the best solution). Is there any why to get the returned number of records (like the recordcount property in ADO ?)

Thanks for your help

iwilld0it

: I'm using an oledbcommand to select some records from a database. Then I populate a datareader. All the records are retrived correctly. The problem that i have is that i can't find the number of the returned records (making a loop counting them isn't the best solution). Is there any why to get the returned number of records (like the recordcount property in ADO ?)
:
: Thanks for your help
:

Unfortunately ADO.NET does not have a record count property for a data reader. However, I have 3 distinct techniques below that are more scalable than looping through all the results to generate a count.

Technique 1 - Works well if you do not need to scroll through any records

Dim sql As String = "SELECT COUNT(*) FROM CartItems"

Dim objCmd As New OleDbCommand(sql, _
New OleDbConnection("connection")

objCmd.Connection.Open()
Dim intCount As Integer = CInt(objCmd.ExecuteScalar)
objCmd.Connection.Close()

ExecuteScalar is optimized to return the first column value of the first row in a data result.

Technique 2 - Works if you need a count in advance and still need to scroll through records

Dim sql As String = "SELECT COUNT(*) FROM CartItems;"
sql &= "SELECT * FROM CartItems"

Dim objCmd As New OleDbCommand(sql, _
New OleDbConnection("connection"))
objCmd.Connection.Open()

Dim objDr As OleDbDataReader = objCmd.ExecuteReader()
Dim intCount As Integer

If objDr.Read Then
intCount = objDr.GetInt32(0)
End If

If objDr.NextResult Then

Do While objDr.Read
' Read data per row
Loop

End If

objDr.Close()
objCmd.Connection.Close()

This example uses a batch query separated by a semi-colon. The use of the NextResult boolean property advances to the next query result if it returns true.

Technique 3 - uses a stored procedure to do the same as technique 2

Below is the stored procedure ...

CREATE PROC StoredProcedureName
(
@Count int OUTPUT
)
AS

SET @Count = (SELECT COUNT(*) FROM CartItems)
SELECT * FROM CartItems

Below is the code I used to access the stored procedure ...

Dim objCmd As New OleDbCommand("StoredProcedureName", _
New OleDbConnection("connection"))
objCmd.CommandType = CommandType.StoredProcedure

objCmd.Parameters.Add(New OleDbParameter("@Count", OleDbType.Integer))
objCmd.Parameters("@Count").Direction = ParameterDirection.Output

objCmd.Connection.Open()
Dim objDr As OleDbDataReader = objCmd.ExecuteReader()

Do While objDr.Read
' Read data per row
Loop

objDr.Close()
objCmd.Connection.Close()

' Get Record Count
Dim intCount As Integer = CInt(objCmd.Parameters("@Count").Value)

Hopefully this helps your programming situation. The last thing you can do is bind the data reader to a control and query the controls item collection count.