はじめてのSQL Server 2005その２

SQLCLRを使ってユーザ定義型を作成してみます。VS2005が作成するユーザ定義型のひな形では、

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct UserDefinedType : INullable

このように、Format.Nativeが指定されています。この場合、ユーザ定義型のメンバーには値型しか含めることが出来ません。しかし、それだとstring型が使えないので結構つらいです。そこで、ユーザ定義型に参照型を含める方法を見てみようと思います。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

/// <summary>
/// 参照型を含むユーザ定義型なので、Format.UserDefinedにして、
/// IBinarySerializeを実装
/// </summary>
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize=132)]
public struct Person : INullable, IBinarySerialize
{
    string firstname;
    string lastname;
    int age;

    public string FirstName
    {
        get { return firstname; }
    }

    public string LastName
    {
        get { return lastname; }
    }

    public int Age
    {
        get { return age; }
    }

    public override string ToString()
    {
        return String.Format("FirstName={0},LastName={1},Age={2}", 
            firstname, lastname, age);
    }

    public bool IsNull
    {
        get
        {
            return m_Null;
        }
    }

    public static Person Null
    {
        get
        {
            Person h = new Person();
            h.m_Null = true;
            return h;
        }
    }

    /// <summary>
    /// 文字列表現「名前,名字,年齢」からオブジェクトを生成
    /// </summary>
    /// <param name="s"></param>
    /// <returns></returns>
    public static Person Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        Person u = new Person();
        string[] ss = s.Value.Split(',');
        u.firstname = ss[0];
        u.lastname = ss[1];
        u.age = Int32.Parse(ss[2]);
        return u;
    }

    private bool m_Null;

    #region IBinarySerialize メンバ

    public void Read(System.IO.BinaryReader r)
    {
        m_Null = r.ReadBoolean();
        if (!m_Null)
        {
            // Nullでないので、メンバーを読み込む
            firstname = r.ReadString();
            lastname = r.ReadString();
            age = r.ReadInt32();
        }
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(m_Null);
        if (!m_Null)
        {
            // Nullでないので、メンバーを書き出す
            w.Write(firstname);
            w.Write(lastname);
            w.Write(age);
        }
    }

    #endregion
}

まず、Format.NativeからFormat.UserDefinedに変更し、使用する最大バイト数を指定します。ここで指定している132は適当なのであまり気にしないでください。(^^; また、IBinarySerializeを実装して、シリアライズ可能にする必要があります。

さて、ユーザ定義型が出来たので、これを使ってテーブルを作成してみましょう。

CREATE TABLE [dbo].[Persons](
  [parson] [dbo].[Person] NULL
) ON [PRIMARY]

ツールで自動生成したスクリプトなので分かり難いですが、テーブルPersonsにはフィールドpersonがあり、その型はユーザ定義型のPersonが指定されています。では、このテーブルに対してクエリーを発行してみましょう。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

partial class Program
{
    static void Main(string[] args)
    {
        SqlConnection conn = new SqlConnection(connectionString);
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * from Persons", conn);
            SqlDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                Person p = (Person)r.GetValue(0);
                Console.WriteLine("{0} {1} ({2})", p.LastName, p.FirstName, p.Age);
            }
            r.Close();
        }
        finally
        {
            if (conn != null)
                conn.Dispose();
        }
    }
}

クエリーの結果をPersonオブジェクトとして受け取ることが出来ますね。