none
I think this is a Typed Dataset bug - not respecting database FK constraints with update cascading RRS feed

  • Question

  • I think there is a bug in the VS2008 Typed Dataset code. The following is a part of a SQL Profiler trace from a transaction that fails trying to update 2 related tables [parent=tb_TaskDefinitions and child=tb_Tasks] where I renamed the primary key [DefName] in the first table which automatically renamed the foreign key reference [DefName] in the second table within the ADO.NET Dataset. Problem is that even though the dataset KNOWS that FK relationships are enforced and cascading updates are in effect in both the database and the ADO.NET Typed Dataset, it still uses the ORIGINAL foreign key value for Defname when verifying the child row it is trying to update when it should be using the CURRENT foreign key value for DefName and so fails since it just updated the value in the parent. I have highlighted the syntax in question. At minimum, there should be Dataset/Datatable options saying whether the database table has FK constraints and cascading in effect so the TableAdapter know whether to use the ORIGINAL or CURRENT FK value when updating the child which would have already been updated from the first update in the transaction due to the update cascading. Is there anyway to do this already built into Type Datasets?  Thanks, Dave

    The first update inside transaction works OK and updates the parent row changing the DefName from "CPU Task" to "CPU Taskxxx":

    exec sp_executesql N'UPDATE [tb_TaskDefinitions] SET [DefName] = @DefName, [DefDesc] = @DefDesc, [DefDisable] = @DefDisable, [DefDebug] = @DefDebug, [DefEventType] = @DefEventType, [DefClearTime] = @DefClearTime, [DefEvtMessage] = @DefEvtMessage, [DefAssembly] = @DefAssembly, [DefLocation] = @DefLocation, [DefXmlSchema] = @DefXmlSchema, [LastUpdate] = @LastUpdate, [LastUserid] = @LastUserid WHERE (([DefName] = @Original_DefName) AND ([DefDesc] = @Original_DefDesc) AND ([DefDisable] = @Original_DefDisable) AND ([DefDebug] = @Original_DefDebug) AND ([DefEventType] = @Original_DefEventType) AND ((@IsNull_DefClearTime = 1 AND [DefClearTime] IS NULL) OR ([DefClearTime] = @Original_DefClearTime)) AND ([DefEvtMessage] = @Original_DefEvtMessage) AND ([DefAssembly] = @Original_DefAssembly) AND ((@IsNull_DefLocation = 1 AND [DefLocation] IS NULL) OR ([DefLocation] = @Original_DefLocation)) AND ((@IsNull_DefXmlSchema = 1 AND [DefXmlSchema] IS NULL) OR ([DefXmlSchema] = @Original_DefXmlSchema)) AND ([LastUpdate] = @Original_LastUpdate) AND ([LastUserid] = @Original_LastUserid));

    SELECT DefName, DefDesc, DefDisable, DefDebug, DefEventType, DefClearTime, DefEvtMessage, DefAssembly, DefLocation, DefXmlSchema, LastUpdate, LastUserid FROM tb_TaskDefinitions WHERE (DefName = @DefName)',N'@DefName varchar(11),@DefDesc varchar(12),@DefDisable bit,@DefDebug bit,@DefEventType int,@DefClearTime int,@DefEvtMessage varchar(9),@DefAssembly varchar(7),@DefLocation varchar(3),@DefXmlSchema varchar(8000),@LastUpdate datetime,@LastUserid varchar(9),@Original_DefName varchar(8),@Original_DefDesc varchar(12),@Original_DefDisable bit,@Original_DefDebug bit,@Original_DefEventType int,@IsNull_DefClearTime int,@Original_DefClearTime int,@Original_DefEvtMessage varchar(9),@Original_DefAssembly varchar(7),@IsNull_DefLocation int,@Original_DefLocation varchar(3),@IsNull_DefXmlSchema int,@Original_DefXmlSchema varchar(8000),@Original_LastUpdate datetime,@Original_LastUserid varchar(9)',@DefName='CPU taskxxx',@DefDesc='Task for CPU',@DefDisable=1,@DefDebug=1,@DefEventType=1,@DefClearTime=5,@DefEvtMessage='$computer',@DefAssembly='cpu.dll',@DefLocation='c:\',@DefXmlSchema=NULL,@LastUpdate='2009-05-30 00:00:00',@LastUserid='win7\dave',@Original_DefName='CPU task',@Original_DefDesc='Task for CPU',@Original_DefDisable=1,@Original_DefDebug=1,@Original_DefEventType=1,@IsNull_DefClearTime=0,@Original_DefClearTime=5,@Original_DefEvtMessage='$computer',@Original_DefAssembly='cpu.dll',@IsNull_DefLocation=0,@Original_DefLocation='c:\',@IsNull_DefXmlSchema=1,@Original_DefXmlSchema=NULL,@Original_LastUpdate='2009-05-30 00:00:00',@Original_LastUserid='win7\dave'

    However, the second Update inside transaction fails trying to change DefName from "CPU Task" to "CPU Taskxxx" in the child row because it has already been changed by the previous update to the parent row, with the ADO.NET TableAdapter full well knowing that the value changed since it has the FK constraint and cascading in effect on the client and had imported that fact from the database using the Typed Dataset wizard:

    exec sp_executesql N'UPDATE [dbo].[tb_Tasks] SET [TskName] = @TskName, [DefName] = @DefName, [TskDesc] = @TskDesc, [TskDisable] = @TskDisable, [TskDebug] = @TskDebug, [TskClearTime] = @TskClearTime, [TskEvtMessage] = @TskEvtMessage, [TskXmlConfiguration] = @TskXmlConfiguration, [TskOutputLocation] = @TskOutputLocation, [LastUpdate] = @LastUpdate, [LastUserid] = @LastUserid WHERE (([TskName] = @Original_TskName) AND ([DefName] = @Original_DefName) AND ([TskDesc] = @Original_TskDesc) AND ([TskDisable] = @Original_TskDisable) AND ([TskDebug] = @Original_TskDebug) AND ((@IsNull_TskClearTime = 1 AND [TskClearTime] IS NULL) OR ([TskClearTime] = @Original_TskClearTime)) AND ([TskEvtMessage] = @Original_TskEvtMessage) AND ((@IsNull_TskOutputLocation = 1 AND [TskOutputLocation] IS NULL) OR ([TskOutputLocation] = @Original_TskOutputLocation)) AND ([LastUpdate] = @Original_LastUpdate) AND ([LastUserid] = @Original_LastUserid));

    SELECT TskName, DefName, TskDesc, TskDisable, TskDebug, TskClearTime, TskEvtMessage, TskXmlConfiguration, TskOutputLocation, LastUpdate, LastUserid FROM tb_Tasks WHERE (TskName = @TskName)',N'@TskName varchar(11),@DefName varchar(11),@TskDesc varchar(12),@TskDisable bit,@TskDebug bit,@TskClearTime int,@TskEvtMessage varchar(9),@TskXmlConfiguration varchar(3),@TskOutputLocation varchar(3),@LastUpdate datetime,@LastUserid varchar(9),@Original_TskName varchar(11),@Original_DefName varchar(8),@Original_TskDesc varchar(12),@Original_TskDisable bit,@Original_TskDebug bit,@IsNull_TskClearTime int,@Original_TskClearTime int,@Original_TskEvtMessage varchar(9),@IsNull_TskOutputLocation int,@Original_TskOutputLocation varchar(3),@Original_LastUpdate datetime,@Original_LastUserid varchar(9)',@TskName='CPU task 80',@DefName='CPU taskxxx',@TskDesc='Task for CPU',@TskDisable=0,@TskDebug=0,@TskClearTime=4,@TskEvtMessage='$COMPUTER',@TskXmlConfiguration='abc',@TskOutputLocation='abc',@LastUpdate='2009-05-30 00:00:00',@LastUserid='win7\dave',@Original_TskName='CPU task 80',@Original_DefName='CPU task',@Original_TskDesc='Task for CPU',@Original_TskDisable=0,@Original_TskDebug=0,@IsNull_TskClearTime=0,@Original_TskClearTime=4,@Original_TskEvtMessage='$COMPUTER',@IsNull_TskOutputLocation=0,@Original_TskOutputLocation='abc',@Original_LastUpdate='2009-05-30 00:00:00',@Original_LastUserid='win7\dave'

    Tuesday, June 2, 2009 5:49 PM

