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

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;

using System.Data;
using System.Data.SqlClient;

namespace LINQ2DATASET
{
    public class Program
    {
        public static void Main(string[] args)
        {
            DataSet northwind = new DataSet("NorthwindDS");

            SqlDataAdapter adapterCustomer = 
                new SqlDataAdapter("select * from Customers;select * from Orders",
                    @"server=(local)\sqlexpress;database=northwind;integrated security=sspi");
            
            adapterCustomer.TableMappings.Add("Table", "Customers");
            adapterCustomer.TableMappings.Add("Table1", "Orders");

            adapterCustomer.Fill(northwind);

            DataTable customer = northwind.Tables["Customers"];
            DataTable orders = northwind.Tables["Orders"];

            #region Einfache Abfrage von Dataset
            var query1 =
                from c in customer.AsEnumerable()
                where c.Field<string>("Country") == "Germany"
                orderby c.Field<string>("CustomerID")
                select c;

            foreach (var item in query1)
            {
                Console.WriteLine(item.Field<string>("CustomerID") + " " +
                    item.Field<string>("CompanyName"));
            }
            #endregion

            Console.WriteLine();

            #region komplexere Abfrage von Dataset
            var query2 =
                from c in customer.AsEnumerable()
                join o in orders.AsEnumerable() on c.Field<string>("CustomerID") equals
                    o.Field<string>("CustomerID") into customerOrders
                where c.Field<string>("CustomerID") == "ALFKI"
                select new { CustomerID = c.Field<string>("CustomerID"), customerOrders};

                foreach (var item in query2)
                {
                    Console.WriteLine(item.CustomerID);

                    foreach (var item2 in item.customerOrders)
                    {
                        Console.WriteLine(item2.Field<int>("OrderID"));
                    }
                }
            #endregion

            Console.ReadLine();
        }
    }
}
