Follow @RoyOsherove on Twitter

Convert a DataReader into a DataSet

Stefano describes a solution they use in a webservice, needing to convert a data reader to a dataset:


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))




While reader.Read()

Dim dataRow As DataRow = dataTable.NewRow()

For intCounter = 0 To reader.FieldCount - 1

dataRow(intCounter) = reader.GetValue(intCounter)



End While

Return dataSet

End Function


While I can see the benefit in this, it seems as though this can create very high perf issues. GetSchemaTable is another round trip to the database and its a costly one. Create a DataTable on the file is also kinda costly. A better solution might be to incorporate a sort of caching mechanism. Remember SqlHelper from the data application block?  It uses a caching mechanism for sproc parameter arrays so that sproc params are "discovered" only once and saved for later use. Same thing could be done here. Caching an array of columns should be done using some sort of key, but, what key can be used here? the reader has no "commandText" property that can be used. What do you think?

Should I get a laptop or a tablet?

The humble programmer