[C# .NET] 小ネタ２

■DataTableで親子孫関係のデータを取得する方法

参考：
http://msdn.microsoft.com/ja-jp/library/bb386943(v=vs.110).aspx
http://teruc.dnsalias.net/blog/2009/11/02/22
http://www.atmarkit.co.jp/fdotnet/extremecs/extremecs_15/extremecs_15_01.html


★問題点
　親子までしか左外部結合の場合、２テーブルまでしかできない。
　親子孫の３テーブルができない。

　親ー子1
　親ー子2

　ならいくつかサンプルがあるが、親子孫は無理なのか？

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create DataSet
            DataSet ds = CreateDataSet();

            // Create Data
            SetData(ref ds);

            // Out Put Console
            //OutPutData(ds);

            // query
            DataTable ptbl = ds.Tables["parent"];
            DataTable ctbl = ds.Tables["child"];
            DataTable gctbl = ds.Tables["grandchild"];

            byte[] p1 = System.Text.Encoding.Unicode.GetBytes("1");

            // join

            // 3テーブルの内部結合
            var q1 = from p in ptbl.AsEnumerable()
                     where p.Field<byte[]>("id").SequenceEqual(p1)
                     join c in ctbl.AsEnumerable()
                     on p.Field<byte[]>("id") equals c.Field<byte[]>("pid")
                     join gc in gctbl.AsEnumerable()
                     on c.Field<byte[]>("id") equals gc.Field<byte[]>("pid")
                     select new
                     {
                         pid = p["id"],
                         pvalue = p["value1"],
                         cid = c["id"],
                         cvalue1 = c["value3"],
                         gcid = gc["id"],
                         gcvalue1 = gc["value5"]

                     };

            foreach (var test in (from q in q1 where q.gcvalue1.ToString() == "子２０孫２１" select q))
            {
                Console.WriteLine(test.pvalue);
                Console.WriteLine(test.cvalue1);
                Console.WriteLine(test.gcvalue1);
            }

            // 外部結合(子孫(子のデータが孫にない場合あり)2テーブルまでしかできない？)
            Console.WriteLine();

            var q2 = from c in ctbl.AsEnumerable()
                     join gc in gctbl.AsEnumerable()
                     on c.Field<byte[]>("id") equals gc.Field<byte[]>("pid")
                     into groupData
                     from data in groupData.DefaultIfEmpty()
                     select new
                     {
                         cid = c["id"],
                         cvalue = c["value3"],
                         // 孫がない場合、初期値を設定する
                         gcid = (data == null ? new byte[] { 0 } : data["id"]),
                         gcvalue1 = (data == null ? "no grand child" : data["value5"]),
                     };

            foreach (var test in q2)
            {
                Console.WriteLine(test.cvalue);
                Console.WriteLine(test.gcvalue1);
            }


            // グループ結合(2テーブルまでしかできない？)
            // 親１
            // 子１＿１
            // 子１＿２
            // 親２・・・
            Console.WriteLine();

            var q3 = from p in ptbl.AsEnumerable()
                     join c in ctbl.AsEnumerable()
                     on p.Field<byte[]>("id") equals c.Field<byte[]>("pid")
                         /*join gc in gctbl.AsEnumerable()
                         on c.Field<byte[]>("id") equals gc.Field<byte[]>("pid")*/
                     into g
                     select new
                     {
                         pid = p["id"],
                         pvalue = p["value1"],
                         cinfo = g,
                     };

            foreach (var test in q3)
            {
                Console.WriteLine(test.pvalue);
                foreach (var test2 in test.cinfo)
                {
                    Console.WriteLine(test2.Field<string>("value3"));
                }
            }


            Console.Read();

        }

        // DataSet Create
        static DataSet CreateDataSet()
        {
            var ds = new DataSet();

            // parent table
            var ptbl = new DataTable();
            {
                var pcolumn = new DataColumn[]{
                              new DataColumn("id", typeof(byte[]))
                             ,new DataColumn("pid", typeof(byte[]))
                             ,new DataColumn("value1", typeof(string))
                             ,new DataColumn("value2", typeof(int))
                             };

                ptbl.Columns.AddRange(pcolumn);
                ptbl.TableName = "parent";
            }

            // child table
            var ctbl = new DataTable();
            {
                var ccolumn = new DataColumn[]{
                              new DataColumn("id", typeof(byte[]))
                             ,new DataColumn("pid", typeof(byte[]))
                             ,new DataColumn("value3", typeof(string))
                             ,new DataColumn("value4", typeof(int))
                             };

                ctbl.Columns.AddRange(ccolumn);
                ctbl.TableName = "child";
            }

            // grandchild table
            var gctbl = new DataTable();
            {
                var gccolumn = new DataColumn[]{
                              new DataColumn("id", typeof(byte[]))
                             ,new DataColumn("pid", typeof(byte[]))
                             ,new DataColumn("value5", typeof(string))
                             ,new DataColumn("value6", typeof(int))
                             };

                gctbl.Columns.AddRange(gccolumn);
                gctbl.TableName = "grandchild";
            }

            // Create DataSet parent child grandchild
            ds.Tables.Add(ptbl);
            ds.Tables.Add(ctbl);
            ds.Tables.Add(gctbl);

            return ds;
        }

        // Data Create
        static void SetData(ref DataSet ds)
        {
            // parent id have two child
            byte[] p1 = System.Text.Encoding.Unicode.GetBytes("1");
            byte[] p2 = System.Text.Encoding.Unicode.GetBytes("2");
            byte[] p3 = System.Text.Encoding.Unicode.GetBytes("3");
            // have only child
            byte[] p4 = System.Text.Encoding.Unicode.GetBytes("4");

            // child id have one child
            byte[] c1 = System.Text.Encoding.Unicode.GetBytes("10");
            byte[] c2 = System.Text.Encoding.Unicode.GetBytes("20");
            byte[] c3 = System.Text.Encoding.Unicode.GetBytes("30");
            byte[] c4 = System.Text.Encoding.Unicode.GetBytes("40");
            byte[] c5 = System.Text.Encoding.Unicode.GetBytes("50");
            byte[] c6 = System.Text.Encoding.Unicode.GetBytes("60");
            byte[] c7 = System.Text.Encoding.Unicode.GetBytes("70");

            // grand child id
            byte[] gc1 = System.Text.Encoding.Unicode.GetBytes("11");
            byte[] gc2 = System.Text.Encoding.Unicode.GetBytes("21");
            byte[] gc3 = System.Text.Encoding.Unicode.GetBytes("31");
            byte[] gc4 = System.Text.Encoding.Unicode.GetBytes("41");
            byte[] gc5 = System.Text.Encoding.Unicode.GetBytes("51");
            byte[] gc6 = System.Text.Encoding.Unicode.GetBytes("61");

            DataTable ptbl = ds.Tables["parent"];
            DataTable ctbl = ds.Tables["child"];
            DataTable gctbl = ds.Tables["grandchild"];

            ptbl.LoadDataRow(new object[] { p1, DBNull.Value, "親１", 1 }, true);
            ptbl.LoadDataRow(new object[] { p2, DBNull.Value, "親２", 2 }, true);
            ptbl.LoadDataRow(new object[] { p3, DBNull.Value, "親３", 3 }, true);
            ptbl.LoadDataRow(new object[] { p4, DBNull.Value, "親４", 4 }, true);

            ctbl.LoadDataRow(new object[] { c1, p1, "親１子１０", 10 }, true);
            ctbl.LoadDataRow(new object[] { c2, p1, "親１子２０", 20 }, true);
            ctbl.LoadDataRow(new object[] { c3, p2, "親２子３０", 30 }, true);
            ctbl.LoadDataRow(new object[] { c4, p2, "親２子４０", 40 }, true);
            ctbl.LoadDataRow(new object[] { c5, p3, "親３子５０", 50 }, true);
            ctbl.LoadDataRow(new object[] { c6, p3, "親３子６０", 60 }, true);
            ctbl.LoadDataRow(new object[] { c7, p4, "親４子７０", 70 }, true);

            gctbl.LoadDataRow(new object[] { gc1, c1, "子１０孫１１", 11 }, true);
            gctbl.LoadDataRow(new object[] { gc2, c2, "子２０孫２１", 21 }, true);
            gctbl.LoadDataRow(new object[] { gc3, c3, "子３０孫３１", 31 }, true);
            gctbl.LoadDataRow(new object[] { gc4, c4, "子４０孫４１", 41 }, true);
            gctbl.LoadDataRow(new object[] { gc5, c5, "子５０孫５１", 51 }, true);
            gctbl.LoadDataRow(new object[] { gc6, c6, "子６０孫６１", 61 }, true);

        }

        // Out Console
        static void OutPutData(DataSet ds)
        {
            foreach (DataTable dt in ds.Tables)
            {
                Console.WriteLine("***");

                Console.WriteLine(dt.TableName);
                Console.WriteLine("");

                foreach (DataColumn col in dt.Columns)
                {
                    Console.Write(col.ColumnName + " ");
                }
                Console.WriteLine("");
                foreach (DataRow dr in dt.Rows)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (dr[col.ColumnName].GetType() == typeof(byte[]))
                        {
                            // change unicode
                            byte[] data = (byte[])dr[col.ColumnName];
                            string strFromByte = Encoding.Unicode.GetString(data);
                            Console.Write(strFromByte + " ");
                        }
                        else
                        {
                            Console.Write(dr[col.ColumnName] + " ");
                        }
                    }
                    Console.WriteLine("");

                }
                Console.WriteLine("");
            }
        }
    }
}