Using Sql Transaction in C Sharp

SqlTransaction  class perform the sql transaction in c sharp 

these are the main thing to be kept in mind

1. Create connection,
2. create transaction,
3. create transaction,
4. if success commit transaction
5. if failure rollback the transaction
6. keep in mind all command should be on one open connection 
7. and after start transaction you have to put try catch block.

sample code

 string sql = "Data Source=***; Initial Catalog=;Integrated Security=False;User ID=***;Password=***;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False"; 

SqlTransaction sqltran = null;
            using (SqlConnection sqlconn = new SqlConnection(sql))
            {
                sqlconn.Open();
                sqltran=sqlconn.BeginTransaction();
                try
                {
                 
                    SqlCommand command = new SqlCommand("insert into tblpat values('P1','kamal',25)",sqlconn,sqltran);
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                    SqlCommand command1 = new SqlCommand("insert into tblen values('E1','P1','gen')",sqlconn,sqltran);
                    command1.CommandType = CommandType.Text;
                    command1.ExecuteNonQuery();
                    sqltran.Commit();
                    MessageBox.Show("saved");
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
         
                    sqltran.Rollback();
                }
                finally
                {
                    sqlconn.Close();
                }
            }

lets break these code into small pices

Connection string 
string sql = "Data Source=***; Initial Catalog=;Integrated Security=False;User ID=***;Password=***;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";

Create Sql Transaction Object

SqlTransaction sqltran = null;

Open the class and start the transaction

  using (SqlConnection sqlconn = new SqlConnection(sql))
            {
                sqlconn.Open();
                sqltran=sqlconn.BeginTransaction();
                try
                {

Write a code for sql function

 try
                {
                   
                    SqlCommand command = new SqlCommand("insert into tblpat values('P1','kamal',25)",sqlconn,sqltran);
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                    SqlCommand command1 = new SqlCommand("insert into tblen values('E1','P1','gen')",sqlconn,sqltran);
                    command1.CommandType = CommandType.Text;
                    command1.ExecuteNonQuery();
                    sqltran.Commit();
                    MessageBox.Show("saved");
                }






Roll back transaction if Error occered
   catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
           
                    sqltran.Rollback();
                }


finally close the opened connection

                finally
                {
                    sqlconn.Close();
                }