SqlDataReader to DataSet Converter VB.NET Function

SqlDataReader to DataSet Converter VB.NET Function

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