Using a single transaction for multiple method C#?

Question

Lets say I got an Insert-button where I got multiple method within it, where they read, insert and update etc in the database. Is it possible to use a single transaction for all these called methods? Like:

private void method_A(){/* doing tons of db stuff.. */}
private void method_B(){/*..*/}
private void method_C(){/*..*/}


protected void Insert_OnClick(object sender, EventArgs e)
{
    //begin transaction

    Method_A();

    Method_B();

    Method_C();

    //end transaction

}

Is this way possible? Never used transaction before. Btw using MS Access db if that matters.

Have you seen [this](http://stackoverflow.com/questions/2078432/do-we-have-transactions-in-ms-access) ? — PaulF, Jun 23 '16 at 09:00
@PaulF it doesn't show how to implement transaction via C#, or would it work if I simply add `BEGIN TRANSACTION` .. C# code and methods.. `COMMIT [TRANSACTION | WORK]` `ROLLBACK [TRANSACTION | WORK]` ? — Nyprez, Jun 23 '16 at 09:07

Andrew Kilburn · Accepted Answer · 2016-06-23T12:51:40.740

1

    using (OleDbConnection connection =
                   new OleDbConnection(connectionString))
        {
            OleDbCommand command = new OleDbCommand();
            OleDbTransaction transaction = null;

            // Set the Connection to the new OleDbConnection.
            command.Connection = connection;

            // Open the connection and execute the transaction.
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
  transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

            // Assign transaction object for a pending local transaction.
            command.Connection = connection;
            command.Transaction = transaction;

                Method1(command.connection);
                Method2(command.connection);

            }
    }

Something like that?

So you using one connection and then set the transaction level and run your methods.

See here for more info: https://msdn.microsoft.com/en-us/library/93ehy0z8(v=vs.110).aspx

edited Jun 23 '16 at 12:51

answered Jun 23 '16 at 11:50

Andrew Kilburn

2,251
6
33
65

Will `Method1();` and `Method2();` being within transaction now or do I have to pass anything in the parameters? – Nyprez Jun 23 '16 at 12:02
@Nyprez I believe you'll have to pass through the connection. But the transaction should stay I think – Andrew Kilburn Jun 23 '16 at 12:19
@ Andrew Kilburn is it `command` or `connection` that I should pass to `Method1();` and `Method2();` ? – Nyprez Jun 23 '16 at 12:32
@You will need to pass the connection but if you want to execute all your commands at once you could by passing in the command variable into your methods + returning it. – Andrew Kilburn Jun 23 '16 at 12:34
@Nyprez How did it go? – Andrew Kilburn Jun 23 '16 at 14:14
Just got home and tried it. Seems like you have to put a `transaction.Commit();` after methods call to execute it or whatever it does. However, I couldn't send `command.connection` in parameters, but just `command` worked, as: `Method1(command);`. Then inside the method I used *e.g.* `command.CommandText = "Insert Into Test (ID) Values (1)"`and it worked. But do I have to use that specific command(that now include transaction?) on **every** db-syntax to include them for transaction, or did I misunderstood the whole point? – Nyprez Jun 23 '16 at 21:07
@ Andrew Kilburn I think I got it worked :) Thanks a lot. Had a rough start of the day understanding how to use transaction. Everything is working as intended now. – Nyprez Jun 24 '16 at 00:25
@Nyprez Glad I could help and I'm also glad that you worked some out on your own :) – Andrew Kilburn Jun 24 '16 at 08:00

Using a single transaction for multiple method C#?

1 Answers1