PGlabo

« [Access VBA] フォームを開く・閉じる | メイン | [ASP.NET] DataGrid に編集ボタンをつける »

2005年11月01日

[VB.NET|SQLServer] レコードの削除・挿入・更新（トランザクション有）

    '*** 宣言
    Imports System.Data.SqlClient
    Private Conn As SqlConnection
    Private DA As SqlDataAdapter
    Private DS As DataSet

    Dim strConn As String = _

        "Server=localhost;User ID=***;Password=***;database=Test;"

    Dim SqlTran As SqlTransaction

    Dim strSQL As String

    Dim cmdSQL As SqlCommand

    Dim dRow As DataRow

    Dim dCol As DataColumn

    Dim i As Integer

    Dim j As Integer

        '*** Form_Load

        Me.Conn = New SqlConnection(strConn)

        Me.DA = New SqlDataAdapter

        Me.DS = New DataSet

        Me.Conn.Open()

        '*** Form_Closed

        Me.DS.Dispose()

        Me.DA.Dispose()

        Me.Conn.Close()

        Me.Conn.Dispose()

'排他処理する場合？
Me.SqlTran = Me.Conn.BeginTransaction(IsolationLevel.Serializable)
strSQL = "SELECT * FROM TestTable WITH(ROWLOCK, UPDLOCK) ORDER BY TestCol1 ASC"

'--- SELECT
        Try
            Me.SqlTran = Me.Conn.BeginTransaction()

            strSQL = "SELECT * FROM TestTable ORDER BY TestCol1 ASC"

            Me.cmdSQL = New SqlCommand(strSQL, Me.Conn, Me.SqlTran)

            Me.DA.SelectCommand = Me.cmdSQL

            Me.DS.Clear()

            Me.DA.Fill(Me.DS, "TestTable")

            Call ListView_Disp(Me.DS)

            Me.SqlTran.Commit()

        Catch ex As Exception

            Debug.WriteLine(ex.ToString)

            MessageBox.Show(ex.ToString)

            Me.SqlTran.Rollback()

        Finally

            Me.cmdSQL.Dispose()

            Me.SqlTran.Dispose()

        End Try

 '--- INSERT
        dRow = Me.DS.Tables("TestTable").NewRow
        dRow(0) = CInt(Me.TextBox1.Text)
        dRow(1) = CStr(Me.TextBox2.Text)
        dRow(2) = CStr(Me.TextBox3.Text)
        Me.DS.Tables("TestTable").Rows.Add(dRow)

        Try

            Me.SqlTran = Me.Conn.BeginTransaction()

            strSQL = "INSERT INTO TestTable VALUES (@Col1, @Col2, @Col3)"

            Me.cmdSQL = New SqlCommand(strSQL, Me.Conn, Me.SqlTran)

            Me.cmdSQL.Parameters.Add("@Col1", SqlDbType.Int, 4, "TestCol1")

            Me.cmdSQL.Parameters.Add("@Col2", SqlDbType.VarChar, 50, "TestCol2")

            Me.cmdSQL.Parameters.Add("@Col3", SqlDbType.VarChar, 50, "TestCol3")

            Me.DA.InsertCommand = Me.cmdSQL

            Me.DA.InsertCommand.Parameters("@Col1").Value = CInt(Me.TextBox1.Text)

            Me.DA.InsertCommand.Parameters("@Col2").Value = CStr(Me.TextBox2.Text)

            Me.DA.InsertCommand.Parameters("@Col2").Value = CStr(Me.TextBox3.Text)

            Me.DA.Update(Me.DS, "TestTable")

            Call ListView_Disp(Me.DS)

            Me.SqlTran.Commit()

        Catch ex As Exception

            Debug.WriteLine(ex.ToString)

            MessageBox.Show(ex.ToString)

            Me.SqlTran.Rollback()

        Finally

            Me.cmdSQL.Dispose()

            Me.SqlTran.Dispose()

        End Try

 '--- DELETE
        For Each dRow In DS.Tables("TestTable").Rows
            If CInt(dRow(0)) = CInt(TextBox1.Text) Then
                dRow.Delete()
                Exit For
            End If
        Next

        Try

            Me.SqlTran = Me.Conn.BeginTransaction

            strSQL = "DELETE FROM TestTable WHERE TestCol1 = @Col1"

            Me.cmdSQL = New SqlCommand(strSQL, Me.Conn, Me.SqlTran)

            Me.cmdSQL.Parameters.Add("@Col1", SqlDbType.Int, 4, "TestCol1")

            Me.DA.DeleteCommand = Me.cmdSQL

            Me.DA.DeleteCommand.Parameters("@Col1").Value = CInt(Me.TextBox1.Text)

            Me.DA.Update(Me.DS, "TestTable")

            Call ListView_Disp(Me.DS)

            Me.SqlTran.Commit()

        Catch ex As Exception

            Debug.WriteLine(ex.ToString)

            MessageBox.Show(ex.ToString)

            Me.SqlTran.Rollback()

        Finally

            Me.cmdSQL.Dispose()

            Me.SqlTran.Dispose()

        End Try

'--- UPDATE
        For Each dRow In DS.Tables("TestTable").Rows
            If CInt(dRow(0)) = CInt(TextBox1.Text) Then
                dRow(1) = TextBox2.Text
                dRow(2) = TextBox3.Text
                Exit For
            End If
        Next

        Try

            Me.SqlTran = Me.Conn.BeginTransaction

            strSQL = "UPDATE TestTable SET TestCol2 = @Col2, TestCol3 = @Col3 WHERE TestCol1 = @Col1"

            Me.cmdSQL = New SqlCommand(strSQL, Me.Conn, Me.SqlTran)

            Me.cmdSQL.Parameters.Add("@Col1", SqlDbType.Int, 4, "TestCol1")

            Me.cmdSQL.Parameters.Add("@Col2", SqlDbType.VarChar, 50, "TestCol2")

            Me.cmdSQL.Parameters.Add("@Col3", SqlDbType.VarChar, 50, "TestCol3")

            DA.UpdateCommand = Me.cmdSQL

            Me.DA.UpdateCommand.Parameters("@Col1").Value = CInt(Me.TextBox1.Text)

            Me.DA.UpdateCommand.Parameters("@Col2").Value = CStr(Me.TextBox2.Text)

            Me.DA.UpdateCommand.Parameters("@Col2").Value = CStr(Me.TextBox3.Text)

            Me.DA.Update(Me.DS, "TestTable")

            Call ListView_Disp(Me.DS)

            Me.SqlTran.Commit()

        Catch ex As Exception

            Debug.WriteLine(ex.ToString)

            MessageBox.Show(ex.ToString)

            Me.SqlTran.Rollback()

        Finally

            Me.cmdSQL.Dispose()

            Me.SqlTran.Dispose()

        End Try

2005 / 11 / 01