Implementing Simple SQL Group by Functionality with LINQ Technology on a DataSet

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Reflection;
using System.Collections.Generic;
namespace LINQGroupby
{

    /// <summary>
    /// Summary description for ClsDB.
    /// This class is used to create two tables: 
    /// 1. Customer Tbale
    /// 2. Order Table.
    /// These two tables are placed in the data set. 
    /// </summary>
    public class ClsDB
    {

         public DataSet MyDataSet { get; set; }

         
         DataTable dtorder;
         DataTable dtCustomer;


         #region (Constructor)
         //Constructor ..which does most of the work for us..

        public ClsDB()
        {
            //create the customer tabel, order table
            CreateCustomerTable();
            CreateOrderTable();

            //place the two tables in to the data set.
            MyDataSet = new DataSet();
            MyDataSet.Tables.Add(dtCustomer);
            MyDataSet.Tables.Add(dtorder);
        }


        #endregion (Constructor)

        #region (Customer Table)

        // This function creats a customer table and insert records in to it.
        private void CreateCustomerTable()
        {
            try
            {
                //creat the table by using the properites of Customer object.
                dtCustomer = CreateTable((new Customer()).GetType().GetProperties());
                //List colection of the records
                List<Customer> CustomerCollection = new List<Customer>(new Customer[] {
                                new Customer{CustomerID=121,CustomerName="Bob",ZipCode="SL30EX" },
                                new Customer{CustomerID=159,CustomerName="James",ZipCode="SR48SA" },
                                new Customer{CustomerID=189,CustomerName="Alex",ZipCode="SE98ZE" },
                                new Customer{CustomerID=780,CustomerName="Lennox",ZipCode="SW82QS" },
                                new Customer{CustomerID=230,CustomerName="Robert",ZipCode="SE78NA" }});
                
                //Insert the record in to the table by using object array[] 
                DataRow dr;
                foreach (Customer objCustomer in CustomerCollection)
                {
                    dr = dtCustomer.NewRow();
                    dr.ItemArray = ConvertToObjectArray(objCustomer);
                    dtCustomer.Rows.Add(dr);
                }

                dtCustomer.TableName = "Customer";
             }
            catch (Exception ex)
            {
                throw new Exception(" Exception occoured while creating the customer data table :" + ex.Message);
            }


        }


        #endregion (Customer Table)


        #region (Order Table)
        // This function creats a Order table and insert records in to it.
        private void CreateOrderTable()
        {
            try
            {
                dtorder = CreateTable((new Order()).GetType().GetProperties());
                List<Order> OrderCollection = new List<Order>(new Order[] {
                                new Order{ OrderID=520, BillAmount=125.20 ,BillDate =new DateTime(2008,5,12),CustomerID=121},
                                new Order{ OrderID=530, BillAmount=1535.70 ,BillDate =new DateTime(2008,5,21),CustomerID=230},
                                new Order{ OrderID=540, BillAmount=50.89 ,BillDate =new DateTime(2008,5,15),CustomerID=159},
                                new Order{ OrderID=550, BillAmount=535.95 ,BillDate =new DateTime(2008,5,16),CustomerID=230},
                                new Order{ OrderID=560, BillAmount=356.70 ,BillDate =new DateTime(2008,5,11),CustomerID=121},
                                new Order{ OrderID=570, BillAmount=3586.00 ,BillDate =new DateTime(2008,5,10),CustomerID=230},
                                new Order{ OrderID=580, BillAmount=595.70 ,BillDate =new DateTime(2008,5,15),CustomerID=230},
                                new Order{ OrderID=590, BillAmount=135.70 ,BillDate =new DateTime(2008,5,16),CustomerID=230},
                                new Order{ OrderID=630, BillAmount=578.70 ,BillDate =new DateTime(2008,5,19),CustomerID=121},
                                new Order{ OrderID=620, BillAmount=25.20 ,BillDate =new DateTime(2008,5,21),CustomerID=121},
                                new Order{ OrderID=730, BillAmount=5.70 ,BillDate =new DateTime(2008,5,21),CustomerID=230},
                                new Order{ OrderID=830, BillAmount=5892.70 ,BillDate =new DateTime(2008,5,25),CustomerID=189},
                                new Order{ OrderID=930, BillAmount=157.70 ,BillDate =new DateTime(2008,5,27),CustomerID=159},
                                new Order{ OrderID=740, BillAmount=159.70 ,BillDate =new DateTime(2008,5,28),CustomerID=780},
                                new Order{ OrderID=840, BillAmount=155.70 ,BillDate =new DateTime(2008,5,29),CustomerID=121},
                                new Order{ OrderID=940, BillAmount=159.70 ,BillDate =new DateTime(2008,5,29),CustomerID=230},
                                new Order{ OrderID=850, BillAmount=1035.70 ,BillDate =new DateTime(2008,5,21),CustomerID=189},
                                new Order{ OrderID=920, BillAmount=1525.70 ,BillDate =new DateTime(2008,5,21),CustomerID=230}});

              
                DataRow dr;
                foreach (Order objorder in OrderCollection)
                {
                    dr = dtorder.NewRow();
                    dr.ItemArray = ConvertToObjectArray(objorder);
                    dtorder.Rows.Add(dr);
                }

                dtorder.TableName = "Order";
 
            }
            catch (Exception ex)
            {
                throw new Exception(" Exception occoured while creating the Order data table :" + ex.Message);
            }


        }

        #endregion (Customer Table)


        #region (Common Functions)

        //Create the Data Table by using the Object properties.
        private DataTable CreateTable(PropertyInfo[] targetProperties)
        {
            try
            {
                DataTable dtTarget = new DataTable();
                foreach (PropertyInfo targetParameter in targetProperties)
                    dtTarget.Columns.Add(targetParameter.Name, targetParameter.PropertyType);
                return dtTarget;
            }
            catch (Exception ex)
            {
                throw new Exception(" Exception occoured while creating the table :" + ex.Message);
            }
        }

        //Converts the class object in to the object array
        private object[] ConvertToObjectArray(object targetObject)
        {
            List<object> objectCollection = new List<object>();

            foreach (PropertyInfo p in targetObject.GetType().GetProperties() )
            {
                objectCollection.Add(p.GetValue(targetObject,null));

            }
            return objectCollection.ToArray();
        }


        #endregion (Common Functions)

    }

    #region (Customer Table Structure)
    // Customer Table Structure

    struct Customer
    {
        public int CustomerID { get; set; }
        public string CustomerName { get; set; }
        public string ZipCode { get; set; }
    }

    #endregion (Customer Table Structure)


    #region (Order Table Structure)
    // Order Table Structure

    struct Order
    {
        public int OrderID {get; set;}
        public double BillAmount{get;set;}
        public DateTime BillDate{get;set;}
        public int CustomerID{get;set;}

    }

    #endregion (Order Table Structure)

}