SQLXMLとシリアル化を利用してSQL Serverからオブジェクトを取得する

はじめに

　本稿では、一連のエンティティで利用するデータアクセス層の設計方法を説明します。ここでは、XSDスキーマを記述して、「XMLストリームをSQL Serverから読み取るヘルパークラス」と、「XMLストリームのシリアル化を解除するヘルパークラス」という、2種類の単純なヘルパークラスを設計します。

開発環境

　使用するテクノロジは、次のとおりです。

SQLXML
SQL Server
.NET

　SQLXMLライブラリは、Microsoftから無償で提供されています。

本稿で取り上げるサンプル

　多くの開発者に共通の課題──それは、「変更がたやすく、パフォーマンスも良好な、再利用できるデータアクセス層を設計すること」です。

　本稿で取り上げるサンプルは、DataReaderを使うクラスをハードコードしたり、結果フィールドに基づいてオブジェクトのプロパティを設定したり、という処理を回避するために私がこれまでに数多く開発してきた、データベーステーブルをオブジェクトにマッピングするシステムの一例です。

　この考えは、SQL Server 2000で「クエリに対する応答としてXMLを返す」という機能がサポートされていることから生まれたものです。XSDスキーマを使えば、そのXMLの正確な構造を定義することができます（MSDNの『Creating XML Views by Using Annotated XSD Schemas』を参照）。

　また、.NETでは、どんな型のオブジェクトでもXMLとしてごく簡単にシリアル化/シリアル化解除できるため、私は「SQL Serverならば、オブジェクトをインスタンス化するための材料となる、正しいXML構造を提供してくれるのでは」と考えました。

データベース構造に対応するクラスの実装

　では、単純な例を挙げ、それを実装してみましょう。まずはデータベース構造に対応するクラスを設計します。ここでは、Northwindサンプルデータベースを使用して注文データを表示することにします（図1を参照）。

図1

　データベース構造に対応するアプリケーションのクラスは図2のようになります。

図2

　ダウンロードサンプルに収録されているプロジェクトを開き、Visual Studioのクラスブラウザでクラスの内容を調べると、データベース内のテーブルフィールドに対応するプロパティがすべてあることがわかります。

SQL Serverから取得したデータのXMLシリアル化

　ここで、OrderCollectionというクラスをビジネスオブジェクトとして操作する場合には、このオブジェクトにSQL Serverから取得したデータを埋め込む必要があります。

　多くのアプリケーションでは、通常は、コネクションを開き、非正規化ビューにクエリを発行するか複数の結果セットを取得してエンティティクラスを循環、作成し、それらをコレクションに追加するといったアプローチをとります。

　しかし本稿のアプローチでは、XMLストリームのシリアル化を解除するだけで、あとの処理はフレームワークが引き受けてくれます。

　次のコードは、XMLシリアル化を適用したOrderCollectionオブジェクトのフォーマットを示しています。アプリケーションに何を返す必要があるのかを知るための参考にしてください。

OrderCollection orders = new OrderCollection();
for(int i=1;i<=3;i++)
{
    Order o = new Order();
    o.Freight = 1.2M;
    o.OrderDate = System.DateTime.Now;
    o.OrderID = i;
    o.RequiredDate = DateTime.Now.AddDays(30);
    o.ShipAddress = "103 Park Avenue";
    o.ShipCity = "Miami";
    o.ShipCountry = "USA";
    o.ShipName = "n/a";
    o.ShipPostalCode = "72100";
    o.ShipRegion = "Florida";
    
    o.OrderLines = new OrderDetailCollection();
    for(int li=1;li<=3;li++)
    {
        OrderDetail ol = new OrderDetail();
        ol.Item = new Product();
        ol.Item.ProductID = li;
        ol.Item.ProductName = "Testing Product";
        ol.Item.UnitPrice = 12;
        ol.Quantity = Convert.ToInt16(li * 5);
        ol.Discount = 0;
        o.OrderLines.Add(ol);
    }
    orders.Add(o);

}
XSerializer.serialize(@"c:est.xml",orders);

　サンプルコードには、オブジェクトのシリアル化/シリアル化解除を行う単純なヘルパークラス（XmlSerializer）が含まれています。それでは、OrderCollectionにテストデータを埋め込むテストコードを実行し、そのデータをシリアル化してディスクに格納し、XMLを確認してみましょう。

