Example to extend SQLProvider with PSQL transactions

gistfile1.fs

let pkCache = Collections.Generic.Dictionary<string,Column>()
type FSharp.Data.Sql.Runtime.SqlDataContext with
    member o.SayHi with get() = "Hi"
    member this.PKLookup(provider : ISqlProvider, table : Table, con' : Data.IDbConnection option) =
        if pkCache.ContainsKey table.FullName then pkCache.[table.FullName]
        else
            let con = match con' with
                        | Some c when c.State = Data.ConnectionState.Open -> c
                        | Some c-> c.Open();c
            let cols = provider.GetColumns(con,table)
            match cols |> Seq.tryFind (fun c -> c.IsPrimarKey) with
            | Some c -> pkCache.Add(table.FullName,c);c
            | None -> sprintf "No primary key was found for table %s" table.FriendlyName |> failwith
            
        
    member this.PSQLTCreate() =
        let sb = Text.StringBuilder()
        let (~~) (t:string) = sb.Append t |> ignore
        let o' = (this :> FSharp.Data.Sql.Common.ISqlDataContext)
        let provider = this.Provider
        let con = provider.CreateConnection o'.ConnectionString
        let ups = o'.GetPendingEntities() |> List.filter (fun e -> e._State = Created)
        let createInsertCommand (pk,entity:SqlEntity) =                 
                let cmd = (provider).CreateCommand(con,"")
                cmd.Connection <- con 
                let columnNames, values = 
                    (([],0),entity.ColumnValues)
                    ||> Seq.fold(fun (out,i) (k,v) -> 
                        let name = sprintf "@param%i" i
                        printf "(e,k,v) %s,%s,%A" entity.Table.FriendlyName k v
                        let p = provider.CreateCommandParameter(QueryParameter.Create(name,i),v)
                        (k,p)::out,i+1)
                    |> fun (x,_)-> x 
                    |> List.rev
                    |> List.toArray 
                    |> Array.unzip

                sb.Clear() |> ignore
                ~~(sprintf "INSERT INTO %s " (entity.Table.FullName.Replace("[","\"").Replace("]","\"")))

                match columnNames with
                | [||] -> ~~(sprintf "DEFAULT VALUES")
                | _ -> ~~(sprintf "(%s) VALUES (%s)"
                           (String.Join(",",columnNames))
                           (String.Join(",",values |> Array.map(fun p -> p.ParameterName))))

                ~~(sprintf " RETURNING %s;" pk)

                values |> Array.iter (cmd.Parameters.Add >> ignore)
                cmd.CommandText <- sb.ToString()
                cmd
        let queue = ref []
        
        let calcDeps (e : SqlEntity) =
            let elems = ref []
            let rec calcDeps (e : SqlEntity) =
                match !elems |> List.tryFind (fun x -> e = x) with
                | Some _ -> sprintf "Recursive dependency found for %s." e.Table.FriendlyName |> failwith
                | None ->
                    elems := e :: !elems
                    e.References
                    |> Seq.iter(fun (_,ref) ->                
                            match !queue |> List.tryFind (fun x -> x = ref.Entity) with
                            | Some e' -> ()
                            | None -> calcDeps ref.Entity)
                    queue := e :: !queue
            calcDeps e

        ups |> Seq.iter calcDeps
        let updateOrder = !queue |> List.rev |> List.map (fun e -> 
                                                            if con.State <> Data.ConnectionState.Open then con.Open() else ()
                                                            (this.PKLookup(provider, e.Table,Some con),e))
        ups |> Seq.iter (fun (e) -> printf "UP: %s\n" e.Table.FriendlyName)
        updateOrder |> Seq.iter (fun (_,e) -> printf "ENT: %s\n" e.Table.FriendlyName)
        use scope = new System.Transactions.TransactionScope()
        try
            if con.State = Data.ConnectionState.Open then con.Close()
            con.Open()
            updateOrder
            |> List.iter (fun (pk,e) ->
                // con.Open()
                // let pk = this.PKLookup(provider, e.Table,Some con)
                let i = createInsertCommand (pk.Name,e) |> (fun cmd -> Common.QueryEvents.PublishSqlQuery cmd.CommandText;cmd.ExecuteScalar())
                match  e.GetColumnOption pk.Name with
                | Some _ -> ()
                | None -> e.SetColumnSilent(pk.Name, i)               
            )
            scope.Complete()
        finally
            con.Close()

sample create

let DB = SqlDataProvider<...>
let ctx = DB.GetDataContext()

let identity = ctx.``[PUBLIC].[IDENTITY]``.Create("username","password")
let user = ctx.``[PUBLIC].[USER]``.Create("Name","Last",identity)

// the `idenity` and `user` are created inside a single transaction
ctx.PSQLTCreate()