ユーザ定義テーブルへのXMLカラム適用


ユーザが定義を決めて、データ格納するテーブルについて考える。
Txt1 , Txt2 …. num1, num2…. < 数に制限ある場合は単純にこれでいいけど。

とりあえずXQueryやインデックス付与可能なXMLカラムについて考えてみる。
http://msdn.microsoft.com/ja-jp/library/ms191497(v=sql.100).aspx

軽く機能確認した限りでは
まあ、コンポーネント作るのと限界性能把握すれば使えなくはなさそう。

確認コード

適当な基底クラス

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

abstract class DbBase : IDisposable
{
  private SqlConnection m_Con = null;

  public SqlConnection Con
  {
    get { return m_Con; }
  }

  public DbBase(string conString)
  {
    SqlConnection con = new SqlConnection(conString);
    con.Open();

    this.m_Con = con;
  }

  public DbBase(SqlConnection con)
  {
    this.m_Con = con;
  }


  protected void excuteCmd(SqlConnection con, List<SqlParameter> sqlParams, string query)
  {
    using (SqlCommand cmd = con.CreateCommand())
    {
      cmd.CommandText = query;
      if (sqlParams != null)
      {
        foreach (var sp in sqlParams)
        {
          cmd.Parameters.Add(sp);
        }//end loop
      }//end if

      cmd.ExecuteNonQuery();
    }//end cmd
  }//end method

  public void Dispose()
  {
    if (this.m_Con != null)
    {
      this.m_Con.Dispose();
      this.m_Con = null;
    }//end if
      
  }//end method
}//end class

定義作成

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

class CreateTest : DbBase
{
  public CreateTest(string conString)
    : base(conString)
  {
  }
  public CreateTest(SqlConnection con)
    : base(con)
  {
  }