　このコードにより、3つの注文を含んだコレクションを作成することができます（各注文は3行から構成されています）。シリアル化されたXMLは次のようになります。

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfOrder xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Order>
        <Freight>1.2</Freight>
        <OrderDate>2004-05-28T21:53:50.2656250+02:00</OrderDate>
        <OrderID>1</OrderID>
        <OrderLines>
            <OrderDetail>
                <Discount>0</Discount>
                <Item>
                    <ProductID>1</ProductID>
                    <ProductName>Testing Product
                    </ProductName>
                    <UnitPrice>12</UnitPrice>
                </Item>
                <Quantity>5</Quantity>
            </OrderDetail>
            <OrderDetail>
                <Discount>0</Discount>
                <Item>
                    <ProductID>2</ProductID>
                    <ProductName>Testing Product
                    </ProductName>
                    <UnitPrice>12</UnitPrice>
                </Item>
                <Quantity>10</Quantity>
            </OrderDetail>
            <OrderDetail>
                <Discount>0</Discount>
                <Item>
                    <ProductID>3</ProductID>
                    <ProductName>Testing Product&
                    #060;/ProductName>
                    <UnitPrice>12</UnitPrice>
                </Item>
                <Quantity>15</Quantity>
            </OrderDetail>
        </OrderLines>
        <RequiredDate>2004-06-27T21:53:50.2812500+02:00</RequiredDate>
        <ShipAddress>103 Park Avenue</ShipAddress>
        <ShipCity>Miami</ShipCity>
        <ShipCountry>USA</ShipCountry>
        <ShipName>n/a</ShipName>
        <ShippedDate>0</ShippedDate>
        <ShipPostalCode>72100</ShipPostalCode>
        <ShipRegion>Florida</ShipRegion>
    </Order>
    <Order>
        <Freight>1.2</Freight>
        <OrderDate>2004-05-28T21:53:50.2812500+02:00</OrderDate>
        <OrderID>2</OrderID>
        <OrderLines>
            <OrderDetail>
                <Discount>0</Discount>
                <Item>
                    <ProductID>1</ProductID>
                    <ProductName>Testing Product
                    </ProductName>
                    <UnitPrice>12</UnitPrice>
                </Item>
                <Quantity>5</Quantity>
            </OrderDetail>
            <OrderDetail>
                <Discount>0</Discount>
                <Item>
                    <ProductID>2</ProductID>
                    <ProductName>Testing Product&
                    #060;/ProductName>
                    <UnitPrice>12</UnitPrice>
                </Item>
                <Quantity>10</Quantity>
            </OrderDetail>
            <OrderDetail>
                <Discount>0</Discount>
                <Item>
                    <ProductID>3</ProductID>
                    <ProductName>Testing Product
                    </ProductName>
                    <UnitPrice>12</UnitPrice>
                </Item>
                <Quantity>15</Quantity>
            </OrderDetail>
        </OrderLines>
        <RequiredDate>2004-06-27T21:53:50.2812500+02:00</RequiredDate>
        <ShipAddress>103 Park Avenue</ShipAddress>
        <ShipCity>Miami</ShipCity>
        <ShipCountry>USA</ShipCountry>
        <ShipName>n/a</ShipName>
        <ShippedDate>0</ShippedDate>
        <ShipPostalCode>72100</ShipPostalCode>
        <ShipRegion>Florida</ShipRegion>
    </Order>
</ArrayOfOrder>

　パフォーマンスを上げるため、あるいは単に自分のニーズに合わせるために、このフォーマットとは異なる結果を取得することもできます。シリアル化について、また、属性を使ったXMLのフォーマット変更については、次の記事に目を通しておくことをお勧めします。

『Serialization in the .NET Framework』
『Introducing XML Serialization』
『Controlling XML Serialization Using Attributes』

XMLスキーマの設計

　OrderCollectionからどのようなXMLが生成されるかを把握したところで、次は、この構造に適合したスキーマを設計する必要があります。XSD、クラス、型付きデータセットの自動生成については、xsd.exeツールを研究したいかもしれませんが、ここではまずOrder複合型から見ていくことにしましょう。

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
 xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
 xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
 elementFormDefault="unqualified" 
 attributeFormDefault="unqualified">
    <xs:complexType name="Order">
        <xs:sequence>
            <xs:element name="Freight" type="xs:decimal"
                        sql:datatype="money"/>
            <xs:element name="OrderID" type="xs:int"
                        sql:field="OrderID"/>
            <xs:element name="OrderDate" type="xs:dateTime"/>
            <xs:element name="RequiredDate" type="xs:dateTime"/>
            <xs:element name="ShippedDate" type="xs:dateTime"/>
            <xs:element name="ShipAddress" type="xs:string"/>
            <xs:element name="ShipCity" type="xs:string"/>
            <xs:element name="ShipRegion" type="xs:string"/>
            <xs:element name="ShipPostalCode" type="xs:string"/>
            <xs:element name="ShipCountry" type="xs:string"/>
        </xs:sequence>
    </xs:complexType>
    <xs:element name="Order" type="Order" sql:relation="Orders"
     sql:key-fields="OrderID"/>
</xs:schema>

