Using Transactions in ADO.NET

by Jagadish Pulakhandam on 9/1/2011 3:01:39 PM
Rated 0 from 0 votes
Brief: Demonstrates on executing SQL statements as part of a transaction in ADO.NET
Posted to: Manipulating (insert/update/delete) Data using ADO.NET
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Demonstrates on the following:
  • Beginning a Transaction using a Connection Object
  • Executing Commands against Transaction
  • Committing a Transaction
  • Rolling back a Transaction
  • Using TransactionScope in ADO.NET

Source Code:

01.using System;
02.using System.Collections.Generic;
03.using System.ComponentModel;
04.using System.Data;
05.using System.Drawing;
06.using System.Linq;
07.using System.Text;
08.using System.Windows.Forms;
09.using System.Data.SqlClient;
10. 
11.namespace CSUsingTransactions
12.{
13.    public partial class Form1 : Form
14.    {
15.        string CONNSTR = "Data Source=.\\sql2k8; Initial Catalog=Sample; User Id=sa; Password=sql2008";
16. 
17.        public Form1()
18.        {
19.            InitializeComponent();
20.        }
21. 
22.        private void btnSave_Click(object sender, EventArgs e)
23.        {
24.            StringBuilder sql1 = new StringBuilder();
25.            sql1.Append("INSERT INTO emp (empno, ename, sal, deptno) ");
26.            sql1.Append("VALUES (9901, 'new1', 4500, 20)");
27. 
28.            StringBuilder sql2 = new StringBuilder();
29.            sql2.Append("INSERT INTO emp (empno, ename, sal, deptno) ");
30.            sql2.Append("VALUES (9902, 'new2', 4500, 10)");
31. 
32.            using (SqlConnection cn = new SqlConnection(CONNSTR))
33.            {
34.                cn.Open();
35.                SqlTransaction tr = cn.BeginTransaction();
36.                try
37.                {
38. 
39.                    using (SqlCommand cmd1 = new SqlCommand(sql1.ToString(), cn))
40.                    {
41.                        cmd1.Transaction = tr;
42.                        cmd1.ExecuteNonQuery();
43.                    }
44.                    using (SqlCommand cmd2 = new SqlCommand(sql2.ToString(), cn))
45.                    {
46.                        cmd2.Transaction = tr;
47.                        cmd2.ExecuteNonQuery();
48.                    }
49.                    tr.Commit();
50.                    MessageBox.Show("Saved Succesfully!");
51.                }
52.                catch (Exception ex)
53.                {
54.                    tr.Rollback();
55.                    MessageBox.Show(ex.Message);
56.                }
57.            }
58.        }
59. 
60.        private void Button1_Click(object sender, EventArgs e)
61.        {
62.            StringBuilder sql1 = new StringBuilder();
63.            sql1.Append("INSERT INTO emp (empno, ename, sal, deptno) ");
64.            sql1.Append("VALUES (9901, 'new1', 4500, 20)");
65. 
66.            StringBuilder sql2 = new StringBuilder();
67.            sql2.Append("INSERT INTO emp (empno, ename, sal, deptno) ");
68.            sql2.Append("VALUES (9902, 'new2', 4500, 30)");
69. 
70.            try
71.            {
72.                using (System.Transactions.TransactionScope trs = new System.Transactions.TransactionScope())
73.                {
74.                    using (SqlConnection cn = new SqlConnection(CONNSTR))
75.                    {
76.                        cn.Open();
77.                        using (SqlCommand cmd1 = new SqlCommand(sql1.ToString(), cn))
78.                        {
79.                            cmd1.ExecuteNonQuery();
80.                        }
81.                        using (SqlCommand cmd2 = new SqlCommand(sql2.ToString(), cn))
82.                        {
83.                            cmd2.ExecuteNonQuery();
84.                        }
85.                    }
86.                    trs.Complete();
87.                    MessageBox.Show("Saved Succesfully!");
88. 
89.                }
90.            }
91.            catch (Exception ex)
92.            {
93.                MessageBox.Show(ex.Message);
94.            }
95.        }
96.    }
97.}


Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout

Attachments / Source Code
You need to Login or Join for FREE to download the following