VB.Net/Database ADO.net/Transaction
Содержание
DataSet transaction
<source lang="vbnet"> 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
</source>
Isolation Level ReadUncommitted
<source lang="vbnet"> 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
</source>
Simple Transaction Commit and RollBack: SqlTransaction has completed; it is no longer usable.
<source lang="vbnet"> 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
</source>
Transaction Commit Demo
<source lang="vbnet"> 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
</source>
Transaction Rollback Demo
<source lang="vbnet"> 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
</source>
Transaction save point and roll back
<source lang="vbnet"> 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
</source>
