ADO Net Database ASP.Net Tutorial

  1. Home
  2. ASP.Net Tutorial
  3. ADO Net Database
  4. Local Transactions
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"  Inherits="LocalTransactions" %>



   Local Transactions


   
      
         

Local Transactions


         
         This example illustrates how to use local transactions with ADO.NET.
         
      
         
                 
      

   


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 += "
" + ex.Message;
         if (trans != null)
         {
            trans.Rollback();
            labMsg.Text += "
" + "Changes rolled back";
         }
      }
      finally
      {
         conn.Close();
         conn = null;
      }
   }
}