JimmyJimm JimmyJimm - 4 months ago 15
Vb.net Question

Multiple SQL Server databases access

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

Answer

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.