Which class can I use to pass a "RecordSet" between an WinForm VB.Net EXE program and a classes DLL?

Question

I have developped a VB.Net DLL that can read Postgres, Oracle and OleDb.

To allow this DLL to execute some SQL commands, I pass it a Connection object and I use it to execute some SQL SELECT command.

My problem now is that I want to pass all data contained in a GridView object that contains data obtained using a SQL command in EXE program (not in DLL) and that user can have changed.

Which type of object can I pass considering following constraints ?

1. argument passed cannot be a WinForm control as DataGridView (no Winform object in DLL)
2. another database's table cannot be filled with DataGridView content (not performant)
3. class used must be part of an .Net assembly (not a COM, too old technology)

Until now, the only solution found consist of using ADO or Microsoft ActiveX Data Object Record Set, but this class is not an .Net assembly and solution found on DataBase Journal does'nt work because Items property of Field class is Read Only.

Here is my code

    Dim rstADO As ADODB.Recordset
    rstADO = New ADODB.Recordset
    With rstADO
        .Fields.Append("EmployeeID", ADODB.DataTypeEnum.adInteger, 0, ADODB.FieldAttributeEnum.adFldKeyColumn)
        .Fields.Append("FirstName", ADODB.DataTypeEnum.adVarChar, 10, ADODB.FieldAttributeEnum.adFldMayBeNull)

        .CursorType = ADODB.CursorTypeEnum.adOpenKeyset
        .CursorLocation = ADODB.CursorLocationEnum.adUseClient
        .LockType = ADODB.LockTypeEnum.adLockPessimistic
        .Open()

        For Each row As DataGridViewRow In grid.Rows
            .AddNew()
            For i = 0 To grid.Columns.Count - 1
                .Fields(i) = row.Cells(i).Value
            Next i
            .Update()
        Next row
    End With

Line .Fields(i) dispay following error BC30526: 'Item' property is 'ReadOnly'.

Which class can I use ?

Answer 1

I have used solution proposed by tramex user.

I want to extract all columns except Edit et PDF columns and I need to add a special column named cash based on another column present in DataGridView.

Here is my code

Dim dt As New DataTable
Dim sName As String
Dim type As Type

Dim oSKipList() As String = {"Edit", "PDF"}
For Each col As DataGridViewColumn In grid.Columns
    sName = col.Name.Replace("Col_", "")
    If Not oSKipList.Contains(sName) Then
        type = col.CellTemplate.FormattedValueType
        dt.Columns.Add(sName, type)
    End If
Next col
dt.Columns.Add("cash", Type.GetType("System.String"))

For Each row As DataGridViewRow In grid.Rows
    Dim dr As DataRow = dt.NewRow
    Dim n = 0
    For Each col As DataGridViewColumn In grid.Columns
        sName = col.Name.Replace("Col_", "")
        If Not oSKipList.Contains(sName) Then
            Dim i = col.Index
            dr(n) = row.Cells(i).Value
            n += 1
        End If
    Next col
    dr(n) = IIf(dr("Account") = "-", "CASH", "")
    dt.Rows.Add(dr)
Next row

oExcelReport.SetDataTable(dt)

The first part of this code initialize Columns and the second part initialize Rows.

This code is working fine on my program.