Editing Updating DataTable

Often necessary to update data base tables indirectly through the table-adapter based data table objects.  You want to queue up changes (adds, modifications, deletions) in your local data table, and then push those changes out to the database at the end of the process.

You will have your normal Data Table defined:

        Dim RuleItem_DT As IT_Security_DataSet.RBA_RuleItemsDataTable = Nothing

You'll load it with data:

        Dim NewItem As IT_Security_DataSet.RBA_RuleItemsRow = RuleItem_DT.NewRBA_RuleItemsRow()
        NewItem.ItemGUID = Guid.NewGuid
        NewItem.RuleGUID = _RuleGUID
        NewItem.ItemEffect = "Recommended"
        RuleItem_DT = RuleItem_TA.GetForRuleGUID(_RuleGUID)

You might add data to the table:

        RuleItem_DT.Rows.Add(NewItem)

You might remove data from the table:

        RuleItem_DT.Rows.RemoveAt(Rec_Index)

And eventually, you want those changes to hit the actual database:

        Dim ChangesMade As Integer = RuleItem_TA.Update(RuleItem_DT)

Before doing the update, you can check the status of the each record, as follows:

        For Each Acct_Rec As ITSec_DataSet.ID_AccountsRow In Acct_DT.Rows
            tspbProgressBar.Increment(1)
            ' Add to counters, based on RowState of the record
            Select Case Acct_Rec.RowState
                Case DataRowState.Added
                    ' This is a NEW record in the table
                    RcdCount_Added += 1
                    LogAction("New: " & Acct_Rec.AccountName)
                Case DataRowState.Modified
                    ' This record EXISTED in the table and was UPDATED, so it was also in the import table
                    RcdCount_Modified += 1
                Case DataRowState.Unchanged
                    ' This record EXISTED in the table and was UNCHANGED, because it was NOT in the import table
                    If LCase(Trim(Acct_Rec.Status)) = "active" Then
                        RcdCount_Unchanged += 1
                        LogAction("Missing: " & Acct_Rec.AccountName)
                    Else
                        RcdCount_Inactive += 1
                    End If
                Case Else
                    ' This state should not exist in this process:
                    RcdCount_Other += 1
                    LogAction("Unknown: " & Acct_Rec.AccountName & " (" & Acct_Rec.RowState & ")")
            End Select
        Next