Transactions in ADO.NET

A detailed description of Transactions in ADO.NET
http://www.codeproject.com/KB/database/transactions.aspx

A sample is done for you please refer this example
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="TransactionDemo.aspx.cs"
    Inherits="TransactionDemo" %>

    Performing a Transaction Using a Single Connection





using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Activities.Statements;

public partial class TransactionDemo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DateTime dt = new DateTime(2011, 6, 8);
        DateTime newDateTime = dt.AddMonths(2);
    }

    protected void btnInsert_Click(object sender, EventArgs e)
    {
        String connectionString = ConfigurationManager.ConnectionStrings["AdvenCON"].ConnectionString;
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            con.Open();
            //Start a local Transaction
            SqlTransaction sqlTran = con.BeginTransaction();
            //Enlist a command in the current Transaction
            SqlCommand cmd = con.CreateCommand();
            cmd.Transaction = sqlTran;
            try
            {
                //One way of writing Insert Query
                // Execute two Separate Commands
                //cmd.CommandText = "INSERT INTO PRODUCTION.SCRAPREASON(Name) VALUES(@Name1)";
                //cmd.Parameters.AddWithValue("@Name1", tbName1.Text);
                //cmd.ExecuteNonQuery();
                //cmd.CommandText = "INSERT INTO PRODUCTION.SCRAPREASON(Name) VALUES(@Name2)";
                //cmd.Parameters.AddWithValue("@Name2", tbName2.Text);
                //cmd.ExecuteNonQuery();
                //Second way of writing Insert Query
                new SqlCommand(String.Format("INSERT INTO PRODUCTION.SCRAPREASON(Name) VALUES('{0}')", tbName1.Text), con, sqlTran).ExecuteNonQuery();
                new SqlCommand(String.Format("INSERT INTO PRODUCTION.SCRAPREASON(Name) VALUES('{0}')", tbName2.Text), con, sqlTran).ExecuteNonQuery();
                //Commit the Transaction
                sqlTran.Commit();
                divMessage.InnerText = "Records inserted Successfully!";

            }
            catch (Exception expSqlTransaction)
            {
                //Handle the exception if the transaction fails to Commit
                divMessage.InnerText = expSqlTransaction.Message;
                try
                {
                    sqlTran.Rollback();
                }
                catch (Exception exRollback)
                {
                    divMessage.InnerText = exRollback.Message;
                }
            }

        }
    }
}

Hope this will help you :-)

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hemant Kumar15y ago
A detailed description of Transactions in ADO.NET
http://www.codeproject.com/KB/database/transactions.aspx

A sample is done for you please refer this example
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="TransactionDemo.aspx.cs"
    Inherits="TransactionDemo" %>

    Performing a Transaction Using a Single Connection







        Name of the Scrap Reason:














using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Activities.Statements;

public partial class TransactionDemo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DateTime dt = new DateTime(2011, 6, 8);
        DateTime newDateTime = dt.AddMonths(2);
    }

    protected void btnInsert_Click(object sender, EventArgs e)
    {
        String connectionString = ConfigurationManager.ConnectionStrings["AdvenCON"].ConnectionString;
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            con.Open();
            //Start a local Transaction
            SqlTransaction sqlTran = con.BeginTransaction();
            //Enlist a command in the current Transaction
            SqlCommand cmd = con.CreateCommand();
            cmd.Transaction = sqlTran;
            try
            {
                //One way of writing Insert Query
                // Execute two Separate Commands
                //cmd.CommandText = "INSERT INTO PRODUCTION.SCRAPREASON(Name) VALUES(@Name1)";
                //cmd.Parameters.AddWithValue("@Name1", tbName1.Text);
                //cmd.ExecuteNonQuery();
                //cmd.CommandText = "INSERT INTO PRODUCTION.SCRAPREASON(Name) VALUES(@Name2)";
                //cmd.Parameters.AddWithValue("@Name2", tbName2.Text);
                //cmd.ExecuteNonQuery();
                //Second way of writing Insert Query
                new SqlCommand(String.Format("INSERT INTO PRODUCTION.SCRAPREASON(Name) VALUES('{0}')", tbName1.Text), con, sqlTran).ExecuteNonQuery();
                new SqlCommand(String.Format("INSERT INTO PRODUCTION.SCRAPREASON(Name) VALUES('{0}')", tbName2.Text), con, sqlTran).ExecuteNonQuery();
                //Commit the Transaction
                sqlTran.Commit();
                divMessage.InnerText = "Records inserted Successfully!";

            }
            catch (Exception expSqlTransaction)
            {
                //Handle the exception if the transaction fails to Commit
                divMessage.InnerText = expSqlTransaction.Message;
                try
                {
                    sqlTran.Rollback();
                }
                catch (Exception exRollback)
                {
                    divMessage.InnerText = exRollback.Message;
                }
            }

        }
    }
}

Hope this will help you :-)
Prabhu Raja15y ago
Hi,

You can get a dozens of Articles written by our own Network Authors.

Use this Link:

http://www.c-sharpcorner.com/Search/?sKeywords=Transactions+in+ADO.NET
Suthish Nair15y ago
Rajesh, Check Our recommended articles section having good articles with example.
Javeed M Shaikh15y ago
Hi Rajesh,

Here is an example:

SqlConnection db = new SqlConnection("connectionstring");
SqlTransaction transaction;

db.Open();
transaction = db.BeginTransaction();

try
{
new SqlCommand("INSERT INTO table1 " +
"(Text) VALUES ('Row1');", db, transaction)
.ExecuteNonQuery();
new SqlCommand("INSERT INTO table2 " +
"(Text) VALUES ('Row2');", db, transaction)
.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlException sqlError)
{
transaction.Rollback();
}
db.Close();

Please do not forget to mark "Accepted Answer".