Here's is a quick way to load an SqlDataReader object data into the DataTable. All you have to do is to pass in the SqlDataReader and a DataTable object will return to the calling method.

Private Function GetDataTableFromSqlDataReader(ByVal dr As SqlDataReader) As DataTable

 

Dim dtSchema As DataTable = dr.GetSchemaTable()

Dim dt As New DataTable

 

Dim listCols As New ArrayList

 

If Not dtSchema Is Nothing Then

For Each drow As DataRow In dtSchema.Rows

Dim columnName As String = System.Convert.ToString(drow("ColumnName"))

Dim column As New DataColumn(columnName, DirectCast(drow("DataType"), Type))

column.Unique = CBool(drow("IsUnique"))

column.AllowDBNull = CBool(drow("AllowDBNull"))

column.AutoIncrement = CBool(drow("IsAutoIncrement"))

listCols.Add(column)

dt.Columns.Add(column)

Next

End If

 

While dr.Read()

Dim dataRow As DataRow = dt.NewRow()

For i As Integer = 0 To listCols.Count - 1

dataRow(DirectCast(listCols(i), DataColumn)) = dr(i)

Next

dt.Rows.Add(dataRow)

End While

 

Return dt

End Function

 

 

 

Leave a comment if you have a better way of doing this.

 
SEO Building Block