F# SqlCommand Output Parameters

Question

In F# best way to set up a SQLCommand with parameters

some very neat solutions were given for constructing SQLCommand input parameters. Now I need to do some output parameters for calling a stored procedure that returns two output parameters.

So far I have:

        let cmd = (createSqlCommand query conn)

        let pec = (new SqlParameter("@errorCode", SqlDbType.Int))
        pec.Direction <- ParameterDirection.Output
        ignore (cmd.Parameters.Add(pec))

        let pet = new SqlParameter("@errorMessage", SqlDbType.VarChar, 2000)
        pet.Direction <- ParameterDirection.Output
        ignore (cmd.Parameters.Add(pet))

        let rc = cmd.ExecuteNonQuery()
        let errorCode = cmd.Parameters.Item("@errorCode").Value.ToString()
        let errorText = cmd.Parameters.Item("@errorMessage").Value.ToString()

Which works, but I find it ugly and too imperative. How can I expand the solutions in the previous example, (especially Tomas, which I'm now using) to handle output parameters too? So input and output in the same command to be issued.

So I tried this:

type Command = 
  { Query : string 
    Timeout : int
    Parameters : (string * Parameter) list 
    OutParameters : Option<(string * OutParameter)> list}

followed by this:

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
    match cmd.OutParameters with
    | Some <string * OutParameter> list ->
        for name, par in list do
            let sqlParameter =
              match par with
              | OutInt -> new SqlParameter(name, SqlDbType.Int)
              | OutVarChar len -> new SqlParameter(name, SqlDbType.VarChar, len)
            sqlParameter.Direction <- ParameterDirection.Output
            sql.Parameters.Add sqlParameter |> ignore
    | _ -> ()

But I can't work out the syntax for the match near the end. I tried:

Some list -> and got

Error 52 This expression was expected to have type Option list but here has type 'a option

Then I tried:

| Some Option<string * OutParameter> list ->

got the same error, So I tried:

| Some <string * OutParameter> list ->

got a different error:

Error 53 Unexpected identifier in pattern. Expected infix operator, quote symbol or other token.

Then tried:

| Some <(string * OutParameter)> list ->

got the error:

Error 53 Unexpected symbol '(' in pattern. Expected infix operator, quote symbol or other token.

Finally tried:

| Some (string * OutParameter) list ->

and got the first error again.

Then, I gave up.

What syntax is needed here?

Thought up a new one:

| Some list : (string * OutParameter) ->
        for name, par in list do

but that errors on "for"

Error 53 Unexpected keyword 'for' in type

New Attempt:

I thought maybe I could define a function to build a sql command expecting output parameters and still use the first createSqlCommand function. I tried this:

type OutCommand = 
  { Query : string 
    Timeout : int
    Parameters : (string * Parameter) list 
    OutParameters : (string * OutParameter) list 
  }

let createSqlCommandOut (cmd : OutCommand) connection = 
  let sql = createSqlCommand {cmd.Query; cmd.Timeout; cmd.Parameters} connection
  for name, par in cmd.OutParameters do
    let sqlParameter = 
        match par with
        | OutInt -> new SqlParameter(name, SqlDbType.Int)
        | OutVarChar len -> new SqlParameter(name, SqlDbType.VarChar, len)
    sqlParameter.Direction <- ParameterDirection.Output
    sql.Parameters.Add sqlParameter |> ignore
  sql 

The idea is to grab the parameters passed in and send them on to the original function to do the work. You probably guessed that this doesn't work. I get the errors;

Error 53 Invalid object, sequence or record expression

On the call to createSqlCommand in the new function. Is this kind of thing possible? Can I make a Command record using the members of an OutCommand record? If so, how do I do the casting? (It seems to be neither an upcast downcast)

Answer 1

Tomas is of course much better qualified to answer this, but I'll give it a try. If he does answer, It'll be interesting to see if I'm on the right track. I guess I'm slightly off.

Bear with me if this doesn't quite run well, since I won't test it. I will base this on the code Tomas gave us.

I think we need a new OutParameter type.

type OutParameter =
  | OutInt
  | OutVarChar of int // the length is needed?

In the Command type we add an extra field named OutParameters.

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

In the cmd function, this must be added.

OutParameters = 
  [ "@errorCode", OutInt
    "@errorMessage", OutVarChar 2000 ]

The function createSqlCommand must now also handle OutParameters. The last for-loop is the only modification here.

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
  for name, par in cmd.OutParameters do
    let sqlParameter =
      match par with
      | OutInt -> new SqlParameter(name, SqlDbType.Int)
      | OutVarChar len -> new SqlParameter(name, SqlDbType.VarChar, len)
    sqlParameter.Direction <- ParameterDirection.Output
    sql.Parameters.Add sqlParameter |> ignore
  sql

After you have run your ExecuteNonQuery, you can again take advantage of your list of OutParameters to parse the output.

Now a function to extract the values.

let extractOutParameters (cmd: SqlCommand) (outParms: (string * OutParameter) list) =
  outParms
  |> List.map (fun (name, outType) ->
    match outType with
    | OutInt -> cmd.Parameters.Item(name).Value :?> int |> Int
    | OutVarChar _ -> cmd.Parameters.Item(name).Value.ToString() |> VarChar
  )

I am not at all sure that casting the values like this is good, and you probably should match on the type instead, to handle errors properly. Test it. But that's a minor issue not much related to what I'm trying to demonstrate.

Notice that this function uses the Parameter type for returning the values, rather than the OutParameter type. At this point I would consider changing the names of one or both types, to better reflect their use.

UPDATE

You can use this to create specific functions for commands and queries. Here is a slightly pseudo-codish F# snippet.

type UserInfo = { UserName: string; Name: string; LastLogin: DateTime }

let getUserInfo con userName : UserInfo =
    let cmd = {
        Query = "some sql to get the data"
        Timeout = 1000
        Parameters = ... the user name here
        OutParameters = ... the userName, Name and LastLogin here
        }
    let sqlCommand = createSqlCommand cmd con
    ... run the ExecuteNonQuery or whatever here
    let outs = extractOutParameters sqlCommand cmd.OutParameters
    {
        UserName = getValOfParam outs "@userName"
        Name = getValOfParam outs "@name"
        LastLogin = getValOfParam outs "@lastLogin"
    }

You will have to create the function getValOfParam, which just searches outs for the parameter with the correct name, and returns its value.

You can then use getUserInfo like this.

let userInfo = getUserInfo con "john_smith"

Even if there were ten fields returned, you'd get them in one record, so it's simple to ignore the fields you don't want.

And if you had built another function with results you weren't interested in at all when calling it, you'd call it like this.

startEngineAndGetStatus con "mainEngine" |> ignore