SQLXMLとシリアル化を使用して.NETオブジェクトをSQL Serverに保存する

はじめに

　前の記事『SQLXMLとシリアル化を利用してSQL Serverからオブジェクトを取得する』を発表した後、私は、同記事で設計したスキーマ定義ファイルと、SQLXMLに含まれるUPDATEGRAMという別の技法を使用して、オブジェクトをデータベースに保存し直す方法について調べました。

　この記事では、マイクロソフトが設計したこの技法について説明します。そして、シリアル化とカスタムクラス属性を使用し、SQL Serverに送信して自動生成文を実行できる適切なXMLストリームを形成する、UPDATEGRAMクラスを作成します。

UPDATEGRAM機能

　まず最初に、Microsoft SQLXMLのUPDATEGRAM機能の概要を紹介します。UPDATEGRAM機能の詳細は、オンラインヘルプ（マシンにSQLXMLライブラリがインストールされている場合）またはMSDNサイトで見ることができます。この概念を要約すると、UPDATEGRAMは、XMLドキュメントであると共にXMLスキーマ定義であり、SQLXMLライブラリによって使用され、レコードを更新、挿入、および削除するコマンドの作成と実行をSQL Serverにおいて自動化するものと言えます。

　各UPDATEGRAMは、主に、一連のSYNC要素によって形成されます。それぞれは、同じトランザクションコンテキストで実行される操作のセットです。各SYNC要素は、実行される操作セットごとにBEFORE要素とAFTER要素を含むことができます。レコードを配置したり作成したりするにはBEFORE要素を使用し、レコードへの変更を保持するにはAFTER要素を使用します。空のBEFORE要素を指定すると、挿入が実行されます。一方、空のAFTERは、削除操作に相当します。両方が存在する場合は、「UPDATE AFTER where BEFORE」のような状態が生成されますが、この記事ではこの状態を扱います。

データ更新用フォームの追加

　さて、前の記事のWindowsアプリケーションに戻り、指定のOrderのプロパティを変更し、データを更新してSQL Serverに戻す新しいフォームを追加しましょう。

　グリッドフォームからフォームを開くためのDoubleClickイベントハンドラを追加し、次のコードを追加します。

private void grd_orders_DoubleClick(object sender, EventArgs e)
   {
      Form f = new OrderForm(
         this.orders[this.grd_orders.CurrentRowIndex]);
      f.Visible=true;
   }

　Loadイベントの中で、Orderをコントロールにバインドします。

private void OrderForm_Load(object sender, System.EventArgs e)
   {
      this.lblOrderNum.Text = this.order.OrderID.ToString();

      //we don’t want null dates,
      //instead the control will be set to current or default
      if(this.order.OrderDate!=DateTime.MinValue)
         this.dtpOrderDate.Value = this.order.OrderDate;
      if(this.order.RequiredDate!=DateTime.MinValue)
         this.dtpRequiredDate.Value = this.order.RequiredDate;

      this.txtShipAddress.Text = this.order.ShipAddress;
      this.txtShipCity.Text = this.order.ShipCity;
      this.txtShipName.Text = this.order.ShipName;
      this.txtShipZip.Text=this.order.ShipPostalCode;
      this.txtShipCountry.Text = this.order.ShipCountry;
   }

［Save］ボタンの実装

　Orderを編集できる状態になったので、［Save］ボタンの実装に進みましょう。Form内のコントロールから値を取得し、それをOrderオブジェクトに設定するメソッド（getChanges()など）を作成する必要があります。我々の目標は、古いOrderをBeforeノード、変更後のOrderをAfterノードとするUPDATEGRAMを生成し、SQL Serverに送信して、更新を自動的に行うことです。

　まずに、Before状態を取得するメソッドを定義する必要があります（オブジェクトの現在の状態がAfter状態になります）。そこで、バイナリシリアル化を使用して、オブジェクトの複製と比較を行うことにしました。これは、プロジェクト内のエンティティの基本クラスで使用される、私が作成した小さなヘルパークラスです。

