SQL is something many of us have gone to great lengths to try to abstract away. The Repository pattern, ORMs, and Type Providers are examples of ab...
For further actions, you may consider blocking this person and/or reporting abuse
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...
Here is what I use to setup the optional type handlers. After that
string option
and many other optional types are handled automatically.Then as part of app startup, I call:
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 ... ;
).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.
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 :)
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.
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:
PS: Am also forced to use Oracle, so would be nice to make that an option :)
Is there any way to avoid boxing when passing int parameters?
Sure. You can manually construct the query to include the data as part of the query. That way, you deal with the types without boxing to construct the query. However, this is considered bad practice because it makes SQL Injection attacks possible.
For parameterized queries, this is currently not possible when using ADO.NET (which is used by Dapper). There is an open issue to use parameters without boxing. I'll have to see what implementation they come up with before I know whether/how it will complicate the functions I posted here.
In practice, I have not found the performance to be a problem... considering that IO is typically slower than boxing perf and there is a hard limit to the number of parameters (~2100). Are you just curious or did you have a specific use case where this was a problem?
It's generally better to avoid additional work for GC, but in this case fsharp type checking forces writing box even for reference types, which is inconvenient, as params are of this type Parameters : (string * obj) list. I have do this for both ints and strings:
[
p "paramInt" (box paramInt)
p "paramString" (box paramString)
]
It is possible to avoid boxing in F#, but there is no point because the value is going to get boxed anyway by the database client (SqlParameter only takes
object
s as values). Avoiding boxing in the F# code will just bloat the code to no benefit.I see, thank you for the clarifications
Hi Kasey, thank you for the useful post.
Is there any problem with or disadvantage to defining p as:
let p name value = (name, (box value))
Which allows you to just type
[ p "paramInt" paramInt ]
Edit: I just re-read the preceding conversation and realized I answered the wrong question. Both yours and @lanayx previously.
No it is not a problem at all for the
p
helper to box the value, to avoid repeatingbox
every time. And in fact that is exactly how I defined it in my own library. I believe it was just an oversight in the post. I will update the post.Leaving the original response below.
I created this tiny helper due to a couple of factors.
obj
anywaySo without the helper, the parameter list looked like this:
Not terrible, but removing characters
, box
to addp
on the front seemed a favorable trade.That ended up being fortuitous because later, using a tuple was not quite enough. Sometimes it is necessary to declare the database type of the parameter. For example, when the value is null. (With Npgsql also when the column is
jsonb
.) So the parameter tuple became a full record type.p
can be changed to work with this, and in a way that does not break existing defined queries. And it will be drastically shorter than spelling out the full SqlParam record.Hi Kasey, thank you for the quick and helpful reply.
Moving from the F# type providers to Dapper has been a real pleasure for me. For the price of giving up the compile time type checking, I now have simple reusable data transfer objects (DTOs) and functions to convert these from domain and back.
Best regards
Roland
Thanks for this! I'm an Elm/OCaml programmer and this really makes me want to learn F#
Nice! I'll have to look into Dapper.