T
Tobias Wassermann
We should consider a SQL Server (2005 – version 9.00.1399.00) database with
two tables Parent and Child. Table Parent has one Id Field (type int) which
is marked as Autoincrement (Is Identity=Yes, Increment=1, Seed=1), and the
Child table has an integer column ParentId pointing via a foreign key
constraint to the Parent.Id. The Foreign Key constraint is defined with
Enforce Foreign Key Constraints = true, Delete Rule = No Action, Update Rule
= No Action.
Additionally we have a .NET application (Windows forms, framework 2.0 with
SP1, C# language), with a typed dataset (auto generated) which contains both
tables described above. The dataset has the relationship defined with
DeleteRule=Cascade and UpdateRule=Cascade.
We have encountered the following scenario:
- In a run of the application we delete the last record in the Parent table,
and subsequently the corresponding record in the Child table.
- Next we close the database connection and close the application.
- We restart the application and reload the data from the database into the
dataset (unfiltered).
Note*: At this point the IDENT of the SQL Server database table will not be
known by the internal application dataset.
- We add two records in the Parent table and subsequently two in the Child
table, and try to Update, using the adapter.Update method.
- The outcome of the Update command is that the ALL Child.ParentId records
will end up with the same value in the database and in the internal
application dataset, and the value is the last of the two Parent.Id values.
To better understand the scenario above let’s try an Example:
1. Let’s assume that the current last ID in the Parent table is 13, and we
delete this record and the corresponding Child record, letting us with the
last (MAX) ID of 12.
2. Close the application and restart, reload the data into the dataset, and
add two new records. As the dataset is not informed on Fill about the current
IDENT value of the sql table, it will generate the IDs: 13 and 14, so the
rows will look like:
Parent: ID = 13, Child: ParentID = 13
Parent: ID = 14, Child: ParentID = 14
3. After Update the returned rows look like:
Parent: ID = 14, Child: ParentID = 15
Parent: ID = 15, ParentID = 15
And this is the problem, the Child rows will be pointing to the wrong Parent.
In any other case (bigger gap of IDs in the database (bigger then 1), or no
gap) we found the Update working properly.
Does everyone seen this bug? Will be there a fix with a future SP?
two tables Parent and Child. Table Parent has one Id Field (type int) which
is marked as Autoincrement (Is Identity=Yes, Increment=1, Seed=1), and the
Child table has an integer column ParentId pointing via a foreign key
constraint to the Parent.Id. The Foreign Key constraint is defined with
Enforce Foreign Key Constraints = true, Delete Rule = No Action, Update Rule
= No Action.
Additionally we have a .NET application (Windows forms, framework 2.0 with
SP1, C# language), with a typed dataset (auto generated) which contains both
tables described above. The dataset has the relationship defined with
DeleteRule=Cascade and UpdateRule=Cascade.
We have encountered the following scenario:
- In a run of the application we delete the last record in the Parent table,
and subsequently the corresponding record in the Child table.
- Next we close the database connection and close the application.
- We restart the application and reload the data from the database into the
dataset (unfiltered).
Note*: At this point the IDENT of the SQL Server database table will not be
known by the internal application dataset.
- We add two records in the Parent table and subsequently two in the Child
table, and try to Update, using the adapter.Update method.
- The outcome of the Update command is that the ALL Child.ParentId records
will end up with the same value in the database and in the internal
application dataset, and the value is the last of the two Parent.Id values.
To better understand the scenario above let’s try an Example:
1. Let’s assume that the current last ID in the Parent table is 13, and we
delete this record and the corresponding Child record, letting us with the
last (MAX) ID of 12.
2. Close the application and restart, reload the data into the dataset, and
add two new records. As the dataset is not informed on Fill about the current
IDENT value of the sql table, it will generate the IDs: 13 and 14, so the
rows will look like:
Parent: ID = 13, Child: ParentID = 13
Parent: ID = 14, Child: ParentID = 14
3. After Update the returned rows look like:
Parent: ID = 14, Child: ParentID = 15
Parent: ID = 15, ParentID = 15
And this is the problem, the Child rows will be pointing to the wrong Parent.
In any other case (bigger gap of IDs in the database (bigger then 1), or no
gap) we found the Update working properly.
Does everyone seen this bug? Will be there a fix with a future SP?