Just Code‎ > ‎

F# - Generic function to query a database with dynamic return object type

posted Dec 30, 2011, 5:24 AM by Peter Henell   [ updated Jan 25, 2012, 5:14 AM ]
This method allows for running queries. You will supply a mapping function that will handle building of the object that you wish to have returned.

In this example i am querying the TestResult table and is only interested in the two fields: Name and Result. In my mapping function i create a tuple with those two fields.

The return type of the ExecuteResultQuery is List 'a. Homework is to make it return a lazy sequence instead.
// Before we can use this we need to add a reference to System.Data.
open System.Data.SqlClient

// Our module SQLDataAccess should handle all the queries to the database.
module SQLDataAccess =
    let ExecuteResultQuery query mapfunc =
        let ReadRow(reader) =
            mapfunc reader
        // We are creating a list here containing all the created objects from the resultset
        let rec inputs(reader:SqlDataReader) = [
            if (reader.Read() = true) then
                yield ReadRow(reader) 
                yield! inputs(reader)           
        use con = new SqlConnection("Data Source=localhost;Initial Catalog=tSQLt_Example;Integrated Security=SSPI;")
        use cmd = new SqlCommand(query, con)
        let reader = cmd.ExecuteReader()
// Example of usage:        
// Create the mapping function that will create whatever object we want to get out of the sql query.
// In this case we get a tuple of (string, string).
// For clarity, i have named the fields.
let TestResultMap (reader:SqlDataReader) = 
    let (Name, Result) = (reader.GetString(0), reader.GetString(1))
    (Name, Result)
// Execute the sql statement, using our mapping functions to decide how the return object should be created, 
// then iterate over the results to print the values     
SQLDataAccess.ExecuteResultQuery "select Name, Result from [tSQLt].[TestResult]" TestResultMap
|> Seq.iter (fun value -> printfn "%O" value)