Custom conversions

SqlFun allows to intercept parameter creation. That is the moment conversions are applied.
We can either convert particuler value to another type (i.e. generate code for it) and then pass it to default parameter builder, or even create command parameter and add it to a command. E.g. parameter builder converting list of int values to comma separated string could look like this:

let listToStringParamBuilder defaultPB prefix name (expr: Expression) names = 
    match expr.Type with 
    | CollectionOf itemType when itemType = typeof<int> ->
        let convert (list: int list) = list |> List.map string |> String.concat ","
        let convertExpr = Expression.Invoke(Expression.Constant(Func<int list, string>(convert)), expr) 
        defaultPB prefix name (convertExpr :> Expression) names
    | _ ->
        defaultPB prefix name expr names

More complex case is when a value can not be transform to one parameter. For example, list of values can be transformed to many command parameters:

    let listParamBuilder isAllowed defaultPB prefix name (expr: Expression) names = 
        match expr.Type with 
        | CollectionOf itemType when isAllowed itemType ->
            [
                prefix + name,
                expr,
                fun (value: obj) (command: IDbCommand) ->
                    let first = command.Parameters.Count
                    for v in value :?> System.Collections.IEnumerable do
                        let param = command.CreateParameter()
                        param.ParameterName <- "@" + name + string(command.Parameters.Count - first)
                        param.Value <- v
                        command.Parameters.Add(param) |> ignore
                    let names = [| for i in 0..command.Parameters.Count - first - 1 do
                                      yield "@" + name + string(i) 
                                |] |> String.concat ","
                    let newCommandText = command.CommandText.Replace("@" + name, names)
                    command.CommandText <- newCommandText
                    command.Parameters.Count
                ,
                [ getFakeValue itemType ] :> obj
            ]       
        | _ ->
            defaultPB prefix name expr names

Of course, when more parameters are needed, the original SQL command must be modified, which is implemented in the example above, in lines 14-16.

To make everything work, instead of default paramBuilder, the composition with custom one should be used in configuration code:

    let createConnection () = new SqlConnection(connectionString)

    let generatorConfig = 
        let defaultConfig = GeneratorConfig.Default createConnection
        { defaultConfig with paramBuilder = myParamBuilder <+> defaultConfig.paramBuilder }
    
    let sql commandText = sql generatorConfig commandText

    let proc name = proc generatorConfig name

    let buildQuery ctx = FinalQueryPart(ctx, generatorConfig, string)