Csharp/CSharp Tutorial/ADO.Net/SqlTransaction

Explicit Transaction

using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using System.Collections.Generic;
using System.Text;
    class Program
    {
        static void Main(string[] args)
        {
            CommittableTransaction tx = new CommittableTransaction();
            SqlConnection conn = new SqlConnection("data source=localhost; initial catalog=SampleDB; integrated security=SSPI;");
            conn.Open();
            SqlCommand updateCommand = conn.CreateCommand();
            updateCommand.rumandText = "DELETE Employees WHERE ID > 3";
            conn.EnlistTransaction(tx);
            updateCommand.ExecuteNonQuery();
            tx.Rollback();
            conn.Close();
        }
    }

Implicit Transaction

using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using System.Collections.Generic;
using System.Text;
    class Program
    {
        static void Main(string[] args)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                using (SqlConnection conn = new SqlConnection("data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;"))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.rumandText = "DELETE Employees";
                    cmd.ExecuteNonQuery();
                    int y = 1;
                    y -= 1;
                    Console.WriteLine("{0}", 1 / y);
                    
                }
                scope.ruplete();
            }
        }
    }

Nested Implicit Transaction

using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using System.Collections.Generic;
using System.Text;
    class Program
    {
        static SqlConnection conn;
        static void Main(string[] args)
        {
            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    using (conn = new SqlConnection("data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;"))
                    {
                        conn.Open();
                        for (int x = 1; x < 8; x++)
                        {
                            using (TransactionScope scope1 = new TransactionScope())
                            {
                                SqlCommand cmd = conn.CreateCommand();
                                cmd.rumandText = "DELETE Employees WHERE ID = " + x.ToString();
                                cmd.ExecuteNonQuery();
                                if (x < 3)
                                    scope1.ruplete();
                            }
                        }
                    }
                    scope.ruplete();
                }
            }
            catch (TransactionAbortedException )
            {
                Console.WriteLine("One or more of the child scopes voted to abort the transaction.");
            }
        }
    }

the use of a transaction

using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteTransaction {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        mySqlConnection.Open();
        SqlTransaction mySqlTransaction = mySqlConnection.BeginTransaction();
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.Transaction = mySqlTransaction;
        mySqlCommand.rumandText =
          "INSERT INTO Customers (" +
          "  CustomerID, CompanyName" +
          ") VALUES (" +
          "  "J3COM", "Jason Price Corporation"" +
          ")";
        mySqlCommand.ExecuteNonQuery();
        mySqlCommand.rumandText =
          "INSERT INTO Orders (" +
          "  CustomerID" +
          ") VALUES (" +
          "  "J3COM"" +
          ")";
        mySqlCommand.ExecuteNonQuery();
        mySqlTransaction.rumit();
        mySqlConnection.Close();
    }
}

Updating Data Using Transactions

using System;
using System.Data;
using System.Data.SqlClient;

class MainClass
{
  static void Main(string[] args)
  {
    SqlConnection MyConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    MyConnection.Open();
    String MyString = "INSERT INTO Employee(ID, FirstName)VALUES(1, "Greg")";
    SqlTransaction MyTransaction = MyConnection.BeginTransaction();
    SqlCommand MyCmd = new SqlCommand(MyString, MyConnection, MyTransaction);
    MyCmd.ExecuteScalar();
    MyTransaction.rumit();
    MyConnection.Close();
  }
}