Database Connections and F#

Question

I wrote the following code to execute a SQLServer StoredProc in F#

module SqlUtility =
  open System
  open System.Data
  open System.Data.SqlClient

  SqlUtility.GetSqlConnection "MyDB"
  |> Option.bind (fun con -> SqlUtility.GetSqlCommand "dbo.usp_MyStordProc" con) 
  |> Option.bind (fun cmd -> 
      let param1 = new SqlParameter("@User", SqlDbType.NVarChar, 50)
      param1.Value <- user
      cmd.Parameters.Add(param1) |> ignore
      let param2 = new SqlParameter("@PolicyName", SqlDbType.NVarChar, 10)
      param2.Value <- policyName
      cmd.Parameters.Add(param2) |> ignore
      Some(cmd)
    )
  |> Option.bind (fun cmd -> SqlUtility.ExecuteReader cmd)
  |> Option.bind (fun rdr -> ExtractValue rdr)         

  let GetSqlConnection (conName : string) =
    let conStr = ConfigHandler.GetConnectionString conName
    try 
      let con = new SqlConnection(conStr)
      con.Open()
      Some(con)
    with
     | :? System.Exception as ex -> printfn "Failed to connect to DB %s with Error %s "  conName ex.Message; None
     | _ -> printfn "Failed to connect to DB %s" conName; None

  let GetSqlCommand (spName : string) (con : SqlConnection) =    
    let cmd = new SqlCommand()
    cmd.Connection <- con
    cmd.CommandText <- spName
    cmd.CommandType <- CommandType.StoredProcedure
    Some(cmd)

  let AddParameters (cmd : SqlCommand) (paramList : SqlParameter list) =
    paramList |> List.iter (fun p -> cmd.Parameters.Add p |> ignore) 

  let ExecuteReader (cmd : SqlCommand ) = 
    try
      Some(cmd.ExecuteReader())
    with
    | :? System.Exception as ex -> printfn "Failed to execute reader with error %s" ex.Message; None

I have multiple problems with this code

1. First and foremost the repeated use of Option.bind is very irritating... and is adding noise. I need a more clearer way to check if the output was None and if not then proceed.

2. At the end there should be a cleanupfunction where I should be able to close + dispose the reader, command and connection. But currently at the end of the pipeline all I have is the reader.

3. The function which is adding parameters... it looks like it is modifying the "state" of the command parameter because the return type is still the same command which was sent it... with some added state. I wonder how a more experienced functional programmer would have done this.

4. Visual Studio gives me a warning at each of the place where i do exception handling. what's wrong with that" it says

This type test or downcast will always hold

The way I want this code to look is this

let x : MyRecord seq = GetConnection "con" |> GetCommand "cmd" |> AddParameter "@name" SqlDbType.NVarchar 50 |> AddParameter "@policyname" SqlDbType.NVarchar 50 |> ExecuteReader |> FunctionToReadAndGenerateSeq |> CleanEverything

Can you recommend how can I take my code to the desired level and also any other improvement?

Answer 1

I think that using options to represent failed computations is more suitable to purely functional langauges. In F#, it is perfectly fine to use exceptions to denote that a computation has failed.

Your code simply turns exceptions into None values, but it does not really handle this situation - this is left to the caller of your code (who will need to decide what to do with None). You may as well just let them handle the exception. If you want to add more information to the exception, you can define your own exception type and throw that instead of leaving the standard exceptions.

The following defines a new exception type and a simple function to throw it:

exception SqlUtilException of string

// This supports the 'printf' formatting style    
let raiseSql fmt = 
  Printf.kprintf (SqlUtilException >> raise) fmt 

Using plain .NET style with a few simplifications using F# features, the code looks a lot simpler:

// Using 'use' the 'Dispose' method is called automatically
let connName = ConfigHandler.GetConnectionString "MyDB"
use conn = new SqlConnection(connName)

// Handle exceptions that happen when opening  the connection
try conn.Open() 
with ex -> raiseSql "Failed to connect to DB %s with Error %s " connName ex.Message

// Using object initializer, we can nicely set the properties
use cmd = 
  new SqlCommand( Connection = conn, CommandText = "dbo.usp_MyStordProc",
                  CommandType = CommandType.StoredProcedure )

// Add parameters 
// (BTW: I do not think you need to set the type - this will be infered)
let param1 = new SqlParameter("@User", SqlDbType.NVarChar, 50, Value = user) 
let param2 = new SqlParameter("@PolicyName", SqlDbType.NVarChar, 10, Value = policyName) 
cmd.Parameters.AddRange [| param1; param2 |]

use reader = 
  try cmd.ExecuteReader()
  with ex -> raiseSql "Failed to execute reader with error %s" ex.Message

// Do more with the reader
()

It looks more like .NET code, but that is perfectly fine. Dealing with databases in F# is going to use imperative style and trying to hide that will only make the code confusing. Now, there is a number of other neat F# features you could use - especially the support for dynamic operators ?, which would give you something like:

let connName = ConfigHandler.GetConnectionString "MyDB"

// A wrapper that provides dynamic access to database
use db = new DynamicDatabase(connName)

// You can call stored procedures using method call syntax
// and pass SQL parameters as standard arguments
let rows = db.Query?usp_MyStordProc(user, policy)

// You can access columns using the '?' syntax again
[ for row in rows -> row?Column1, row?Column2 ]

For more information about this, see the following MSDN series:

• How to: Dynamically Invoke a Stored Procedure
• Step 1: Create a Database and Show the Poll Options
• Step 2: Implement Voting for an Option