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

Post date: Dec 30, 2011 1:24:51 PM

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) cmd.Connection.Open() let reader = cmd.ExecuteReader() inputs(reader) // 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)