Android Code Snippet MSMySQL_InsertHelper

The purpose of this function is to return an insert command after you pass it a map of field, value pairs to add with the MSMySQL library. This code has been adopted from DBUtils. As you will note, all the fields to be passed are assumed to be strings. It's not using parameters. Enjoy.

Public Sub MSMySQL_InsertHelper(tblName As String, iMap As Map) As String
    ' pass a map with field names and field values to
    ' build an insert SQL string for MSMySQL
    ' assuming all values to insert are string
    Dim sb As StringBuilder
    Dim fTot As Int
    Dim fCnt As Int
    Dim colName As String
    Dim colValue As String
    ' find the size of the map
    fTot = iMap.size - 1
    ' start building the sql query
    sb.initialize
    sb.Append("INSERT INTO " & tblName & " SET ")
    For fCnt = 0 To fTot
        ' get field name
        colName = iMap.GetKeyAt(fCnt)
        ' get field value
        colValue = iMap.GetValueAt(fCnt)
        sb.append(colName)
        sb.append(" = '")
        sb.append(colValue)
        sb.append("'")
        If fCnt <> fTot Then sb.Append(", ")
    Next
    sb.Append(";")
    Return sb.tostring
End Sub

Example usage would be...

Dim nSite As Map
        nSite.Initialize
        nSite.Put("SiteName", txtSiteName.text)
        nSite.Put("Depth", txtDepth.text)
        nSite.Put("Radius", txtRadius.text)
        nSite.Put("MaxPressure", txtMaxPressure.Text)
        nSite.Put("MinPressure", txtMinPressure.Text)
        nSite.Put("SiteType", cboType.SelectedItem)
        nSite.Put("Latitude", txtLatitude.text)
        nSite.Put("Longitude", txtLongitude.text)
        nSite.Put("Formulae", "")
  
        ' add the site to the database, lets build the sql command from the map
        Dim iQry As String = modMashiane.MSMySQL_InsertHelper("Sites", nSite)
        ' execute the query
        hDB.db.ExecuteASync(iQry, "addsite")

 

[Mashiane]

This is a replace helper. A Replace statement first deletes and then inserts the new record should the primary key match. If there is an existing record, the batch outcome will be 2 records affected.

Public Sub MSMySQL_ReplaceHelper(tblName As String, iMap As Map) As String
    ' pass a map with field names and field values to
    ' build an insert SQL string for MSMySQL
    ' assuming all values to insert are string
    Dim sb As StringBuilder
    Dim fTot As Int
    Dim fCnt As Int
    Dim colName As String
    Dim colValue As String
    ' find the size of the map
    fTot = iMap.size - 1
    ' start building the sql query
    sb.initialize
    sb.Append("REPLACE INTO " & tblName & " SET ")
    For fCnt = 0 To fTot
        ' get field name
        colName = iMap.GetKeyAt(fCnt)
        ' get field value
        colValue = iMap.GetValueAt(fCnt)
        sb.append(colName)
        sb.append(" = '")
        sb.append(colValue)
        sb.append("'")
        If fCnt <> fTot Then sb.Append(", ")
    Next
    sb.Append(";")
    Return sb.tostring
End Sub