TransactionBuilder about donald

[pimbrouwers]

I'm so happy to hear that it worked out for you. It's been nice working together!

Your wish is my command:

Donald/src/Donald.Tests/Tests.fs

Line 412 in d457627

member __.``dbResult {...} for...do loop`` () =

If you enjoy the lib, I'd love a star. And tell all your friends as they say :)

[FoggyFinder]

uh, and Combine method is required

A test that doesn't compile currently:

    [<Fact>]
    member __.``dbResult {...} for...do loop with return`` () =
        let cmds = 
            [1..10]
            |> Seq.map (fun n -> dbCommand conn {
                cmdText (sprintf "SELECT %i" n)
            })

        dbResult {
            for cmd in cmds do
                cmd |> Db.scalar Convert.ToInt32 |> ignore

            return 
                cmd |> Seq.head |> Db.scalar Convert.ToInt32
        }
        |> shouldNotBeError ignore

If you enjoy the lib, I'd love a star.

I was a sixth one if github keeps the order in the stargazers list

[pimbrouwers]

Hello again!

Transactional work is already supported through the command expression. See this example:

// Safely begin transaction or throw CouldNotBeginTransactionError on failure
use tran = conn.TryBeginTransaction()

// Build and execute the IDbCommand
dbCommand conn {
    cmdText  "INSERT INTO author (full_name)"
    cmdParam [ "full_name", SqlType.String "John Doe" ]
    cmdTran  tran
}
|> Db.exec // DbResult<unit>

// Attempt to commit, rollback on failure and throw CouldNotCommitTransactionError
tran.TryCommit()

// OR, safely rollback
tran.TryRollback()

[FoggyFinder]

Transactional work is already supported through the command expression. See this example:

I'm aware but sample doesn't cover case with uh, let's say 2, 3 and more queries

For now my code looks like (parameters are omitted, names are different)

let addSomething obj1 obj2 = 
    use conn = openConn()
    use tran = conn.TryBeginTransaction()
    
    let res = 
        dbCommand conn {
            cmdText SQLQueries.someQuery1
            cmdParam [ ... ]
            cmdTran tran
        }
        |> Db.execSingle
    match res with
    | Result.Ok number ->
        let r2 = 
            dbCommand conn {
                cmdText SQLQueries.someQuery2
                cmdParam [ ... ]
                cmdTran tran
            }
            |> Db.execSingle
        match r2 with
        | Result.Ok id ->
            let r3 =
                dbCommand conn {
                    cmdText SQLQueries.someQuery3
                    cmdParam [ ... ]
                    cmdTran tran
                }
                |> Db.execSingle
                |> function | Result.Ok _ -> true | _ -> false

            if r3 then
                if tran.TryCommit () then
                   { obj1 with Id = id ; Number = number } |> Some
                else
                   None
            else
                tran.TryRollback () |> ignore
                None
        | Result.Error error ->
            tran.TryRollback () |> ignore
            None
    | Result.Error error ->
        tran.TryRollback () |> ignore
        None

maybe I use it in a wrong way?

[pimbrouwers]

Ahh the dreaded pyramid of doom! I understand now. You have multiple dependent commands executing within a transaction.

Without seeing your SQL I cannot tell whether this applies or not. But I personally will attempt to write most (if not all) work like this as a single SQL statement that will either all succeed (or fail) as a unit (in SQL Server XACT_ABORT ensure this for example). Since, as your code insinuates, this is often how these scenarios work anyway. It's much simpler to test and maintain in my experience.

That said, I fully acknowledge this is not always possible and I believe this example perfectly demonstrates the need for
DbResult.bind, DbResult.map functions and an associated computation expression dbResult { ... }.

With that in place your code could become (keep in mind I wrote this in notepad):

let addSomething obj1 obj2 = 
    use conn = openConn()
    use tran = conn.TryBeginTransaction()

    let cmd1 = dbCommand conn {
        cmdText SQLQueries.someQuery1
        cmdParam [ ... ]
        cmdTran tran
    }

    let cmd2 = dbCommand conn {
        cmdText SQLQueries.someQuery2
        cmdParam [ ... ]
        cmdTran tran
    }

    let cmd3 = dbCommand conn {
        cmdText SQLQueries.someQuery3
        cmdParam [ ... ]
        cmdTran tran
    }
    
    dbResult {
        let! res = cmd1 |> Db.execSingle

        let! r2 = cmd2 |> Db.execSingle

        let! r3 = 
            cmd3
            |> Db.execSingle
            |> function | Result.Ok _ -> true | _ -> false

        if r3 then
            tran.TryCommit () |> ignore // this will rollback for you on failure
            Some { obj1 with Id = id ; Number = number }             
        else
            tran.TryRollback () |> ignore
            None
    }

As an aside, I'd inject the conn and tran as function parameters to make the dependencies clearer and more testable. But not core to the problem here.

[pimbrouwers]

Just added this in. You can test this out in the 6.1.0-beta1 package if you'd like! Would love your feedback!

[FoggyFinder]

Since, as your code insinuates, this is often how these scenarios work anyway. It's much simpler to test and maintain in my experience.

100%. I just don't know how to do it properly with SQLite. I'll add this to a task list of this project though. But since it is mainly for my use only I doubt I get to it soon.

Just added this in. You can test this out in the 6.1.0-beta1 package if you'd like! Would love your feedback!

Wow! If I knew that it would be so fast I didn't spend so much time to write such awful code like the one above :-)

Just one thing: currently For method isn't supported, i.e.

let commands =
    xs
    |> Seq.map(fun item ->
        dbCommand conn {
            cmdText SQLQueries.someQuery
            cmdParam [ ... ]
            cmdTran tran
        }
    )

for command in commands do
    command
    |> Db.exec

Otherwise works smoothly. At least at first glance.

[pimbrouwers]

Doh! I'll get that added in.

(And thank you for you very early support)

[FoggyFinder]

Works like a charm