W mojej ostatniej notce pisałem o zachowywaniu historii danych za pomocą pól tabeli ValidFrom i ValidTo. Taki sposób zarządzania danymi wymaga oczywiście przedefiniowania operacji INSERT, UPDATE i DELETE. Tutaj chciałbym przedstawić pewien problem związany taką aktualizacją danych poprzez DataSet.
Tym razem struktura danych jest prostsza, mianowicie mamy relację 1:n (oczywiście dla relacji m:n występuje ten sam problem):
Nie wchodząc w szczegóły: klucze w tabelach są zdefiniowane jako wartości unikalne, generowane automatycznie, pola ValidFrom mają ustawioną wartość domyślną 01.01.1900, pola ValidTo analogicznie 31.12.9999. Dla tej struktury wygenerowałem sobie w Visual Studio 2005 dataset i zdefiniowałem dla tabel operacje SELECT, INSERT, UPDATE i DELETE. Typ relacji w datasecie ustawiłem na „FK & Relation” i włączyłem kaskadowego update’a. Kod dołączyłem do tekstu, tutaj pokaże tylko, najistotniejszą dla dalszej części, implementację operacji UPDATE dla tabeli ParentTable:
ad.UpdateCommand = new SqlCommand(
@"UPDATE ParentTable SET ValidTo=@versionTime WHERE Id=@id
INSERT INTO ParentTable (Value, ValidFrom) VALUES (@value, @versionTime)
SELECT ID, Value, ValidFrom, ValidTo FROM ParentTable
WHERE ID=@@IDENTITY", cnn, tr);
ad.UpdateCommand.Parameters.Add("@value",
System.Data.SqlDbType.NVarChar,
50,
"Value");
ad.UpdateCommand.Parameters.Add("@id", System.Data.SqlDbType.Int, 4, "ID");
ad.UpdateCommand.Parameters.AddWithValue("@versionTime", VersionTime);
Testy przeprowadziłem na tabeli Parent zawierającej jeden wiersz o wartości ID=1 powiązany z jednym wierszem w tabeli Child, także o ID=1. Test polegał na zmianie wartości Value wiersza tabeli Parent, dodaniu to tej tabeli nowego wiersza i aktualizacji danych.
Po zmodyfikowaniu danych próba wykonania operacji UPDATE na pierwszym wierszu kończy się wyjątkiem ConstraintException i komunikatem, że wiersz o ID=2 już istnieje w tabeli. Skąd tez identyfikator? A stąd, że w ramach aktualizacji danych został wpisany do bazy nowy wiersz, ze zmienioną wartością Value i nowym identyfikatorem. Kończąca operację instrukcja SELECT pobiera właśnie ten nowy wiersz powodując wewnętrzny konflikt w datasecie.
Mądry Exception Helper w Visual Studio proponuje w tym momencie wyłączenie sprawdzania integralności danych na czas ich aktualizacji. Jeśli jednak to zrobimy, to efekty będą conajmniej... zaskakujące:
Czytam dane:
1 Parent1 01.01.1900 00:00:00 - 01.01.9999 00:00:00
1(1) Child1 01.01.1900 00:00:00 - 01.01.9999 00:00:00
Modyfikuje dataset
1 Parent1* 01.01.1900 00:00:00 - 01.01.9999 00:00:00
1(1) Child1 01.01.1900 00:00:00 - 01.01.9999 00:00:00
2 Parent2 01.01.1900 00:00:00 - 01.01.9999 00:00:00
Zapisuje dane do bazy
2 Parent1* 24.01.2008 14:23:29 - 01.01.9999 00:00:00
3 Parent2 01.01.1900 00:00:00 - 01.01.9999 00:00:00
2(3) Child1 24.01.2008 14:23:29 - 01.01.9999 00:00:00
Czytam dane:
2 Parent1* 24.01.2008 14:23:29 - 01.01.9999 00:00:00
3 Parent2 24.01.2008 14:23:29 - 01.01.9999 00:00:00
2(3) Child1 24.01.2008 14:23:29 - 01.01.9999 00:00:00
1 Parent1 01.01.1900 00:00:00 - 24.01.2008 14:23:29
1(1) Child1 01.01.1900 00:00:00 - 24.01.2008 14:23:29
Pierwszy blok to dane początkowe, drugi zmodyfikowane. Trzeci to stan po wykonaniu synchronizacji danych, czwarty to cała baza. Jak widać, wiersz w tabeli podrzędnej zmienił swojego „ojca”! Jak? Wiersze w tabeli Parent przetwarzene są sekwencyjnie, w takiej kolejności w jakiej są zapisane w tabeli. Kaskadowy update dla relacji oznacza, że każda zmiana wartości ID zostanie wprowadzona automatycznie w tabeli Child. Wiersze zostaną więc zmieniowe w następującej sekwencji:
- W wyniku operacji UPDATE w pierwszym wierszu tabeli Parent ID=1 zostanie zmienione na ID=2
- W wyniku kaskadowego update w tabeli Child wszystkie rekordy z IDParent=1 zostaną zmienione na IDParent=2
- W wyniku operacji INSERT dla drugiego wiersza tabeli Parent jego identyfikator ID=2 zostanie zmieniony na ID=3
- W wyniku kaskadowego update w tabeli Child wszystkie rekordy z IDParent=2 zostaną zmienione na IDParent=3
I tu jest pies pogrzebany...
Rozwiązanie problemu jest bajecznie proste i co ciekawe zostało zaimplementowane w DataSet Designerze w VS 2008. Otoż wystarczy zmienić wartości dwóch właściwości dla kolumny ID w datasecie. AutoIncrementSeed zmienić z 0 na -1 a AutoIncrementStep z 1 ma -1. Mamy w tym momencie gwarantowane, że ID datasetu i bazy nigdy się nie pokryją.
A wniosek z tego wszystkiego taki: nie wyłączajmy sprawdzania integralności datasetu jeśli naprawdę tego nie potrzebujemy.