Convert AdoRecordSet to DataTable

[alex_schmitt]

Hi Robert,

is there a straightforward way to convert an AdoRecordSet into a DataTable?

Background: I have implemented a function to export a data set to XLS in C#. Of course the types AdoRecordSet from VO2Ado and .Net DataTables are not compatible. any better best practice?

Thanks and best,
Alex

[robert]

Alex,
There is no built in way to do this.
You would have to create a DataTable and its columns and add rows for each of the rows in the recordset.
I would probably use GetRows() to get all rows in the RecordSet and then use the ItemArray property of the DataRow to set all field values in one assignment.
The X# runtime has a function _ArrayToObjectArray() that you can use to convert each row returned from GetRows() to an object[] that you can assign to the ItemArray.
The code would somewhat like this (no error handling)

Code: Select all

Function AdoRecordSetToDataTable(oRs as AdoRecordSet) as DataTable
var oDT := DataTable()
foreach oField as AdoField in oRs:Fields
     var oValue    := (OBJECT) oField:Value
     var oColumn := DataColumn{oField:Name, oValue:GetType()}
     oDT:Columns:Add(oColumn)
next
var aRows := oRs:GetRows()
foreach aRow as Array in aRows
     var oRow:= oDt:NewRow()
     oRow:ItemArray := _ArrayToObjectArray(aRow)
     oDt:Tows:Add(oRow)
next
return oDT

If you have a working version, please share that here.

Robert

[Kees Bouw]

Hi Robert,

In your example, on this line:

Code: Select all

foreach oField as AdoField in oRs:Fields

I get this error:

Error XS1579 foreach statement cannot operate on variables of type 'Xs2Ado.AdoFields' because 'Xs2Ado.AdoFields' does not contain a public instance or extension definition for 'GetEnumerator'

How do I fix that? oRS:Fields is of class Xs2Ado.AdoFields which is apparently a collection of Xs2Ado.AdoField (without the s) objects.

Kees.

[robert]

Kees,
For now, try this:

Code: Select all

foreach oField as AdoField in oRs:Fields:AsArray()

Robert