title: How to Rollback SQL transaction in between a try-catch block in ASP.Net?
body: <p>I am executing a Submit routine in ASP.Net. The problem is, while debugging the code in try-catch block, if I/user encounters an error, the SQL Transaction never rollbacks. </p>
  
  <p>SQL Server 2008 hangs totally if I break this submit routine in between. I am unable to do Select/Insert operations even from SSMS. At the end, i have to restart SQL Server in order to rollback the transactions.</p>
  
  <p>Code for submit:</p>
  
  <pre><code>SqlConnection conn = Db.getConn();
  if (conn.State == ConnectionState.Closed) conn.Open();
  
  SqlTransaction trn;
  trn = conn.BeginTransaction();
  
  SqlCommand sqlCmd = new SqlCommand("", conn);
  sqlCmd.Transaction = trn;
  
  try
  {
      string query = GetQuery(); // works fine
      sqlCmd.CommandText = query;
  
      sqlCmd.ExecuteNonQuery();
  
  
      using (SqlBulkCopy bcp = new SqlBulkCopy(conn,SqlBulkCopyOptions.Default, trn))        
          {
              bcp.ColumnMappings.Add("FaYear", "FaYear");
              bcp.ColumnMappings.Add("CostCode", "CostCode");
              bcp.ColumnMappings.Add("TokenNo", "TokenNo");
  
              bcp.DestinationTableName = "ProcessTokenAddress";
              bcp.WriteToServer(globaltblAddress.DefaultView.ToTable());
          }
      trn.commit();
  }
  catch (SqlException ex)
  {
     trn.Rollback();
  }
  </code></pre>
  
  <p><strong>NOTE:</strong> Just while writing the code here, i realized i have catched <strong>SqlException</strong> and not <strong>Exception</strong>. Is that what is causing the error? phew?</p>
  
  <p><strong>IMPORTANT:</strong> Do i need to rollback the transaction in Page_UnLoad or some other event handler which could handle unexpected situations (for eg. user closes the browser while the transaction is in progress, user hits back button etc).</p>