Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click
Dim cn As New SqlClient.SqlConnection 'SQL接続用オブジェクト
Dim cmd As New SqlClient.SqlCommand 'SQL接続コマンド
Dim ds As DataSet 'データセット
Dim tb As DataTable 'データテーブル
Dim ttt As String
'接続文字列を指定
cn.ConnectionString = "Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=TESTDB;" & _
"Data Source=FUKU;" & _
"Workstation ID=FUKU;"
'接続
Try
cn.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'コマンド作成
cmd.Connection = cn
'クエリ文字列を指定する
cmd.CommandText = "SELECT * FROMTEST_tbl WHERE NAME = 'BBB'"
adp.SelectCommand = cmd
'データセットクリアー＆作成
ds = New DataSet
ds.Clear()
'データセット作成
Try
adp.Fill(ds, "TEST_tbl")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'データセットの内容を変更
tb = ds.Tables(0)
tb.Rows(0).Item("NAME") = "XXX"
'トランザクション開始
tx = cn.BeginTransaction(IsolationLevel.ReadCommitted, "TRAN")
cmd.Transaction = tx
cmd.CommandText = "UPDATE TEST_tbl SET NAME = 'XXX' WHERE ID ='20'"
adp.UpdateCommand = cmd
Try
i = adp.Update(ds, "TEST_tbl") '更新
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'コミット
Try
cmd.Transaction.Commit()
Catch ex As Exception
Try
'ロールバック
tx.Rollback("TRAN")
Catch ex2 As Exception
If Not tx.Connection Is Nothing Then
tx.Rollback("TRAN")
End If
End Try
End Try
cn.Close()
End Sub
|