'Edits the data to the table.
'ListOfMaps - A list with maps as items. Each map represents a record where the map keys are the columns names
'and the maps values are the values.
'Note that you should create a new map for each record (this can be done by calling Dim to redim the map).
Sub UpdateMaps(SQL As SQL, TableName As String, ListOfMaps As List, WhereFieldEquals As Map)
Dim sb, columns, values As StringBuilder
'Small check for a common error where the same map is used in a loop
If ListOfMaps.Size > 1 AND ListOfMaps.Get(0) = ListOfMaps.Get(1) Then
Log("Same Map found twice in list. Each item in the list should include a different map object.")
ToastMessageShow("Same Map found twice in list. Each item in the list should include a different map object.", True)
Return
End If
If WhereFieldEquals.Size > 1 AND WhereFieldEquals.Get(0) = WhereFieldEquals.Get(1) Then
Log("Same Map found twice in where. Each item in the where should include a different map object.")
ToastMessageShow("Same Map found twice in where. Each item in the where should include a different map object.", True)
Return
End If
If WhereFieldEquals.Size = 0 Then
Log("WhereFieldEquals map empty!")
Return
End If
SQL.BeginTransaction
Try
For i1 = 0 To ListOfMaps.Size - 1
sb.Initialize
values.Initialize
Dim listOfValues As List
listOfValues.Initialize
sb.Append("UPDATE [" & TableName & "] SET ")
Dim m As Map
m = ListOfMaps.Get(i1)
For i2 = 0 To m.Size - 1
If i2 > 0 Then
sb.Append(", ")
End If
sb.Append("[").Append(m.GetKeyAt(i2)).Append("] = ?")
listOfValues.Add(m.GetValueAt(i2))
Next
If i1 = 0 Then Log("UpdateMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
' where condition
sb.Append(" WHERE ")
For i = 0 To WhereFieldEquals.Size - 1
If i > 0 Then sb.Append(" AND ")
sb.Append("[").Append(WhereFieldEquals.GetKeyAt(i)).Append("] = ?")
listOfValues.Add(WhereFieldEquals.GetValueAt(i))
Next
If i1 = 0 Then Log("UpdateMaps (first query out of " & WhereFieldEquals.Size & "): " & sb.ToString)
SQL.ExecNonQuery2(sb.ToString, listOfValues)
Next
SQL.TransactionSuccessful
Catch
ToastMessageShow(LastException.Message, True)
Log(LastException)
End Try
SQL.EndTransaction
End Sub
