my ado helper reusable library attempt

AdoHelper.fs

module Pm.Dal.AdoHelper

// desired features 
// "do we have a connectionstring or connection?" ignorance-capable coding
// "are we in a transaction?" ignorance-capable coding
// massive reduction in using blocks necessary
// remove any reliance/requirement to refer directly to System.Data in layers that need not

// conventions: 
//  pascal cased functions if they are specially crafted to be easier for C# to consume
//  F# method params are in an order that makes more sense for partial application
//  C# targeted method params are in an order that makes more sense for C# consumption

// possible future features:
// automatic sql exception catching to add ex.data, then rethrow
// adding nice extension methods to help IDbConnection act as fully featured as SqlConnection (there are some features like cmd.Params.AddWithValue missing in IDbCommand)

open System.Collections.Generic
open System.Data
open System.Data.SqlClient

//[<System.Obsolete("Use Option.toObj")>]
//let optionToNull (x: _ option) = match x with Some x -> x | None -> null  //if x.IsSome then x.Value else null // is this duplicated, better, or worse than what's in Nullable.fs?
let nullToDbNull x = if not <| isNull x then x :> obj else upcast System.DBNull.Value
let nullToOption (x:_) = if not <| isNull x then Some x else None // is this duplicated, better, or worse than what's in Nullable.fs?

let dbNullToOption (x:obj) : obj option= 
    if System.DBNull.Value.Equals x then 
        None
    else Some x

let dbNullToObjNull (x:obj) : obj = 
    match dbNullToOption x with
    |Some x-> x
    |None -> null

//type SqlConnector = 
//    | CString of string
//    | SqlCon of SqlConnection

type System.DBNull with
    static member OfObj (x:obj) = if not <| isNull x then x else upcast System.DBNull.Value
    static member ToObj (x:obj) = dbNullToObjNull x
    static member OfOption (x:obj option) = match x with Some x -> System.DBNull.OfObj x | None -> upcast System.DBNull.Value
    static member ToOption (x:obj) = dbNullToOption x

module Connections = 
    // The heart of the code-ignorance possibilities
//    [<NoComparison>][<NoEquality>]
//    type ConnectorI<'a when 'a :> IDbConnection> =
//        | Create of (unit -> 'a)
//        | ICon of 'a
    [<NoComparison>][<NoEquality>]
    type Connector = 
        | CString of string
        | ICon of IDbConnection

    let inline getCnFromCs cs = Connector.CString cs  //Create (fun () -> new System.Data.SqlClient.SqlConnection(cs))
    //    with
    //        static member NewCCreate (fConn:System.Func<_>) = Connector<_>.Create fConn.Invoke

    let inline openConnection (conn: #IDbConnection) =
        if conn.State = ConnectionState.Closed then
            conn.Open()
    /// Expecations:
    ///     Connector.ICon expects an open connection
    /// as long as you aren't returning an IEnumerable that depends on the connection staying open, this method is safe for anything
    let inline runWithConnection connector f = 
            match connector with
            | ICon con -> f con
            | CString cs -> 
                use conn = new SqlConnection(cs)
                openConnection conn
                f conn

    let inline getItems connector f = runWithConnection connector (f >> Array.ofSeq)

    //let inline runWithCn connector f = f connector

    //let CreateNewCreateConnector (fConn:System.Func<_>) = ConnectorI<_>.Create fConn.Invoke
    /// get a sequence of items, which is automatically pulled into an array so that the disposal of the connection is safe
    let GetItems<'tResult> (runWithConnectionFunc:System.Func<IDbConnection, IEnumerable<'tResult>>) cn = getItems cn (runWithConnectionFunc.Invoke >> Array.ofSeq)
    let ActWithConnection connector (f:System.Action<_>) = runWithConnection connector f.Invoke
    let RunWithConnection connector (f:System.Func<_,_>) = runWithConnection connector f.Invoke
    let RunWithCn connector (f:System.Func<_,_>) = runWithConnection connector (Connector.ICon >> f.Invoke)


