During application development sometime we fetch data from the database using DataReader. Later to do some other operations we required that DataReader data in a DataSet. In this case the below function can help you. Here I created the function which will accept a DataReader & will return DataSet for you.
The Logic I implemented here is very simple. After receiving DataReader as a parameter to the function I am storing its schema to a DataTable. Then using a for loop I am adding row & columns definition to the DataTable. After define the row & column adding DataTable to DataSet using dataSet.Tables.Add method. Now to fill data in DataTable executing a while loop until reader.Read(). Creating an instance for new row & using a for loop adding DataRow dynamically. To store value from DataReader here I am using GetValue() method. Finally using dataTable.Rows.Add method adding all rows to the DataTable. Already before binding rows data, my DataTable is added to DataSet. Now by just using return dataSet I am getting DataSet with DataReader values.
While Creating this function create it as public shared function so that you can easily use this with your executeReader method like ds = convertDataReaderToDataSet(cmd.ExecuteReader()).
DataReader to DataSet Converter Function
Public Shared Function convertDataReaderToDataSet(ByVal reader As SqlDataReader) As DataSet
Dim dataSet As New DataSet()
Do
' Create a New Data Table to store DataReader data temporarily
Dim tblSchema As DataTable = reader.GetSchemaTable()
Dim dataTable As New DataTable()
If tblSchema IsNot Nothing Then
For i As Integer = 0 To tblSchema.Rows.Count - 1
Dim dataRow As DataRow = tblSchema.Rows(i)
' Creating unique Column name to Data Table
Dim columnName As String = DirectCast(dataRow("ColumnName"), String)
Dim column As New DataColumn(columnName, DirectCast(dataRow("DataType"), Type))
dataTable.Columns.Add(column)
Next
dataSet.Tables.Add(dataTable)
' Fill the data table from Data Reader
While reader.Read()
Dim dataRow As DataRow = dataTable.NewRow()
For i As Integer = 0 To reader.FieldCount - 1
dataRow(i) = reader.GetValue(i)
Next
dataTable.Rows.Add(dataRow)
End While
Else
' No records to Return
Dim column As New DataColumn("AffectedRows")
dataTable.Columns.Add(column)
dataSet.Tables.Add(dataTable)
Dim dataRow As DataRow = dataTable.NewRow()
dataRow(0) = reader.RecordsAffected
dataTable.Rows.Add(dataRow)
End If
Loop While reader.NextResult()
Return dataSet
End Function
