Dynamic in F#: Reading data from SQL database
When reading data from a SQL database in F#, you have a couple of options.
You can use F# implementation of LINQ, which allows you to write queries directly
in the F# language or you can use standard ADO.NET classes such as SqlCommand.
The second option is often good enough - when you just need to call a simple
query, you probably don't want to setup the whole LINQ infrastructure (generate
classes, reference F# PowerPack, etc.) Unfortunately, ADO.NET classes are a bit
ugly to use. When calling an SQL stored procedure, you need to specify the name
of the procedure as a string, you need to add parameters one-by-one by creating
instances of SqlParameter, you need to dynamically cast results from
the obj type and so on...
In this article, we'll look how to use the dynamic operator to make the experience of using ADO.NET from F# dramatically better. Dynamic operator (there are actually two of them) are a simple way of supporting dynamic invoke in F#. We can use it to write code that looks almost like an ordinary method call or property access, but is resolved dynamically at runtime (using the name of the method or property). The following example shows what we'll be able to write at the end of this article:
// Call 'GetProducts' procedure with 'CategoryID' set to 1 use conn = new DynamicSqlConnection(connectionString) use cmd = conn?GetProducts cmd?CategoryID <- 1 conn.Open() // Read all products and print their names use reader = cmd.ExecuteReader() while reader.Read() do printfn "Product: %s" reader?ProductName
If you ever tried to call a SQL stored procedure directly using the
SqlCommand, then you can surely appreciate the elegance of this code
snippet. Let's now take a look at a larger example and some of the neat
tricks that make this possible...
Calling stored procedure: The usual way
We'll start the article by looking at a single, more complete, example of calling an SQL stored procedure. I wrote this while porting the Personal Web Site Starter Kit to F# (which I'll describe in the next blog post). The project has a simple photo gallery that contains albums and each album contains some photos. When reading photos from the database, we can nicely store them in the following F# record type:
// Record type that represents photo with caption type Photo = { PhotoID:int PhotoAlbumID:int PhotoCaption:string }
Now that we have a type to represent photos, we can look at a function
that reads photos from the database. Our database has an SQL stored
procedure called GetPhotos that takes two parameters. The
parameter AlbumID specifies the ID of the album and the
parameter IsPublic specifies whether we want to get only
publicly accessible photos in the gallery. The following function calls the
stored procedure and reads the returned photos. The whole function body is
wrapped in a sequence expression, so the return type will be seq<Photo>:
// Function that obtains photos in a specified album from database let GetPhotos (albumID:int) = seq { // Create a command to call SQL stored procedure use conn = new SqlConnection(connectionString) use cmd = new SqlCommand("GetPhotos", conn) cmd.CommandType <- CommandType.StoredProcedure // Add parameters to the stored procedure call cmd.Parameters.Add(SqlParameter("@AlbumID", albumID)) |> ignore cmd.Parameters.Add(SqlParameter("@IsPublic", true)) |> ignore // Run the command and read results into an F# record conn.Open() use reader = cmd.ExecuteReader() while reader.Read() do yield { PhotoID = unbox (reader.["PhotoID"]) PhotoAlbumID = unbox (reader.["AlbumID"]) PhotoCaption = unbox (reader.["Caption"])} }
As you can see, we first create an instance of the SqlConnection
type, then create a new command and set its type to StoredProcedure.
Then we need to specify parameters of the procedure, which is done by calling
cmd.Parameters.Add, which takes SqlParameter as an argument.
To read the result, we get SqlDataReader and then repeatedly call
Read. When processing a single result row, we need to use the
indexer to access individual columns of the result by name. The indexer returns
the data as value of type obj, so we use unbox to cast
the result to the type expected by the record.
There are no particularly tricky places in the code, but if you look at it,
there is quite a lot of noise that doesn't express anything important. For
example, we need to create SqlCommand explicitly (giving it the name
of the procedure as a string). When adding a parameter, we need to create
an object (giving it the name of the parameter as a string), call a method
and ignore the result. And finally, when reading the results, we need to insert
explicit conversion.
You could try writing a function for creating the SQL command to make the code
simple. However, there are still some problems. For example, if you decided to
pass parameters of the procedure as a list of name-value pairs, the value would
have to be of type obj and you would need to add explicit boxing.
Luckily, we can throw all this noise away using the dynamic invoke operator...
Calling stored procedure: Using dynamic operator
Let's look how to write the same function using the dynamic operator. In order to make this work, you need to get an implementation of the operator that is available at the end of the article. The file with the implementation also defines several wrapper types that add the dynamic functionality to standard ADO.NET types.
Instead of creating
standard SqlConnection, we need to create an instance of the
DynamicSqlConnection type (we'll look at why this is needed and
how does it work soon). Once we do this change, we can use ?
to create commands representing SQL procedure calls, to specify parameters
of the procedure and also to access columns when reading the result:
// Function that obtains photos in a specified album from database let GetPhotos (albumID:int) = seq { use conn = new DynamicSqlConnection(connectionString) use cmd = conn?GetPhotos cmd?AlbumID <- albumID cmd?IsPublic <- true // Run the command and read results into an F# record conn.Open() use reader = cmd.ExecuteReader() while (reader.Read()) do yield { PhotoID = reader?PhotoID PhotoAlbumID = reader?AlbumID PhotoCaption = reader?Caption } }
Let's now look at the most interesting places in the code where we use the dynamic invoke operator:
- Creating commands (e.g.
conn?GetPhotos) - Here we use the operator to create a command that will call the SQL stored procedure namedGetPhotos. This snippet doesn't call the procedure yet - it only returns a command, so that we can specify parameters of the procedure and decide how to call the command (in our example, we're usingExecuteReaderto get access to the returned result set). - Setting parameters (e.g.
cmd?AlbumID <- albumID) - This is an example of using an operator?<-, which sets a value of dynamically accessed property. We're using it to specify parameters of the command created in the previous step. - Accessing results (e.g.
reader?PhotoID) - In this case, we're using dynamic access to get a value of a column with the specified name. The result type of the operator is generic and the implementation automatically converts the result to the specified type. Since the F# compiler infers the type of the result from the context (e.g. when assigning result to a field of a specific type), the type casting is done fully automatically.
Looking under the cover
We're using the ? operator in two ways. It can be called on an object
representing SQL connection (e.g. conn?GetPhotos) in which case it
returns a command for calling a stored procedure. It can be also called on an
object representing SQL data reader (e.g. reader?PhotoID) in which case
it accesses column from the result set. As a result, we cannot write a single
global declaration of the operator (using the let keyword), but we need
to associate the implementation with some type. However, we cannot add operators
to existing .NET types, so we create simple wrappers for the well-known ADO.NET
types.
Let's start by looking at the DynamicSqlDataReader type, which
wraps a SqlDataReader. We didn't need to use this type directly in the
previous examples, because the wrapping is done automatically - when we invoke
ExecuteReader on a wrapped command object, it automatically wraps the
returned data reader. The implementation is slightly simplified (you can get
a full version at the end of the article), but it shows the two most important
things. Firstly, we expose the method Read for iterating through the
result set and secondly, we implement the ? operator for accessing
results. (Note that the operator must be static, so we need to expose
the wrapped reader using a private property):
// SqlDataReader wrapper that provides access to columns // of the result-set using dynamic access operator type DynamicSqlDataReader(reader:SqlDataReader) = member private x.Reader = reader member x.Read() = reader.Read // Read the specified column and casts it to the specified type static member (?) (dr:DynamicSqlDataReader, name:string) : 'R = unbox (dr.Reader.[name])
The implementation of the dynamic operator is quite simple. The only interesting
aspect is the fact that the operator is generic (in the return type) and that
it uses unbox to convert the obtained value from obj
to the type (which will be deduced by F# type inference based on how we use the
result). The next wrapper wraps the SqlCommand type and adds a
support for adding parameters using the ?<- operator:
// SqlCommand wrapper that allows setting properties of a // stored procedure using dynamic setter operator type DynamicSqlCommand(cmd:SqlCommand) = member private x.Command = cmd // Adds parameter with the specified name and value static member (?<-) (cmd:DynamicSqlCommand, name:string, value) = let p = SqlParameter("@" + name, box value) cmd.Command.Parameters.Add(p) |> ignore // Execute command and wrap returned SqlDataReader member x.ExecuteReader() = new DynamicSqlDataReader(cmd.ExecuteReader())
The implementation is again pretty straightforward. Note that we need to
provide an operator named ?<-, because F# uses different dynamic
access operator when reading properties and when setting value of a property.
The operator takes three parameters - the target command (which needs to expose
the underlying command), a name of the property and a value to be used. We also
expose the ExecuteReader method, which automatically wraps the
returned reader (so that we can dynamically access columns).
The last wrapper is the only one that needs to be created explicitly. It represents
connection to the database and provides an implementation of the ?
operator that can be used for creating commands that call SQL stored procedure
(the returned command is automatically wrapped using the previous type):
// SqlConnection wrapper that allows creating stored // procedure calls using the dynamic access operator type DynamicSqlConnection(connStr:string) = let conn = new SqlConnection(connStr) member private x.Connection = conn member x.Open() = conn.Open() // Creates command that calls the specified stored procedure static member (?) (conn:DynamicSqlConnection, name) = let command = new SqlCommand(name, conn.Connection) command.CommandType <- CommandType.StoredProcedure new DynamicSqlCommand(command)
The type exposes the Open method of the underlying connection and
implements the dynamic access operator ?. The implementation of the
operator constructs a new command, specifies its type and wraps it using the
DynamicSqlCommand type (so that we can set parameters of the stored
procedure call).
Summary & downloads
In this article, we've seen how to make accessing data using ADO.NET from F# nicer using dynamic operators. This allows us to reduce the number of noise that has to be written in tasks related to data-access such as creating SQL commands, specifying parameters of a stored procedure and reading data from the returned result-set.
We've also seen one useful technique of implementing dynamic access in general.
Because we needed to provide different implementation of the operator for different
types, we couldn't use a global implementation (written using the let
keyword). Instead, we implemented the operator in several wrappers that wrap standard
ADO.NET classes and augment them with the dynamic features. I believe that this technique can be
quite useful in other related scenarios.
Below you can download the implementation of the wrappers that were used and presented in this article. The download doesn't contain any examples (you can use the example from the article as the starting point). I'll post an ASP.NET project that uses the wrappers in another blog post soon...
Published: July 9, 2010 02:03
Tags: Functional Programming in .NET | Web | F# language
Share:
- RE: Dynamic in F#: Reading data from SQL database by Gert-Jan van der Kamp (7/19/2010 10:56:54 PM)
Hi Thomas,
Thanks for this post. I was looking into this very issue a short while back. To me it seems like quite an omission from F# that it handles data rather poorly. When working with historical market data you really don't want to suck that all up into memory before you can work with it.
What's more, a list and a datareader are functionally so similar. There's a current element that we deal with now, and there's a tail with other elements that we worry about when we get there. I tried to create a class to expose a SqlDataReader like this through recursion. Worked too but it ran out of stack space for larger sets because I couldn't get it to tail call. I think you'd need to drop down to compiler/IL level to get this right, but that would really be worth it.
Would definitely be an huge addition to F# to be able to lazy evaluate large datasets. And since a DataReader is so much like a List it shouldn't be too hard to achieve, would it? WHile you're at it, please create a generic infrastructure that can be used for file based data as well.
Pasted the code here, if you can get it to run I will transfer 200 coolness points to your acount.
Regards Gert-Jan
open System.Collections
open System.Data
open System.Data.SqlClient
// wrapper to return a SqlDataReader
let GetReader(sCn, sSql) =
let cn = new SqlConnection(sCn)
cn.Open()
let cmd = new SqlCommand(sSql, cn)
cmd.ExecuteReader()
// a wrapper that takes a reader and visitor and applies it to every row in the reader
let rec ApplyToDataReader (dr:IDataReader, visitor) =
match dr.Read() with
true -> visitor(dr) + ApplyToDataReader(dr, visitor)
|false -> 0
// just count the rows for now, return 1 for each row
let myVisitor myObj =
1
// this actually works for small sets but throws a stackoverlfow for larger datasets
let res = ApplyToDataReader(GetReader("server=.;database=kampie;integrated security=true","select top 100 * from big"), myVisitor) - RE: Dynamic in F#: Reading data from SQL database by Tomas (7/20/2010 1:43:13 AM)
@Gert-Jan: To make your example tail-recursive, you need to modify the ApplyToDataReader function - currently, you're first calling visitor and (recursively) ApplyToDataReader and then add the results using + operator. In a tail-recursive version, you'd need to pass the result of visitor call to the recursive function (search for "accumulator parameter" for more information).
Anyway, this looks like a neat idea - however, one difference between functional lists and data readers is that functional lists are immutable, while data reader changes when you read the next record (you cannot read the same line multiple times). For this reason, I think that F# seq<'T> type is a more natural representation. - RE: Dynamic in F#: Reading data from SQL database by Gert-Jan van der Kamp (7/21/2010 9:47:34 AM)
Thanks, it works like a charm now! The call obviously wasn't in tail position if i'm going to add it to another number, can't believe I missed that.
You actually seem to know this stuff ;-) I'll look at the seq<'T> approach as well.
Thanks again,
Gert-Jan
P.S: For whoever's interested, here's the working code:
open System.Collections
open System.Data
open System.Data.SqlClient
// wrapper to return a SqlDataReader
let GetReader(sCn, sSql) =
let cn = new SqlConnection(sCn)
cn.Open()
let cmd = new SqlCommand(sSql, cn)
cmd.ExecuteReader()
// a wrapper that takes a reader and function and applies the function to every row in the reader accumulating the result
let rec ApplyToDataReader (dr:IDataReader, func, accum) =
match dr.Read() with // is there a next row?
true -> ApplyToDataReader(dr, func, accum + func(dr)) // then pass the reader, function and the accumulator to the next call
|false -> accum // last row: return the result
// just count the rows for now, return 1 for each row
let myFunc currentRow = 1
// open a SqlDataReader
let rdr = GetReader("server=.;database=kampie;integrated security=true","select top 100000 * from big")
// and apply the counting function to each row
let res = ApplyToDataReader(rdr, myFunc, 0)
- RE: Dynamic in F#: Reading data from SQL database by (7/21/2010 3:57:06 PM)
Just to let you know, worked out this idea some more and wrote an article on CodeProject on it.
http://www.codeproject.com/KB/net-languages/FS_Recursion_on_Reader.aspx
Thanks again,
GJ - RE: Dynamic in F#: Reading data from SQL database by Adrian Mok (7/22/2010 6:51:23 AM)
Thanks you for your great article. I tried to run the code. I have an error in DynamicSqlDataReader.
static member (?) (dr:DynamicSqlDataReader, name:string) : 'R =
unbox (dr.Reader.[name])
The following is the error message:
InvalidCastException was unhandled by user code
Specified cast is not valid.
When casting from a number, the value must be a number less than infinity. Make sure the source type is convertiable to he destination type.
Thanks again,
Adrian Mok - RE: Dynamic in F#: Reading data from SQL database by Tomas (7/22/2010 7:37:24 PM)
@Adrian: It looks like the value that you're getting from the underlying SqlDataReader cannot be converted to the type (that was inferred based on the context). The error message suggests that you have infinity/nan as the value in the database - could that be the case? That would be tricky, because the standard unbox function doesn't seem to handle this case - I guess it would be possible to handle this as a special case...
- RE: Dynamic in F#: Reading data from SQL database by Adrian (7/26/2010 6:10:01 AM)
I found out the data type of column of the table in the database is smallint caused the problem. Because if I changed it from smallint to int. The complier does not complain.
- RE: Dynamic in F#: Reading data from SQL database by Nick (9/27/2010 7:56:43 PM)
Hi,
This is a great piece of code. However, when you create an instance of the DynamicSqlConnection, a stack overflow exception is thrown because of :
new (connStr:string) =
new DynamicSqlConnection(new DynamicSqlConnection(connStr))
A quick change to new (connStr:string) =
new DynamicSqlConnection(new SqlConnection(connStr)) sorted the problem
