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

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,134
    : 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.