Read from database into dictionary VB.net

Question

I have a problem that is solved by selecting all columns of particular records from a DB2 database and shows them to the user as a datagridview. The user will select a single record changes a column's value and saves. Doing so will insert an updated record, so to do that I need the columns names since some may be empty and I have to recreate the record.

The only way I came up with is creating a dictionary that contains the column name and whatever value it corresponds to, manipulating it somehow and doing an insert.

For instance if I do not need the column name, I do it using LotsList as a List(Of LotDTO).

Using db2reader As OdbcDataReader = db2Com.ExecuteReader
      While db2reader.Read
            LotsList.Add(New LOTDTO With
                             {
                             .LotNo = If(IsDBNull(db2reader("dlot")), "", db2reader("dlot")),
                             .ExpDate = If(IsDBNull(db2reader("ExpDate")), "", db2reader("ExpDate")),

                              }
                         )
       End While

But how to do it if I am to use a Dictionary(Of String, LotDTO). Here is my query.

Dim SQL As String = "SELECT ITEMID, TRXDATE, TRXTYPE, REFERNO, ... From DB Where ....

Also open to any suggestion that is not beyond the capabilities of a beginner.

Answer 1

Instead I would recommend you use a DataTable object, which is designed for this sort of thing.

See this answer for how to use a DataAdapter to fill a DataTable (you will need to convert the code to use ODBC classes).

Then you can use the DataGridView.DataSource property to bind the DataTable.

Then use the following code to retrieve the edited values from the DataGridView for your update statements (dgvSource is the name of the DataGridView):

Dim dgvr As DataGridViewRow = dgvSource.Rows(intRow)
Dim drw As DataRow = DirectCast(dgvr.DataBoundItem, DataRowView).Row

(don't just use .Rows as this will give you the wrong data if the user sorts the datagridview)