Local Transactions : SqlTransaction « ADO.net Database « ASP.NET Tutorial

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="LocalTransactions" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
   <title>Local Transactions</title>
</head>
<body>
   <form id="form1" runat="server">
      <div id="container">
         <h1>Local Transactions</h1>
         
         This example illustrates how to use local transactions with ADO.NET.
         
      
         <asp:Label ID="labMsg" runat="server" />
                 
      </div>
   </form>
</body>
</html>

File: Default.aspx.cs

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class LocalTransactions : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       ModifyData();
    }

   private void ModifyData()
   {
      string connString = ConfigurationManager.ConnectionStrings["Book"].ConnectionString;

      int pubIdToDelete = 9;

      SqlConnection conn = new SqlConnection(connString);

      SqlTransaction trans = null;
      try
      {
         conn.Open();

         trans = conn.BeginTransaction();

         string sqlA = "DELETE FROM Publishers ";
         sqlA += " WHERE PublisherId=@pubid";

         SqlCommand cmdA = new SqlCommand(sqlA, conn, trans);

         cmdA.Parameters.AddWithValue("@pubId", pubIdToDelete);

         cmdA.ExecuteNonQuery();

         string sqlB = "DELETE FROM NonExistantTable ";
         sqlB += " WHERE PublisherId=@pubid";

         SqlCommand cmdB = new SqlCommand(sqlB, conn, trans);

         cmdB.Parameters.AddWithValue("@pubId", pubIdToDelete);

         cmdB.ExecuteNonQuery();

         trans.Commit();
         labMsg.Text = "Database updated successfully";
      }
      catch (Exception ex)
      {
         labMsg.Text = "[btnUpdate_Click] ";
         labMsg.Text += "Error occurred accessing the database";
         labMsg.Text += "<br/>" + ex.Message;
         if (trans != null)
         {
            trans.Rollback();
            labMsg.Text += "<br/>" + "Changes rolled back";
         }
      }
      finally
      {
         conn.Close();
         conn = null;
      }
   }
}
18.54.SqlTransaction
	18.54.1.	Local Transactions