How to use transaction with data adapter and autogenerated commands

[silverblatt]

I'm buiding a VB.Net client-server application using VS2017 with an SQL back end. The application includes a form which will allow the user to insert a new record or to display (and optionally update or delete) an existing record. I need to use a transaction to prevent concurrency issues. Because the table has dozens of columns, I also want to have the data adapter autogenerate the appropriate insert, update and delete commands, rather than manually coding the SQL. However, I can't figure out how to make transactions work with autogenerated commands - no matter how I re-arrange my code I get some sort of run-time error. For example, the following code produces the error "Object reference not set to an instance of an object." when it executes the last line shown:

Public Function InitializedSQLDataAdapter(SelectStatement As String, SQLConn As SqlConnection,
Optional UseCommandBuilder As Boolean = False) As SqlDataAdapter
'returns an SQLDataAdapter object, using SelectStatement and SQLConn, and including INSERT,
'UPDATE and DELETE commands if UseCommandBuilder is True
Dim DAdapter As SqlDataAdapter = New SqlDataAdapter(SelectStatement, SQLConn)
Try
If UseCommandBuilder Then
Dim CmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(DAdapter)
CmdBuilder.GetInsertCommand()
CmdBuilder.GetUpdateCommand()
CmdBuilder.GetDeleteCommand()
End If
Return DAdapter
Catch ex As SystemException
DisplayException(ex, "Utility.InitializedSQLDataAdapter")
End Try
End Function
...
Conn.Open()
SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
DAdapter = InitializedSQLDataAdapter(SQL, Conn, True)
trans = Conn.BeginTransaction(IsolationLevel.Serializable)
DAdapter.UpdateCommand.Transaction = trans

If I use the following code instead, I get the same error in the same place:

Conn.Open()
trans = Conn.BeginTransaction(IsolationLevel.Serializable)
SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
DAdapter = New SqlDataAdapter(SQL, Conn)
DAdapter.UpdateCommand.Transaction = trans

If I use the following code instead, I get a different error ("ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.") on the last line shown:

Conn.Open()
trans = Conn.BeginTransaction(IsolationLevel.Serializable)
SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
DAdapter = New SqlDataAdapter(SQL, Conn)
DAdapter.Fill(DTable)

So, it seems that the command itself has to have a transaction, but when I try to assign one to it I always get one of these errors. How can I make this work?

[jmcilhinney]

Please use appropriate formatting tags when posting code snippets for readability.

I'm buiding a VB.Net client-server application using VS2017 with an SQL back end. The application includes a form which will allow the user to insert a new record or to display (and optionally update or delete) an existing record. I need to use a transaction to prevent concurrency issues. Because the table has dozens of columns, I also want to have the data adapter autogenerate the appropriate insert, update and delete commands, rather than manually coding the SQL. However, I can't figure out how to make transactions work with autogenerated commands - no matter how I re-arrange my code I get some sort of run-time error. For example, the following code produces the error "Object reference not set to an instance of an object." when it executes the last line shown:

Public Function InitializedSQLDataAdapter(SelectStatement As String, SQLConn As SqlConnection,
  Optional UseCommandBuilder As Boolean = False) As SqlDataAdapter
 'returns an SQLDataAdapter object, using SelectStatement and SQLConn, and including INSERT,
  'UPDATE and DELETE commands if UseCommandBuilder is True
  Dim DAdapter As SqlDataAdapter = New SqlDataAdapter(SelectStatement, SQLConn)
  Try
    If UseCommandBuilder Then
      Dim CmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(DAdapter)
      CmdBuilder.GetInsertCommand()
      CmdBuilder.GetUpdateCommand()
      CmdBuilder.GetDeleteCommand()
    End If
    Return DAdapter
  Catch ex As SystemException
    DisplayException(ex, "Utility.InitializedSQLDataAdapter")
  End Try
End Function

...

Conn.Open()
SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
DAdapter = InitializedSQLDataAdapter(SQL, Conn, True)
trans = Conn.BeginTransaction(IsolationLevel.Serializable)
DAdapter.UpdateCommand.Transaction = trans

If I use the following code instead, I get the same error in the same place:

Conn.Open()
trans = Conn.BeginTransaction(IsolationLevel.Serializable)
SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
DAdapter = New SqlDataAdapter(SQL, Conn)
DAdapter.UpdateCommand.Transaction = trans

If I use the following code instead, I get a different error ("ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.") on the last line shown:

Conn.Open()
trans = Conn.BeginTransaction(IsolationLevel.Serializable)
SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
DAdapter = New SqlDataAdapter(SQL, Conn)
DAdapter.Fill(DTable)

So, it seems that the command itself has to have a transaction, but when I try to assign one to it I always get one of these errors. How can I make this work?

[jmcilhinney]

I need to use a transaction to prevent concurrency issues.

That doesn't seem to make sense right off the bat. The point of a transaction is to ensure that multiple operations occur atomically, which has nothing to do with concurrency. Are you sure that you're trying to solve the right problem?

Generally speaking, you'd use a transaction in a situation like where you have a parent record and one or more child records to insert and you have to insert the parent record first in order to get the ID to include in the child records, which get saved afterwards. By wrapping the whole lot in a transaction, you can ensure that, if an error occurs after the parent record is inserted, that insertion is rolled back and the database left in its original state. Basically, you're making sure that either every one of multiple operations succeeds or none of them do. In this case, it appears that you are performing a single operation on a single record, so what use is a transaction?

Concurrency is a completely different problem. If you retrieve this record, edit it and then try to save it while another user has modified it in between, a concurrency error will occur. You will need to catch the exception that is thrown and then decide what to do about it.

[jmcilhinney]

