static void Execute_UpdateTemplate_SQLXML()
{
...
cmd.CommandText = "..\\Templates\\XMLTemplate3.xml";
cmd.CommandType = SqlXmlCommandType.TemplateFile;
SqlXmlParameter prm = cmd.CreateParameter();
prm.Name = "@Имя"; prm.Value = "ааа";
prm = cmd.CreateParameter();
prm.Name = "@Фамилия"; prm.Value = "ббб";
cmd.ExecuteNonQuery();
}
равнозначно тому, как если бы эти запросы были выполнены обычным путем. Возникает
вопрос: можно ли модифицировать данные непосредственно на сервере (не кэшируя их
предварительно в DataSet) и работая с ними, как с XML, а не через реляционные операторы.
Такой способ предоставляет UpdateGrams.
Пример файла UpdateGrams, с которым мы будем работать, приведен на рис.8.
<?xml version="1.0" encoding="utf-8" ?>
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:header>
<updg:param name="Дата"/>
<updg:param name="Стоимость" />
</updg:header>
<updg:sync mapping-schema="..\Schemas\SQLSchema1.xsd">
<updg:before>
<Клиент Имя="Ana Trujillo" updg:id="x" />
<Клиент Имя="Antonio Moreno" updg:id="y" />
</updg:before>
<updg:after>
<Клиент updg:id="y"
Фирма="Рога&Копыта"
Имя="Дон Педро" />
</updg:after>
<updg:before>
<Клиент CustomerID="ALFKI" />
</updg:before>
<updg:after>
<Клиент CustomerID="ALFKI">
<Заказы>
<Заказ>
<Дата>
$Дата
</Дата>
<Стоимость>
cast(@Стоимость as money)
</Стоимость>
</Заказ>
</Заказы>
</Клиент>
</updg:after>
</updg:sync>
<updg:sync mapping-schema="..\Schemas\SQLSchema2.xsd">
<updg:after>
<Сотрудник updg:at-identity="x"
Имя="Альбус"
Фамилия="Дамблдор" >
<Сотрудник Имя="Минерва"
Фамилия="МакГонагалл"
ReportsTo="x" />
</Сотрудник>
</updg:after>
</updg:sync>
</ROOT>
Поскольку я постарался напихать в него по максимуму показательных вещей, разберем
данный updategrams по частям. Начнем с того, что это XML-файл, структура которого довольно
близка к уже рассмотренным нами шаблонам. В
</updg:sync>
может задаваться аннотированная
схема, которая отображает реляционную схему на XSD и благодаря которой мы можем работать
с информацией в БД как с XML. Если схема не указана, предполагается отображение по
умолчанию: каждая запись таблицы - элемент, поля в ней - атрибуты. По большому счету
updategrams состоит из секций трех типов:
<updg:header>
- в ней передаются возможные
параметры;
<updg:before> и
<updg:after>.
Если запись фигурирует только в
<updg:before>, она
удаляется; если только в
<updg:after> - вставляется; если и там, и там - обновляется. Рассмотрим,
например, ситуацию, когда весь updategrams состоит только из
<updg:sync mapping-schema="SQLSchema1.xsd">
<updg:before>
<Клиент Имя="Ana Trujillo" />
</updg:before>
<updg:after>
</updg:after>
</updg:sync>
В процессе его выполнения на SQL Server происходит следующее:
SET XACT_ABORT ON
BEGIN TRAN
DECLARE @eip INT, @r__ int, @e__ int
SET @eip = 0
DELETE Customers WHERE ( ContactName=N'Ana Trujillo' ) ;
SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT
IF (@e__ != 0 OR @r__ != 1) SET @eip = 1
IF (@r__ > 1) RAISERROR
( N'SQLOLEDB Error Description: Ambiguous delete, unique identifier required Transaction aborted ', 16, 1)
ELSE IF (@r__ < 1) RAISERROR
( N'SQLOLEDB Error Description: Empty delete, no deletable rows found Transaction aborted ', 16, 1)
IF (@eip != 0) ROLLBACK ELSE COMMIT
SET XACT_ABORT OFF
Из этого сразу видно несколько важных вещей. Первое: каждая секция
<updg:sync>
открывает отдельную транзакцию (см. BEGIN TRAN). Второе: описание модифицируемого
элемента (
<Клиент Имя="Ana Trujillo" />)
в
<updg:before>
и/или
<updg:after> должно
соответствовать одной записи, неважно, идет ли маппирование по дефолту или через
аннотированную схему. Если записей, отвечающих эквивалентному условию WHERE не
находится (@r__ < 1) или больше одной (@r__ > 1), будет сгенерирована ошибка (RAISERROR)
и транзакция откатится (ROLLBACK).
Поскольку в каждой секции
<updg:before>
и
<updg:after> может находиться несколько записей,
то необходимо как-то сопоставить их друг другу в случае обновления. Например, при парсинге
этого шаблона
<updg:before>
<Клиент Имя="Ana Trujillo" updg:id="x" />
<Клиент Имя="Antonio Moreno" updg:id="y" />
</updg:before>
<updg:after>
<Клиент updg:id="y"
Фирма="Рога&Копыта"
Имя="Дон Педро" />
</updg:after>
SqlXml должен понимать, что клиента по имени Ana Trujillo мы хотим просто удалить, а клиента
по имени Antonio Moreno обновляем, поэтому строка
<Клиент updg:id="y"
Фирма="Рога&Копыта" Имя="Дон Педро" />
в
<updg:after>
соответствует именно ему. Это можно
сделать двумя способами. Первый - пометить их одним и тем же
updg:id. Второй способ -
однозначно идентифицировать записи при помощи первичного ключа таблицы. Для этого нужно
а) определить ключ в аннотированной схеме (вспоминайте аннотацию
ms:key-fields) и б) явно
сослаться на него в
<updg:before>/
<updg:after>
(скажем,
<Сотрудник ID_Сотрудника="..." />).
Следующая часть
<updg:before>
<Клиент CustomerID="ALFKI" />
</updg:before>
<updg:after>
<Клиент CustomerID="ALFKI">
<Заказы>
<Заказ>
<Дата>
$Дата
</Дата>
<Стоимость>
cast(@Стоимость as money)
</Стоимость>
</Заказ>
</Заказы>
</Клиент>
</updg:after>
производит обновление и вставку одновременно. В заказы, сделанные клиентом с
идентификатором
ALFKI добавляется еще один заказ. При этом SQL Server сам распознает, что
новую запись в таблице Orders нужно подчинить данному клиенту в таблице Customers и
автоматически устанавливает для нее CustomerID в "ALFKI".
exec sp_executesql N'...
INSERT Orders (OrderDate, Freight, CustomerID)
VALUES (@Дата, cast(@Стоимость as money), N''ALFKI'') ... ',
N'@Дата nvarchar(19),@Стоимость sql_variant',
N'08.04.2002 18:41:34', 100
Это происходит благодаря тому, что поле CustomerID указано в качестве связующего в
<ms:relationship> схемы SQLSchema1.xsd. Обратите внимание, что несмотря на то, что в
аннотирующей схеме ему явно не соответствует никакой элемент/атрибут, на него можно
ссылаться в updategrams-файле. Для автоматического подчинения родительскую запись в
<updg:before>/
<updg:after>
необходимо идентифицировать именно по CustomerID, которое
значится как
parent-key в схеме (
parent-key="CustomerID").
Определение записи по другим
атрибутам, пусть даже однозначно ее идентифицирующим (например,
<Клиент
Имя="Maria Anders" Фирма="Alfreds Futterkiste" ...>), к такому эффекту не приводит.
Следующая часть updategrams-файла:
<updg:sync mapping-schema="..\Schemas\SQLSchema2.xsd">
<updg:after>
<Сотрудник updg:at-identity="x"
Имя="Альбус"
Фамилия="Дамблдор" >
<Сотрудник Имя="Минерва"
Фамилия="МакГонагалл"
ReportsTo="x" />
</Сотрудник>
</updg:after>
</updg:sync>
открывает другую транзакцию и работает с другой аннотирующей схемой, которая, как вы
помните, превращает parent-child таблицу в XML-иерархию. Здесь демонстрируется не
автоматическое, а "ручное" подчинение. Мы вставляем одновременно две записи. Чтобы
подчинить вторую первой, нужно значение поля
ReportsTo для второй установить в первичный
ключ первой. Положение осложняется тем, что первичный ключ - это identity, и его значение
априори неизвестно. Выйти из положения позволяет аннотация
updg:at-identity. Кстати, здесь
мы снова обращаемся к полю (ReportsTo), которое нигде в схеме не засвечено, а используется
только в описании отношения (
<ms:relationship>).
Дата и стоимость заказа передаются в виде параметров. Если параметр подставляется standalone,
можно использовать XPath-обозначение (
$Дата), если же над ним по ходу выполняются какие-то
SQL-преобразования - то SQLное (
@Стоимость).
Я не стал дальше усложнять пример, но хотел бы отметить еще две полезных аннотации.
<updg:nullvalue> позволяет оговорить SQLный NULL:
<updg:sync mapping-schema="SQLSchema2.xsd" updg:nullvalue="Пусто">
<updg:after>
<Сотрудник updg:at-identity="x"
Имя="Альбус"
Фамилия="Дамблдор" >
<Сотрудник Имя="Минерва"
Фамилия="МакГонагалл"
ReportsTo="Пусто" />
</Сотрудник>
</updg:after>
</updg:sync>
В этом случае второй сотрудник не будет подчинен первому, т.к. несмотря на то, что в XML эта
запись вложена, при занесении ее в таблицу ей не будет назначено никакого руководителя
(
ReportsTo=NULL).
Аннотация
ms:inverse применяется не в UpdateGrams, а при описании аннотированной схемы.
Мы не рассматривали ее в п.9, потому что она имеет смысл только тогда, когда та используется
затем в updategrams. Дело в том, что SQL Server считает, что иерархия "родитель-потомок" в
XML соответствует ограничению первичный - внешний для ключей связанных таблиц.
Рассмотрим updategrams вида
<updg:sync mapping-schema="..\Schemas\SQLSchema2.xsd">
<updg:before>
<Клиент CustomerID="ALFKI">
<Заказы>
<Заказ />
</Заказы>
</Клиент>
</updg:before>
</updg:sync>
Сразу ясно, что этот пример вызовет ошибку, поскольку данный клиент сделал более одного
заказа, а условием UpdateGrams является однозначная идентификация записи. Но дело не в этом.
На сервере будут выполнены действия в следующем порядке: DELETE Orders WHERE
CustomerID = N'ALFKI'; DELETE Customers WHERE CustomerID = N'ALFKI'. (Использование
атрибутов, не входящих в определение
<ms:relationship>,
- напр.,
<Клиент Имя="Maria Anders">
приведет к полному DELETE Orders). Из этой последовательности видно, что SqlXml сначала
пытается произвести обновления / удаления в дочерней таблице, а уже потом из родительской,
чтобы по возможности не противоречить referential constraints. В жизни бывают ситуации, когда
схема может задавать вложенность элементов, противоположную направлению действия
ограничения primary key/foreign key в таблицах. Тогда SqlXml, предполагая, что вложенный
элемент соответствует внешнему ключу, полезет на самом деле в первичную таблицу, получит
от SQL Server по рукам и откатит транзакцию. Чтобы побороть такую ситуацию, в
<ms:relationship>
нужно поставить
ms:inverse="true".
Полный список аннотаций, как всегда, можно найти в документации к SQLXML 3.0.
Вызов UpdateGrams из приложения происходит аналогично вызову шаблона через файл (см.
Скрипт 12) или Stream. При его тестировании в БД Northwind предварительно нужно превратить
связь Orders -> [Order Details] из строгой в каскадную. Поскольку [Order Details] не участвует в
updategrams, ее FK будет препятствовать обновлению Orders (в отличие от Customer -> Orders,
где это учитывается автоматически за счет указания в relationship в аннотированной схеме).
...
cmd.CommandText = "..\\Templates\\UpdateGrams1.xml";
cmd.CommandType = SqlXmlCommandType.TemplateFile;
SqlXmlParameter prm = cmd.CreateParameter();
prm.Name = "@Дата"; prm.Value = DateTime.Now.ToString();
prm = cmd.CreateParameter();
prm.Name = "@Стоимость"; prm.Value = 100;
cmd.ExecuteNonQuery();
Скрипт 12
|