SQL transaction statement in vb.net

Question

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 1

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 SaveNewIncident(ByVal clientName As String, dateStart As Date, dateEnd As Date, ByVal incidentProblem As String, ByVal timeStart As String, ByVal timeEnd As String,
                                       ByVal incidentSolved As Boolean, ByVal incidentSolution As String, _con As OleDbConnection)

    Dim tr As OleDbTransaction = Nothing

    Try

        Dim Dc As New OleDbCommand
        Dc.Connection = _con

        tr = _con.BeginTransaction()

        Dc.CommandType = CommandType.Text
        Dc.CommandText = "INSERT INTO dbo.tblIncidents VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
        Dc.Transaction = tr
        Dc.Parameters.Add("@clientName", OleDbType.VarChar).Value = clientName
        Dc.Parameters.Add("@dateStart", OleDbType.Date).Value = dateStart
        Dc.Parameters.Add("@dateEnd", OleDbType.Date).Value = dateEnd
        Dc.Parameters.Add("@incidentProblem", OleDbType.LongVarChar).Value = incidentProblem
        Dc.Parameters.Add("@timeStart", OleDbType.VarChar).Value = timeStart
        Dc.Parameters.Add("@timeEnd", OleDbType.VarChar).Value = timeEnd
        Dc.Parameters.Add("@incidentSolved", OleDbType.Boolean).Value = incidentSolved
        Dc.Parameters.Add("@incidentSolution", OleDbType.LongVarChar).Value = incidentSolution

         Dim personID As Integer

        Dc.CommandText = "SELECT SCOPE_IDENTITY() AS personID"
        Dc.CommandType = CommandType.Text
        personID = CType(Dc.ExecuteScalar(), Integer)

        tr.Commit()

    Catch ex As Exception

        tr.Rollback()

        Throw
    End Try

End Function