How to Transaction in ADO.NET

By: Rajesh P.S.

A transaction in database operations refers to a cohesive unit consisting of a single command or a group of commands that are executed together. Its purpose is to ensure that a set of database operations either succeed entirely or fail entirely, maintaining the integrity of the data.

Transactions offer the ability to combine multiple operations into a single logical unit of work. If any failure occurs during the execution of the transaction, all the updates made within that transaction can be rolled back, effectively reverting the database to its pre-transaction state. This ensures that the data remains consistent and accurate.

BeginTransaction

Connection.BeginTransaction.

In ADO.NET, the management of transactions is facilitated through the Connection and Transaction objects. To initiate a local transaction, you can use the BeginTransaction statement, which establishes a transaction scope. Once a transaction has been initiated, you can enlist a command within that transaction using the Transaction property of the Command object. This ensures that the command is executed within the ongoing transaction.

new SqlCommand("Your SQL Statemnt Here", Connection, transaction).ExecuteNonQuery();

Commit()

Using the Transaction object, you can subsequently commit or rollback modifications made at the data source based on the success or failure of the individual components of the transaction. If all the operations within the transaction are successful, the changes are committed to the database permanently. However, if any operation fails, the transaction can be rolled back, undoing all the modifications made within that transaction.

transaction.Commit();

The following example shows how to perform a transaction in an ADO.Net programming.

C# Source Code

using System; using System.Data; using System.Data.SqlClient; using System.Windows.Forms; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection cnn; SqlCommand cmd; SqlTransaction transaction; connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"; cnn = new SqlConnection(connetionString); try { cnn.Open(); transaction = cnn.BeginTransaction(); cmd = new SqlCommand("Your SQL Statemnt Here", cnn, transaction); cmd.ExecuteNonQuery(); cmd = new SqlCommand("Your SQL Statemnt Here", cnn, transaction); cmd.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } cnn.Close(); } } }

VB.Net Source Code

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 connetionString As String Dim cnn As SqlConnection Dim cmd As SqlCommand Dim transaction As SqlTransaction connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" cnn = New SqlConnection(connetionString) Try cnn.Open() transaction = cnn.BeginTransaction() cmd = New SqlCommand("Your SQL Statemnt Here", cnn, transaction) cmd.ExecuteNonQuery() cmd = New SqlCommand("Your SQL Statemnt Here", cnn, transaction) cmd.ExecuteNonQuery() transaction.Commit() Catch ex As Exception MessageBox.Show("Can not open connection ! ") End Try cnn.Close() End Sub End Class

Conclusion

Exploring the capabilities of ADO.NET's Connection and Transaction objects, developers can effectively control and manage transactions, ensuring that database operations are performed in a reliable and atomic manner.

Next > What is ADO.Net Locking

Related Topics

More Related Topics.....