type Connector = Connections.Connector

module Transactions =
    [<NoComparison>]
    type Transaction =
    | ConnTran of IDbConnection
    | TranScope
    /// For situations where there is already an ambient connection, so don't create one
    /// assumes all provided code respects the ambient transaction type that was created
    | UseAmbient

    let runInTrans tranType (f: unit -> bool) = 
        match tranType with 
        |UseAmbient -> f () |> ignore //TODO: we have to produce a bool we won't use =(
        |ConnTran con ->
            use tran = con.BeginTransaction ()
            let commit = f ()
            if commit then tran.Commit ()
            else tran.Rollback ()
        |TranScope ->
            use tran = new System.Transactions.TransactionScope ()
            let commit = f ()
            if commit then tran.Complete()

/// all SqlClient specific code should live in this module
module SqlConnections =
    type SqlConnector = Connections.Connector

    /// Expecations:
    ///     Connector.ICon expects an open connection
    /// as long as you aren't returning an IEnumerable that depends on the connection staying open, this method is safe for anything
    let inline runWithConnection (connector:SqlConnector) f = Connections.runWithConnection connector (fun con -> con :?> SqlConnection |> f)
    let inline getInTrans tranType connector f =  Transactions.runInTrans tranType (fun () -> runWithConnection connector f)

    let RunWithConnection connector (f:System.Action<_>)=  runWithConnection connector f.Invoke
    /// as long as you aren't returning an IEnumerable that depends on the connection staying open, this method is safe for anything
    let GetFromConnection connector (f:System.Func<_,_>)= runWithConnection connector f.Invoke
    /// get a sequence of items, which is automatically pulled into an array so that the disposal of the connection is safe
    let GetItems connector (f:System.Func<_,_ seq>) = runWithConnection connector (f.Invoke >> Array.ofSeq)
    let RunInConnectionInTrans tranType connector (f:System.Func<_,bool>) = getInTrans tranType connector f.Invoke
    let GetInConnectionInTrans tranType connector (f:System.Func<_,_>) = 
        let mutable result = null
        getInTrans tranType connector (fun con ->
            result <- f.Invoke con
            true
        )
    /// Use this when you are returning nothing and it should always complete the transaction as long as there are no exceptions
    let ExecuteInTransaction tranType connector (transAction:System.Action<_>) = getInTrans tranType connector (fun con -> transAction.Invoke con; true)

