1

Preparing an application which will be used by around 40 users in office with local SQL Server at local network. Application developed in VB.NET. I already read some documentation but would like to get some knowledge directly from your side about access to data.

This is a Winforms app and I wonder whether transactions I am using will be just enough to protect data e.g when one user uses some data and other one will change it in same time, does transaction would protect it? Can someone explain me briefly how it is?

Example of SQL transaction I use in my application

Dim result As Boolean = True
Dim strcon = New AppSettingsReader().GetValue("ConnectionString", GetType(String)).ToString()
Using connection As New SqlConnection(strcon)
    '-- Open generall connection for all the queries
    connection.Open()
    '-- Make the transaction.
    Dim transaction As SqlTransaction
    transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
    Try
        For Each sentId In pSentsId
            '-- Insert aricle to Article's table (T_Artikel) and get inserted row id to use it in other queries
            Using cmd As New SqlCommand("INSERT INTO T_Sentence_SubSec_SecKatSubKat_SubSubKat (FK_Sentence_ID, FK_SubSec_SecKatSubKat_SubSubKat) VALUES (@FK_Sentence_ID, @FK_SubSec_SecKatSubKat_SubSubKat)", connection)
                cmd.CommandType = CommandType.Text
                cmd.Connection = connection
                cmd.Transaction = transaction
                cmd.Parameters.AddWithValue("@FK_Sentence_ID", sentId)
                cmd.Parameters.AddWithValue("@FK_SubSec_SecKatSubKat_SubSubKat", SubSec_SecKatSubKat_SubSubKat)
                cmd.ExecuteScalar()
            End Using
        Next
        transaction.Commit()
    Catch ex As Exception
        result = False
        '-- Roll the transaction back.
        Try
            transaction.Rollback()
        Catch ex2 As Exception
            ' This catch block will handle any errors that may have occurred
            ' on the server that would cause the rollback to fail, such as
            ' a closed connection.
            'Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
            'Console.WriteLine("  Message: {0}", ex2.Message)
        End Try
    End Try
End Using
Return result
Improve this question
7
  • 3
    You create SqlCommand in each loop. This is bad practice. Create SqlCommand once and add parameters outside the loop. Set parameter values and ExecuteNonQuery in the loop. Catch SqlException first. Commented Jul 25, 2016 at 19:51
  • 1
    There isn't a lot of value of wrapping a single insert statement inside of a transaction. There is already an implicit transaction when inserting data. I think what you are asking about is concurrency or possibly deadlock issues. Using a transaction around an insert statement is not going to do much there. As a side note you should be careful using AddWithValue in pass through queries like this. The datatype will sometimes get interpreted incorrectly. blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… Commented Jul 25, 2016 at 19:52
  • I believe as this is running if another user inserted a record with the same key values, then it would trigger (think dbConcurrencyException). As written, your exception handler should fire and roll back, but this may not be what you're intending to do. Commented Jul 25, 2016 at 20:05
  • @AlexKudryashev or any one else - how to rebuild that query to make it better? Does it mean also current implementation is not enough? Commented Jul 25, 2016 at 20:25
  • The query itself looks perfect. A performance issue comes from creating SqlCommand in each loop. See my previous comment. No need to set IsolationLevel.ReadCommitted; this is default value. Depending on your business rules you can commit / rollback each insert or the whole group. Add Finally where you close connection. Commented Jul 25, 2016 at 20:40

1 Answer 1

Reset to default
1

There are two versions according to your business rules.
A) All inserts succeed or all fail. 

Dim result As Boolean = True
Dim strcon = New AppSettingsReader().GetValue("ConnectionString", GetType(String))'.ToString()'no need. It is already **String**
Using connection As New SqlConnection(strcon)
  ''//--Following 2 lines are OK 
  ''//--Using cmd As New SqlCommand("INSERT INTO T_Sentence_SubSec_SecKatSubKat_SubSubKat (FK_Sentence_ID, FK_SubSec_SecKatSubKat_SubSubKat) VALUES (@FK_Sentence_ID, @FK_SubSec_SecKatSubKat_SubSubKat)", connection)
    ''//--cmd.CommandType = CommandType.Text
   ''//--but these two are better
   Using cmd As New SqlCommand("dbo.T_Sentence_SubSec_SecKatSubKat_SubSubKat_ins ", connection)
  ''//-- Insert aricle to Articles table (T_Artikel) and get inserted 
  ''//--row id to use it in other queries  
    cmd.CommandType = CommandType.StoredProcedure
   ''//-- Open generall connection for all the queries
    ''//--open connection in try block
    ''//--connection.Open()
    ''//-- Make the transaction.
    ''//--Dim transaction As SqlTransaction
    ''//--transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
    Try
        connection.Open()
        cmd.Transaction = connection.BeginTransaction()
        cmd.Parameters.Add("@FK_Sentence_ID", SqlDbType.Int) ''//--or whatever
        cmd.Parameters.Add("@FK_SubSec_SecKatSubKat_SubSubKat", SqlDbType.Int)

       For Each sentId In pSentsId
           cmd.Parameters("@FK_Sentence_ID").Value = sentId
           cmd.Parameters("@FK_SubSec_SecKatSubKat_SubSubKat").Value =  SubSec_SecKatSubKat_SubSubKat
           cmd.ExecuteNonQuery() ''//--returns rows affected. We do not use result
        Next
        ''//--everything is OK
        cmd.Transaction.Commit()
    Catch ex as SqlException
       result = False
       ''//--SqlException is more informative for this case
       If cmd.Transaction IsNot Nothing
         cmd.Transaction.Rollback
         ''//--extra try...catch if you wish
       End If
    Catch ex As Exception
        result = False
        ''//-- Roll the transaction back.
        Try
            cmd.Transaction.Rollback()
        Catch ex2 As Exception
            ''// This catch block will handle any errors that may have occurred
            ''// on the server that would cause the rollback to fail, such as
            ''// a closed connection.
            ''//Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
            ''//Console.WriteLine("  Message: {0}", ex2.Message)
        End Try
    Finally
      If connection.State <> Closed
        connection.Close()
      End If
    End Try
  End Using''//cmd
End Using''//connection
Return result

B) Each insert is independent. Don't use overall transaction. Add try...catch inside for loop.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.