CODEWITHSUNDEEP
c#sql
Sandeep Kumar Thenua
Sandeep Kumar Thenua

Reputation: 69

How to implement Transaction?

I am working on sql, my query is that i have some methods in which i am doing different-different tasks. for example:- 1st method - Inserting 2nd method - Updation 3rd method - deletion 4th methos - Inserting

now i want to execute all these in once. but if somehow any error occur then the whole process roll back it self.

code :

private void btnSubmit_Click(object sender, EventArgs e)
{
    DialogResult dr = MessageBox.Show("Are you sure you want to submit the information? Click 'Yes' to Submit or 'No' to re-Check.", "eParty - Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
    if (dr == DialogResult.Yes)
    {
        this.Cursor = Cursors.WaitCursor;

        INSERT();
        UPDATE();
        DELETE();
        INSERTAGAIN();

        MessageBox.Show("Booked successfully.", "eParty - Done!", MessageBoxButtons.OK, MessageBoxIcon.Information);
        this.Cursor = Cursors.Default;

        this.Cursor = Cursors.WaitCursor;

        this.Close();
    }
    else
    {

    }
}

now how to implement Transaction at 'btnSubmit_Click' method...

Upvotes: 2

Views: 2596

Answers (7)

Alex Filipovici
Alex Filipovici

Reputation: 32541

Please refer to this topic: Implementing an Implicit Transaction using Transaction Scope.

The TransactionScope class provides a simple way to mark a block of code as participating in a transaction, without requiring you to interact with the transaction itself. A transaction scope can select and manage the ambient transaction automatically. Due to its ease of use and efficiency, it is recommended that you use the TransactionScope class when developing a transaction application. In addition, you do not need to enlist resources explicitly with the transaction. Any System.Transactions resource manager (such as SQL Server 2005) can detect the existence of an ambient transaction created by the scope and automatically enlist.

I would also like to recommend you to read this question and it's answers. It touches interesting topics about using transactions over multiple connections.

Upvotes: 0

Jens Kloster
Jens Kloster

Reputation: 11277

Try this:

  using(var scope = new TransactionScope())
  {
    INSERT();
    UPDATE();
    DELETE();
    INSERTAGAIN();

    scope.Complete(); //if we make it here - commit the changes, 
                      //if not - everything is rolled back

  }

remember to include System.Transactions to you project

Upvotes: 2

user2122730
user2122730

Reputation: 21

Maybe you should create one stored procedure and based on a parameter execute your CRUD operations. For example:

    BEGIN TRANSACTION

    IF(@PartyID <= 0)
    BEGIN
        //INSERT
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION
            RETURN
        END
    END
    ELSE
    BEGIN
        //UPDATE
    IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRANSACTION
            RETURN
        END
    END

Upvotes: 0

guruprasath
guruprasath

Reputation: 277

You can use .Net's TransactionScope class to achieve this. http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx You can choose Isolation level's as per need. If you are not sure, please use the Serializable level which is the most consistent and least performant. But if your sql stored procedures have isolation levels, they will gain precedence. If this is a critical design please read about transactions before proceeding.

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062560

Typically something like:

void DoStuff(args)
{
    using (var conn = CreateOpenConnection())
    using (var tran = conn.BeginTransaction())
    {
        try
        {
            // various operations here
            ForExample(conn, tran);
            tran.Commit();
        }
        catch
        {
            tran.Rollback();
            throw;
        }
    }
}

void ForExample(DbConnection conn, DbTransaction tran = null)
{
    using (var cmd = conn.CreateCommand())
    {
        cmd.Transaction = tran;
        cmd.CommandText = "For example";
        // cmd.Parameters.Add(...)
        cmd.ExecuteNonQuery();
    }
}

Although most of this can be reduced using any of the tools / libraries that are available that sit above raw ADO.NET.

Upvotes: 0

Alex
Alex

Reputation: 8937

You can use ADO transactions and send your transaction as a param to all your methods

SqlConnection db = new SqlConnection("connstringhere");
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction();
try 
{
     INSERT(transaction);
     UPDATE(transaction);
     DELETE(transaction);
     INSERT(transaction);
     transaction.Commit();
} 
catch (SqlException sqlError) 
{
     transaction.Rollback();
}

Upvotes: 0

Ivan Manzhos
Ivan Manzhos

Reputation: 743

It depends on what db access technology you have.

If you use Entity Framework, SaveChanges() method already has transaction support.

In case of pure ADO.NET please see this https://stackoverflow.com/a/224702/210994

Upvotes: 0

Related Questions