1
\$\begingroup\$

I'd really like to get this to be as clean as possible such that the C# access to it looks succinct. To that end, I've made C# helper methods that abstract out things like FSharp.Option and FSharpFunc I feel like it's more verbose than it has to be and I'm not talking about the length of the variable names.

I do not have access to .BeginExecuteReader which might make this solution plug-and-play as the project is in .net 4.0

module Foundation.SqlExecution

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

type Connector = 
    | ICon of IDbConnection
    | CString of string
 //of IDataReader
type DbCommandResult<'a> = 
    | NonQuery of int
    | Scalar of obj
    | Reader of 'a
    | ReaderArray of 'a[]


type DbCommand<'a> =
    | NonQuery 
    | Scalar 
    | Reader of (IDataReader -> 'a)
    //| ReaderList of (IDataReader -> 'a list)
    //| ReaderCollection of (IDataReader -> ICollection<'a>)
    | ReaderArray of (IDataRecord -> 'a)

let nullToDbNull (x:obj) = if x <> null then x else upcast System.DBNull.Value
let dbNullToOpt (x:obj) = if not (System.DBNull.Value.Equals x) && x <> null then Some x else None
let optionToNull (x: _ option) = if x.IsSome then x.Value else null
let nullToOption (x:_) = if x <> null then Some x else None

let loadParameters (cmd:'a when 'a :> IDbCommand) (parameters:IDictionary<string,obj> option) =
    if parameters.IsNone || parameters.Value = null then () else
    parameters.Value
    |> Seq.iter (fun (KeyValue(k,v)) -> 
        let param = cmd.CreateParameter()
        param.Value <- nullToDbNull v
        param.ParameterName <- k
        )

let prepareCommand cmdText cmdType parameters (extraPrep:('a -> unit) option) (cmd:'a when 'a :> IDbCommand) = 
    if cmd.Connection.State <> ConnectionState.Open then cmd.Connection.Open()
    cmd.CommandText <- cmdText
    cmd.CommandType <- cmdType
    loadParameters cmd parameters
    if extraPrep.IsSome then extraPrep.Value cmd
    cmd
let exeReader (cmd:'a when 'a :> IDbCommand) f = 
    use reader = cmd.ExecuteReader()
    f reader |> DbCommandResult.Reader

let sql connector prep dbCommand =
    let exec (cmd:'a when 'a:> IDbCommand) = 
        match dbCommand with 
        | DbCommand.NonQuery -> DbCommandResult.NonQuery <| cmd.ExecuteNonQuery()
        | DbCommand.Scalar -> DbCommandResult.Scalar <| cmd.ExecuteScalar()
        | DbCommand.Reader f -> 
            use reader = cmd.ExecuteReader()
            f reader |> DbCommandResult.Reader
        | DbCommand.ReaderArray f ->
            use reader = cmd.ExecuteReader()
            seq{
                while reader.Read() do
                    yield f reader
            }
            |> Array.ofSeq
            |> DbCommandResult.ReaderArray

            //f reader |> DbCommandResult.ReaderArray

    let f (con:'a when 'a :> IDbConnection) =
        use cmd = con.CreateCommand()
        prep cmd
        |> exec

    match connector with 
    | ICon con -> f con
    | CString connectionString -> 
        use con = new SqlConnection(connectionString)
        f con

let executeCommand (connector:Connector) cmdText cmdType map parameters  extraPrep  =
    let f= sql connector <| prepareCommand cmdText cmdType parameters extraPrep
    f DbCommand.NonQuery
    |> ignore

let executeC cmdText cmdType connectionString parameters = sql (Connector.CString connectionString) <| prepareCommand cmdText cmdType (nullToOption parameters) None
// begin C# ease-of-use methods
let ExecuteScalar cmdText cmdType connectionString parameters = executeC cmdText cmdType connectionString parameters
let ExecuteScalarC cmdText cmdType con parameters = sql (Connector.ICon con) <| prepareCommand cmdText cmdType (nullToOption parameters) None

//type ReaderExecution<'t> = string -> CommandType -> string -> IDictionary<string,obj> -> System.Func<IDataReader,'t> -> 't

// not for iteration (yield return methods)
let ExecuteReader<'t> cmdText cmdType connectionString parameters (map:System.Func<IDataReader,'t>) = 
    sql (Connector.CString connectionString) <| prepareCommand cmdText cmdType (nullToOption parameters) None
    <| DbCommand.Reader (fun r -> DbCommandResult.Reader (map.Invoke(r)))
    |> fun e-> match e with | DbCommandResult.Reader x -> x | _ -> failwithf "Invalid Operation returned from reader command"
let ExecuteReaderArray<'t> cmdText cmdType connectionString parameters (map:System.Func<IDataRecord,'t>) = 
    sql (Connector.CString connectionString) <| prepareCommand cmdText cmdType (nullToOption parameters) None
    <| DbCommand.Reader (fun r -> map.Invoke(r))
    |> fun e-> match e with | DbCommandResult.ReaderArray x -> x | _ -> failwithf "Invalid Operation returned from reader command"

Code also available for experimentation in browser

Or a C# version that I'm working towards making the F# replace

Can I refactor this to be more succinct, or make better use of the F# language?

  • not really looking for shorter variable names, but actual substantive refactorings that clean up duplication or features that weren't used appropriately.
\$\endgroup\$
  • \$\begingroup\$ related codereview.stackexchange.com/questions/63480/… \$\endgroup\$ – Maslow Feb 26 '15 at 15:36
  • \$\begingroup\$ Since you're using F#, I highly suggest you take a look at SQL Type Providers or FSharp.Data.SqlClient. Either will be far easier to use in the long run than reproducing low-level SqlDataReader access. \$\endgroup\$ – Dan Lyons Feb 26 '15 at 19:07
  • \$\begingroup\$ agreed, however, I don't have buy-in to convert the entire Dal to F#, I believe I can get the buy-in to convert just the Dal abstraction layer. \$\endgroup\$ – Maslow Feb 26 '15 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.