public class CloneHelpers
   {
      public static object DeepClone(object source)
      {
         MemoryStream m = new MemoryStream();
         BinaryFormatter b = new BinaryFormatter();
         b.Serialize(m, source);
         m.Position = 0;
         return b.Deserialize(m);
            
      }
      public static bool DeepEquals(object objA,object objB)
      {
         MemoryStream serA = serializedStream(objA);
         MemoryStream serB = serializedStream(objB);
         if(serA.Length!=serA.Length)
            return false;
         while(serA.Position<serA.Length)
         {
            if(serA.ReadByte()!=serB.ReadByte())
               return false;
         }
         return true;

      }
      public static MemoryStream serializedStream(object source)
      {
         MemoryStream m = new MemoryStream();
         BinaryFormatter b = new BinaryFormatter();
         b.Serialize(m, source);
         m.Position = 0;
         
         return m;
      }
   }

　次に、基本のビジネスクラスを定義しましょう。

[Serializable]
   public abstract class BaseBusinessEntity 
   {
      public object DeepClone()
      {
         return CloneHelpers.DeepClone(this);
      }

   
      public bool DeepEquals(object obj)
      {
         return CloneHelpers.DeepEquals(this,obj);
      }
      
   }

[Serializable]
   public abstract class BaseBusinessEntityCollection : CollectionBase
   {
      public object DeepClone()
      {
         return CloneHelpers.DeepClone(this);
      }
   }

　OrderFormでは、操作するエンティティの初期状態を定義するコードとプロパティを設定できます。

