Convert AdoRecordSet to DataTable

This forum is the place to discuss issues related to ReportPro, Xs2Ado, Vo2Ado, bBrowser and other 3rd party products
alex_schmitt
Posts: 85
Joined: Wed Jan 23, 2019 7:54 pm
Location: Germany

Convert AdoRecordSet to DataTable

Post 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
User avatar
robert
Posts: 4555
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

Re: Convert AdoRecordSet to DataTable

Post 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
XSharp Development Team
The Netherlands
robert@xsharp.eu
User avatar
Kees Bouw
Posts: 124
Joined: Wed Nov 06, 2019 11:35 am
Location: Netherlands

Re: Convert AdoRecordSet to DataTable

Post 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.
User avatar
robert
Posts: 4555
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

Re: Convert AdoRecordSet to DataTable

Post by robert »

Kees,
For now, try this:

Code: Select all

foreach oField as AdoField in oRs:Fields:AsArray()
Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
User avatar
Kees Bouw
Posts: 124
Joined: Wed Nov 06, 2019 11:35 am
Location: Netherlands

Re: Convert AdoRecordSet to DataTable

Post by Kees Bouw »

Hi,

What about using an AdoServer object to fill a System.Data.DataTable object? I am trying to populate a Winforms DataGridView with data from an AdoServer object. For ADS it is like this: create an AdsDataReader object and a System.Data.DataTable object, then do DataTable:Load(AdsDataReader) and DataGridView:DataSource := DataTable. How can I do a similar thing with AdoServer?

Kees.
User avatar
robert
Posts: 4555
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

Re: Convert AdoRecordSet to DataTable

Post by robert »

Kees,
If you want an Ado.Net DataTable then it makes much more sense to also use "plain" Ado.Net to fetch the data and not Xs2Ado.
Which database are you connecting to and which OleDb provider are you using with Xs2Ado?

Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
User avatar
Kees Bouw
Posts: 124
Joined: Wed Nov 06, 2019 11:35 am
Location: Netherlands

Re: Convert AdoRecordSet to DataTable

Post by Kees Bouw »

robert wrote: Fri Nov 29, 2024 1:33 pm Kees,
If you want an Ado.Net DataTable then it makes much more sense to also use "plain" Ado.Net to fetch the data and not Xs2Ado.
Which database are you connecting to and which OleDb provider are you using with Xs2Ado?

Robert
Robert,

The database I am connecting to is Microsoft SQL Server and the ODBC driver is (Microsoft) "SQL Server" (SQLSRV32.DLL). Thank you for your help, can you also explain a little bit about why using plain Ado is better than Xs2Ado in this case?

Kees.
User avatar
robert
Posts: 4555
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

Re: Convert AdoRecordSet to DataTable

Post by robert »

Kees,
DataTable was designed to work with Ado.Net

I personally would not use ODBC for Ado.Net, but the native SqlClient.
However, this should work (untested)

Code: Select all

using System.Data.Odbc
using System.Data
.
.

var oDa := OdbcDataAdapter{cSelectStatement, cConnectionString}
var oDt := DataTable{}
oDa:Fill(dt)
Instead of OdbcDataAdapter you can also use SqlDataAdapter from the System.Data.SqlClient namespace.
The connection string needs to be a bit different then, since you're not using ODBC.
But the performance should be better using that SqlDataAdapter

Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
User avatar
Kees Bouw
Posts: 124
Joined: Wed Nov 06, 2019 11:35 am
Location: Netherlands

Re: Convert AdoRecordSet to DataTable

Post by Kees Bouw »

Robert,

In your example below I get error XS7036 There is no argument given that corresponds to the required formal parameter 'lRows' of 'Xs2Ado.AdoRecordSet.GetRows(usual, usual, usual)' on the line

Code: Select all

var aRows := oRs:GetRows()
I looked it up, it seems that the second and third parameter are optional but the first one is not. However, I can't find anywhere in the documentation what parameter "lRows" could be or what the meaning of it is. The other two parameters are explained but not the first one.

Kees.
robert wrote: Fri Mar 29, 2024 8:05 am 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
User avatar
robert
Posts: 4555
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

Re: Convert AdoRecordSet to DataTable

Post by robert »

Kees,

Simply call GetRows() with 3 NIL variables should work

Code: Select all

oRs:GetRows(NIL,NIL,NIL) 
I am working on an update for Xs2Ado. I will make the parameters optional again.

Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
Post Reply