« Go home

SQL |> F#: Simple & powerful data access for .NET

I often times think that people new to F# are so drawn to all the fancy toys that it can be easy to lose sight of the fact that it's still just plain old .NET.


FEBRUARY 28, 2020

With that said, in the spirit of starting small and finishing big, we'll look at the simple task of working with a relational database.

Rather than immediately reaching for one of the libraries like SqlProvider (which is an excellent tool, but not the point here), we'll stick to just ADO.

To achieve a very useful module you actually need very little code, which is one of the major benefits of F# in my opinion. For our simple scope, we know we need:

To support the above syntax we'll create ourselves a module called SQL, import System.Data and for this example we'll use System.Data.SqlClient (though you could sub this with any vendor). Once we've gotten our dependencies imported, creating wrapper for ADO becomes trivial.

module SQL =
  open System.Data
  open System.Data.SqlClient

  let newConnection (connectionString : string) =
      let conn = new SqlConnection(connectionString)
      conn.Open()
      conn

  let createParameter (cmd: SqlCommand) (name : string, value : obj) =
      let p = cmd.CreateParameter()
      p.ParameterName <- name
      p.Value <- value
      p

  let addParameter (cmd: SqlCommand) (p : SqlParameter) = 
      cmd.Parameters.Add(p) |> ignore

  let newCommand 
      (sql : string) 
      (parameters : seq<string * obj> 
      (conn : IDbConnection) =        
      let cmd = new SqlCommand(connection = conn, cmdText = sql)
      cmd.CommandType <- CommandType.Text        

      let createParam = createParameter cmd
      let addParam = addParameter cmd
      parameters 
      |> Seq.iter (fun p -> p |> createParam |> addParam)
      cmd 

  let exec 
      (sql : string) 
      (parameters : seq<string * obj> 
      (conn : IDbConnection) =        
      let cmd = newCommand sql param conn
      cmd.ExecuteNonQuery()

  let query 
      (sql : string) 
      (parameters : seq<string * obj> 
      (conn : IDbConnection) =        
      let cmd = newCommand sql param conn
      use rd = cmd.ExecuteReader()
      [ while rd.Read() do
          yield map rd ]
Notice that there is mutation happening here (anywhere you see <-). But that's okay because we've tucked it away and sand boxed it with a function wrapper.

So in about 30 lines of code we've created ourselves a small, well-understood and self documenting solution that can help us fully achieve the goal of interacting with a database. Pretty sweet!

If you find this approach helpful, feel free to checkout my OS project Donald, a well-tested, idiomatic F# wrapper for ADO.NET (note: it is NOT vendor specific).