title: More FP-correct way to create an update sql query
body: <p>I am working on access a database using F# and my initial attempt at creating a function to create the update query is flawed.</p>
  
  <pre><code>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("'" ) |&gt; ignore
      if (oldUser.LastName.Equals(newUser.LastName) = false)  then buf.Append("SET last_name='").Append(newUser.LastName).Append("'" ) |&gt; ignore
      if (oldUser.UserName.Equals(newUser.UserName) = false)  then buf.Append("SET username='").Append(newUser.UserName).Append("'" ) |&gt; ignore
      buf.Append(" WHERE id=").Append(newUser.Id).ToString()
  </code></pre>
  
  <p>This doesn't properly put a <code>,</code> between any update parts after the first, for example:</p>
  
  <pre><code>UPDATE users SET first_name='Firstname', last_name='lastname' WHERE id=...
  </code></pre>
  
  <p>I could put in a mutable variable to keep track when the first part of the <code>set</code> clause is appended, but that seems wrong.</p>
  
  <p>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 <code>StringBuilder</code> to a recursive function, returning back a <code>boolean</code> which is then passed as a parameter to the recursive function.</p>
  
  <p>Does this seem to be the best approach, or is there a better one?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Here is what I am using as my current solution, as I wanted to make it more generalized, so I just need to write an abstract class for my entities to derive from and they can use the same function. I chose to split up how I do the function so I can pass in how to create the <code>SET</code> part of the update so I can test with different ideas.</p>
  
  <pre><code>let BuildUserUpdateQuery3 (oldUser:UserType) (newUser:UserType) =
      let properties = List.zip3 oldUser.ToSqlValuesList newUser.ToSqlValuesList oldUser.ToSqlColumnList 
      let init = false, new StringBuilder()
      let anyChange, (formatted:StringBuilder) = 
          properties |&gt; Seq.fold (fun (anyChange, sb) (oldVal, newVal, name) -&gt;
              match(oldVal=newVal) with
              | true -&gt; anyChange, sb
              | _ -&gt;
                  match(anyChange) with
                  | true -&gt; true, sb.AppendFormat(",{0} = '{1}'", name, newVal)
                  | _ -&gt; true, sb.AppendFormat("{0} = '{1}'", name, newVal)                    
              ) init
      formatted.ToString()
  
  let BuildUserUpdateQuery (oldUser:UserType) (newUser:UserType) (updatequery:UserType-&gt;UserType-&gt;String) =
      let buf = StringBuilder("UPDATE users SET ");
      buf.AppendFormat(" {0} WHERE id={1}", (updatequery oldUser newUser), newUser.Id)
  
  let UpdateUser conn (oldUser:UserType) (newUser:UserType) =
      let query = BuildUserUpdateQuery oldUser newUser BuildUserUpdateQuery3
      execNonQuery conn (query.ToString())
  </code></pre>