Skip to content

Instantly share code, notes, and snippets.

@tamago324
Created October 4, 2017 05:03
Show Gist options
  • Save tamago324/89e49bcde90a67e0008bb4d91f3e02d6 to your computer and use it in GitHub Desktop.
Save tamago324/89e49bcde90a67e0008bb4d91f3e02d6 to your computer and use it in GitHub Desktop.
VB.net transaction
Dim con As System.Data.SqlClient.SqlConnection = Nothing
Dim tran As System.Data.SqlClient.SqlTransaction = Nothing
Try
Dim serverName As String = "localhost" ' 例) 192.168.0.173
Dim dbName As String = "testdb"
Dim userId As String = "sa"
Dim password As String = "sqlserver"
con = New System.Data.SqlClient.SqlConnection()
con.ConnectionString =
"Data Source = " & serverName &
";Initial Catalog = " & dbName &
";User ID = " & userId &
";Password = " & password
con.Open()
'トランザクションの開始
tran = con.BeginTransaction
' SQL実行するためのオブジェクト?
Dim sqlCmd As SqlClient.SqlCommand
sqlCmd = con.CreateCommand()
' トランザクションをすることを明示する
sqlCmd.Transaction = tran
sqlCmd.CommandText = "CREATE TABLE TEST_TABLE1 (ID CHAR(3) PRIMARY KEY)"
sqlCmd.ExecuteNonQuery()
' VARCHARがVRCHARになっているため、エラーになる
sqlCmd.CommandText = "CREATE TABLE TEST_TABLE2 (NAME VRCHAR(256) PRIMARY KEY)"
sqlCmd.ExecuteNonQuery()
' コミット
tran.Commit()
Console.WriteLine("コミット")
Catch ex As Exception
Console.WriteLine("Error! {0}", ex.Message)
' ロールバック
tran.Rollback()
Console.WriteLine("ロールバック")
Finally
' コネクションが閉じられていないとき閉じる
If Not con.State = ConnectionState.Closed Then
con.Close()
End If
' リソースの開放
tran.Dispose()
con.Dispose()
End Try
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment