// 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();
            }

 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