Stefano Demiliani WeBlog

Convert a DataReader to a DataSet

Many applications that retrieves data from a database use the Datareader to return results or bind to a datagrid due to performance gains.

Web Services however cannot return a Datareader... readers will need to be converted in order to return the results to the client of your service.

The conversion method to return a dataset to the client from our service can be implemented as follow:

Public Function ConvertDataReaderToDataSet(ByVal reader As SqlDataReader) As DataSet

Dim dataSet As DataSet = New DataSet()

Dim schemaTable As DataTable = reader.GetSchemaTable()

Dim dataTable As DataTable = New DataTable()

Dim intCounter As Integer

For intCounter = 0 To schemaTable.Rows.Count - 1

Dim dataRow As DataRow = schemaTable.Rows(intCounter)

Dim columnName As String = CType(dataRow("ColumnName"), String)

Dim column As DataColumn = New DataColumn(columnName, _

CType(dataRow("DataType"), Type))

Next

While reader.Read()

Dim dataRow As DataRow = dataTable.NewRow()

For intCounter = 0 To reader.FieldCount - 1

Next

End While

Return dataSet

End Function

What we do in this method is accept the reader that we want to convert to a dataset. We then declare a new dataset to store our values from the reader object and then structure our dataset with a table.

Once a table is created then go through the schema of the reader and create our data table structure based on the reader's structure by looping through the columns and adding them to our new table. Once the structure of the new table is defined we then iterate through the rows in the reader and add the data that is contained within the reader. Finally add the new table to the dataset and then return the newly created dataset.

This is my solution and i think it's good :)