VB.Net/Database ADO.net/Transaction

DataSet transaction

Imports System
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Data

Public Class MainClass
    
    Shared Sub Main()
        Dim ConnectionString As String
        Dim myconnection As SqlConnection
        Dim mytransaction As SqlTransaction
        Dim mycommand1 As SqlCommand
        Dim myparam As SqlParameter
        Dim da As SqlDataAdapter
        Dim ds As New DataSet()
        Dim args() As String = Environment.GetCommandLineArgs
        ConnectionString = "Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI"
        myconnection = New SqlConnection(ConnectionString)
        myconnection.Open()
        da = New SqlDataAdapter("select * from Employee", myconnection)
        da.Fill(ds, "Employee")
        myconnection.Close()
        ds.Tables(0).Rows(0)("FirstName") = "11"
        mycommand1 = New SqlCommand("update Employee set FirstName=@FirstName " & "where ID=@ID ", myconnection)
        myparam = New SqlParameter("@ID", SqlDbType.SmallInt)
        myparam.SourceColumn = "ID"
        myparam.SourceVersion = DataRowVersion.Current
        mycommand1.Parameters.Add(myparam)
        myparam = New SqlParameter("@FirstName", SqlDbType.Int)
        myparam.SourceColumn = "FirstName"
        myparam.SourceVersion = DataRowVersion.Current
        mycommand1.Parameters.Add(myparam)
        myconnection.Open()
        mytransaction = myconnection.BeginTransaction()
        mycommand1.Transaction = mytransaction
        da.UpdateCommand = mycommand1
        Try
            da.Update(ds, "Employee")
            mytransaction.rumit()
            Console.WriteLine("Order modified successfully !")
        Catch e As Exception
            mytransaction.Rollback()
            Console.WriteLine(e.Message)
        Finally
            myconnection.Close()
        End Try
    End Sub
End Class

Isolation Level ReadUncommitted

Imports System
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Data

Public Class MainClass
    
    Shared Sub Main()
        Dim myconnection1, myconnection2 As SqlConnection
        Dim mycommand1, mycommand2 As SqlCommand
        Dim mytransaction1, mytransaction2 As SqlTransaction
        Dim myreader As SqlDataReader
        Dim ConnectionString As String
        "open a database connection
        ConnectionString = "Server=(local)\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI"
        myconnection1 = New SqlConnection(ConnectionString)
        myconnection2 = New SqlConnection(ConnectionString)
        Try
            myconnection1.Open()
            myconnection2.Open()
            "start a transaction
            mytransaction1 = myconnection1.BeginTransaction()
            mytransaction2 = myconnection2.BeginTransaction(IsolationLevel.ReadUncommitted)
            mycommand1 = New SqlCommand()
            mycommand1.Connection = myconnection1
            mycommand1.Transaction = mytransaction1
            mycommand2 = New SqlCommand()
            mycommand2.Connection = myconnection2
            mycommand2.Transaction = mytransaction2

            mycommand1.rumandText = "insert into Employee  values (101, "F", "L")"
            mycommand1.ExecuteNonQuery()
            mycommand1.rumandText = "insert into Employee values (101, "F", "L")"
            mycommand1.ExecuteNonQuery()
            mycommand2.rumandText = "select * from Employee"
            myreader = mycommand2.ExecuteReader()
            Console.WriteLine("Last 2 Orders - Transaction is pending")
            Console.WriteLine("======================================")
            While myreader.Read()
                Console.WriteLine(myreader.GetInt32(0))
            End While
            myreader.Close()
            Console.ReadLine()
            mytransaction1.Rollback()
            mycommand2.rumandText = "select * from Employee"
            myreader = mycommand2.ExecuteReader()
            Console.WriteLine("Last 2 Orders - Transaction rolled back")
            Console.WriteLine("=======================================")
            While myreader.Read()
                Console.WriteLine(myreader.GetInt32(0))
            End While
            Console.ReadLine()
        Catch e As Exception
            Console.WriteLine(e.Message)
        Finally
            myconnection1.Close()
            myconnection2.Close()
        End Try
    End Sub
End Class

Simple Transaction Commit and RollBack: SqlTransaction has completed; it is no longer usable.

Imports System
Imports System.Data
Imports System.Data.SqlClient
public class MainClass
   Shared Sub Main()
        Dim myconnection As SqlConnection
        Dim mycommand As SqlCommand
        Dim mytransaction As SqlTransaction
        Dim ConnectionString As String

        myconnection = New SqlConnection("server=(local)\SQLEXPRESS;" & _
          "integrated security=sspi;database=MyDatabase")
        myconnection.Open()
        "start a transaction
        mytransaction = myconnection.BeginTransaction()
        "configure command object to use transaction
        mycommand = New SqlCommand
        mycommand.Connection = myconnection
        mycommand.Transaction = mytransaction
        Try
            mycommand.rumandText = "insert into Employee(ID," & _
                      "FirstName,LastName) values(119, " & _
                      ""F","L")"
            mycommand.ExecuteNonQuery()
            mytransaction.Rollback()
            Console.WriteLine("Rollback()")
            mycommand.rumandText = "insert into Employee(ID," & _
                      "FirstName,LastName) values(119, " & _
                      ""F","L")"
            mytransaction.rumit()
        Catch e As Exception
            Console.WriteLine(e.Message)
        Finally
            myconnection.Close()
        End Try
   End Sub
