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 ?
- argument passed cannot be a WinForm control as
DataGridView
(no Winform object in DLL) - another database's table cannot be filled with
DataGridView
content (not performant) - 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 ?
CodePudding user response:
You can use a .Net DataTable object.
Here is some lines of code
Dim dt As New DataTable
dt.Columns.Add("EmployeeID", GetType(Integer))
dt.Columns.Add("FirstName", GetType(String))
For Each row As DataGridViewRow In grid.Rows
dt.Rows.Add(row.Cells(0), row.Cells(1))
Next row
and when it is filled, you can pass it to your DLL
SetDataTable(dt)
CodePudding user response:
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.