LinqToDataSet

DataTable(customer_dt) 만들기

DataTable customer_dt = new DataTable("customer");

// 컬럼 4개 정의
DataColumn col1 = new DataColumn();
DataColumn col2 = new DataColumn();
DataColumn col3 = new DataColumn();
DataColumn col4 = new DataColumn();

col1.DataType = System.Type.GetType("System.Int16");
col1.ReadOnly = true;
col1.AllowDBNull = false;
col1.Unique = true;
col1.ColumnName = "ID";
col1.AutoIncrement = true;
col1.AutoIncrementSeed = 1;

col2.DataType = System.Type.GetType("System.String");
col2.ColumnName = "Name";

col3.DataType = System.Type.GetType("System.String");
col3.ColumnName = "Addr";
col3.DefaultValue = "서울";

col4.DataType = System.Type.GetType("System.String");
col4.ColumnName = "Tel";

customer_dt.Columns.Add(col1);
customer_dt.Columns.Add(col2);
customer_dt.Columns.Add(col3);
customer_dt.Columns.Add(col4);

 

DataTable에 데이터 생성하기

DataRow row1 = customer_dt.NewRow();
row1[1] = "가길동";
row1[2] = "수원";
row1[3] = "111-2222";
customer_dt.Rows.Add(row1);

DataRow row2 = customer_dt.NewRow();
row2[1] = "나길동";
row2[2] = "울산";
row2[3] = "222-2222";
customer_dt.Rows.Add(row2);

DataRow row3 = customer_dt.NewRow();
row3[1] = "다길동";
row3[2] = "부산";
row3[3] = "333-2222";
customer_dt.Rows.Add(row3);

 

DataTable 데이터 출력

foreach(DataColumn header in customer_dt.Columns)
{
    Console.Write("{0,-6}\t", header.ColumnName);
}

Console.WriteLine("\n-------------------------------");

foreach(DataRow rows in customer_dt.Rows)
{
    foreach(DataColumn cols in customer_dt.Columns)
    {
        Console.Write("{0,-4}\t", rows[cols.ColumnName]);
    }
    Console.Write("\n");
}

결과

 

DataTable(customer_dt)을 DataSet에 삽입 후 XML로 출력

DataSet set = new DataSet("customer_sales");
set.Tables.Add(customer_dt);

// DataSet의 내용을 XML로 출력
Console.WriteLine(set.GetXml());

결과

 

쿼리식 기반의 LinqToDataSet으로 전체고객 출력

IEnumerable query = from customer in customer_dt.AsEnumerable()
                    select customer;

Console.WriteLine("Customers : ");
foreach(DataRow r in query)
{
    Console.WriteLine(r.Field<string>("Name") + "::" + r.Field<string>("Addr") 
    + "::" + r.Field<string>("Tel"));
}

결과

 

쿼리식 기반의 LinqToDataSet으로 고객 이름 출력

 IEnumerable query1 = from customer in customer_dt.AsEnumerable()
                     select customer.Field<string>("Name");

Console.WriteLine("Customer Names : ");
foreach (string customerName in query1)
{
    Console.WriteLine(customerName);
}

결과

 

메소드 기반의 LinqToDataSet으로 전체 고객 출력

var query2 = customer_dt.AsEnumerable().
    Select(customer => new
    {
        Name = customer.Field<string>("Name"),
        Addr = customer.Field<string>("Addr"),
        Tel = customer.Field<string>("Tel")
    });


Console.WriteLine("Customers : ");
foreach (var customerInfo in query2)
{
    Console.WriteLine("Name: {0}, Addr: {1}, Tel: {2}",
        customerInfo.Name, customerInfo.Addr, customerInfo.Tel);
}

결과

 

DataTable(sales_dt)생성 후 DataSet에 삽입 - customer_dt와 sales_dt를 내부조인(inner join)과 외부 조인(outer join) 후 데이터 출력

DataTable sales_dt = new DataTable("sales");

DataColumn scol1 = new DataColumn();
DataColumn scol2 = new DataColumn();

scol1.DataType = System.Type.GetType("System.String");
scol1.ColumnName = "Name";

scol2.DataType = System.Type.GetType("System.String");
scol2.ColumnName = "Goods";

sales_dt.Columns.Add(scol1);
sales_dt.Columns.Add(scol2);

DataRow srow1 = sales_dt.NewRow();
srow1[0] = "가길동";
srow1[1] = "수박";
sales_dt.Rows.Add(srow1);

DataRow srow2 = sales_dt.NewRow();
srow2[0] = "나길동";
srow2[1] = "참외";
sales_dt.Rows.Add(srow2);

set.Tables.Add(sales_dt);

// 내부조인 (using System.Linq)
Console.WriteLine("내부 조인 ----------------------------------");
var query3 = from customer in customer_dt.AsEnumerable()
             join sales in sales_dt.AsEnumerable()
             on customer.Field<string>("Name")
                    equals sales.Field<string>("Name")
             select new
             {
                 Name = customer.Field<string>("Name"),
                 Addr = customer.Field<string>("Addr"),
                 Goods = sales.Field<string>("Goods")
             };


foreach(var customerSale in query3)
{
    Console.WriteLine("Name: {0}, Addr: {1}, Goods: {2}",
       customerSale.Name, customerSale.Addr, customerSale.Goods);
}

Console.WriteLine("외부 조인----------------------------------");
var query4 = from customer in customer_dt.AsEnumerable()
             join sales in sales_dt.AsEnumerable()
             on customer.Field<string>("Name")
                    equals sales.Field<string>("Name") into tmp
             from sales in tmp.DefaultIfEmpty()
             select new
             {
                 Name = customer.Field<string>("Name"),
                 Addr = customer.Field<string>("Addr"),
                 Goods = (sales == null) ? "상품없음" : sales.Field<string>("Goods")
             };


foreach (var customerSale in query4)
{
    Console.WriteLine("Name: {0}, Addr: {1}, Goods: {2}",
       customerSale.Name, customerSale.Addr, customerSale.Goods);
}

결과