в 16:40, 26 мая 2010

2010-05-26T16:40:06Z

Admin: 1 версия

2010-05-26T12:44:59Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:40, 26 мая 2010
(нет различий)

VB.Net/Database ADO.net/Transaction - История изменений

в 16:40, 26 мая 2010

Admin: 1 версия