　この複合型では、Orderの構造だけを定義します。それぞれの要素は、名前と型のプロパティを表します（個々の要素をどれだけ精密に定義できるかについては、SQLXMLのオンラインヘルプを参照してください）。sql:field属性を指定しない場合は、要素名がデータベース列名になるものと仮定されます。

　これで、複合型を反映した要素を挿入できるようになり、sql:relation属性や主キーとsql:key-fieldsの組み合わせを使ってテーブルやビューを生成することができます。

クエリの実行

　クエリを実行するには、SQLXMLアセンブリをプロジェクトにインポートする必要があります。ライブラリが必要ですが、まだライブラリをインストールしていない場合は、マイクロソフトダウンロードセンターからファイルをダウンロードし、インストールを実行してください。

　「Microsoft.Data.SQLXML.dll」は、グローバルアセンブリキャッシュ（GAC）にあります。Visual Studioから追加するには、図3のようにメインタブを使用します。

図3

　SQLXMLを使用し、スキーマを渡してXMLReader（後でシリアル化解除に使用）を返すクエリを実行するには、もう1つのヘルパークラス（sqlxmlHelper.cs）が必要です。

private static SqlXmlCommand getCommand(
    string xpathQuery,string schemaPath,string rootTag)
{
    SqlXmlCommand retVal = getCommand(xpathQuery,schemaPath);
    if (string.Empty!=rootTag) retVal.RootTag = rootTag;
        return retVal;
}

private static SqlXmlCommand getCommand(string xpathQuery)
{
    SqlXmlCommand retVal = getCommand();
    retVal.CommandType = SqlXmlCommandType.XPath;
    retVal.CommandText = xpathQuery;
    return retVal;
}

private static SqlXmlCommand getCommand(
    string xpathQuery, string schemaFile)
{
    SqlXmlCommand retVal = getCommand(xpathQuery);
    retVal.SchemaPath = ConfigurationSettings
        .AppSettings["sqlxmlSchemasFolder "] + schemaFile;
    return retVal;
}

public static XmlReader executeXmlReader(
    string xpathQuery, string schemaPath, string rootTag)
{
    SqlXmlCommand cmd = 
    getCommand(xpathQuery,schemaPath,rootTag);
    return cmd.ExecuteXmlReader();
}

　SqlXmlCommandの定義は、ごく単純です。XSDスキーマへのパスを提供し（「app.config」で宣言した一連のファイルのディレクトリパス（下記の設定セクションを参照）の保守を容易にするため）、XPathクエリを設定し、結果が単一レコードでない場合はルートタグ名を提供する必要があります（結果のXMLにルート要素を含めるため）。

<appSettings>
    <add key="sqlxmlConnString" 
     value="Provider=SQLOLEDB.1;Integrated Security=SSPI;
Initial Catalog=Northwind;Data Source=.;"/>
    <add key="sqlxmlSchemasFolder"
     value="D:Documents and SettingslucaMy DocumentsVisual
 Studio Projectssqlxml_deserializationOrdersMgmtDALschemas"/>
</appSettings>

　これらの値は、自分のワークステーションの設定を反映するよう変更する必要がありますが、現時点では、SQLXMLはOLEDBプロバイダでしか動作しないことに注意してください。

　次のテストスクリプトをクライアントアプリケーション内で記述し、実行してください。

// we execute the reader with all Orders based on the Orders.xsd
// with ArrayOfOrder as root node

XmlReader reader = sqlxmlHelper
    .executeXmlReader("/Order","Orders.xsd","ArrayOfOrder");
reader.MoveToContent();
string xmlstring = reader.ReadOuterXml();
reader.Close();

// lets write the content to a file 
// so we can see if match the test.xml
// and can be deserialized as the OrderCollection
StreamWriter writer = new StreamWriter(@"c:estFromSQLServer.xml");

writer.Write(xmlstring);
writer.Flush();
writer.Close();

　「c:estFromSQLServer.xml」ファイルを開き、SQL Serverから戻された結果を調べると、次のようになっています。

<ArrayOfOrder>
    <Order>
        <Freight>32.38</Freight>
        <OrderID>10248</OrderID>
        <OrderDate>1996-07-04T00:00:00</OrderDate>
        <RequiredDate>1996-08-01T00:00:00</RequiredDate>
        <ShippedDate>1996-07-16T00:00:00</ShippedDate>
        <ShipAddress>59 rue de l’Abbaye</ShipAddress>
        <ShipCity>Reims</ShipCity>
        <ShipPostalCode>51100</ShipPostalCode>
        <ShipCountry>France</ShipCountry>
    </Order>
    ... All the Orders are xml nodes
</ArrayOfOrder>

　このXMLストリームは、OrderCollectionの基本構造を反映しています。つまり、このXMLストリームのシリアル化を解除して、実際のオブジェクトを作成することができます。

