Transactions in ADO.NET

Question

Hi How can we define Transactions in ADO.NET?

Hemant Kumar · Answer

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 Inherits="TransactionDemo" %> Performing a Transaction Using a Single Connection Name of the Scrap Reason: font-weight: 25" runat="server" id="divMessage"> 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 Raja · Answer

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 Nair · Answer

Rajesh, Check Our recommended articles section having good articles with example.

Javeed M Shaikh · Answer

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".

Transactions in ADO.NET

Answers (4)