VB.NetDatabase Topics

ASP.Net - Database Transaction Sample 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
    Dim intRow As Integer
    Dim ctInsertedRec As Integer = 0
    Dim ctUpdatedRec As Integer = 0
    Dim ctDeletedRec As Integer = 0

    If MessageBox.Show("Are you sure you want to save all your changes?", "Please Confirm Save.", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = System.Windows.Forms.DialogResult.Yes Then
        Dim blnCommit As Boolean = True
        _InEditMode = False

        Dim currentTransaction As ITransaction _
            = DataLayer.APM.TransactionBegin("frmSalary.cmdSave.Click", "UpdateData")

        cnSalary = New SqlConnection
        cnSalary.ConnectionString = Salary.ConnectionString

        Dim intNextID As Int32 = GetSeed()
        'Assign valid PositionKey to records added during the edit session
        For intRows As Int32 = 1 To grdSalary.Rows.Count - 1
            ' if id needed for this row
            If (grdSalary(intRows, Posn.enPositionKey) = 1) Then
                intNextID += 1
                grdSalary(intRows, Posn.enPositionKey) = intNextID
            End If
        Next

        If cnSalary.State = ConnectionState.Closed Then cnSalary.Open()
        trnSalary = cnSalary.BeginTransaction

        Try
            'Loop through grid and perform CRUD
            For intRow = 1 To grdSalary.Rows.Count - 1
                Trace.WriteLine("----------" & intRow)
                If grdSalary.Rows(intRow).StyleDisplay.ForeColor.Equals(Color.Red) Then
                    ' Row marked for deletion
                    If IsDBNull(grdSalary(intRow, Posn.enPositionRecId)) Then
                        ' Newly inserted rows by user have null value for PositionRecId
                        ' Remove row from grid
                        grdSalary.Rows.Remove(intRow)
                    Else
                        ' This is an existing record in database
                        ' Remove record from table in database
                        If DeletePositions(intRow) = False Then
                            blnCommit = False
                            Exit For
                        Else
                            ctDeletedRec += 1
                        End If
                    End If

                ElseIf grdSalary.Rows(intRow).StyleDisplay.ForeColor.Equals(Color.Blue) Then
                    ' Row modified by user or newly inserted by user
                    If IsDBNull(grdSalary(intRow, Posn.enPositionRecId)) Then

                        'lngRecordID = CreateID()
                        grdSalary(intRow, Posn.enPositionRecId) = CreateID()
                        If Insert2Positions(intRow) = False Then
                            blnCommit = False
                            Exit For
                        Else
                            ctInsertedRec += 1
                        End If
                    Else
                        ' This is an existing record in database
                        ' Update record in table 
                        If UpdatePositions(intRow) = False Then
                            blnCommit = False
                            Exit For
                        Else
                            ctUpdatedRec += 1
                        End If
                    End If
                End If
            Next

            If blnCommit Then
                trnSalary.Commit()
                PopulateGrid(m_strSQL, "Save")
                MsgBox("All changes posted to database.  " & ctInsertedRec & " inserted, " & ctUpdatedRec & " updated, " & ctDeletedRec & " deleted.", , "Salary")
            Else
                trnSalary.Rollback()
            End If
        Catch ex As Exception
            MsgBox("Error occurred while saving changes." & vbCrLf & ex.Message, , "Error")
            trnSalary.Rollback()
        Finally
            cnSalary.Close()

            DataLayer.APM.TransactionEnd(currentTransaction)
        End Try

        For x As Posn = Posn.enAnnualSalary To Posn.enModDateTime
            grdSalary.Cols(x).Visible = True
        Next

    End If
End Sub