Transaction save point and roll back : Transaction « Database ADO.net « VB.Net
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.CommandText = "insert into Employee values ('111','F','L')"
mycommand.ExecuteNonQuery()
mytransaction.Save("firstorder")
mycommand.CommandText = "insert into Employee values ('112','F','L')"
mycommand.ExecuteNonQuery()
mycommand.CommandText = "insert into Employee values ('113','F','L')"
mycommand.ExecuteNonQuery()
mytransaction.Rollback("firstorder")
mycommand.CommandText = "insert into Employee values ('114','F','L')"
mycommand.ExecuteNonQuery()
mycommand.CommandText = "insert into Employee values ('115','F','L')"
mycommand.ExecuteNonQuery()
mytransaction.Commit()
mycommand.CommandText = "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
