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 row 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)
If tb.Rows.Count = 0 Then
MessageBox.Show("レコードがありません")
cn.Close()
Exit Sub
End If
tbl = ds.Tables("TEST_tbl")
tb.Rows(0).Delete()
'トランザクション開始
tx = cn.BeginTransaction(IsolationLevel.ReadCommitted, "TRAN")
cmd.Transaction = tx
cmd.CommandText = "DELETE TEST_tbl WHERE NAME = 'BBB'"
adp.DeleteCommand = 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
|