ADO Net Database ASP.Net Tutorial

  1. Home
  2. ASP.Net Tutorial
  3. ADO Net Database
  4. Execute database commands within a single local transaction You can test rollback and partial rollback
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"  Inherits="Default"%>



    Begin Local Transactions


        
        
          

Click this button to display current data.


        
        
        

Click this button to execute a transaction


        
        
        

Click this button to restore the original state of data


        
        

         
        

        
          
          
          
                
                
                
                
        
        
    



File: Default.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Default : System.Web.UI.Page
{
    private const string ConnString = "SERVER=(local);DATABASE=northwind;Trusted_Connection=yes;";
    private const string SelectCmd = "SELECT employeeid, firstname, lastname FROM Employees";
    private const string UpdateCmd = "UPDATE Employees SET firstname='Michela' WHERE EmployeeID=1";
    private const string InsertCmd = "INSERT INTO Employees (firstname,lastname) VALUES ('Dino', 'Esposito')";
    protected void ShowButton_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection(ConnString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = SelectCmd;
            conn.Open();
            grid.DataSource = cmd.ExecuteReader();
            grid.DataBind();
            grid.Visible = true;
            conn.Close();
        }
    }
    protected void ExecuteButton_Click(object sender, EventArgs e)
    {
        using (SqlConnection conn = new SqlConnection(ConnString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            conn.Open();
            SqlTransaction t = conn.BeginTransaction();
            cmd.Transaction = t;
            try
            {
                cmd.CommandText = UpdateCmd;
                cmd.ExecuteNonQuery();
                t.Save("AfterUpdate");
                cmd.CommandText = InsertCmd;
                cmd.ExecuteNonQuery();
                if (CheckBox_Partial.Checked)
                    t.Rollback("AfterUpdate");
                t.Commit();
                lblMessage.Text = "Done";
                grid.Visible = false;
            }
            catch (Exception exc)
            {
                t.Rollback();
                lblMessage.Text = "Error occurred: " + exc.Message;
            }
        }
    }
    protected void RestoreButton_Click(object sender, EventArgs e) {
        using (SqlConnection conn = new SqlConnection(ConnString)) {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            conn.Open();
            SqlTransaction t = conn.BeginTransaction();
            cmd.Transaction = t;
            try {
                cmd.CommandText = "UPDATE Employees SET firstname='Nancy' WHERE EmployeeID=1";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM Employees WHERE EmployeeID >9";
                cmd.ExecuteNonQuery();
                t.Commit();
                lblMessage.Text = "Done";
                grid.Visible = false;
            }
            catch (Exception exc)
            {
                t.Rollback();
                lblMessage.Text = "Error occurred: " + exc.Message;
            }
        }
    }
}