I’ve been playing a lot with F# lately, particularly in the area of financial option modelling, which requires quite a lot of number crunching – a perfect scenario for tinkering around in F# interactive.
However, I need to get data out of my data store, and use it to create collections of records, that represent the data that I need.
This was becoming a little cumbersome, so I thought I’d create a little ORM function to do the trick
open System.Data.SqlClient
open Microsoft.FSharp.Reflection
let BuildData<'T> (connection:string, command:string) =
let conn = new SqlConnection(connection)
let comm = new SqlCommand(command,conn)
let recordType = typeof<'T>
let fieldCount = FSharpType.GetRecordFields(recordType).Length
conn.Open()
let db = comm.ExecuteReader()
let rec populate (reader:SqlDataReader) (l:'T list) =
match reader.Read() with
| false -> l
| _ ->
let vals = Array.create<obj> fieldCount null
ignore(reader.GetValues(vals))
let dataObj = FSharpValue.MakeRecord(recordType,vals) :?> 'T
let x = dataObj::l
populate reader x
let data = populate db []
conn.Close()
data
run the above in interactive, and you’ll get
val BuildData : string * string -> 'T list
now lets give it a spin
type pricedata = { Price : Decimal; Symbol : String; PriceDate : DateTime }
let conn = "Data Source=DEAN-PC\SQLEXPRESS;Initial Catalog=StockData;Integrated Security=SSPI"
let comm = "select price, symbol, pricedate from symboldata where symbol = 'AA'"
let data = BuildData<pricedata>(conn,comm)
so we now have a strongly-typed collection, ready for pumping into our financial modelling functions
or alternatively, you could display the data in a grid :-
open System.Windows.Forms
let grid data =
let form = new System.Windows.Forms.Form(Visible=true,TopMost=true)
let g = new System.Windows.Forms.DataGrid(Dock = DockStyle.Fill, Visible=true)
g.DataSource <- List.toArray data
form.Controls.Add(g)
grid data
and the result is like this
