Click here to Skip to main content
12,955,959 members (65,628 online)
Click here to Skip to main content


15 bookmarked
Posted 26 Jul 2008

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
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

            //place the two tables in to the data set.
            MyDataSet = new DataSet();

        #endregion (Constructor)

        #region (Customer Table)

        // This function creats a customer table and insert records in to it.
        private void CreateCustomerTable()
                //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.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()
                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.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)
                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() )

            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.


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


About the Author

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

You may also be interested in...

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