How to Transaction in ADO.NET

Performing a Transaction Using ADO.NET

A transaction consists of a single command or a group of commands that execute together. When we do some database operations in such a way that either all the database operations are successful or all of them fail. This would result in the amount of information being same once the transaction is complete or it fails.

Transactions allow you to combine multiple operations into a single unit of work. If a failure occurs at one point in the transaction, all of the updates can be rolled back to their pre-transaction state. In ADO.NET, you can control transactions using the Connection and Transaction objects. You can initiate a local transaction using BeginTransaction statement.

Connection.BeginTransaction.

Once you have begun a transaction, you can enlist a command in that transaction using the Transaction property of the Command object.

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

You can then use the Transaction object to commit or rollback modifications made at the data source based on the success or failure of the components of the 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

Next : What is ADO.Net Locking

Related Topics