public OrderForm(OrdersMgmt.Order ord)
   {
      //get the order from the grid form
      this.order=ord;
      //set the initial state
      this.orderBefore = (OrdersMgmt.Order) ord.DeepClone();

　この時点で、次のように考える方がいるかもしれません。初期状態のオブジェクトをXMLへとシリアル化してBeforeノードを作成し、オブジェクトのプロパティが変更されたら、オブジェクトをXMLへ再びシリアル化してAfterノードを取得する、というやり方です。

　ここで、UPDATEGRAMの生成を抽象化してみましょう。そして、XMLストリームを正しく形成および実行するプロパティを保持するクラスを設計します。

　このクラスを最も重要なプロパティであるXSDファイルでインスタンス化します。

public updategram(string schemaFile)
   {
      //set the current schema to use 
      //when sending updategram to SQLSERVER
      this.schemaFilePath = schemaFile;
      //initialize the state of the updategram
      //and open the xml document
      this.InitUpdgDocument();
      this.syncCollection = new Hashtable();
   }

　InitUpdgDocument()でXMLドキュメントが開始します（ここでは話を簡単にするためにdocumentオブジェクトを使用していますが、自分の技術レベルに合わせて自由に最適化できます）。

private void InitUpdgDocument()
   {
      if(this.updgDocument==null)
      {
         //create empty updategram
         updgDocument = new XmlDocument();
         updgDocument
            .LoadXml("<ROOT xmlns:"+prefix+"=""+ns+""></ROOT>");

      }
   }

　syncCollectionは、初期状態をキーとして、かつ、実際の状態を値として、変更する必要があるすべてのオブジェクトを保持しています。UPDATEGRAMとフォームの初期状態が作成されたので、［Save］ボタンをクリックするとどうなるかを確認できます。

private void btnUpdate_Click(object sender, System.EventArgs e)
   {
      //get form values and set entity values
      this.getChanges();
      //compare to object and create new sync node if something changed
      updg.Process(this.orderBefore,this.order);
      //commit all sync nodes created
      updg.Commit();

      //set new initial state
      this.orderBefore = (OrdersMgmt.Order) this.order.DeepClone();

      //this.Close();
   }

　getChages()演算では、コントロールからorder値を設定し、UPDATEGRAMのprocessメソッドを呼び出してオブジェクトを比較し、2つの状態が異なる場合は新しいキー/値エントリをsyncCollectionに挿入します。この例の場合、操作しているオブジェクトは1つだけですが、複数のオブジェクトの変更も問題なく行われます。すべての更新をコミットできる状態になったら、updategramのCommitメソッドを呼び出します。SQL Serverで実際の更新作業が実行されます。Commitメソッドのコードを見てみましょう。

public void Commit()
   {
      //cycle all syncs with original keys
      foreach(object objOriginal in this.syncCollection.Keys)
      {
         //get reference to changed object
         object objChanged = this.syncCollection[objOriginal];

         //initialize a the serializer provider
         UpdgXSerializer x = 
            new UpdgXSerializer(objOriginal,objChanged);
         //get the serializer for the before element
         this.beforeSerializer = 
            x.getUpdgSerializer(UpdategramElement.Before);
         //get the serializer for the after element
         this.afterSerializer = 
            x.getUpdgSerializer(UpdategramElement.After);

         //create the sync element
         this.createSync();

         //create the before element
         this.BeginUpdate(objOriginal);

         //create the after element
         this.EndUpdate(objChanged);
      }

      //verify that we have a document to submit
      if(this.updgDocument==null)
         return;


      //create the stream with the xml document
      MemoryStream ms;
      ms = new MemoryStream();
      this.updgDocument.Save(ms);
      ms.Position = 0;

      try
      {
         //execute the xml stream
         XmlReader results = 
            sqlxmlHelper.executeUpdateGram(this.schemaFilePath,ref ms);
      }
      catch(Exception ex)
      {
         throw new Exception(
            "Error while committing operations to DB, "+ex.Message,ex);
      }
      finally
      {
         //clean up after submit
         ms.Close();
      }

      this.syncCollection = new Hashtable();
      updgDocument = null;
   }

　既定のシリアル化では、すべてのOrderLinesが子ノードとして順々に含まれるXMLが形成されます。

<updg:before>
   <Order>
      <OrderID>10250</OrderID>
      ...
      <OrderLines>
         <OrderDetail>
            <Quantity>10</Quantity>
            <Discount>0</Discount>
            <Item>
               <ProductID>41</ProductID>
               <ProductName>Jack’s New England Clam Chowder
               </ProductName>
               <UnitPrice>9.65</UnitPrice>
            </Item>
            <OrderID>10250</OrderID>
            <ProductID>41</ProductID>
         </OrderDetail>
         <OrderDetail>
            <Quantity>35</Quantity>
            <Discount>0.15</Discount>
            <Item>
               <ProductID>51</ProductID>
               <ProductName>Manjimup Dried Apples</ProductName>
               <UnitPrice>53</UnitPrice>
            </Item>
            <OrderID>10250</OrderID>
            <ProductID>51</ProductID>
         </OrderDetail>

問題点

　SQLXMLに送信されると、次のエラーが発生します。

　つまり、ノード（オブジェクト）ごとに、インデックスまたはIDを設定して、updg:id属性を指定する必要があります。今回の例では、OrderDetailオブジェクトに読み取り/書き込みプロパティを用意し、このプロパティにXML内での名前と記述方法を定義する属性を持たせるという簡単な方法を採用しています。

[XmlAttribute(AttributeName="id",
 Namespace="urn:schemas-microsoft-com:xml-updategram")]
   public int OrderIDKey
   {
      get
      {
         return this.ProductID;
      }
      set
      {
      }
   }

　このケースのProductIDは、Order内のOrderDetailごとに一意であるため、IDとして適しています。また、プロパティの読み取り/書き込みを行うには空のsetが必要です。そうでないと、XMLでシリアル化されません。

　Orderオブジェクトに対しても同じ操作を行い、複数のOrderのバッチ更新を可能にします。

[XmlAttribute(AttributeName="id",
 Namespace="urn:schemas-microsoft-com:xml-updategram")]
   public int OrderIDKey
   {
      get
      {
         return OrderID;
      }
      set
      {
      }
   }

　OrderとOrderDetailの属性を使用した新しいUPDATEGRAMのXMLを見てみましょう。

<Order updg:id="10250">
  <OrderLines>
    <OrderDetail updg:id="41">

　このケースでは、SQL Serverに送信されても何も問題は生じません。

　しかし、IT環境では、当初とまったく変わらないものなどあり得ません。そこで、既定のシリアル化が動作しないいくつかのケースと、その問題の解決方法について考えてみましょう。

null値の処理

　まず、null値の処理です。Webフォームの入力フィールドを処理したことがある人ならば誰でも、ユーザーがフィールドをクリアして、null値ではなく空白が、文字列型、または数値や日付の既定値としてデータベースに保存されるという問題を経験したことがあるでしょう。また、nullプロパティは空の要素でシリアル化されるため、SQLXMLはそのカラムを更新しません。

　SQLXMLのNULL処理については、MSDNライブラリで説明されています。XMLルート要素で定義される既定値を使用すると（例ではupdg:nullvalue="|isnull|"を使用）、その値を含むすべての要素がDBNULLで更新されます。今回の例では、null化が必要なすべてのプロパティ名をパブリックフィールドに格納するという方法を採用しています。

[XmlArray("nullProps"),XmlArrayItem("prop")]
   public string[] nullProps;

　次のようにして、プロパティ名をリストに追加するメソッドを公開します。

public void nullProperty(string propertyName)
   {
      foreach(PropertyInfo pInfo in this.GetType().GetProperties()) 
      {
         if(pInfo.Name==propertyName)
         {
            if(nullProps!=null && 
               Array.IndexOf((Array)nullProps,propertyName)==-1){
               string[] tmp = (string[]) nullProps.Clone();
               nullProps = new string[tmp.Length+1];
               nullProps[0] = propertyName;
               tmp.CopyTo(nullProps,1);
            }
            else
               nullProps = new string[]{propertyName};
            break;
         }
      }
   }

　フォームのgetChanges()メソッドでは、次のようにして、挿入されたnull値を簡単に処理できます。

      if(this.txtShipZip.Text==string.Empty)
         this.order.nullProperty("ShipPostalCode");
      if(this.txtShipCountry.Text==string.Empty)
         this.order.nullProperty("ShipCountry");

　UPDATEGRAMクラスのwriteAfterXMLを見てみましょう。このメソッドでは、Afterノードを作成し、null値を設定します。

private void writeAfterXML(ref object obj,ref XmlElement after)
   {
      MemoryStream ms;
      StreamReader sr;

      ms = new MemoryStream();

      XSerializer.serialize(ref ms
         ,obj,this.afterSerializer);

      sr = new StreamReader(ms);
      sr.BaseStream.Position = 0;

      //remove declaration
      after.InnerXml = sr.ReadToEnd().Remove(0,23);

      //set null values
      //get the list of properies to set DBNULL 
      //for each object at any level
      XmlNodeList list = after.SelectNodes("//*[nullProps]");
      foreach(XmlNode node in list)
      {
         //select the list of properties
         XmlNodeList propsToNull = node.SelectNodes("nullProps/prop");
         foreach(XmlNode prop in propsToNull)
         {
            XmlNode property = node.SelectSingleNode(prop.InnerText);
            if(property!=null)
               property.InnerText = UPDG_NULL_CODE;
         }
         //remove nullProps node to avoid sqlxml conflict
         node.RemoveChild(node.SelectSingleNode("nullProps"));
      }

      //remove empty elements
      list = after.SelectNodes("//*[.=’’]");
      foreach(XmlNode node in list)
      {
         node.ParentNode.RemoveChild(node);
      }
      sr.Close();
      ms.Close();
   }

　writeBeforeXMLでは、削除されてnullノードになるプロパティだけが問題です。1つのOrderについてテストを行い、郵便番号と国を削除し、保存すると、結果は次のようになります。

リレーショナルデータの整合性とルックアップ値

　もう1つの問題は、リレーショナルデータの整合性とルックアップ値に関するものです。次のコード行で、Orderから先頭のOrderLineを削除するものとします。

this.order.OrderLines.RemoveAt(0);

　AFTERノード内のこの後のXMLシリアル化が、削除されたOrderLineを失うのは当然ですが、これが原因で、OrderDetailだけでなく関連するProductsも削除されるかどうかは定かではありません。関係が適切に定義されている「Northwind」データベースの場合は、次の図のような例外がスローされます。

　Productは別のOrderLineによって参照され、Productが削除された場合、連鎖処理は実行されません。これとは別に、関係が存在しない場合は、オブジェクトのこの部分をUPDATEGRAMに含まない方法を見つける必要があります。私は考えて、これらのプロパティを除外するメソッドを作成することにし、オブジェクトのシリアル化をカスタマイズすることにしました。

シリアル化のカスタマイズ

　そこで、まず最初に、XMLシリアル化から除外するプロパティをマークする属性を作成しました。

public class LookupAttribute:System.Attribute
   {
      public LookupAttribute()   {}
   }

　次に、常に確実に必要なプロパティ（プライマリキーや外部キーなど）について考慮して、2番目の属性を作成しました。

public class IntegrityCheckAttribute:System.Attribute
   {
      public IntegrityCheckAttribute()   {}
   }

　その後、プロパティをOrderDetail内のProductと同じように定義しました。

[Lookup]
public Product Item

　また、すべてのプライマリキーと外部キーを、Order内のOrderIDと同じように定義しました。

[IntegrityCheck]
public int OrderID

　オブジェクトの状態に応じてマーク付きプロパティをXMLシリアル化から除外する、という機能を持ったオブジェクト用のカスタムシリアライザを作成できます。XmlAttributeOverridesクラスについては、MSDNの記事を参照してください。

　このシリアライザの構成は次のとおりです。

　コンストラクタのコードを次に示します。

public UpdgXSerializer(object objBefore,object objAfter)
   {
      //hold reference to before state
      this._Before=objBefore;
      //hold reference to after state
      this._After=objAfter;
   }

　UDATEGRAMノードのSerializerオブジェクトを返すメインの処理は次のとおりです。

public XmlSerializer getUpdgSerializer(UpdategramElement updgElement)
   {
      //init. the collection of overrides
      XmlAttributeOverrides myOverrides = new XmlAttributeOverrides();

      Type objType=null;
      //choose the right object
      switch(updgElement)
      {
         case UpdategramElement.Before:
            //parse the object to retrive attribute overrides
            this.parseType(this._Before,ref myOverrides,updgElement);
            objType = this._Before.GetType();

            break;
         case UpdategramElement.After:
            this.parseType(this._After,ref myOverrides,updgElement);
            objType = this._After.GetType();

            break;
      }
      //advanced serializer with attribute overrides
      XmlSerializer retVal = new XmlSerializer(objType, myOverrides);

      return retVal;
   }

　parseTypeプライベート呼び出しは、隠す必要があるプロパティを見つけるための処理を開始します。

private void parseType(object source,
    ref XmlAttributeOverrides xmloverrides,
    UpdategramElement updgElement)
   {
      Type objType = source.GetType();
      // Iterate through all the properties of the class
      foreach(PropertyInfo pInfo in objType.GetProperties()) 
      {
         //get the attribute overrides for the property
         getAttributesForProperty(
            pInfo,ref xmloverrides,objType,updgElement);
         if(typeof(IList).IsAssignableFrom(pInfo.PropertyType))
         {
            //when we have non empty collection
            //we cyle iterate the parsing operations
            IList val = (IList) pInfo.GetValue(source,null);
            if(val !=null && val.Count>0)
            {
               parseType(val[0],ref xmloverrides,updgElement);
            }
         }
      }
   }

　getAttributesForPropertyプライベート呼び出しは、属性オーバーライドの実際の挿入を行います。

private void getAttributesForProperty(
      PropertyInfo pInfo,
      ref XmlAttributeOverrides xmloverrides,
      Type objType,
      UpdategramElement updgElement)
   {
      // need this as we start from the assumption that in 
      // the Before we need the minimum in the After we need the more
      bool ignore = false;

      if(updgElement==UpdategramElement.Before)
      {
         ignore = true;
      }

      // Iterate through all the Attributes for each property.
      foreach (Attribute attr in Attribute.GetCustomAttributes(pInfo)) 
      {
         Type tmp = attr.GetType();
         //remove from before the lookup values 
         //and leave the primary/foreign keys
         if(updgElement==UpdategramElement.Before)
         {
            if(tmp==typeof(IntegrityCheckAttribute))
            {
               ignore = false;
            }
            if(tmp==typeof(LookupAttribute))
               ignore = true;
         }
            //remove from after node
            //the lookup attribute (if it’s not an insert)
         else if(updgElement==UpdategramElement.After)
         {
            if((tmp==typeof(LookupAttribute)) && this._Before!=null)
               ignore=true;
         }
      }
      if(ignore)
      {
         XmlAttributes myAttributes = new XmlAttributes();
         myAttributes.XmlIgnore = true;
         xmloverrides.Add(objType,pInfo.Name,myAttributes);
      }
   }

　コメントを読めば、コードのロジックは理解できるでしょう。次に示すのは、Orderオブジェクトに関する最終的なUPDATEGRAMです（先頭のOrderLineが削除されています）。

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
   <updg:sync updg:nullvalue="|isnull|">
      <updg:before>
         <Order updg:id="10250">
            <OrderID>10250</OrderID>
            <OrderLines>
               <OrderDetail updg:id="41">
                  <OrderID>10250</OrderID>
                  <ProductID>41</ProductID>
               </OrderDetail>
               <OrderDetail updg:id="51">
                  <OrderID>10250</OrderID>
                  <ProductID>51</ProductID>
               </OrderDetail>
               <OrderDetail updg:id="65">
                  <OrderID>10250</OrderID>
                  <ProductID>65</ProductID>
               </OrderDetail>
            </OrderLines>
         </Order>
      </updg:before>
      <updg:after>
         <Order updg:id="10250">
            <OrderID>10250</OrderID>
            <OrderDate>1996-07-05T00:00:00.0000000+02:00</OrderDate>
            <RequiredDate>1996-08-07T00:00:00.0000000+02:00
            </RequiredDate>
            <ShippedDate>1996-07-12T00:00:00.0000000+02:00
            </ShippedDate>
            <Freight>65.83</Freight>
            <ShipName>Hanari Carnes</ShipName>
            <ShipAddress>Rua do Paulo, 69</ShipAddress>
            <ShipCity>Rio de Janeiro</ShipCity>
            <ShipPostalCode>283458</ShipPostalCode>
            <ShipRegion>RJ</ShipRegion>
            <ShipCountry>test</ShipCountry>
            <OrderLines>
               <OrderDetail updg:id="51">
                  <Quantity>35</Quantity>
                  <Discount>0.15</Discount>
                  <OrderID>10250</OrderID>
                  <ProductID>51</ProductID>
               </OrderDetail>
               <OrderDetail updg:id="65">
                  <Quantity>15</Quantity>
                  <Discount>0.15</Discount>
                  <OrderID>10250</OrderID>
                  <ProductID>65</ProductID>
               </OrderDetail>
            </OrderLines>
         </Order>
      </updg:after>
   </updg:sync>
</ROOT>

まとめ

　このプロトタイプは、多くの点で改善の余地があります。2つのオブジェクトの状態を適切に比較することでXMLを適切にクリーンアップしたり、BeforeノードとAfterノードの全プロパティを使用することで、オフライン環境でのデータ整合性を維持するためのデータセット内部DiffGramのような手段を実現したり、タイムスタンプを使用してこれを実現したりすることもできるでしょう。すべては、自分自身と自分が実現する実装にかかっています。ここで定義したのは、SQLXMLと.NET Frameworkを使用して、SQL Server上でのエンティティレベルのデータベース読み取り/書き込み操作を自動化するための基本的な枠組みにすぎません。これをどう活用するかは皆さん次第です。