number of records in Datareader - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

number of records in Datareader

pavlospapavlospa Posts: 7Member
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

Comments

  • iwilld0itiwilld0it Posts: 1,134Member
    : 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.
Sign In or Register to comment.