Phillip Trelford's Array

POKE 36879,255

Pattern Matching Database Records

Last week Jon Harrop showed me some interesting data access code (Jon and I often share the same Cambridge to London train). The code used pattern matching to match records returned from a SQL command, for example.:

let values = [for index in 0..reader.FieldCount-1 -> reader.GetValue index]
match values with
| [Date date; Price o; Price h; Price l; Price c] -> 
    yield date, o, h, l, c
| _ -> invalidOp "Record mismatch"

The code fragment above matches open-high-low-close (OHLC) field values read from an IDataReader. The first line creates a list of field values; these values are then matched using a List pattern with Active Patterns to unbox the objects to strongly typed values, i.e:

let (|Date|) (value:obj) : DateTime = unbox value
let (|Price|) (value:obj) : float = unbox value

Instead of constructing a list each time a pre-allocated array can be employed using the IDataRecord.GetValues method and an Array pattern:

seq {
    let values = Array.zeroCreate reader.FieldCount
    while reader.Read() do       
        reader.GetValues values |> ignore
        match values with
        | [|Date date; Price o; Price h; Price l; Price c|] -> 
            yield date, o, h, l, c
        | _ -> invalidOp "Record mismatch"


The OHLC values are returned as a tuple from inside a sequence expression which is the equivalent of a C# iterator block.

Pattern Matching

I think this is another great example of the power of pattern matching, a feature common to functional programming languages. Somewhat strangely it’s a feature missing from older and more established object-orientated languages like C++, C# and Java. However there is nothing intrinsically stopping these OO languages from supporting pattern matching. Pattern matching has been successfully implemented in newer moustache/curly-brace based languages like Scala, Nemerle and Kotlin. Progress in Java seems to be severely hampered by committees, just look at how long it took to get to Java 7. C# seems to be making more progress but I think may in part be hampered by the compiler being implemented in C++, which Project Roslyn may fix in the future.


The pattern matching approach makes use of the order of the fields. Another approach employed in examples in both Jon’s F# for Scientists and Don’s Expert F# book, is to get the field values based on their ordinals, i.e.:


String Literals

Yet another approach, used as an example in Professional F# 2.0, is to use the field names:

    reader.["Date"]  :?> DateTime,
    reader.["Open"]  :?> float,
    reader.["High"]  :?> float,
    reader.["Low"]   :?> float,
    reader.["Close"] :?> float

Dynamic Lookup

And now for a one line ORM which simply overloads F#’s dynamic lookup operator:

let (?) (reader:IDataReader) (name:string) = unbox reader.[name]

This really helps reduce the boilerplate:


For more information on this approach see Tomas Petricek’s excellent post:


Now we’ve explored 5 different ways of doing exactly the same thing it’s probably time to do something useful like plotting.

Again Tomas has a tutorial for that:

Plotting a chart from inside F# interactive using Carl Nolan’s new FSharpChart library:

#load "FSharpChart.fsx"
open MSDN.FSharp.Charting

[ for date,o,h,l,c in getHistoricalPrices "MSFT" -> date, (h,l,o,c) ]
|> List.filter (fun (date, _) -> date >= DateTime(2011,08,01))
|> FSharpChart.Candlestick

Gives us a Candlestick chart for MSFT stock since August:


Final Thought

With Type Providers in F# 3 a lot of complexity will disappear, as we get type safe data access with intellisense (rather than strings or ordinals) without the need for code generation!

Comments (4) -

  • ahala

    9/27/2011 10:59:53 AM |

    how's type providers make this simpler? I saw the minicsvProvider example is far more complicated than the ways above for dohlc csv data?

  • Phil

    9/27/2011 11:44:58 PM |

    Using the minicsvProvider doesn't look all that hard:

    let csv = new MiniCsvProvider.MiniCsv<"test.csv">()
    let row1 = csv.Data |> Seq.head
    let distance = row1.Distance

  • ahala

    9/28/2011 5:52:00 AM |

    @phil, in your blog, the first column is datetime rather than float. so one has to revise minicsvprovider, which make it harder?

  • Phil

    9/28/2011 6:09:51 AM |

    @ahala, you're right the minicsvprovider sample only does floats Smile but it is just a sample.
    I believe the intention is that there will be type providers for common data sources out of the box on the full release of F# 3.0, as has already been done for SQL Server and OData. For less common data sources, you have the option of writing a custom type provider if that makes sense for you.
    Using the SQL type provider we'd end up with:
    yield row.Date, row.Open, row.High, row.Low, row.Close

Pingbacks and trackbacks (1)+

Comments are closed