transaction

transaction


  Many time row processing becomes dependent process.


Below stuff will shade some lights on

1. Transaction using ADO.net application
2. Transaction using SQL server / stored procedure


E.g. Your application has processed the CC information to third party web and its waiting
       for response.
       What if their site is down? or there is network falure or communication error?

Obviousely, you need to roll back the transaction. Using C#.Net below code snippet will help you to understand and use
SqlTransaction techniuque

Code is self explanatory


// create SQL connection object and pass connection string to it from application settings

 SqlConnection sqlconnect = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);


// create SQL command object

            SqlCommand sqlcommdt = new SqlCommand();                       

// Tell command object where to connect

            sqlcommdt.Connection = sqlconnect;

// Tell command object what to do once get connected

// In this case, I'm telling it to execute Stored procedure

            sqlcommdt.CommandText = "SP_Name";

// set type of command object

            sqlcommdt.CommandType = CommandType.StoredProcedure;
    
       

            try
            {

//  Open connection

                sqlcommdt.Connection.Open();

//  Create SQL Transaction

                SqlTransaction transaction = sqlconnect.BeginTransaction();

// Tell command object to use SQL Transaction object

                sqlcommdt.Transaction = transaction;

                DataTable dt = new DataTable();

                dt.Load(sqlcommdt.ExecuteReader());
               
                try
                {

                    if (dt.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {

                            // Process the row
                            // It may throw excetion. In this case, immediate catch block will throw that exception to outer one.
                         
                              ProcessRow(dr);   

                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }

// If there is no exception, commit the transaction.

                sqlcommdt.Transaction.Commit();
            }

            catch (Exception ex)
            {
           
// If there is an error, you can roll back the transaction.

                sqlcommdt.Transaction.Rollback();
            }


2. When to go for SQL transaction using stored procedure?

See below code snippet.

 Begin transaction tr1
   begin try
  
           -- Do insertion on one table
           -- Do deletion from xyz table

   -- If every thing worked fine commit transaction.

    commit transaction tr1

   End try

   begin catch

-- If any error occured during insertion or delition, you can roll back that transaction.

    rollback transaction tr1

  end catch


Scenario when transaction invlove insertion / updation / deletion on multiple SQL server, you need to
go for MSDTC.

Tags:

OLE DB provider SQLNCLI for linked server returned message The partner transaction manager has disabled its support for remotenetwork transactionsTransaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim Rerun the transactiontransactionMS SQL server Transaction commit rollbackA-nested-transaction-was-required-because-the-XACT_ABORT-option-was-set-to-OFFOLE DB provider SQLNCLI for linked server returned message Cannot start more transactions on this sessionConnection PoolingSend SMS and Pepsi Canhow to purchase onlineExecuted as user NT AUTHORITY5cSYSTEM A cursor with the name does not existPaypal purpose code India

Author

My name is Satalaj, but people call me Sat. Here is my homepage: . I live in Pune, PN and work as a Software Engineer. I'm former MVP in ASP.net year 2010.
Disclaimer: Views or opinion expressed here are my personal research and it has nothing to do with my employer. You are free to use the code, ideas/hints in your projects. However, you should not copy and paste my original content to other web sites. Feel free to copy or extend the code.
If you want to fight with me, this website is not for you.
 

I'm Satalaj.