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

Before you do anything else you need to read about, understand and start using parameterized queries.Your code is wide open to sql injection. You also should look at using SCOPE_IDENTITY instead of @@Identity. Hopefully what you were advised of is to use parameterized queries. A transaction by itself does NOT perform inserts etc. — Sean Lange, Jul 18 '16 at 13:06
You should also be careful with `Shared` functions like your `SaveNewPerson`. If you use shared variables in it this could mess things up in a multi-threading system. — Alex B., Jul 18 '16 at 13:13
Another recommendation would be to store birthdate instead of age. Age is a calculated value based on the current real world time, not stored as a fact in a table. — Sean Lange, Jul 18 '16 at 13:16
First, take @SeanLange advice and learn to use parameterized queries. Secondly, look at the docs for the `OleDbConnection` class. You will find methods called `BeginTransaction`, `Commit` and `Rollback`. Basically, you would call `BeginTransaction`, then perform any DB commands, and if there were no errors, you call `Commit`. If an exception is encountered, you call `Rollback`. — Chris Dunaway, Jul 18 '16 at 13:17
@SeanLange So, if I was to use something like `$firstName = $_REQUEST('firstName')` to create the parameters, would this work to prevent it? I'm only going off what this article has said https://blogs.msdn.microsoft.com/sqlphp/2008/09/30/how-and-why-to-use-parameterized-queries/ — , Jul 18 '16 at 13:17
You can try something like [this](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#Anchor_6) (from MSDN) — gofr1, Jul 18 '16 at 13:18
@joe that example if for php. That isn't going to do anything here except confuse you more. You need to find a tutorial on using parameters with dotnet. — Sean Lange, Jul 18 '16 at 13:22
@AlexB. I don't understand your comment either. What do you mean by that? — Sean Lange, Jul 18 '16 at 13:22
If you have multiple-threads calling a Shared function and you don´t lock the (shared) variables than it´s likely you get [concurrency issues](https://en.wikipedia.org/wiki/Thread_(computing)#Concurrency_and_data_structures). This may be irrelevant if the QO only uses only one thread. — Alex B., Jul 18 '16 at 13:31
I don't see where the concurrency concern is from this query. There does not appear to be any kind of a race condition here at all. It is a simple insert and none of the values present appear to be influenced by concurrency problems. — Sean Lange, Jul 18 '16 at 13:34
**Currently** there is no race condittion. But it´s easy to create one. So why risk it at all and create a shared function? Use an object oriented way and get rid of drawbacks which might you hunt later and are painful to find. — Alex B., Jul 18 '16 at 13:37
@SeanLange the only tutorials on this that I can find are using SqlClient, whereas I am using OleDb... Do I need to revert it all to SqlClient or will it work with OleDb? — , Jul 18 '16 at 13:51
One downside of OleDb is you can't name your parameters. Is there a reason you are using OleDb? Here is the MS page that provides a walk through with parameterized queries in OleDb. https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx — Sean Lange, Jul 18 '16 at 14:47
@SeanLange Whilst I personally agree that it makes much more sense to use SqlClient with this sort of program, I too use OleDb, but only because that's how I was taught it at college, and the guys at the software house I work in use it over SqlClient too. Strange, really. — David, Jul 18 '16 at 15:11

David · Accepted Answer · 2016-08-02T13:13:12.767

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

this worked, what about paramatising code to make it less open to sql injection? — , Jul 18 '16 at 15:24
PLEASE don't post answers that are wide open to sql injection like this. It needs to be parameterized. — Sean Lange, Jul 18 '16 at 15:24
@SeanLange I only posted this to help with the initial problem. I myself am not totally sure how paramatising works, that may be for a different question. But who says this is going to be a program that goes live? It may not even be a worry for this if it isn't used and is only for a course/degree etc — David, Jul 18 '16 at 15:27
Sadly, the techniques people learn in school are the same way they program in the real world. Learning to do it correctly initially promotes good coding practice. And learning to do it incorrectly promotes poor develop practices. This should be parameterized and it should use SCOPE_IDENTITY instead. Then it would be a great answer. — Sean Lange, Jul 18 '16 at 15:37
@SeanLange I can/have added in the SCOPE_IDENTITY statement (although I don't see too many disadvantages with it, I did read that @@IDENTITY causes problems when inserting records with conditional statements?) I can see that this is better, and will be using this more regularly now, thank you! — David, Jul 18 '16 at 15:56
@David. You can read more details about it here. http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ — Sean Lange, Jul 18 '16 at 16:00

SQL transaction statement in vb.net

1 Answers1