|
Страница 11 из 15
UpdateGrams До сих пор под XML-взаимодействием с SQL Server понималось, в основном, чтение данных с сервера в XML-формате с помощью SELECT ... FOR XML или XPath. Возникает вопрос: можно ли их модифицировать в рамках XML-представления. Про возможность модификации косвенно упоминалось пару раз: когда мы говорили про возможности ADO.Net (п.7) и про шаблоны (п.10). Первый способ предусматривает связь с сервером через DataAdapter и работу с DataSet в рамках его XMLной ипостаси. Второй можно реализовать, поместив запросы на обновление в секцию <sql:query> шаблона:
<Root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:header>
<sql:param name="Имя"></sql:param>
<sql:param name="Фамилия"></sql:param>
</sql:header>
<sql:query>
UPDATE Customers SET ContactName = 'Maria Anders' WHERE CustomerID = 'ALFKI'
INSERT Employees (FirstName, LastName) Values (@Имя, @Фамилия)
</sql:query>
</Root>
Его выполнение:
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 |