Are you still using Framework 1 or 1.1? Are you using DNN 3? Did you know there is a function to convert a data reader object into a dataset built into DNN 3? Neither did I, so I wrote one. And then I found one Dan Thayer of Logical Advantage had sent me in an email a few months ago. Dan uses reflection, so his is pretty slick. If you want to see Dan's solution, let me know. I'm sure he would be happy to lend his permission for me to publish it.
Anyway, it was right after I discovered the second preexisting solution to the same issue that I decided to install the VB Snippet Editor (http://msdn.microsoft.com/vbasic/downloads/tools/default.aspx) so maybe I can reduce the number of times I re-invent the wheel.
If you are lucky enough to already be doing production work in Framework 2, all this should be unnecessary. I’ve seen a load function in the dataset (or data table?) in 2.0 that uses the data reader as input.
When we implemented this function, we coded a couple of overloads making the name parameters optional. Yes, I know we can use the optional attribute directly on the parameters, but that doesn’t play well with some of the less sophisticated CLR languages. Look it over. Add it to your Snippets if you like it. Oh and yes, the VB Snippet Editor requires Framework 2 to run, but it doesn’t mind if you save J#, C# or Framework 1.x code. The Snippet Editor is definitely a handy tool.
Bill J
''' -----------------------------------------------------------------------------
''' ''' Uses the dataRdr schema to create a DataTable. Populates the new DataTable
''' from the dataRdr and loads it to a DataSet using the tableName provided.
'''
Public Shared Function ReaderToDataSet(ByVal dataRdr As IDataReader, \_
ByVal tableName As String, \_
ByVal datasetName As String) As DataSet
Dim myTable As New DataTable
Dim myDS As New DataSet
Dim myRow As DataRow
Try
SetTableSchemaHelper(myTable, dataRdr)
myTable.TableName \= tableName
myDS.Tables.Add(myTable)
Do While dataRdr.Read
myRow \= myTable.NewRow()
For i As Integer \= 0 To dataRdr.FieldCount \- 1
myRow(i) \= dataRdr.GetValue(i)
Next
myTable.Rows.Add(myRow)
Loop
dataRdr.Close()
Return myDS
Catch ex As Exception
Throw New ApplicationException("ReaderToDataSet \- " & ex.Message)
End Try
End Function
''' -----------------------------------------------------------------------------
''' ''' Helper method to add columns to a table from a schema table loaded from
''' a DataReader object. Note the target table is accessed by reference.
''' ''' -----------------------------------------------------------------------------
Public Shared Sub SetTableSchemaHelper(ByRef dtTable As DataTable, \_
ByVal dataRdr As IDataReader)
Dim mySchema As DataTable
Dim myCol As DataColumn
mySchema \= dataRdr.GetSchemaTable
For i As Integer \= 0 To mySchema.Rows.Count \- 1
myCol \= New DataColumn
With mySchema.Rows(i)
myCol.ColumnName \= DirectCast(.Item("ColumnName"), String)
myCol.AllowDBNull \= DirectCast(.Item("AllowDBNull"), Boolean)
myCol.AutoIncrement \= DirectCast(.Item("IsIdentity"), Boolean)
myCol.Unique \= myCol.AutoIncrement
myCol.DataType \= DirectCast(.Item("DataType"), Type)
If myCol.DataType Is System.Type.GetType("String") Then
myCol.MaxLength \= DirectCast(.Item("ColumnSize"), Integer)
End If
myCol.ReadOnly \= DirectCast(.Item("IsReadOnly"), Boolean)
If myCol.Unique \= False Then
myCol.Unique \= (myCol.DataType Is System.Type.GetType("GUID"))
End If
End With
dtTable.Columns.Add(myCol)
Next i
End Sub