Howdy, Stranger!

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

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.