TraversingDataTables.fsx

//===================================
// Example of using DataTables in F#
//===================================

open System
open System.Data

//===================================
// General DataTable helpers
//
// these methods work for all datatables
//===================================


let setOptionalValue (row:DataRow) (colName:string) = function
    | Some v -> row.[colName] <- v
    | None -> row.[colName] <- DBNull.Value

let getRequired (row:DataRow) (colName:string) :'a = 
    let v = row.[colName] 
    v :?> 'a   // this could throw so be careful of what result type you specify

let getOptional (row:DataRow) (colName:string) :'a option= 
    let v = row.[colName] 
    if v = box DBNull.Value 
    then None
    else Some (v :?> 'a) // this could throw so be careful of what result type you specify

//===================================
// The type I want to use
//===================================

type MyRow = {first:string; last: string; age: int option}


//===================================
// helper to set up a table for this particular type
//
// you could also use reflection to make it more generic
//===================================



// set up a table
let setupTable() = 
    let ds = new DataSet()
    let dt = new DataTable("Contact")
    ds.Tables.Add(dt)

    let firstNameCol = dt.Columns.Add()
    firstNameCol.ColumnName <- "FirstName" 
    firstNameCol.MaxLength <- 50

    let lastNameCol = dt.Columns.Add()
    lastNameCol.ColumnName <- "LastName" 
    lastNameCol.MaxLength <- 50

    let ageCol = dt.Columns.Add()
    ageCol.ColumnName <- "Age" 
    ageCol.AllowDBNull <- true
    ageCol.DataType <- typeof<int>

    // return it
    dt

//===================================
// helpers to put data into a DataTable for this particular type
//
// you could also use reflection to make it more generic
//===================================

/// convert my record to a row
let toDataRow (dt:DataTable) myRow = 
    let row = dt.NewRow()
    row.["FirstName"] <- myRow.first
    row.["LastName"] <- myRow.last
    myRow.age |> setOptionalValue row "Age"
    row

let insertMyRow dt myRow = 
    myRow 
    |> toDataRow dt
    |> dt.Rows.Add

let insertMyRows dt myRows = 
    myRows |> List.iter (insertMyRow dt)

//===================================
// helpers to get data out of a DataTable for this particular type
//
// you could also use reflection to make it more generic
//===================================

let fromDataRow (row:DataRow) = 
    let first = getRequired row "FirstName" 
    let last = getRequired row "LastName" 
    let age =  getOptional row "Age"
    {first=first; last=last; age=age}

let fromDataTable (dt:DataTable) = 
    dt.Rows 
    |> Seq.cast<DataRow>
    |> Seq.map fromDataRow
    |> Seq.toList

//===================================
// test 
//===================================

// set up some F# data
let myRows = [
    {first="Alice"; last="Adams"; age = None}
    {first="Bilbo"; last="Baggins"; age = Some 111}
    ]

// insert the data
let dt = setupTable()
myRows |> insertMyRows dt

// check the internal data by converting it to XML
let tableXml = 
    use sw = new System.IO.StringWriter()
    use tw = new System.Xml.XmlTextWriter(sw)
    tw.Formatting <- System.Xml.Formatting.Indented
    dt.WriteXml(tw)
    sw.ToString()

printfn "%s" tableXml


// get the data out again
let myRows2 = 
    dt |> fromDataTable 

// assert that the input and output are the same

if myRows <> myRows2 then
    printfn "Error: input and output are different"