For comparison sake, I will continue to use SQLite and Bitcoin trade data as I did in the previous post. Since Dapper is a light-weight ORM that leverages classes, it makes data access much cleaner than just using a raw SqlReader. Its implementation also facilitates building some functional wrappers around the database calls. Note, there will be some repetive setup code in the beginning of the post, since the basic database creation and setup has not changed.
Using Paket, here is the paket.dependencies file.
The initial setup is similar to the previous post, with the major addition of Dapper references. The code is identical to the previous post. I will use the same
TradeData record type and sample data. Dapper extends the sql connection object, so I perform database creation and connection opening as I did before.
Now, the new stuff. I will look at inserting first. Dapper passes parameters as anonymous classes. This has several implications. For my purpose, this means I can use the
TradeData type and its fields will auto-map to the parameters in the
connection.Execute call. As before, I use
sum to total up the records inserted.
let insertTradeSql =
This works, but it would be nice to have a more functional interface. To do that, I create a
dbExecute that takes a connection, sql string, and data object. Before I run more inserts, I delete the previously added records, just to keep things clean.
// Clear out records from insert1, so I don't end up with duplicate records
Now that there are records in the database, it is time to perform a parameterized select. Dapper’s
Query uses generics to return a sequence of the specified class type, or in this case, F# type
TradeData. This is a convenient method to access the results. As before, I could pass a type in for the parameters, but I would need to custom build a type for any query. A more dynamic alternative is to use an
IDictionary<string, obj> for the key/value pairs, where key is the parameter name and value is the value of the parameter. The big note is the value must be an object, and it must be boxed. This isn’t a problem, more of a syntax annoyance.
let filteredSql =
This is all well and good, but it doesn’t feel very idiomatic. It can better; for this I add two supporting components. First, the fat arrow provides a cleaner syntax for building the key/value pair. Second, a
dbQuery function similar to the previous
dbExecute function. To make it flexible, the output type is a generic, and the input parameters are optional. After all this, the end result is the same, but the code just feels cleaner.
let inline (=>) k v = k, box v
Now that I’m done, close the connection.
Here is the output from the above actions.
As you can see, leveraging Dapper not only provides a cleaner interface, but facilitates a more functional approach to database interaction. Hopefully you have found this short exploration into F# and Dapper useful. Until next time.