public static object deserialize(XmlReader reader ,object source)
{
    XmlSerializer ser = new XmlSerializer(source.GetType());
    MemoryStream ms;
    StreamWriter writer = null;
    try
    {
        reader.MoveToContent();
        string xmlstring = reader.ReadOuterXml();
        reader.Close();
        ms = new MemoryStream();
        writer = new StreamWriter(ms);
        writer.Write(xmlstring);
        writer.Flush();
        ms.Position = 0;
        source = ser.Deserialize(ms);
        ms.Close();
        writer.Close();
    }
    catch(InvalidOperationException iex)
    {
        if(reader.ReadState != ReadState.Closed) reader.Close();
        if(writer!=null) writer.Close();
        throw new Exception(
            "error deserializing object from xml reader", iex);
    }
    finally
    {
        if(reader.ReadState != ReadState.Closed) reader.Close();
        if(writer!=null) writer.Close();
    }
    return source;
}

　このコードでは最適化を重視していないため、間違いなく改善の余地があることを頭に入れておいてください。ここでは単に、あるオブジェクトの既存のインスタンスを使うメソッドを作成し、それをXmlReaderからシリアル化して、呼び出し元に戻しているだけです。

　これで、次のようにコードをあと3行追加すれば、OrderCollectionを単純なグリッドにバインドできるようになります。

OrderCollection orders = new OrderCollection();
orders = (OrderCollection) XSerializer
    .deserialize(sqlxmlHelper.executeXmlReader(
    "/Order", "Orders.xsd", "ArrayOfOrder"), orders);
this.grd_orders.DataSource = orders;

クラスの階層構造の設定

　図4は、私が作った単純なフォームです。

図4

　それぞれの注文（Order）に対してOrderLinesというプロパティがありますが、まだデータは入っていません。面白いのはここからです。スキーマ定義では、OrderLineおよび関連するProductエンティティ用に、新しい複合型を追加する必要があります。

<xs:complexType name="OrderDetail">
    <xs:sequence>
        <xs:element name="Quantity" type="xs:int"
                    sql:datatype="smallint" />
        <xs:element name="Discount" type="xs:float"
                    sql:datatype="real" />
        <xs:element name="Item" type="Product"
                    sql:relationship="OrderDetailProduct"
                    sql:relation="Products"/>
    </xs:sequence>
</xs:complexType>
<xs:complexType name="Product">
    <xs:sequence>
        <xs:element name="ProductID" type="xs:int"
                    sql:datatype="int" />
        <xs:element name="ProductName" type="xs:string" />
        <xs:element name="UnitPrice" type="xs:decimal"
                    sql:datatype="money" />
    </xs:sequence>
</xs:complexType>

　Productという型のOrderDetailの中に、Itemという要素がある点に注目してください。これは、同じクラス構造とXMLのシリアル化フォーマットを再現することが目的です。

　これで、OrderLinesという名前の新しい要素を宣言して「sql:is-constant="true"」に設定すれば、OrderLinesをOrderに含めて、子レコード1つ1つに対してXMLノードが再生成されるのを避けることができるほか、さらに重要な点である「sql:relationship="OrderDetails"という子要素同士が、スキーマで宣言されているどのリレーションシップによって結合されているか」を調べられるようになります。

<xs:element name="OrderLines" sql:is-constant="true">
    <xs:complexType>
        <xs:sequence>
            <xs:element name="OrderDetail" type="OrderDetail" 
             sql:relationship="OrderDetails"
             sql:relation="[Order Details]" />
        </xs:sequence>
    </xs:complexType>
</xs:element>

　リレーションシップは、ドキュメントの一番上で宣言します。

<xs:annotation>
    <xs:appinfo>
        <sql:relationship name="OrderDetails" parent="Orders"
         parent-key="OrderID" child="[Order Details]"
         child-key="OrderID" />
        <sql:relationship name="OrderDetailProduct"
         parent="[Order Details]" parent-key="ProductID"
         child="Products" child-key="ProductID" />
    </xs:appinfo>
</xs:annotation>

　このことを理解するのは、それほど難しくありません。これは見かけが外部キー宣言に似ており、複数のキーがカンマで区切られ、親と子は通常はテーブルとなりますが、ビューになることもできます。

　テストアプリケーションをもう一度実行し、今度はOrderLinesをクリックしてください。すると、図5のようになります。

図5

まとめ

　これで、SQLXMLとシリアル化によってデータアクセス層を抽象化し、クラスの階層構造を取得する方法の説明を終わります。このスキーマを使い、XPathクエリにフィルタを追加すれば、1つの注文のサブセットだけを取得することができます。これは、SQL Serverから取得したデータを.NETオブジェクトに埋め込むための、非常に強力な方法となります。