A functional wrapper over ADO.NET

ADO.NET is the de-facto basic library for data access in .NET, and as everything in the BCL, it's object oriented. Which forces you to write object-oriented code when you use ADO.NET in F#.

Nothing wrong with that, and in fact F# is a great language to write object-oriented code. But I believe I speak for many F# coders when I say we prefer functional programming over OOP whenever possible.

So it boils down to this: you either use ADO.NET's objects directly (like this or this), or you wrap it to give a more functional style (like this, this, or this)

So here's an attempt at creating a generic functional wrapper over ADO.NET, I called this library FsSql. UPDATE 3/30/2011: I recently released FsSql 0.1, binaries available in github and NuGet.

Let's start with some examples...

The connection manager

A simple function to open a connection:

let openConn() = 
    let conn = new System.Data.SQLite.SQLiteConnection("Data Source=test.db;Version=3;New=True;") 
    conn.Open() 
    conn :> IDbConnection 

Let's create a table:

let ddl = "create table user (id int primary key not null, name varchar not null, address varchar null)"

Sql.execNonQuery (Sql.withNewConnection openConn) ddl [] |> ignore

That was quite verbose! The "Sql.withNewConnection openConn" piece is the "connection manager", it basically encapsulates how to create and dispose the connection. In general we'll always use the same connection manager, so we can use partial application around it for all operations:

let connMgr = Sql.withNewConnection openConn 
let execScalar sql = Sql.execScalar connMgr sql 
let execReader sql = Sql.execReader connMgr sql 
let execReaderf sql = Sql.execReaderF connMgr sql 
let execNonQueryf sql = Sql.execNonQueryF connMgr sql 
let execNonQuery sql p = Sql.execNonQuery connMgr sql p |> ignore 
let exec sql = execNonQuery sql []

Non-queries and Parameters

Using the previous definitions now we can write:

execNonQuery 
    "insert into user (id, name, address) values (@id, @name, @address)"  
    (Sql.parameters ["@id",box 1; "@name",box "John"; "@address",box None])

All that parameter boxing gets boring fast, we can define parameters in other ways:

let P = Sql.Parameter.make

execNonQuery 
    "insert into user (id, name, address) values (@id, @name, @address)"  
    [P("@id", 2); P("@name", "George"); P("@address", None)]

Note that I used None for the address parameter. None parameters are automatically mapped to DBNull.

Queries

Let's count the records in our table:

let countUsers(): int64 = 
    execScalar "select count(*) from user" [] |> Option.get 
    
printfn "%d users" (countUsers())

When reading a field from a row in a resultset (or a scalar), you get it as an Option (None if the field is DBNull, otherwise Some x), so it forces you to deal with nullness (database nullness, in this case) as is usual in F#.

Here's an example of querying and iterating over the results:

execReader "select * from user" [] 
|> Seq.ofDataReader 
|> Seq.iter (fun dr -> 
    let id = (dr?id).Value 
    let name = (dr?name).Value 
    let address = 
        match dr?address with 
        | None -> "No registered address" 
        | Some x -> x 
    printfn "Id: %d; Name: %s; Address: %s" id name address)

Here Seq.ofDataReader converts the IDataReader into a sequence of IDataRecords. The dynamic operator is used to get the data out of the fields with option types, again forcing you to deal with nullness.

Also note how connection management is implicit. The connection is automatically closed when the datareader is disposed, which happens at the end of the iteration.

Stored procedures

You can also call stored procedures instead of inline SQL. Here's an example for the AdventureWorks sample database:

let managers = Sql.execSPReader connMgr 
                "uspGetEmployeeManagers" 
                (Sql.parameters ["@EmployeeID", box 1]) 
                |> List.ofDataReader 

Formatted SQL

Let's say we want to create a function to retrieve a record by id. It would look like this:

let selectById (id: int) =
     execReader "select * from user where id = @id" [P("@id", id)]

We can do better than this, using Sql.execReaderF instead:

let selectById = execReaderf "select * from user where id = %d"

The SQL here is interpreted as a printf-formatted string using the printf manipulation I described a couple of months ago. Even though this has its limitations, it's a nifty alternative for little queries like this one.

Async

An ofter overlooked capability of some ADO.NET providers is being able to run commands/queries asynchronously. Maybe it's because (as far as I know) only SqlClient actually implements this properly. Anyway you can use async database calls with FsSql:

async { 
    use! reader = Sql.asyncExecReader connMgr "select * from user" [] 
    let r = reader |> List.ofDataReader 
    return r.Length 
}

Keep in mind that async database calls do not imply better overall scalability by themselves. As usual, make sure by measuring for your specific scenario.

In the second part of this post we'll see transactions and mapping.