  public void Test()
  {
    var con = base.Con;

    string schemaTab = "#FREE_SHEMA";
    string tabName = "#FREE_PT";

    string createSchemaQuery = string.Format(
@"CREATE TABLE
 {0}
(
  pid int primary key
 ,schema_data XML
)
", schemaTab);

    string createQuery = string.Format(
@"CREATE TABLE
 {0}
(
  pid int primary key IDENTITY(1,1)
 ,schemaid  int
 ,dataxml xml
)", tabName);

    string fkQuery = string.Format(
@"
ALTER TABLE {0}
ADD CONSTRAINT FK_{1}_{0} FOREIGN KEY (schemaid) 
    REFERENCES {1} (pid) 
    ON DELETE CASCADE
    ON UPDATE CASCADE

", tabName, schemaTab);

    base.excuteCmd(con, null, createSchemaQuery);
    base.excuteCmd(con, null, createQuery);
    base.excuteCmd(con, null, fkQuery);


  }//end method
}//end class

テストデータ投入

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml.Serialization;

class InsertTest : DbBase
{
  public InsertTest(string conString)
    : base(conString)
  {
  }
  public InsertTest(SqlConnection con)
    : base(con)
  {
  }

  public void Test()
  {
    var con = base.Con;

    string schemaTab = "#FREE_SHEMA";
    string tabName = "#FREE_PT";


    string insSchemaQuery =
      string.Format("INSERT INTO {0} (pid, schema_data) VALUES (@P1, @P2)", schemaTab);
    string insQuery =
      string.Format("INSERT INTO {0} (schemaid,dataxml) VALUES (@P1, @P2)", tabName);

    base.excuteCmd(con, this.shemaTest1(), insSchemaQuery);
    base.excuteCmd(con, this.schemTest2(), insSchemaQuery);

    base.excuteCmd(con, this.data1_1(), insQuery);
    base.excuteCmd(con, this.data2_1(), insQuery);
    base.excuteCmd(con, this.data1_2(), insQuery);
    base.excuteCmd(con, this.data2_2(), insQuery);

  }//end method


  private List<SqlParameter> shemaTest1()
  {
    DataTable dt = new DataTable();
    dt.TableName = "TEST1";
    dt.Columns.Add("E_ID", typeof(int));
    dt.Columns.Add("NAME", typeof(string));
    dt.Columns.Add("AGE", typeof(int));
    dt.Columns.Add("MPAY", typeof(int));

    var strXml = this.toXml(dt);

    int stInx = strXml.IndexOf(Environment.NewLine);
    strXml = strXml.Substring(stInx, strXml.Length - stInx);

    List < SqlParameter > insSchema = new List<SqlParameter>()
    {
      new SqlParameter("@P1", 1),
      new SqlParameter("@P2", strXml)
    };
    insSchema[1].DbType = DbType.Xml;

    return insSchema;
  }//end method

  private List<SqlParameter> schemTest2()
  {
    DataTable dt = new DataTable();
    dt.TableName = "TEST2";
    dt.Columns.Add("S_ID", typeof(int));
    dt.Columns.Add("NAME", typeof(string));
    dt.Columns.Add("S_VAL", typeof(int));

    var strXml = this.toXml(dt);

    int stInx = strXml.IndexOf(Environment.NewLine);
    strXml = strXml.Substring(stInx, strXml.Length - stInx);

    List<SqlParameter> insSchema = new List<SqlParameter>()
    {
      new SqlParameter("@P1", 2),
      new SqlParameter("@P2", strXml)
    };
    insSchema[1].DbType = DbType.Xml;

    return insSchema;
  }//end method

  private List<SqlParameter> data1_1()
  {
    SerializableDictionary<string, object> data = new SerializableDictionary<string, object>()
    {
      {"E_ID", 1 },
      {"NAME", "テスト太郎" },
      {"AGE", 32 },
      {"MPAY", 260000 }
    };
    var strXml = this.toXml(data);

    int stInx = strXml.IndexOf(Environment.NewLine);
    strXml = strXml.Substring(stInx, strXml.Length - stInx);

    List<SqlParameter> insSchema = new List<SqlParameter>()
    {
      new SqlParameter("@P1", 1),
      new SqlParameter("@P2", strXml)
    };
    insSchema[1].DbType = DbType.Xml;

    return insSchema;
  }//end method

  private List<SqlParameter> data2_1()
  {
    SerializableDictionary<string, object> data = new SerializableDictionary<string, object>()
    {
      {"S_ID", 1},
      {"NAME", "テスト店1"},
      {"S_VAL", 403 }
    };
    var strXml = this.toXml(data);

    int stInx = strXml.IndexOf(Environment.NewLine);
    strXml = strXml.Substring(stInx, strXml.Length - stInx);

    List<SqlParameter> insSchema = new List<SqlParameter>()
    {
      new SqlParameter("@P1", 2),
      new SqlParameter("@P2", strXml)
    };
    insSchema[1].DbType = DbType.Xml;

    return insSchema;
  }//end method

  private List<SqlParameter> data1_2()
  {
    SerializableDictionary<string, object> data = new SerializableDictionary<string, object>()
    {
      {"E_ID", 2 },
      {"NAME", "テスト次郎" },
      {"AGE", 26 },
      {"MPAY", 230000 }
    };
    var strXml = this.toXml(data);

    int stInx = strXml.IndexOf(Environment.NewLine);
    strXml = strXml.Substring(stInx, strXml.Length - stInx);

    List<SqlParameter> insSchema = new List<SqlParameter>()
    {
      new SqlParameter("@P1", 1),
      new SqlParameter("@P2", strXml)
    };
    insSchema[1].DbType = DbType.Xml;

    return insSchema;
  }//end method

  private List<SqlParameter> data2_2()
  {
    SerializableDictionary<string, object> data = new SerializableDictionary<string, object>()
    {
      {"S_ID", 2},
      {"NAME", "テスト店2"},
      {"S_VAL", 203 }
    };
    var strXml = this.toXml(data);

    int stInx = strXml.IndexOf(Environment.NewLine);
    strXml = strXml.Substring(stInx, strXml.Length - stInx);

    List<SqlParameter> insSchema = new List<SqlParameter>()
    {
      new SqlParameter("@P1", 2),
      new SqlParameter("@P2", strXml)
    };
    insSchema[1].DbType = DbType.Xml;

    return insSchema;
  }//end method


  private string toXml(object obj)
  {
    string res = null;

    XmlSerializer serializer = new XmlSerializer(obj.GetType());
    using (MemoryStream mem = new MemoryStream())
    using (StreamWriter sw = new StreamWriter(mem))
    {
      serializer.Serialize(sw, obj);
      res = Encoding.UTF8.GetString(mem.ToArray());
    }//end stream
    return res;
  }//end method


}//end class

選択テスト

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml.Serialization;


class selectTest : DbBase
{
  public selectTest(string conString)
    : base(conString)
  {
  }
  public selectTest(SqlConnection con)
    : base(con)
  {
  }

  public void Test()
  {
    var con = base.Con;

    string schemaTab = "#FREE_SHEMA";
    string tabName = "#FREE_PT";

    var schema = this.getSchema(schemaTab);

    string select =
      string.Format(@"SELECT schemaid,dataxml FROM {0}", tabName);
    using (SqlCommand cmd = con.CreateCommand())
    {
      cmd.CommandText = select;
      using (SqlDataReader reader = cmd.ExecuteReader())
      {
        if (!reader.HasRows)
          return;

        while (reader.Read())
        {
          int shemaid = reader.GetInt32(0);
          string xml = reader.GetString(1);

          SerializableDictionary<string, object> data =
            (SerializableDictionary<string, object>)fromXML(xml, typeof(SerializableDictionary<string, object>));

          var tab = schema[shemaid];
          var row = tab.NewRow();
          foreach (DataColumn col in tab.Columns)
          {
            if (data.ContainsKey(col.ColumnName))
              row[col] = data[col.ColumnName];
          }
          tab.Rows.Add(row);
        }//end loop
      }//end reader

      foreach (var key in schema.Keys)
      {
        using (var tab = schema[key])
        {
          List<string> colName = new List<string>(tab.Columns.Count);
          foreach (DataColumn col in tab.Columns)
          {
            colName.Add(col.ColumnName);
          }
          Console.WriteLine( string.Join(",", colName.ToArray() ));
          colName = null;

          
          foreach (DataRow row in tab.Rows)
          {
            bool t = false;
            foreach (DataColumn col in tab.Columns)
            {
              if (t)
                Console.Write(",");
              else
                t = true;

              Console.Write(row[col]);
            }//end col loop
            Console.Write(Environment.NewLine);

          }//end row loop

        }//end table
      }//edn schema loop

    }//end cmd

  }//end method


  private Dictionary<int, DataTable> getSchema(string schemaTab)
  {
    var con = base.Con;
    Dictionary<int, DataTable> result = new Dictionary<int, DataTable>();

    string selectSchema =
      string.Format(@"SELECT * FROM {0}", schemaTab);

    using (SqlCommand cmd = con.CreateCommand())
    {
      cmd.CommandText = selectSchema;
      using (SqlDataReader reader = cmd.ExecuteReader())
      {
        if (!reader.HasRows)
          return result;

        while (reader.Read())
        {
          var pid = reader.GetInt32(0);
          var xml = reader.GetString(1);
          var dt = (DataTable)fromXML(xml, typeof(DataTable));
          result.Add(pid, dt);
        }
      }//end reader
    }//end cmd
    return result;
  }//end method
  
  
  private object fromXML(string xml, Type t)
  {
    XmlSerializer serializer = new XmlSerializer(t);
    using (MemoryStream mem = new MemoryStream(Encoding.UTF8.GetBytes(xml)))
    using (StreamReader sr = new StreamReader(mem))
    {
      return serializer.Deserialize(sr);
    }//edn stream
  }//end method}


}//end class
カテゴリー: 開発 タグ: パーマリンク