sql transaction control

[costa100]

Hi again,

I have a requirement to process a bunch of sql tables, and I have to do it in a transaction.

Can you please add a static method WithConnection(cn: SqlConnection, tran: SqlTransaction) in the generated code to control the transaction?

Right now, the only way - that I can see - to control the transaction is to use ConfigureCommand or to set the userConfigureCommand property.

You have this code:

               DbGen.Procedures.dbo.ProcInsert
                  .WithConnection(conn)
                  .ConfigureCommand(fun cmd -> cmd.Transaction <- tran)

If you have one statement, that's fine, but to specify this over and over in different parts of the code, it's getting annoying. The simplest thing to do would be for Facil to provide an override where I can pass the transaction (imo).

What do you think?

I hope I am not missing something, but if you have other suggestions please let me know.

Also, these guys provide such on override: http://fsprojects.github.io/FSharp.Data.SqlClient/transactions.html.

    use tran = conn.BeginTransaction()

    //Implicit assumption that
    assert (tran.Connection = conn)
    //Supply connection and transaction 
    use cmd = new InsertCurrencyRate(conn, **tran**)

[cmeeren]

If you have one statement, that's fine, but to specify this over and over in different parts of the code, it's getting annoying.

I do that over and over again in different parts of my code and don't find that in the least annoying. It's one extra line.

In any case, this sounds trivial to add, so why not.

[cmeeren]

Actually, it's not trivial after all. I thought I could just add a WithConnection overload that accepted a transaction, and call ConfigureCommand after constructing the builder. But that would mean that if the user calls ConfigureCommand themselves, they would override it and the transaction would not be used. Circumventing this would require rewriting some of the script/sproc type internals.

Given how trivial it is to currently use transactions through ConfigureCommand (which has been the intended way to use transactions from the start), this is not something I am currently willing to do.

If several other users complain that using ConfigureCommand is unnecessarily verbose, I may revisit this decision.

[cmeeren]

For future readers: This is now implemented.