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

[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.

[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

[Kees Bouw]

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.

[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

[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.

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

[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