Expose a PrepareRawCommand method on SqlCommand objects

[smoothdeveloper]

Due to specifics of return value, I sometimes need to use manually created System.Data.SqlClient.SqlCommand objects that I obtain from typed SqlCommand objects, I'd like to actually still leverage type checking on input parameters of those.

I'm thinking adding a PrepareRawCommand method with parameters matching the Execute method and returning the raw command would avoid cases where I need to set a list of parameters without compile time checks on the name and type of values.

Would such an addition to the API being considered if I provide the implementation?

[dsevastianov]

Can you please elaborate a bit on the specifics of return value. There are known limitations, e.g. #246, if demand for this functionality is high we can have closer look on those. The idea by itself looks viable on the first glance. I would like to understand usecase better first though.

[smoothdeveloper]

Instead of this:

let GetRawCommand<'a when 'a :> ISqlCommand> (cmd : 'a) = 
  let command = cmd.Raw.Clone()
  for p in command.Parameters do
    if p.ParameterName.StartsWith("@") then
      p.ParameterName <- p.ParameterName.Substring(1)
  command

let sampleCode (param1: int) (param2: DateTime) =
    let command : Database.dbo.my_command = CreateCommand context // CreateCommand is my own constructor taking a context object
    let rawCommand = GetRawCommand command
    rawCommand
        .SetParameterValue("param1", param1)
        .SetParameterValue("param2", param2)
    // use rawCommand the way I like

I'd like to be able to do this:

let sampleCode (param1: int) (param2: DateTime) =
    let command : Database.dbo.my_command = CreateCommand context // CreateCommand is my own constructor taking a context object
    let rawCommand = command.PrepareRawCommand(param1 = param1, param2 = param2)
    // use rawCommand the way I like

This allow me to leverage type checking on the input parameters while still handling the execution of the command myself.

One reason is that some command return type will be int unless I specify with result sets (which I can't use since I have to support SQL 2008).

[dsevastianov]

It looks reasonable. Unless @dmitry-a-morozov has other concerns I think such addition can be valuable to address corner cases like support for 2008. We'll consider implementation, thanks for working on this!

[smoothdeveloper]

I'm discussing sibbling matters:

https://github.com/fsprojects/FSharp.Data.SqlClient/issues/334#issuecomment-481697157 https://github.com/fsprojects/FSharp.Data.SqlClient/issues/265#issue-210774106

The way https://github.com/rspeele/Rezoom.SQL handles this is to have a split between prepare (which doesn't need any hot context) and execution, and this is a direction to consider for extending this library.

This would also facilitate a bit client code for requirements like having generic retrials or exception management logic compared to how the API works right now.