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
row = tb.NewRow
tb.Rows.Add(row)
tb.Rows(0).Item("NAME") = "XXX"
'トランザクション開始
tx = cn.BeginTransaction(IsolationLevel.ReadCommitted, "TRAN")
cmd.Transaction = tx
cmd.CommandText = "INSERT INTO TEST_tbl VALUES('XXX')"
adp.InsertCommand = 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
|