How to manage transactions in Ado.net with SQL Server

To implement a transaction in Ado.net, we create an object of the SqlTransaction class using the BeginTransaction() method of the SqlCommand class. The return type of BeginTransaction() method is a SqlTransaction object, so the object can be created without using the new keyword. To submit the changes permanently we use the Commit() method of the SqlTransaction class and to undo the changes we use the Rollback() method.

Note:- Do not forget to assign the SqlTransaction object to the transaction property of the SqlCommand object. If we forget this step then an invalid operation exception will be thrown at the time we execute our code.

To understand, how the queries work we develop windows form application as follows:

To maintain the connectivity with the database, the following code is added into the App.config file

We add another class named ‘SQL helper’ in which we mention the code for the transaction to occur.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace SqlTransAdo
{
    class sqlhelper
    {
        public string _Connectionstring
        { get
            {
                return ConfigurationManager.ConnectionStrings["mydb"].ConnectionString;
            }
        }

        public int RunDml(params string[] Query)
        {
            SqlConnection con = new SqlConnection(_Connectionstring);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            int count = 0;
            con.Open();
            SqlTransaction trn = con.BeginTransaction();
            cmd.Transaction = trn;
            try
            {
                for (int i = 0; i < Query.Length; i++)
                {
                    cmd.CommandText = Query[i];
                    count += cmd.ExecuteNonQuery();
                }
                trn.Commit();
                MessageBox.Show("Transaction Committed");
            }

            catch
            {
                trn.Rollback();
                MessageBox.Show("Transaction rolled back");
            }
            finally
            {
                con.Close();
            }
            return count;
        }

        internal DataTable Getrecords(string v)
        {
            SqlDataAdapter adp = new SqlDataAdapter(v, _Connectionstring);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            return dt;
        }
    }
}

In the above code, we first created a property named ‘_Connectionstring’ to read the connection string in the Application Configuration file. Then we created a method named RunDml which takes multiple queries as parameters. In this method, we created the objects of SqlConnection, SqlCommand, and SqlTransaction classes. We inserted the remaining code in the try block because if an exception occurs it could be handled by the catch block which will undo the changes made to the database. On the other hand, if no exception occurs the transaction is committed in the try block to make the changes permanent. Finally block is added at last which is always executed no matter an exception occurs or not. In this block, the connection is closed. Because it is a good practice to open the connection as late as possible and close it as early as possible.

The code within the Form1 class is given below;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SqlTransAdo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        sqlhelper sql = new sqlhelper();
        private void button1_Click(object sender, EventArgs e)
        {
           int res= sql.RunDml("insert into Empl values(89,'aniie',8999,111)","insert into Empl values(88,'bina',7999,222)", "insert into Empl values(87, 'molika', 5999, 111)");
            if(res>=3)
            {
                MessageBox.Show("Data saved");
            }
            else
            {
                MessageBox.Show("Data not saved");
            }
        }

         private void Form1_Load(object sender, EventArgs e)
        {
            DataTable dd = sql.Getrecords("Select * from Empl");
            dataGridView1.DataSource = dd;
        }
    }
}

In the above code, we passed three queries and if the result variable has a value greater than 3 then a message box appears stating that the data is saved otherwise data not saved. In the end, we pass the DataTable object as the data source to the data grid view so that the table contents are visible as soon as the form loads.

OUTPUT

Now If I try to execute the same code again then it would generate an exception because the ids have to be unique and the transaction would be rolled back

An important point to note is that if we would have used an Adhoc query instead of a transaction then even though an exception occurs but still the query would be executed. And then it would have impacted the data by making modifications in the database and finally would throw an exception leading to the incomplete execution. This is because we are executing multiple queries and the queries with no exception would get executed. But that should not actually happen. This is the reason the transactions come into the picture and maintain the data integrity of the database.