How to convert a datareader to datatable

Question

I have a question about converting a datareader to a datatable. In my code, I have a datareader created in one class and passed to another class that I want to convert it to a datatable.

When I do this, it does not seem to work, as the table remains empty. If I do the conversion in the same function, it works fine.

Its only when I pass the datareader to another function that it stops working. Is this because the dr is closed or something? How do I overcome this problem? Any help would be great.

Solution 1

Check this out:

Public Function ExecuteQuery(ByVal s As String, ByVal condb As SqlConnection, ByVal ParamArray params() As SqlParameter) As DataTable
        Dim dt As DataTable = Nothing
        Using da As New System.Data.SqlClient.SqlDataAdapter(s, condb)               
            dt = New DataTable
            If params.Length > 0 Then
                da.SelectCommand.Parameters.AddRange(params)
            End If
            If da.SelectCommand.Connection.State <> ConnectionState.Open Then da.SelectCommand.Connection.Open()
            da.Fill(dt)                    
        End Using
        Return dt
End Function

Solution 2

Use DataTable Load() method.

 // Given a DataReader called "reader"
 DataTable dt = new DataTable();
 dt.Load(reader)

Comments

[balexandre]

You should never pass DataReader's as you will completely miss to dispose the objects or several times the data will not be there anymore. Can you edit the method that is in use, the one before returning the DataReader? So for example you can add a DataTable property to that method and populate it prior to the return so you can grab outside that class...

[jason]

no, i was instructed to only return a datareader. the logic being that the function that passes the datareader can be used elsewhere

[jason]

thanks for the response. is there anyway to do this without knowing the connection or parameters? The only thing that I'm passed is the datareader. thanks

[HndlCode]

ParamArray is optional so you dont need to pass any parameter :), please mark my anser as Right