mugyu
9/16/2012 - 2:40 AM

C#のDataSet, DataTable関係メモ

C#のDataSet, DataTable関係メモ

using System;
using System.Data;
using System.Linq;

class Program
{
  static void Main()
  {
    // dataset 作成
    var db = new DataSet();
    // datasetにtableを2つ作成
    db.Tables.Add("person table");
    db.Tables.Add("address table");
    // datasetに登録しているtable名列挙
    foreach (DataTable t in db.Tables)
    {
      Console.WriteLine("table name: {0}", t.ToString());
    }

    // table作成
    var table = db.Tables["person table"];
    // tableのcolumn設定
    table.Columns.Add("id", typeof(int));
    table.Columns.Add("name", typeof(string));
    table.Columns.Add("age", typeof(int));
    // data投入
    table.Rows.Add(1, "hoge", 17);
    table.Rows.Add(2, "piyo", 23);
    table.Rows.Add(3, "foo",  19);
    table.Rows.Add(4, "bar",  31);
    table.Rows.Add(5, "hoge",  6);
    table.Rows.Add(6, "foo",  17);

    //--------------------------------------------------------------------
    Console.WriteLine("-*- normal -*-");
    //--------------------------------------------------------------------
    // tableのカラム名を出力
    Console.Write("Columns:\t");
    foreach (DataColumn column in table.Columns)
    {
      Console.Write(column + "\t");
    }
    Console.Write("\n");

    // tableのカラムの値を出力
    foreach (DataRow row in table.Rows)
    {
      Console.WriteLine("name:{0},\tage:{1}",
                        row["name"],
                        row["age"]);
    }

    // カラムのカラム名と値を合わせて出力
    foreach (DataRow row in table.Rows)
    {
      foreach (DataColumn column in table.Columns)
      {
        Console.Write("{0}: '{1}'\t", column, row[column]);
      }
      Console.WriteLine("");
    }

    // データ集約の為のviewを作成
    DataView view = new DataView(table);
    view.Sort = "age";
    // 第2引数がtrueは第3以降引数のカラムで集約する
    DataTable uniqTable = view.ToTable("UniqTable", true, "name");
    uniqTable.Columns.Add("sumAge");
    uniqTable.Columns.Add("count");

    //--------------------------------------------------------------------
    Console.WriteLine("-*- uniqName -*-");
    //--------------------------------------------------------------------
    foreach (DataRow row in uniqTable.Rows)
    {
      row["sumAge"]
          = table.Compute("SUM(age)", "name = '" + row["name"] + "'");
      row["count"]
          = table.Compute("COUNT(age)", "name = '" + row["name"] + "'");
      Console.WriteLine("name:{0},\tage:{1},\tcount:{2}",
          row["name"],
          row["sumAge"],
          row["count"]);
    }

    Console.WriteLine("");
    //--------------------------------------------------------------------
    Console.WriteLine("-*- Linq -*-");
    Console.WriteLine("-*- query -*-");
    //--------------------------------------------------------------------
    var query3 = from x in table.AsEnumerable()
        where x["name"].ToString() == "hoge"
        select x;
    foreach (var row in query3)
    {
      Console.WriteLine("name:{0},\tage:{1}", row["name"], row["age"]);
    }

    var query4 = from x in table.AsEnumerable()
        where x["name"].ToString() == "foo"
        select new {Name = x["name"], Age = x["age"]};
    foreach (var row in query4)
    {
      Console.WriteLine("name:{0},\tage:{1}", row.Name, row.Age);
    }

    //--------------------------------------------------------------------
    Console.WriteLine("-*- method & lambda -*-");
    //--------------------------------------------------------------------
    var query1 = table.AsEnumerable()
        .Where(x => x["name"].ToString() == "hoge")
        .Select(x => x);
    foreach (var row in query1)
    {
      Console.WriteLine("name:{0},\tage:{1}", row["name"], row["age"]);
    }

    var query2 = table.AsEnumerable()
        .Where(x => x["name"].ToString() == "foo")
        .Select(x => new {Name = x["name"], Age = x["age"]});
    foreach (var row in query2)
    {
      Console.WriteLine("name:{0},\tage:{1}", row.Name, row.Age);
    }

    var query5 = table.Rows.Cast<DataRow>()
        .Where(x => x["name"].ToString() == "hoge")
        .Select(x => x);
    foreach (var row in query5)
    {
      Console.WriteLine("name:{0},\tage:{1}", row["name"], row["age"]);
    }

    //--------------------------------------------------------------------
    Console.WriteLine("-*- group by -*-");
    //--------------------------------------------------------------------
    var queryUniq = table.AsEnumerable()
        .GroupBy(x => new { Name = x["name"] })
        .Select(g => new { Name = g.Key.Name as string,
                           Count = g.Count(),
                           SumAge = g.Sum(x => (int)x["age"])});
    foreach (var row in queryUniq)
    {
      Console.WriteLine("key is {0},\tcount: {1},\tsum: {2}",
              row.Name, row.Count, row.SumAge);
    }

    //--------------------------------------------------------------------
    Console.WriteLine("-*- DataView.RowFilter -*-");
    //--------------------------------------------------------------------
    DataView view2 = table.DefaultView;
    view2.RowFilter = "name = 'hoge'";
    DataView view2 = table.DefaultView;
    view2.RowFilter = "name = 'hoge'";
    foreach(DataRowView row in view2) // var だと object になるのでダメ
    {
        Console.WriteLine("name = {0}", row["name"]);
    }
  }
}