Transactions in ADO.NET
This article provides you the basic insight about the Transactions in ADO.NET. The ADO.NET provides you to implement the transaction while executing statements across the Data Base from front end code. We will discuss the topic, what is a Transaction?. How can we Implement Transaction in ADO.NET.
What is a Transaction?.
We can say, Transactions are Data Base commands that execute as set of package. For Example if we have two statements if first statement fails second statement should not execute or if first statement executes success and second statement failed, now inserted records should be rolled back.
Transactions are very much powerful and efficient means of performance in .NET. We are having three types of blocks are there in ADO.NET Transactions.
1) Begin Transaction
2) Commit Transaction
3) Roll Back TransactionBelow is the sample program to use the Transaction in ADO.NET.
VB.NET Code
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim SQLConn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=PMDB;Integrated Security=SSPI;")
SQLConn.Open()
' Start a local transaction.
Dim SQLTran As SqlTransaction = SQLConn.BeginTransaction()
' Enlist the command in the current transaction.
Dim SQLCmd As SqlCommand = SQLConn.CreateCommand()
SQLCmd.Transaction = SQLTran
Try
SQLCmd.CommandText = "Insert into Amount (Amount, TranNumber) VALUES (100, '1000011')"
SQLCmd.ExecuteNonQuery()
SQLCmd.CommandText = "Insert into Transaction (Amount, TranNumber) VALUES (101, '1000011')"
SQLCmd.ExecuteNonQuery()
SQLTran.Commit()
Console.WriteLine("Both records are written to database.")
Catch e As Exception
Try
SQLTran.Rollback()
Catch ex As SqlException
If Not SQLTran.Connection Is Nothing Then
Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If
End Try
Console.WriteLine("An exception of type " & e.GetType().ToString() & _
"was encountered while inserting the data.")
Console.WriteLine("Neither record was written to database.")
Finally
SQLConn.Close()
End Try
End Sub
End ClassC# Code
using System;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SqlConnection SQLConn = new SqlConnection("Data Source=localhost;Initial Catalog=PMDB;Integrated Security=SSPI;");
SQLConn.Open();
SqlTransaction SQLTran = SQLConn.BeginTransaction();
SqlCommand SQLCmd = SQLConn.CreateCommand();
SQLCmd.Transaction = SQLTran;
try
{
SQLCmd.CommandText = "Insert into Amount (Amount, TranNumber) VALUES (100, '1000011')";
SQLCmd.ExecuteNonQuery();
SQLCmd.CommandText = "Insert into Transaction (Amount, TranNumber) VALUES (101, '1000011')";
SQLCmd.ExecuteNonQuery();
SQLTran.Commit();
Console.WriteLine("Both records are written to database.");
}
catch (Exception exc)
{
try
{
SQLTran.Rollback();
}
catch (SqlException ex)
{
if (SQLTran.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine("An exception of type " + e.GetType() +
"was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
SQLConn.Close();
}
}
}
} SqlTransaction:
SqlTransaction is class Which represents a Transact SQL transaction to be made in SQL Server Data Base. It is derived from the System.Data.SqlClient namespace. We can declate and use like as below.
SqlConnection SQLConn = new SqlConnection("Data Source=localhost;Initial Catalog=PMDB;Integrated Security=SSPI;");
SQLConn.Open();
SqlTransaction SQLTran = SQLConn.BeginTransaction();
The BeginTransaction states that the DataBase transactin is start on this connection. The Commit transaction says that the the above executed statemets should be committed and the Rollback transaction is to roll bacl the inserted records after error occure in the command block.