Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

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

, 26 Jul 2008 CPOL
A simple SQL group by functionality is implemented with LINQ
GroupbyLINQ.zip
GroupbyLINQ
App_Code
App_Data
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)

}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sunil Yenugudati
Software Developer (Senior) London , U.K.
United Kingdom United Kingdom
No Biography provided

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411022.1 | Last Updated 26 Jul 2008
Article Copyright 2008 by Sunil Yenugudati
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid