More FP-correct way to create an update sql query
- by James Black
I am working on access a database using F# and my initial attempt at creating a function to create the update query is flawed.
let BuildUserUpdateQuery (oldUser:UserType) (newUser:UserType) =
let buf = new System.Text.StringBuilder("UPDATE users SET ");
if (oldUser.FirstName.Equals(newUser.FirstName) = false) then buf.Append("SET first_name='").Append(newUser.FirstName).Append("'" ) |> ignore
if (oldUser.LastName.Equals(newUser.LastName) = false) then buf.Append("SET last_name='").Append(newUser.LastName).Append("'" ) |> ignore
if (oldUser.UserName.Equals(newUser.UserName) = false) then buf.Append("SET username='").Append(newUser.UserName).Append("'" ) |> ignore
buf.Append(" WHERE id=").Append(newUser.Id).ToString()
This doesn't properly put a , between any update parts after the first, for example:
UPDATE users SET first_name='Firstname', last_name='lastname' WHERE id=...
I could put in a mutable variable to keep track when the first part of the set clause is appended, but that seems wrong.
I could just create an list of tuples, where each tuple is oldtext, newtext, columnname, so that I could then loop through the list and build up the query, but it seems that I should be passing in a StringBuilder to a recursive function, returning back a boolean which is then passed as a parameter to the recursive function.
Does this seem to be the best approach, or is there a better one?
