Example of generic DataReader code (.net)

As my experience with .Net really began as working on existing in-house applications for a company, I feel I've picked up so many bad coding practices without realising it. One that i am desperately trying to move on from is that DataSets are used for everything. (I do get that strongly typed datasets have there place and they certainly have some uses...but not for the maj开发者_运维技巧ority of cases e.g. selecting data)

I'm building up a "helper" class for generic database work...I've got a method which returns a data table (for selects etc.) and I guess by default (and most examples in books/online) would use the DataAdapter's Fill method but certainly as a performance gain, want to replace this with a data reader that reads all the rows and then closes...which I guess is how the Fill method works underneath...but I'd prefer not to simply go for the lazy way if performance on large result sets is potentially going to impact.

Anyway, I can't for the life of me find an example of a dataReader being used to generically fill a datatable...I'm sure there would be both good and bad examples and therefore an agreed best practice on how to perform such a task. A link (or even a post) to such code would be brilliant! I'm mostly VB.Net but c# is no obstacle.

Note: Sorry if this sounds lazy also, I just figured this sort of example would be posted everywhere...no need to re-invent the wheel etc. thanks!

---

The reason you can't find an example of a DataReader being used to generically fill a DataTable is because you can do the same thing with the Fill() method in the DataSet, so you would just be reinventing the wheel.

You're not going to find a performance benefit by populating the DataTable directly with a DataReader.

---

If I had to guess, I don't think there will be any performance benefit of using a sqldatareader to fill a datatable compared to using a sqldataadapter with the Fill() method.

The only way to truly verify that theory would be to write your own implementation and compare. But you can also look at the code that Fill() is executing to see what it's doing exactly. I'd suggest downloading Reflector to take a look at the code.

I just did this myself. Here's what is eventually called after you make a call to Fill():

Protected Overridable Function Fill(ByVal dataTables As DataTable(), ByVal dataReader As IDataReader, ByVal startRecord As Integer, ByVal maxRecords As Integer) As Integer
    Dim num3 As Integer
    Dim ptr As IntPtr
    Bid.ScopeEnter(ptr, "<comm.DataAdapter.Fill|API> %d#, dataTables[], dataReader, startRecord, maxRecords" & ChrW(10), Me.ObjectID)
    Try 
        ADP.CheckArgumentLength(dataTables, "tables")
        If (((dataTables Is Nothing) OrElse (dataTables.Length = 0)) OrElse (dataTables(0) Is Nothing)) Then
            Throw ADP.FillRequires("dataTable")
        End If
        If (dataReader Is Nothing) Then
            Throw ADP.FillRequires("dataReader")
        End If
        If ((1 < dataTables.Length) AndAlso ((startRecord <> 0) OrElse (maxRecords <> 0))) Then
            Throw ADP.NotSupported
        End If
        Dim num2 As Integer = 0
        Dim enforceConstraints As Boolean = False
        Dim dataSet As DataSet = dataTables(0).DataSet
        Try 
            If (Not dataSet Is Nothing) Then
                enforceConstraints = dataSet.EnforceConstraints
                dataSet.EnforceConstraints = False
            End If
            Dim i As Integer
            For i = 0 To dataTables.Length - 1
                If dataReader.IsClosed Then
                    goto Label_00DE
                End If
                Dim container As DataReaderContainer = DataReaderContainer.Create(dataReader, Me.ReturnProviderSpecificTypes)
                If (container.FieldCount > 0) Then
                    If ((0 < i) AndAlso Not Me.FillNextResult(container)) Then
                        goto Label_00DE
                    End If
                    Dim num4 As Integer = Me.FillFromReader(Nothing, dataTables(i), Nothing, container, startRecord, maxRecords, Nothing, Nothing)
                    If (i = 0) Then
                        num2 = num4
                    End If
                End If
            Next i
        Catch exception1 As ConstraintException
            enforceConstraints = False
            Throw
        Finally
            If enforceConstraints Then
                dataSet.EnforceConstraints = True
            End If
        End Try
    Label_00DE:
        num3 = num2
    Finally
        Bid.ScopeLeave((ptr))
    End Try
    Return num3
End Function

You'll notice that this then makes a call to FillFromReader():

Friend Function FillFromReader(ByVal dataset As DataSet, ByVal datatable As DataTable, ByVal srcTable As String, ByVal dataReader As DataReaderContainer, ByVal startRecord As Integer, ByVal maxRecords As Integer, ByVal parentChapterColumn As DataColumn, ByVal parentChapterValue As Object) As Integer
    Dim num2 As Integer = 0
    Dim schemaCount As Integer = 0
    Do
        If (0 < dataReader.FieldCount) Then
            Dim mapping As SchemaMapping = Me.FillMapping(dataset, datatable, srcTable, dataReader, schemaCount, parentChapterColumn, parentChapterValue)
            schemaCount += 1
            If (((Not mapping Is Nothing) AndAlso (Not mapping.DataValues Is Nothing)) AndAlso (Not mapping.DataTable Is Nothing)) Then
                mapping.DataTable.BeginLoadData
                Try 
                    If ((1 = schemaCount) AndAlso ((0 < startRecord) OrElse (0 < maxRecords))) Then
                        num2 = Me.FillLoadDataRowChunk(mapping, startRecord, maxRecords)
                    Else
                        Dim num3 As Integer = Me.FillLoadDataRow(mapping)
                        If (1 = schemaCount) Then
                            num2 = num3
                        End If
                    End If
                Finally
                    mapping.DataTable.EndLoadData
                End Try
                If (Not datatable Is Nothing) Then
                    Return num2
                End If
            End If
        End If
    Loop While Me.FillNextResult(dataReader)
    Return num2
End Function

After looking at all of this I may have to change my mind. There may indeed be a measurable improvement to writing your own implementation for all of this. There is more logic in these functions than I expected, as well as some function call overhead when calling FillNextResult() over and over.

---

Here is an example of a dataReader being used to generically fill a datatable. You can simply use the Load method of the DataTable and pass it the DataReader:

DataTable dt= new DataTable();
dt.Load(cmd.ExecuteReader()); //cmd being declared as SqlCommand