Page 1 of 1
Convert AdoRecordSet to DataTable
Posted: Thu Mar 28, 2024 9:47 pm
by 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
Re: Convert AdoRecordSet to DataTable
Posted: Fri Mar 29, 2024 8:05 am
by 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
Re: Convert AdoRecordSet to DataTable
Posted: Thu Oct 17, 2024 3:28 pm
by 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.
Re: Convert AdoRecordSet to DataTable
Posted: Fri Oct 18, 2024 8:10 am
by robert
Kees,
For now, try this:
Code: Select all
foreach oField as AdoField in oRs:Fields:AsArray()
Robert