PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
How to use transaction with data adapter and autogenerated commands-VBForums
Results 1 to 9 of 9

Thread: How to use transaction with data adapter and autogenerated commands

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2010
    Posts
    14

    How to use transaction with data adapter and autogenerated commands

    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?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,704

    Re: How to use transaction with data adapter and autogenerated commands

    Please use appropriate formatting tags when posting code snippets for readability.
    Quote Originally Posted by silverblatt View Post
    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:

    vb.net Code:
    1. Public Function InitializedSQLDataAdapter(SelectStatement As String, SQLConn As SqlConnection,
    2.   Optional UseCommandBuilder As Boolean = False) As SqlDataAdapter
    3.  'returns an SQLDataAdapter object, using SelectStatement and SQLConn, and including INSERT,
    4.   'UPDATE and DELETE commands if UseCommandBuilder is True
    5.   Dim DAdapter As SqlDataAdapter = New SqlDataAdapter(SelectStatement, SQLConn)
    6.   Try
    7.     If UseCommandBuilder Then
    8.       Dim CmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(DAdapter)
    9.       CmdBuilder.GetInsertCommand()
    10.       CmdBuilder.GetUpdateCommand()
    11.       CmdBuilder.GetDeleteCommand()
    12.     End If
    13.     Return DAdapter
    14.   Catch ex As SystemException
    15.     DisplayException(ex, "Utility.InitializedSQLDataAdapter")
    16.   End Try
    17. End Function
    ...
    vb.net Code:
    1. Conn.Open()
    2. SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
    3. DAdapter = InitializedSQLDataAdapter(SQL, Conn, True)
    4. trans = Conn.BeginTransaction(IsolationLevel.Serializable)
    5. DAdapter.UpdateCommand.Transaction = trans

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

    vb.net Code:
    1. Conn.Open()
    2. trans = Conn.BeginTransaction(IsolationLevel.Serializable)
    3. SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
    4. DAdapter = New SqlDataAdapter(SQL, Conn)
    5. 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:

    vb.net Code:
    1. Conn.Open()
    2. trans = Conn.BeginTransaction(IsolationLevel.Serializable)
    3. SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
    4. DAdapter = New SqlDataAdapter(SQL, Conn)
    5. 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?

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,704

    Re: How to use transaction with data adapter and autogenerated commands

    Quote Originally Posted by silverblatt View Post
    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.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,704

    Re: How to use transaction with data adapter and autogenerated commands

    As for your issue, it seems that you are under some misconception about what this code is going to do:
    vb.net Code:
    1. CmdBuilder.GetInsertCommand()
    2. CmdBuilder.GetUpdateCommand()
    3. 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:
    vb.net Code:
    1. Public Function InitializedSQLDataAdapter(SelectStatement As String, SQLConn As SqlConnection,
    2.   Optional UseCommandBuilder As Boolean = False) As SqlDataAdapter
    3.  'returns an SQLDataAdapter object, using SelectStatement and SQLConn, and including INSERT,
    4.   'UPDATE and DELETE commands if UseCommandBuilder is True
    5.   Dim DAdapter As SqlDataAdapter = New SqlDataAdapter(SelectStatement, SQLConn)
    6.   Try
    7.     If UseCommandBuilder Then
    8.       Dim CmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(DAdapter)
    9.       CmdBuilder.GetInsertCommand()
    10.       CmdBuilder.GetUpdateCommand()
    11.       CmdBuilder.GetDeleteCommand()
    12.     End If
    13.     Return DAdapter
    14.   Catch ex As SystemException
    15.     DisplayException(ex, "Utility.InitializedSQLDataAdapter")
    16.   End Try
    17. End Function
    might become this:
    vb.net Code:
    1. Public Function InitializedSQLDataAdapter(SelectStatement As String, SQLConn As SqlConnection,
    2.   Optional UseCommandBuilder As Boolean = False) As SqlDataAdapter
    3.  'returns an SQLDataAdapter object, using SelectStatement and SQLConn, and including INSERT,
    4.   'UPDATE and DELETE commands if UseCommandBuilder is True
    5.   Dim DAdapter As SqlDataAdapter = New SqlDataAdapter(SelectStatement, SQLConn)
    6.   Try
    7.     If UseCommandBuilder Then
    8.       Dim CmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(DAdapter)
    9.       Dim trans = SQLConn.BeginTransaction(IsolationLevel.Serializable)
    10.  
    11.       CmdBuilder.GetInsertCommand().Transaction = trans
    12.       CmdBuilder.GetUpdateCommand().Transaction = trans
    13.       CmdBuilder.GetDeleteCommand().Transaction = trans
    14.     End If
    15.     Return DAdapter
    16.   Catch ex As SystemException
    17.     DisplayException(ex, "Utility.InitializedSQLDataAdapter")
    18.   End Try
    19. 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.

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,704

    Re: How to use transaction with data adapter and autogenerated commands

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

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

  6. #6

    Thread Starter
    New Member
    Join Date
    Apr 2010
    Posts
    14

    Re: How to use transaction with data adapter and autogenerated commands

    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.

  7. #7

    Thread Starter
    New Member
    Join Date
    Apr 2010
    Posts
    14

    Re: How to use transaction with data adapter and autogenerated commands

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

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,413

    Re: How to use transaction with data adapter and autogenerated commands

    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:
     code here

    Quote Originally Posted by silverblatt View Post
    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.
    Last edited by si_the_geek; Sep 9th, 2019 at 11:18 AM.

  9. #9
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,396

    Re: How to use transaction with data adapter and autogenerated commands

    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.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width