Generic conversion of record array to DataTable and vice versa in F#

Question

I have a simple F# array of records of the following type (or some minor variation relying on system types):

type Transaction= {
    date: DateTime;
    amount: float;
    mutable details: string }

I would like to display them to a DataGridView control in Winforms, including support for changing the content, and adding or deleting rows/records. Binding the DataViewGrid to the array of records does not seem to allow to add/delete rows (or I must be doing it wrong?). However DataTable structures seem to be a better match for this purpose. Creating a DataTable of the above type seems to match quite well the original record type, e.g. :

let dataTable = new DataTable()
    dataTable.Columns.Add("date", typeof<DateTime>) |> ignore
    dataTable.Columns.Add("amount", typeof<float>) |> ignore
    dataTable.Columns.Add("details", typeof<string>) |> ignore)

So I wonder if there is an existing system function to convert an array of records (e.g., type "Transaction array") for any type of record relying on system types into the corresponding DataTable, and vice versa? If there isn't such a function, how could this be done in a concise way?

Answer 1

There is no built-in function for this.

The most direct way to implement the conversion is to use F# reflection, but naive use of reflection (like this) may be quite slow, so you should measure whether the performance is sufficient for what you're trying to do (if it is one off conversion for showing things in the UI, than that's probably fine).

Here is a sketch of how you could do it:

open Microsoft.FSharp.Reflection

let recordsToDataTable (items:'T list) =
  // Create data table and add fields based on the record type
  let dataTable = new DataTable() 
  let fields = FSharpType.GetRecordFields(typeof<'T>)
  for fld in fields do
    dataTable.Columns.Add(fld.Name, fld.PropertyType) |> ignore
  // Add values from the specified list to the table
  for itm in items do
    let row = dataTable.NewRow()
    // Set all fields of the current row
    for fld in fields do
      row.[fld.Name] <- fld.GetValue(itm)
    dataTable.Rows.Add(row)
  dataTable      

let dataTableToRecords (table:DataTable) : 'T list =
  let fields = FSharpType.GetRecordFields(typeof<'T>)
  // Create a list with item for every row in the table
  [ for row in table.Rows ->
      // Get values of all fields from DT and create a record value
      let values = [| for fld in fields -> row.[fld.Name] |]
      FSharpValue.MakeRecord(typeof<'T>, values) :?> 'T ]