module Commands = 
    [<NoComparison;NoEquality>]
    type Input = {CommandText:string; OptCommandType:CommandType option; OptParameters:IDictionary<string,obj> option}

    [<NoComparison;NoEquality>]
    type InputC = {CommandTextC:string; CommandTypeOpt: System.Nullable<CommandType>; ParametersOpt:IDictionary<string,obj>; }
        with
            member x.ToSqlCommandInput = 
                {       CommandText = x.CommandTextC
                        OptCommandType = if x.CommandTypeOpt.HasValue then Some x.CommandTypeOpt.Value else None
                        OptParameters = if isNull x.ParametersOpt then None else Some x.ParametersOpt 
                        //OptExtraPrep = if isNull x.ExtraPrepOpt then None else Some x.ExtraPrepOpt.Invoke 
                        }
            
    // works with null just as well as `None`
    let loadParameters (cmd: #IDbCommand) (parameters: IDictionary<string,obj> option) =
        let inline loadParam (KeyValue(k,v)) = 
            let param = cmd.CreateParameter ()
            param.Value <- System.DBNull.OfObj v
            param.ParameterName <- k
            cmd.Parameters.Add param |> ignore
        match parameters with
        | None -> ()
        | Some x when isNull x -> ()
        | Some items -> items |> Seq.iter loadParam

    let inline internal logData cmdText params' =
        printfn "SqlCommand: %s params=%A" cmdText params'

    let inline prepareCommand sci (cmd:'a when 'a :> IDbCommand) =
        //let sci = {CommandText=cmdText; CommandType = Some cmdType; Parameters = parameters}
        // Connections.openConnection cmd.Connection // does this belong here? should it be done away with?
        cmd.CommandText <- sci.CommandText
        match sci.OptCommandType with
        |Some ct -> 
            cmd.CommandType <- ct
        | None -> ()
        loadParameters cmd sci.OptParameters
//        match sci.OptExtraPrep with
//        | Some f -> f cmd
//        | None -> cmd

    // sci is solely for diagnostic output on failure
    let inline runWithSqlDiag sci f = 
        printfn "sql: %s params: %A" sci.CommandText sci.OptParameters
        let sw = System.Diagnostics.Stopwatch.StartNew ()
        try
            let result = f ()
            sw.Stop ()
            if sw.ElapsedMilliseconds > 700L then
                    System.Diagnostics.Debug.WriteLine(sprintf "runWithSqlDiag took %A" sw.ElapsedMilliseconds)
            result
        with ex ->
                ex.Data.Add("CommandText", sci.CommandText) 
                ex.Data.Add("CommandType", sci.OptCommandType)
                ex.Data.Add("Parameters", sprintf "%A" sci.OptParameters)
                reraise ()

    let inline useCmd (con: #IDbConnection) sci f = 
        use cmd = con.CreateCommand()
        prepareCommand sci cmd
        runWithSqlDiag sci (fun () -> f cmd)

    let inline executeNonQuery (cmd: #IDbCommand) = cmd.ExecuteNonQuery ()
    let inline executeScalar (cmd: #IDbCommand) = cmd.ExecuteScalar ()
    let inline executeReader (cmd: #IDbCommand) = cmd.ExecuteReader ()
    let inline executeTable (fDataAdapter: _ -> #System.Data.Common.DbDataAdapter) (cmd: #IDbCommand) = 
        let dt = new DataTable()
        use da = fDataAdapter cmd
        let result = da.Fill(dt)
        result |> ignore
        dt

    let inline executeReaderArray f (cmd: #IDbCommand) = 
        use reader = cmd.ExecuteReader ()
        reader
        |> Seq.unfold(fun r -> if r.Read() then Some (r :> IDataRecord |> f,r) else None)
        |> Array.ofSeq

    /// Works with non-nullable return values (an int column that allows nulls for instance, would fail in the case of null)
    let inline getScalarT<'t> cmd =  executeScalar cmd :?> 't

    let inline getNonQueryFromCon con sci= useCmd con sci executeNonQuery

    let inline getScalarFromCon con sci= useCmd con sci executeScalar
    let inline getScalarIntFromCon con sci = useCmd con sci getScalarT<int>

    let inline getTableFromCon fAdapter con sci = 
        let f = executeTable fAdapter
        useCmd con sci f

    // a single runReader doesn't make sense unless reading a single row of data
    let inline getReaderArrayFromCon con sci f= useCmd con sci (executeReaderArray f >> Array.ofSeq)

    // unless you cast the identity to int (within your sql statements) it will be a decimal (http://dba.stackexchange.com/questions/4696/why-is-select-identity-returning-a-decimal)
    /// select @@identity or SCOPE_IDENTITY() both return Numeric(38,0)
    /// see also http://dba.stackexchange.com/questions/4696/why-is-select-identity-returning-a-decimal
    let inline getScalarIdentityFromCon con sci = useCmd con sci (getScalarT<decimal> >> int)


    let UseCmd connector (scic:InputC) (f:System.Func<_,_>) = useCmd connector scic.ToSqlCommandInput f.Invoke
    let RunReaderArray cmd (f:System.Func<_,_>) = executeReaderArray f.Invoke cmd
    let ExecuteReaderArray con (scic:InputC) (f:System.Func<_,_>) = getReaderArrayFromCon con scic.ToSqlCommandInput f.Invoke
    


let inline private flip f x y = f y x

let inline private runComplete f cn (sci:Commands.Input) = Connections.runWithConnection cn (flip f sci)

let getNonQuery cn= runComplete Commands.getNonQueryFromCon cn
let getScalar cn= runComplete Commands.getScalarFromCon cn 
let getScalarInt cn= runComplete Commands.getScalarIntFromCon cn
let getScalarIdentity cn= runComplete Commands.getScalarIdentityFromCon cn
let getReaderArray cn sci f= Connections.runWithConnection cn (fun con -> Commands.getReaderArrayFromCon con sci f)
let getTable fAdapter cn sci = Connections.runWithConnection cn (fun con -> Commands.getTableFromCon fAdapter con sci)

let inline private createScicFromParts cmdText cmdType parameters = 
    let scic : Commands.InputC = {Commands.InputC.CommandTextC = cmdText; CommandTypeOpt = cmdType; ParametersOpt = parameters}
    scic
let inline private createSciFromParts cmdText cmdType parameters = 
    let scic = createScicFromParts cmdText cmdType parameters
    scic.ToSqlCommandInput

let GetReaderArray cn (scic:Commands.InputC) (f:System.Func<_,_>) = getReaderArray cn scic.ToSqlCommandInput f.Invoke

let ExecuteScalar cmdText cmdType cn parameters =           createSciFromParts cmdText cmdType parameters       |> getScalar cn
let ExecuteScalarInt cmdText cmdType cn parameters =        createSciFromParts cmdText cmdType parameters       |> getScalarInt cn
let ExecuteScalarIdentity cmdText cmdType cn parameters =   createSciFromParts cmdText cmdType parameters       |> getScalarIdentity cn
let ExecuteNonQuery cmdText cmdType cn parameters =         createSciFromParts cmdText cmdType parameters       |> getNonQuery cn
let ExecuteTable cmdText cmdType cn parameters fAdapter =   createSciFromParts cmdText cmdType parameters       |> getTable fAdapter cn

let ExecuteReaderArray cmdText cmdType cn parameters f =    createScicFromParts cmdText cmdType parameters      |> GetReaderArray cn <| f

/// If you are using Microsoft's Sql Server specifically and need that functionality, or just find it easier to work with fewer generic params
module SqlCommands = 
    let inline getSqlCommandInput (scic:Commands.InputC) = scic.ToSqlCommandInput
    let inline createAdapter (cmd:SqlCommand) = new SqlDataAdapter(cmd)
    let inline useSqlCmd (con:SqlConnection) sci f= 
        use cmd = con.CreateCommand()
        Commands.runWithSqlDiag sci (fun () -> f cmd)

    let inline private runComplete f (cn:Connections.Connector) (sci:Commands.Input) = runComplete f cn sci

    // begin ease of use (generic parameters getting to be unwiedly) helpers for C#
    let ExecuteNonQuery cn scic = getSqlCommandInput scic |> runComplete Commands.getNonQueryFromCon cn
    let ExecuteScalar cn scic = getSqlCommandInput scic |> runComplete Commands.getScalarFromCon cn
    let ExecuteScalarInt cn scic = getSqlCommandInput scic |> runComplete Commands.getScalarIntFromCon cn 
    let ExecuteScalarIdentity cn scic = getSqlCommandInput scic |> runComplete Commands.getScalarIdentityFromCon cn

    let ExecuteReaderArray scic (f:System.Func<_,_>) cn = getReaderArray cn (getSqlCommandInput scic) f.Invoke
    let ExecuteReaderArraySci commandText commandType cn parametersOpt f = ExecuteReaderArray {CommandTextC = commandText; CommandTypeOpt = System.Nullable commandType; ParametersOpt = parametersOpt} f cn
    let ExecuteTableCon scic con= useSqlCmd con (getSqlCommandInput scic) (Commands.executeTable createAdapter)
    let ExecuteTable scic cn = SqlConnections.runWithConnection cn (fun con -> ExecuteTableCon scic con)
    let ExecuteTableM cmdText cmdType cn parameters = createScicFromParts cmdText cmdType parameters |> (fun scic -> ExecuteTable scic cn)

module ConnectionTests = 
    let openCon cs =
        use con = new SqlConnection(cs)
        con.Open()