Answers

  • Hi CodeSligner,

    We are investigating this issue and hope to get it fixed in next release.

    I tried to catch the exception, and the changes are all saved successfully. Will this work for you?

    Thanks,
    Jing Li
    Friday, June 19, 2009 1:48 AM

All replies

  • *** Note this is an updated version as the first version had a bug ***

    Here is a workaround from my test project but having to do this is not ideal as it would be nice if this was built into the DataSet class and fixed the AcceptRejectRule=cascade to work with just the cascaded FK values and not all of the row values! I put this code into the update database button code. Please comment on whether you see issues with this technique or whether I'm missing some feature in the Dataset that already magically does this. Dave

            private void button1_Click(object sender, EventArgs e)
            {
                // Check each child table lRow to see if the child FK has been changed since the parent table
                // update to the database will cascade it to the child table in the database and ADO.NET
                // is not smart enough to realize that even though the Typed Dataset wizard did the import
                // of the relationship!
    
                // get the count of the lRow columns and the index of the FK column
                int lColCount = cmdManNewDataSet.tb_Tasks.Columns.Count;
                int lFKIndex = cmdManNewDataSet.tb_Tasks.Columns["DefName"].Ordinal;
    
                object[] current = new object[lColCount];
                object[] original = new object[lColCount];
                bool[] changed = new bool[lColCount];
                int i;
    
                foreach (DataRow lRow in cmdManNewDataSet.tb_Tasks.Rows)
                {
                    // would have liked to have just done the following but cannot assign to the original version - it is read only!
                    //if (lRow["DefName", DataRowVersion.Original] != lRow["DefName", DataRowVersion.Current])
                    //    lRow["DefName", DataRowVersion.Original] = lRow["DefName", DataRowVersion.Current];
    
                    // check if the child lRow FK has changed due to cascading relationship in our Dataset
                    // TODO: this assumes this is the only FK so modify accordingly!!!
                    if (lRow["DefName", DataRowVersion.Original] != lRow["DefName", DataRowVersion.Current])
                    {
                        // 1. remember which row columns changed as well as the current and original values
                        for (i = 0; i < lColCount; i++)
                        {
                            changed[i] = (lRow[i, DataRowVersion.Original] != lRow[i, DataRowVersion.Current]);
                            current[i] = lRow[i, DataRowVersion.Current];
                            original[i] = lRow[i, DataRowVersion.Original];
                        }
    
                        // 2. accept changes so that the FK is now an original value
                        lRow.AcceptChanges();
    
                        // 3. put all of the original values back except the FK
                        for (i = 0; i < lColCount; i++)
                        {
                            if (i != lFKIndex)
                                lRow[i] = original[i];
                        }
    
                        // 4. accept changes so that all of the originals are back as well as the changed FK
                        //    since this has already changed in the database due to FK constraints and cascading
                        lRow.AcceptChanges();
    
                        // 5. put just the changes back but do not accept them so that the TableAdapter will update the database
                        for (i = 0; i < lColCount; i++)
                        {
                            if (changed[i] == true)
                                lRow[i] = current[i];
                        }
                    }
                }
                
                // Now update all the changed rows through each table adapter
                this.tableAdapterManager.UpdateAll(cmdManNewDataSet);
            }
    
    
    Tuesday, June 2, 2009 8:11 PM
  • Hi CodeSlinger,

    Could you please provide me the source code or steps to generate the transaction?

    Thanks,
    Jing Li
    Tuesday, June 16, 2009 6:25 AM
  • My test used a 3rd party grid [Infragistics] and ran against one of my databases -  so not sure it would be useful. Does this not make sense that the typed dataset adapter currently works like this? I sort of see that it would but think it could be enhanced to allow for what I'm suggesting and what my workaround does. Dave

    Tuesday, June 16, 2009 6:51 AM
  • Hi CodeSlinger,

    About FK constraints with update cascade, do you set this constraint in database or in dataset?


    I tried the following code in 2 scenarios:

        cmdmanNewDataSet.tb_TasksRow parentRow = this.cmdmanNewDataSet.tb_Task.FindByDefName("CPU Task");
        parentRow["DefName"] = "CPU Taskxxx";
        tableAdapterManager.UpdateAll(cmdmanNewDataSet); 

    Scenario 1: set FK constraint with update cascade in database only
                      it works well.
    Scenario 2: set FK constraint with update cascade in both database and dataset: Exception is thrown when UpdateAll.

                     System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.


    Please correct me if my code or scenario is wrong.

    Thanks,
    Jing Li
    Wednesday, June 17, 2009 4:51 AM
  • I basically have scenario #2 in my code since I need for the views to reflect the key name changes immediately. If I used scenario #1, it would break the child view based on the parent row name.

    The relationships and constraints were defined both in the database to help protect from other avenues of update, and on the dataset side so that child views would reflact cahnges in the parent. It seems obvious that when the parent key name updates are cascaded in the database, the optimistic locking mechanism in the dataset update would fail unless the typed dataset allowed for it by realizing that the child keys would be renamed already in the database. This is what my "fix" addresses. Does it make sense to hope that the typeed dataset class is enhanced to also recognize this situation and update its child keys when the parent update succeeds and there is such a relationship defined in the database and the dataset? Afterall, the definition is quite explicit in the dataset and can be turned on or off.

    Thanks, Dave
    • Edited by CodeSlinger Wednesday, June 17, 2009 4:40 PM clarify
    Wednesday, June 17, 2009 4:22 PM
  • Hi CodeSligner,

    We are investigating this issue and hope to get it fixed in next release.

    I tried to catch the exception, and the changes are all saved successfully. Will this work for you?

    Thanks,
    Jing Li
    Friday, June 19, 2009 1:48 AM
  • Hi Jing Li,

    I'm sorry but do not understand what you mean. Once you verify this phenomemon, are you saying you will add some sort of fix similar to the work around I made? Thanks,

    Dave
    Friday, June 19, 2009 1:03 PM
  • Hi Dave,

    Right now I am not sure how we will fix this issueL. We have to investigate the cost and risk in order to avoid bringing a lot of regressions. Sorry for the inconvenience.

    Thanks,
    Jing Li
    Monday, June 22, 2009 1:33 AM
  • That I understand. Indeed, I hope you fix the issue but it would not be good to regress current code. Perhaps just add a new option that is off by default that says to respect the cascading updates made in the database or something...Thanks, Dave

    Monday, June 22, 2009 3:24 AM
  • Thanks Dave for understanding. And your proposal sounds quite good. We will consider this.

    Your advice is always appreciated.

    Thanks,
    Jing Li
    • Proposed as answer by Jing Li Tuesday, June 23, 2009 2:59 AM
    Tuesday, June 23, 2009 2:59 AM
  • Something else that might be very helpful is to be able to turn off referential integrity and cascading inside of a transaction when updating from a dataadapter. I use "alter table xxx nocheck constraint" statements to at least turn off RI which works since I turned off the cascading permanently inside the database and just found out about the "on delete" and "on update" clauses so may be able to just turn all the RI and cascading off in TSQL commands wrapped around adapter update of several tables inside a transaction.
    Sunday, August 23, 2009 4:37 AM
  • Dear Sirs,

    I have exactly the same problem and would like to ask you, if you found a solution to this.

    Thank you!

     

    Rufus

    Monday, January 23, 2012 7:53 PM