As for your issue, it seems that you are under some misconception about what this code is going to do:

CmdBuilder.GetInsertCommand()
CmdBuilder.GetUpdateCommand()
CmdBuilder.GetDeleteCommand()

Those methods will each return a SqlCommand object and that's all. You seem to be under the impression that they will set the corresponding properties of the SqlDataAdapter. They won't. You need to actually use the command objects returned by those methods. For example, this:

Public Function InitializedSQLDataAdapter(SelectStatement As String, SQLConn As SqlConnection,
  Optional UseCommandBuilder As Boolean = False) As SqlDataAdapter
 'returns an SQLDataAdapter object, using SelectStatement and SQLConn, and including INSERT,
  'UPDATE and DELETE commands if UseCommandBuilder is True
  Dim DAdapter As SqlDataAdapter = New SqlDataAdapter(SelectStatement, SQLConn)
  Try
    If UseCommandBuilder Then
      Dim CmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(DAdapter)
      CmdBuilder.GetInsertCommand()
      CmdBuilder.GetUpdateCommand()
      CmdBuilder.GetDeleteCommand()
    End If
    Return DAdapter
  Catch ex As SystemException
    DisplayException(ex, "Utility.InitializedSQLDataAdapter")
  End Try
End Function

might become this:

Public Function InitializedSQLDataAdapter(SelectStatement As String, SQLConn As SqlConnection,
  Optional UseCommandBuilder As Boolean = False) As SqlDataAdapter
 'returns an SQLDataAdapter object, using SelectStatement and SQLConn, and including INSERT,
  'UPDATE and DELETE commands if UseCommandBuilder is True
  Dim DAdapter As SqlDataAdapter = New SqlDataAdapter(SelectStatement, SQLConn)
  Try
    If UseCommandBuilder Then
      Dim CmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(DAdapter)
      Dim trans = SQLConn.BeginTransaction(IsolationLevel.Serializable)
 
      CmdBuilder.GetInsertCommand().Transaction = trans
      CmdBuilder.GetUpdateCommand().Transaction = trans
      CmdBuilder.GetDeleteCommand().Transaction = trans
    End If
    Return DAdapter
  Catch ex As SystemException
    DisplayException(ex, "Utility.InitializedSQLDataAdapter")
  End Try
End Function

That said, I have a feeling that I actually tested just that a number of years ago and it didn't work as expected. From memory, I just couldn't get a command builder to work the way the documentation said that it should. Personally, my advice is to just not use command builders. So what if your table has dozens of columns? You wasted more time on this issue already than it would have taken you to write out the INSERT and UPDATE statements by hand and you can get SSMS to generate SQL templates for you anyway.

[jmcilhinney]

On an unrelated note, you ought to look at this for your DisplayException method.

https://docs.microsoft.com/en-us/dot...rnameattribute

[silverblatt]

Jim:

First, thanks for your detailed replies. I will respond to them in order.

How can I format code in the same way as in your examples? I searched the FAQs for instructions and couldn't find anything relevant, and wrapping the code in tags doesn't seem to do anything.

I generally use transactions only as you suggested - to ensure that multiple database changes succeed or fail as a unit. In this case, I spent many frustrating hours researching how to deal with concurrency issues, and the only thing I came up with (mistakenly apparently) was setting an isolation level, and it seemed that an isolation level could only be specified in a transaction, which is why I went that route. So, here's what I actually need to accomplish: whenever user A attempts to load a record for viewing, updating or deletion, if the same record is currently loaded by user B in the same application, user A must either be prevented from loading the record, or they must be allowed to simply view it in its last saved state without the ability to update or delete it (with appropriate messages displayed in either case). Or, to put it another way, I need to be able to place read or write locks on individual records, and have code in place to determine whether a particular record is locked. A nice bonus (but not essential) would be the ability to identify (and display) the identity of the other user who holds the lock. If there's a straightforward way to do that, please advise.

Thanks for the tip about the CallerMemberNameAttribute class - I've been looking for something like that for a while, and it will both simplify and improve my exception reporting.

[silverblatt]

Oops, I mis-read and mis-typed your name. Sorry, John.

[si_the_geek]

You can use code tags either by pressing the # or VB buttons in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [code] code here [/code]
eg:

 code here

So, here's what I actually need to accomplish: whenever user A attempts to load a record for viewing, updating or deletion, if the same record is currently loaded by user B in the same application, user A must either be prevented from loading the record, or they must be allowed to simply view it in its last saved state without the ability to update or delete it (with appropriate messages displayed in either case).

Determining whether a particular record is being viewed in an application on another computer (even if it is another instance of the same application) is complex and unreliable.

With a significant chunk of effort you can somehow store which record(s) have been loaded by a user, and then block a second user from reading/editing it... but keeping track of which ones are no longer being viewed by the first user is harder than you may think, because any kind of failure of the application/computer/network will prevent the change of status taking place. If a computer crashes for some reason then any "locks" it had open at the time will stay, or if the user goes on holiday etc without closing the program then the locks could stay for a long time.


A more standard way to deal with concurrency issues is to allow users to view/edit any records they like, and when they attempt to write their changes to the database, inform them if the data has changed since they loaded it (you can also inform them at the point they start editing, or possibly check every 60 seconds while they are editing). You could also keep track of what records other users are viewing, and show a label with the record to say that it is currently being viewed by other people.

[GaryMazzone]

I would normally use a lastModifiedDate (orLastUpdatedDate) and ModifedBy field. When you go to save the record on either update of delete, you use the PrimaryKey in the where clause a long with the lastModifiedDate. That way if someone modified the row in the database before you do then the update fails... You capture the failure and tell the user to refresh their data.