End Class

Transaction Commit Demo

Imports System
Imports System.Data
Imports System.Data.SqlClient

public class MainClass
   Shared Sub Main()
      Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
          "integrated security=sspi;database=MyDatabase")
      " SQL Delete Commands
      Dim sql1 As String = "DELETE FROM Employee " & _
         "WHERE ID = 10" 
      Dim sql2 As String = "DELETE FROM Employee " & _
         "WHERE ID = 9"
      " Create command
      Dim thisCommand As New SqlCommand(sql1, thisConnection)
      " Create Transaction
      Dim thisTransaction As SqlTransaction
      Try
         " Open Connection
         thisConnection.Open()
         " Begin transaction and attach it to command
         thisTransaction = thisConnection.BeginTransaction()
         thisCommand.Transaction = thisTransaction
         " Run first delete command
         thisCommand.ExecuteNonQuery()
         " Setup and run second delete command
         thisCommand.rumandText = sql2
         thisCommand.ExecuteNonQuery()
         " Commit transaction
         thisTransaction.rumit()
         Console.WriteLine("Transaction Committed. Data Deleted")
      Catch ex As Exception
         " Roll back transaction
         thisTransaction.Rollback()
         Console.WriteLine("Transaction rolled back : " & ex.Message)
      Finally
         thisConnection.Close()
      End Try
   End Sub
End Class

Transaction Rollback Demo

Imports System
Imports System.Data
Imports System.Data.SqlClient

public class MainClass
   Shared Sub Main()
      Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
          "integrated security=sspi;database=MyDatabase")
      " SQL Delete Commands
      Dim sql As String = "DELETE FROM Employee " & _
         "WHERE ID = 10" 
      " Create command
      Dim thisCommand As New SqlCommand(sql, thisConnection)
      " Create Transaction
      Dim thisTransaction As SqlTransaction
      Try
         " Open Connection
         thisConnection.Open()
         " Begin transaction and attach it to command
         thisTransaction = thisConnection.BeginTransaction()
         thisCommand.Transaction = thisTransaction
         " Run delete command
         thisCommand.ExecuteNonQuery()
         " Commit transaction
         thisTransaction.rumit()
         " Display success
         Console.WriteLine("Transaction Committed. Data Deleted")
      Catch ex As Exception
         " Roll back transaction
         thisTransaction.Rollback()
         Console.WriteLine("Transaction rolled back : " & ex.Message)
      Finally
         " Close Connection
         thisConnection.Close()
      End Try
   End Sub
End Class

Transaction save point and roll back

Imports System
Imports System.Data
Imports System.Data.SqlClient
public class MainClass
   Shared Sub Main()
        Dim myconnection As SqlConnection
        Dim mycommand As SqlCommand
        Dim mytransaction As SqlTransaction
        Dim myreader As SqlDataReader
        "open a database connection
        myconnection = New SqlConnection("server=(local)\SQLEXPRESS;" & _
          "integrated security=sspi;database=MyDatabase")
        myconnection.Open()
        mytransaction = myconnection.BeginTransaction()
        mycommand = New SqlCommand()
        mycommand.Connection = myconnection
        mycommand.Transaction = mytransaction
        Try
            mycommand.rumandText = "insert into Employee values ("111","F","L")"
            mycommand.ExecuteNonQuery()
            mytransaction.Save("firstorder")
            mycommand.rumandText = "insert into Employee values ("112","F","L")"
            mycommand.ExecuteNonQuery()
            mycommand.rumandText = "insert into Employee values ("113","F","L")"
            mycommand.ExecuteNonQuery()
            mytransaction.Rollback("firstorder")
            mycommand.rumandText = "insert into Employee values ("114","F","L")"
            mycommand.ExecuteNonQuery()
            mycommand.rumandText = "insert into Employee values ("115","F","L")"
            mycommand.ExecuteNonQuery()
            mytransaction.rumit()
            mycommand.rumandText = "select * from Employee"
            myreader = mycommand.ExecuteReader()
            Console.WriteLine("3 Records")
            While myreader.Read()
                Console.WriteLine(myreader.GetInt32(0))
            End While
        Catch e As Exception
            Console.WriteLine(e.Message)
            Console.ReadLine()
        Finally
            myconnection.Close()
        End Try
   End Sub
End Class