F# and ADO.NET - idiomatic F#

Question

I'm just starting to learn F#. I wrote this F#/ADO.NET code last night. In what ways would you improve the syntax - make it feel like idiomatic F#?

    let cn = new OleDbConnection(cnstr)
    let sql = "SELECT * FROM People"
    let da = new OleDbDataAdapter(new OleDbCommand(sql, cn))
    let ds = new DataSet()
    cn.Open()
    let i = da.Fill(ds)
    let rowCol = ds.Tables.[0].Rows
    let rowCount = rowCol.Count
    printfn "%A" rowCount

    for i in 0 .. (rowCount - 1) do
        let row:DataRow = rowCol.[i]
        printfn "%A" row.["LastName"]

Note: I did find the syntax checker did not like rowCol.[i].["LastName"] What is the proper way to handle dual-indexers? I had to break up the code over two lines.

Also If I hadn't gone down the DataSet route and used a SqlDataReader that loaded its data into F# records. What collection structure should I use for containing the records? The standard .NET List<>?

Answer 1

Well, there's not much you can change in the first bit but whenever you're processing collections of data like in the last few rows, you can use the built-in Seq, List, Array functions.

for i in 0 .. (rowCount - 1) do
  let row:DataRow = rowCol.[i]
  printfn "%A" row.["LastName"]

=

rowCol |> Seq.cast<DataRow> 
       |> Seq.iter (fun row -> printfn "%A" row.["LastName"])

Answer 2

The key part of your code deals with .NET API that is not functional, so there is no way to make this part of the code particularly more idiomatic or nicer. However, the key thing in functional programming is abstraction, so you can hide this (ugly) code into some idiomatic and reusable function.

For representing collections of data in F#, you can either use standard F# list type (which is good for functional data processing) or seq<'a> (which is standard .NET IEnumerable<'a> under the cover), which works nicely when working with other .NET libraries.

Depending on how you access database elsewhere in your code, the following could work:

// Runs the specified query 'sql' and formats rows using function 'f'
let query sql f = 
  // Return a sequence of values formatted using function 'f'
  seq { use cn = new OleDbConnection(cnstr) // will be disposed 
        let da = new OleDbDataAdapter(new OleDbCommand(sql, cn)) 
        let ds = new DataSet() 
        cn.Open() 
        let i = da.Fill(ds) 
        // Iterate over rows and format each row
        let rowCol = ds.Tables.[0].Rows 
        for i in 0 .. (rowCount - 1) do 
            yield f (rowCol.[i]) }

Now you can use the query function to write your original code roughly like this:

let names = query "SELECT * FROM People" (fun row -> row.["LastName"])
printfn "count = %d" (Seq.count names)
for name in names do printfn "%A" name

// Using 'Seq.iter' makes the code maybe nicer 
// (but that's a personal preference):
names |> Seq.iter (printfn "%A")

Another example you could write is:

// Using records to store the data
type Person { LastName : string; FirstName : string }
let ppl = query "SELECT * FROM People" (fun row -> 
  { FirstName = row.["FirstName"]; LastName = row.["LastName"]; })

let johns = ppl |> Seq.filter (fun p -> p.FirstName = "John")

BTW: Regarding the suggestion by Mau I wouldn't use higher-order functions excessively if there is a more direct way to write the code using language constructs such as for. The example with iter above is simple enough and some people will find it more readable, but there is no general rule...

Answer 3

I wrote a functional wrapper over ADO.NET for F#. With this library your example looks like this:

let openConn() =
   let cn = new OleDbConnection(cnstr)
   cn.Open()
   cn :> IDbConnection

let query sql = Sql.execReader (Sql.withNewConnection openConn) sql

let people = query "select * from people" |> List.ofDataReader
printfn "%d" people.Length
people |> Seq.iter (fun r -> printfn "%s" (r?LastName).Value)