none
DeleteCommand Generated SQL - why is it so complex? RRS feed

  • Question

  • Here is the sql generated and parameters required for the DeleteCommand for my tableadapter's strongly-typed dataset:

    DELETE FROM ANSWER_LISTS
    WHERE     (Answer_List_ID = ?) AND (? = 1 AND NAME IS NULL OR
                          NAME = ?) AND (? = 1 AND LONGNAME IS NULL OR
                          LONGNAME = ?) AND (? = 1 AND CITATION_ID IS NULL OR
                          CITATION_ID = ?) AND (? = 1 AND DETAILS IS NULL OR
                          DETAILS = ?) AND (? = 1 AND EDITABLE IS NULL OR
                          EDITABLE = ?)

    Why would the Designer create such a complex statement when a primary key is defined? 
    Wednesday, October 21, 2009 1:34 PM

Answers

  • Ah, found it. Knew I've seen it somewhere:

    "Generating Commands with CommandBuilders (ADO.NET)"
    http://msdn.microsoft.com/en-us/library/tf579hcz.aspx

     
    Optimistic Concurrency Model for Updates and Deletes

    The logic for generating commands automatically for UPDATE and DELETE statements is based on optimistic concurrency--that is, records are not locked for editing and can be modified by other users or processes at any time. Because a record could have been modified after it was returned from the SELECT statement, but before the UPDATE or DELETE statement is issued, the automatically generated UPDATE or DELETE statement contains a WHERE clause, specifying that a row is only updated if it contains all original values and has not been deleted from the data source. This is done to avoid overwriting new data. Where an automatically generated update attempts to update a row that has been deleted or that does not contain the original values found in the DataSet, the command does not affect any records, and a DBConcurrencyException is thrown.

    If you want the UPDATE or DELETE to complete regardless of original values, you must explicitly set the UpdateCommand for the DataAdapter and not rely on automatic command generation.

    //Michael


    This posting is provided "AS IS" with no warranties.
    Thursday, October 22, 2009 8:33 AM
  • Hi ckelker,

     

    I agree with Michael.  Due to optimistic concurrency checking concern, the T-SQLs to insert/update/delete strongly typed DataSet are auto-generated this way.  

     

    However, we can manually turn off the optimistic concurrency setting.  In the .xsd designer, right click the certain TableAdapter à Configureà Advanced Optionsà Uncheck Use optimistic concurrency. 

     

    Also, we can manually configure the insert/update/delete query.  In the TableAdapter’s properties window, we can open the certain Query Builder by clicking the CommandText property of DeleteCommand/InsertCommand/SelectCommand/UpdateCommand. 

     

    Some additional references:

    http://msdn.microsoft.com/en-us/library/dex7k4dw.aspx

    http://msdn.microsoft.com/en-us/library/kda44dwy.aspx

    http://msdn.microsoft.com/en-us/library/bz9tthwx.aspx

    http://msdn.microsoft.com/en-us/library/6s413yfa.aspx

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 22, 2009 3:29 AM
    Moderator

All replies

  • I've not done a full reserach on this but my theory is that since the idea in ADO.Net is to be disconnected then this approach
    will make it 'safer'.

    For example, I retrieve a copy of this row which may hang around locally for a while in my disconnected environment.

    Then another user comes in, deletes this row in the database, then inserts a new one (with the same primary key, but new values)
    then if I would delete it by using the primary key, then the 'wrong' row would be deleted. Ie the one that the other user inserted between my
    retrieval and delete. 
    By using all columns we make sure that if the exact row doesn't exist in the database, it will not be deleted.

    Hope this makes sense.

    //Michael
    This posting is provided "AS IS" with no warranties.
    Wednesday, October 21, 2009 3:28 PM
  • Hi ckelker,

     

    I agree with Michael.  Due to optimistic concurrency checking concern, the T-SQLs to insert/update/delete strongly typed DataSet are auto-generated this way.  

     

    However, we can manually turn off the optimistic concurrency setting.  In the .xsd designer, right click the certain TableAdapter à Configureà Advanced Optionsà Uncheck Use optimistic concurrency. 

     

    Also, we can manually configure the insert/update/delete query.  In the TableAdapter’s properties window, we can open the certain Query Builder by clicking the CommandText property of DeleteCommand/InsertCommand/SelectCommand/UpdateCommand. 

     

    Some additional references:

    http://msdn.microsoft.com/en-us/library/dex7k4dw.aspx

    http://msdn.microsoft.com/en-us/library/kda44dwy.aspx

    http://msdn.microsoft.com/en-us/library/bz9tthwx.aspx

    http://msdn.microsoft.com/en-us/library/6s413yfa.aspx

     

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 22, 2009 3:29 AM
    Moderator
  • Ah, found it. Knew I've seen it somewhere:

    "Generating Commands with CommandBuilders (ADO.NET)"
    http://msdn.microsoft.com/en-us/library/tf579hcz.aspx

     
    Optimistic Concurrency Model for Updates and Deletes

    The logic for generating commands automatically for UPDATE and DELETE statements is based on optimistic concurrency--that is, records are not locked for editing and can be modified by other users or processes at any time. Because a record could have been modified after it was returned from the SELECT statement, but before the UPDATE or DELETE statement is issued, the automatically generated UPDATE or DELETE statement contains a WHERE clause, specifying that a row is only updated if it contains all original values and has not been deleted from the data source. This is done to avoid overwriting new data. Where an automatically generated update attempts to update a row that has been deleted or that does not contain the original values found in the DataSet, the command does not affect any records, and a DBConcurrencyException is thrown.

    If you want the UPDATE or DELETE to complete regardless of original values, you must explicitly set the UpdateCommand for the DataAdapter and not rely on automatic command generation.

    //Michael


    This posting is provided "AS IS" with no warranties.
    Thursday, October 22, 2009 8:33 AM
  • Hi ckelker,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, October 27, 2009 10:42 AM
    Moderator