More FP-correct way to create an update sql query

Posted by James Black on Stack Overflow See other posts from Stack Overflow or by James Black
Published on 2010-05-07T02:00:50Z Indexed on 2010/05/07 2:08 UTC
Read the original article Hit count: 302

Filed under:
|
|
|

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?

© Stack Overflow or respective owner

Related posts about F#

Related posts about sql