F# best way to set up a SQLCommand with parameters

Question

My F# program needs to talk to SQL Server. In one part I have something like this:

 let workFlowDetailRuncommand = new SqlCommand(query, econnection) 
    workFlowDetailRuncommand.CommandTimeout <- 100000
    workFlowDetailRuncommand.Parameters.Add("@1", SqlDbType.Int).Value <- 42
    workFlowDetailRuncommand.Parameters.Add("@2", SqlDbType.VarChar).Value <- "answer"
    workFlowDetailRuncommand.Parameters.Add("@3", SqlDbType.VarChar).Value <- mydate.ToString("yyyy.MM.dd")
    workFlowDetailRuncommand.Parameters.Add("@4", SqlDbType.VarChar).Value <- "D. Adams"
    workFlowDetailRuncommand.Parameters.Add("@5", SqlDbType.DateTime).Value <- DateTime.Now
    workFlowDetailRuncommand.Parameters.Add("@6", SqlDbType.Text).Value <- filename

Is there a more idomatic way to do this (with less typing!) without setting the parameters one at a time like this.

Take a look at SqlCommandProvider here. Might not perfectly fit your use case (esp. in case the query is dynamic), but handles the parameters nicely: http://fsprojects.github.io/FSharp.Data.SqlClient/ — Honza Brestan, Mar 15 '17 at 20:36

score 4 · Answer 1 · answered Mar 15 '17 at 20:48

I think the answer from Bent gives you very nice DSL for constructing the standard SqlCommand objects. That might very well be just what you need - if you just want nicer syntax for creating a couple of commands, it will work perfectly.

If you wanted to do more things with your SQL commands, then the DSL has one limitation, which is that it is still based on the underlying mutable SqlCommand type - it looks functional, but it mutates objects under the cover, which might get you in trouble.

A more comprehensive option would be to define your own functional types to capture the domain - that is, the kind of queries you want to run:

type Parameter =
  | Int of int
  | VarChar of string
  | Text of string
  | DateTime of System.DateTime

type Command = 
  { Query : string 
    Timeout : int
    Parameters : (string * Parameter) list }

Then you can construct queries using normal F# types (and you could even implement a DSL like the one Bent suggested on top of this, while still keeping things immutable):

let cmd = 
  { Query = query
    Timeout = 100000 
    Parameters = 
      [ "@1", Int 42
        "@2", VarChar "answer"
        "@3", VarChar (mydate.ToString("yyyy.MM.dd"))
        "@4", VarChar "D. Adams"
        "@5", DateTime DateTime.Now
        "@6", Text filename ] }

The last bit would be to write a function that takes command and a connection and turns it into SqlCommand:

let createSqlCommand cmd connection = 
  let sql = new SqlCommand(cmd.Query, connection) 
  sql.CommandTimeout <- cmd.Timeout
  for name, par in cmd.Parameters do
    let sqlTyp, value = 
      match par with
      | Int n -> SqlDbType.Int, box n
      | VarChar s -> SqlDbType.VarChar, box s
      | Text s -> SqlDbType.Text, box s
      | DateTime dt -> SqlDbType.DateTime, box dt
    sql.Parameters.Add(name, sqlTyp).Value <- value
  sql

What is the best approach will depend on your use case - and interacting with databases is inherently impure, so perhaps keeping things isolated and impure is perfectly fine. Though I wanted to show this as a possible option if you wanted to be more functional and focus on the domain (using the strong domain-driven modelling side of F#!).

I used this technique to handle construction of huge amounts of XML based on F# types, and it worked wonderfully. — Bent Tranberg, Mar 15 '17 at 21:14
This is terrific! I'm starting to use it. one follow up question. Since the SQLConnection supports Dispose, I have been doing ```use connection = new SqlConnection(connString)``` How do I fit that into this idea? — user1443098, Mar 16 '17 at 15:43
@user1443098 This is the reason why I did not include `SqlConnection` in the `Command` type - that way, you can configure the command independently and then open the connection using `use`, call `createSqlCommand` and run the SQL command - and then close the connection. There is still imperative part, but it gets pushed to the very end. — Tomas Petricek, Mar 16 '17 at 16:26

Bent Tranberg · Answer 2 · 2017-03-15T21:09:46.193

I haven't tested this.

Create some helper functions.

let createSqlCommand query connection =
    new SqlCommand(query, connection)

let setTimeout timeout (sqlCommand: SqlCommand) =
    sqlCommand.CommandTimeout <- timeout
    sqlCommand

let addInt name (value: int) (sqlCommand: SqlCommand) =
    sqlCommand.Parameters.Add(name, SqlDbType.Int).Value <- value
    sqlCommand

Use them like this.

let mySqlCommand =
    createSqlCommand someQuery someConnection
    |> setTimeout 100000
    |> addInt "@1" 41
    |> addString "@s" "Hello"
    |> addDateTime "@dt1" DateTime.Now
    |> addFloat "@f1" 5.1

If you have an int, it makes sense to always use SqlDbType.Int.

But if you have a string, there are several obvious candidates for field types. For that reason it may be a good idea to let the names of addXxx functions reflect the field types rather than the F#/.NET types. So that you would create addVarChar, addNVarChar, addChar, etc.

|> addInt "@i1" myInt
|> addDateTime "@dt1" myDateTime
|> addText "@tagText" myTagText
|> addNVarChar "@letter" myLetterBody

F# best way to set up a SQLCommand with parameters

2 Answers2

Linked