Haskell HDBC Elegance in F#?

ado.net, f#, haskell, hdbc

Solution

I agree with @JonnyBoats comment that generally using an F# SQL type provider like SqlDataConnection (LINQ-to-SQL) or SqlEntityConnection (Entity Framework) would be far more elegant than any kind of solution involving building insert statement strings by hand.

But, there is one important qualifier to your question: "At between 9,000 and 10,000 records per day to import to each of the two tables, I want to do this as efficiently as possible." In a scenario like this, you'll want to use SqlBulkCopy for efficient bulk inserts (it leverages native database driver features for much faster inserts than you are likely getting with HDBC's `executeMany`).

Here's a small example that should help you getting started using `SqlBulkCopy` with F#: https://stackoverflow.com/a/8942056/236255. Note that you'll be working with a DataTable to stage the data which though old and somewhat awkward to use from F#, is still superior to building insert statement strings in my opinion.

Update in response to comment

Here's a generalized approach to using `SqlBulkCopy` which is improved for your scenario (we pass in a column specification separately from the row data, and both are dynamic):

//you must reference System.Data and System.Xml
open System
open System.Data
open System.Data.SqlClient

let bulkLoad (conn:SqlConnection) tableName (columns:list<string * Type>) (rows: list<list<obj>>) =
    use sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, null, BatchSize=500, BulkCopyTimeout=1200, DestinationTableName=tableName)
    sbc.WriteToServer(
        let dt = new DataTable()
        columns
        |> List.iter (dt.Columns.Add>>ignore)

        for row in rows do
            let dr = dt.NewRow()
            row |> Seq.iteri(fun i value -> dr.[i] <- value)
            dt.Rows.Add(dr)
        dt)

//example usage:

//note: since you know all your columns are of type string, you could define columns like
//let columns = ["Field1", "Field2", "Field3"] |> List.map (fun name -> name, typeof<String>)
let columns = [
    "Field1", typeof<String>
    "Field2", typeof<String>
    "Field3", typeof<String>
]

let rows = [
    ["a"; "b"; "c"]
    ["d"; "e"; "f"]
    ["g"; "h"; "i"]
    ["j"; "k"; "l"]
    ["m"; "n"; "o"]
]

//a little funkiness to transform our list<list<string>> to list<list<obj>>, 
//probably not needed in practice because you won't be constructing your lists literally
let rows = rows |> List.map (fun row -> row |> List.map (fun value -> value :> obj))

bulkLoad conn "tblPrior" columns rows

You could get even fancier / more terse using an approach involving reflection. e.g. create a type like

type RowData = { Field1:string; Field2:string; Field3:string }

and make a `bulkLoad` with a signature that takes a `list<'a>` argument such that it reflects over the property names and types of `typeof<'a>` to build the `DataTable` `Columns`, and similarly uses reflection to iterate over all the properties of a row instance to create and add a new row to the `DataTable`. In fact, this question shows how to make a generic `ToDataTable` method that does it (in C#).

Problem

Original source

Related problems

• Convert IEnumerable to DataTable