Discussion on: Dirt simple SQL queries in F#

View post

This looks really good -- in my attempts I am having trouble with null values. I've tried the linked OptionHandler but doesn't seem to work...

Are you able to provide an example handling Options<string>?

Also, have you considered releasing what you've got as a library?
If not, i might once I can work out the details...

Kasey Speakman • Jan 22 '18 • Edited

Here is what I use to setup the optional type handlers. After that string option and many other optional types are handled automatically.

namespace Foo.MsSql

open System
open Dapper

type OptionHandler<'T>() =
    inherit SqlMapper.TypeHandler<option<'T>>()

    override __.SetValue(param, value) = 
        let valueOrNull = 
            match value with
            | Some x -> box x
            | None -> null

        param.Value <- valueOrNull    

    override __.Parse value =
        if isNull value || value = box DBNull.Value then
            None
        else
            Some (value :?> 'T)


module DapperConfig =

    let RegisterOptionTypes () =
        SqlMapper.AddTypeHandler(new OptionHandler<bool>())
        SqlMapper.AddTypeHandler(new OptionHandler<int>())
        SqlMapper.AddTypeHandler(new OptionHandler<int64>())
        SqlMapper.AddTypeHandler(new OptionHandler<string>())
        SqlMapper.AddTypeHandler(new OptionHandler<Guid>())
        SqlMapper.AddTypeHandler(new OptionHandler<DateTime>())
        SqlMapper.AddTypeHandler(new OptionHandler<single>())
        SqlMapper.AddTypeHandler(new OptionHandler<double>())
        SqlMapper.AddTypeHandler(new OptionHandler<decimal>())

Then as part of app startup, I call:

    DapperConfig.RegisterOptionTypes()

To me this is still a bit too jagged of an edge. Probably the way around having to call something at app startup is to include the initialization as a let statement in the Sql module. But I am not sure if it would get optimized away in a production build, since it will never get referenced.

One other gotcha: The column order in the returned query data must match the property order on F# records. Maybe this is because of the way the F# record constructor is generated, but Dapper cannot figure it out if the returned data is in a different order.

I will setup a github repo for it tonight, time permitting. I can include multi queries too (i.e. SELECT ... ; SELECT ... ;).

Kasey Speakman • Jan 23 '18

Created an initial repo. Still need to figure out how to make a NuGet package, add examples, etc.

SlimSql - seems a stupid name, but best I could come up with tonight.

Adam Davies • Mar 13 '18

Thanks for this!

I had the project on hold that could use this, but am restarting it now.

I currently have bits of code from your blogpost, plus other bits I scrapped together, as I discover things I need.

When it settles down I will try and switch to SlimSql... and perhaps add a PR or two :)

Kasey Speakman • Mar 13 '18

Sure thing. I need to figure out how to execute the startup code so the user doesn't have to wire it into their app startup. I hate that Dapper requires this.

Adam Davies • Mar 14 '18

FWIW I am currently calling this as part of opening the connection. The result is a noop since the method checks if the typehandler already exists in the SqlMapper.typeHandlers dictionary.

Looks something like this:

    let registerTypeHandlers() = 
        SqlMapper.AddTypeHandler(OptionHandler<Guid>())
        SqlMapper.AddTypeHandler(OptionHandler<int64>())
        ...

    let dbConnect string = 
        registerTypeHandlers()
        new OracleConnection(string)

PS: Am also forced to use Oracle, so would be nice to make that an option :)