joe joe - 4 months ago 11
SQL Question

SQL transaction statement in vb.net

I'm making a project that is linked to Microsoft SQLServer, used to enter, remove, edit data about customers and orders. The full system works, however I've been advised to use transactions rather than regular SQL statements to add/remove/edit data etc.

The trouble is, I've not used these before and from my research over the last few hours, I can't work out how to even begin them.

Can anybody advise me how to turn the following code into a transaction?

Public Shared Function SaveNewPerson(ByVal firstName As String, lastName As String, ByVal age As Integer, ByVal postcode As String, m_cn As OleDbConnection)

Dim Dc As New OleDbCommand
Dc.Connection = m_cn

m_cn.Open()

Dc.CommandText = "INSERT INTO tblPerson([firstName], [lastName], [age], [postcode]) VALUES('" & firstName & "', '" & lastName & "', '" & age & "', '" & postcode & "')"

Dc.ExecuteNonQuery()

Dim personID As Integer

Dc.CommandText = "SELECT @@IDENTITY"
Dc.CommandType = CommandType.Text
personID = CType(Dc.ExecuteScalar(), Integer)

m_cn.Close()

End Function

Answer

I've just been learning TSQL, see if this sort of code will work for you (note that you need to Dim tr (with a different variable name, if you like) and use it in multiple places, but unlike in some languages you don't need to set up objects for the different methods.

 Public Shared Function SaveNewPerson(ByVal firstName As String, lastName As String, ByVal age As Integer, ByVal postcode As String, m_cn As OleDbConnection)

    Dim tr As OleDbTransaction = Nothing

    Try
        tr = m_cn.BeginTransaction()

        Dim Dc As New OleDbCommand
        Dc.Connection = m_cn

        Dc.CommandText = "INSERT INTO tblPerson([firstName], [lastName], [age], [postcode]) VALUES('" & firstName & "', '" & lastName & "', '" & age & "', '" & postcode & "')"
        Dc.Transaction = tr
        Dc.ExecuteNonQuery()

        Dim personID As Integer

        Dc.CommandText = "SELECT @@IDENTITY"
        Dc.CommandType = CommandType.Text
        personID = CType(Dc.ExecuteScalar(), Integer)

        tr.Commit()

    Catch ex As Exception

        tr.Rollback()

        Throw
    End Try

End Function
Comments