SqlDataReader to DataTable

Tuesday, March 21, 2006 @ 3:20 pm by -DS-

It’s not a good practice to return a SqlDataReader from a function, but, I like to do that because it’s easier to work with DataReaders than DataTables. Why is bad to return Data Readers? Because the object keeps a connection to the database open until the object gets destroyed or you close it manually.

Why I like to work with Data Readers?

Because the code looks more “readable” and the Data Reader acts more like a Recordset from the old ADO, but since the connection is always open while using a Data Reader it becomes a real pain in the ass if you need to open another connection to the database because at some point you could eat the entire connection pool. To avoid that I created a function that puts a SqlDataReader into a DataTable, and then another small function to convert the DataTable into a DataView. I could do it all at once, connect to the database and the function returns a DataView with all the information I want, BUT, I want my programs to be easy readable for when someone needs to maintain the code. I usually create a Class that contains all the Database functions I need so I don’t have to deal with Database Connection inside the core of the application. So, in order to have all the options I want from inside the class that has all the Database functions I created these two functions, one that converts into a DataTable a SqlDataReader and one that converts a DataTable into a DataView.

PLAIN TEXT
VB.NET:
  1. Public Function DataReaderToDataTable(ByVal rdrReader As SqlDataReader) As DataTable
  2. 'This function receives a Data Reader and returns a Data Table
  3.  
  4. Try
  5.  
  6. 'Table Schema
  7. Dim schemaTable As DataTable = rdrReader.GetSchemaTable()
  8.  
  9. 'Data Table
  10. Dim dataTable As DataTable = New DataTable
  11. Dim intCounter As Integer
  12. 'Now to create the Schema on the DataTable
  13. For intCounter = 0 To schemaTable.Rows.Count - 1
  14. 'Current Row
  15. Dim dataRow As DataRow = schemaTable.Rows(intCounter)
  16. 'Current Column Name
  17. Dim columnName As String = CType(dataRow("ColumnName"), String)
  18. 'Current Column
  19. Dim column As New DataColumn(columnName, _
  20. CType(dataRow("DataType"), Type))
  21. 'Add Column to the DataTable
  22. dataTable.Columns.Add(column)
  23. Next intCounter
  24. 'Now to fill the table with the reader
  25. While rdrReader.Read()
  26. 'New Row
  27. Dim dataRow As DataRow = dataTable.NewRow()
  28. 'Loop the fields
  29. For intCounter = 0 To rdrReader.FieldCount - 1
  30. 'Insert the current value of the DataReader to the DataRow
  31. dataRow(intCounter) = rdrReader.GetValue(intCounter)
  32. Next
  33.  
  34. 'Insert the Row into the DataTable
  35. dataTable.Rows.Add(dataRow)
  36. End While
  37.  
  38. 'Close the reader
  39. rdrReader.Close()
  40. 'Return the DataTable
  41. Return dataTable
  42.  
  43. Catch ex As Exception
  44. MsgBox(ex.Message)
  45. End Try
  46. End Function
  47.  
  48. Public Function DataTableToDataView(ByVal DTable As DataTable) As DataView
  49. 'This only works for a SINGLE table
  50. Dim DV As DataView
  51. DV = DTable.DefaultView
  52. Return DV
  53. End Function

Then I create another function that returns the information I want, it goes something like this:

PLAIN TEXT
VB.NET:
  1. Public Function GetNewSignatures() As DataView
  2.  
  3. Dim DV As DataView
  4. V = DataTableToDataView(DataReaderToDataTable(GetNewSignaturesFromTable))
  5. Return DV
  6.  
  7. End Function

So when I need the list of new signatures (in this case) all I do is

PLAIN TEXT
VB.NET:
  1. Dim DV As DataView = DF.GetNewSignatures

I know, I know, the code could be faster if I just returned the DataView from GetNewSignaturesFromTable, but I like to have options when working with Tables, sometimes I use the same function for different purposes and those different purposes mean that sometimes I will use a DataTable and sometimes a DataReader, so I just return a reader, which is the common denominator, and deal with the rest with those functions.

Posted in English Writings, Visual Basic.NET |