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.Configuration;
using System.Data;
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;
using LINQGroupby;


public partial class _Default : System.Web.UI.Page 
{
    
    ClsDB objClsDB;

    /// <summary>
    /// STEP 1 :  In the page load event, we are initializing the clsDB class object.
    ///           ClsDB class creats and inserts the data in to two data tables Customer and Orders. 
    ///           By using the MyDataSet public property of clsDB, we can access these two table.
    ///           In brief, with the clsdb object I have created a DataSet with two tables.
    ///           
    /// STEP 2:   Bind the DataSet to the Data Grid. 
    /// 
    /// 
    /// NOTE : I have used the clsDB class to create and fill a data set in memory to avoid DB connections. 
    ///        Instead of this you can write the standard sql connection code to get the data and fill the data set from back end.
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Page_Load(object sender, EventArgs e)
    {

        lblCaption.Text = "Orders Table <BR> With out using LINQ";

        if (!IsPostBack)
        {

            //STEP 1:
            objClsDB = new ClsDB();
            Session["DBObject"] = objClsDB;

            //STEP 2:
            DtGrid.DataSource = objClsDB.MyDataSet.Tables["Order"];
            DtGrid.DataBind();
         
        }
        else
        {
            objClsDB = (ClsDB)Session["DBObject"];
        }


    }

    /// <summary>
    /// 
    /// In this function, we are using a LINQ expression to display all the records in order table, by using the 
    /// simple group by condition.
    /// 
    /// Simple SQl Query : select [Order].* from [Order]
    ///                     group by [Order].["CustomerID"]
    ///                    
    /// LINQ Query Expression: 
    /// 
    ///  [QueryExpression] = from [Object] in <IEnumerable>DataSource
    ///                          group [Object] by [group conditioning column]
    ///                              
    /// Example:                          
    /// var groupbyfilter = from dr in objClsDB.MyDataSet.Tables["Order"].AsEnumerable()
    ///                           group dr by dr["CustomerID"];
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnGroupby_Click(object sender, EventArgs e)
    {
        //set Labels text to display the query string on web page.....
        lblCaption.Text = "Display all the records in Order Table, By grouping the Customer IDs.  <BR> LINQ is applied on a Single Table";
        lblSQL.Text  = "Simple SQl Query : <BR> select [Order].* from [Order] <BR> group by [Order].[CustomerID]";
        lblLINQ.Text  = "LINQ Expression : <BR> var groupbyfilter = from dr in objClsDB.MyDataSet.Tables[Order].AsEnumerable() <BR> group dr by dr[CustomerID];";
    

        // Step 1: Create LINQ Query Expression: 
        var groupbyfilter = from dr in objClsDB.MyDataSet.Tables["Order"].AsEnumerable()
                            group dr by dr["CustomerID"] ;


        DataTable dt=objClsDB.MyDataSet.Tables["Order"].Clone();

        // Step 2: Execute the Query and store the results in to temp table, 
        foreach (var x in groupbyfilter)
            x.CopyToDataTable(dt, LoadOption.OverwriteChanges);
  
        // Step 3: Temp tables is binded to the Dat Grid. To display the query results.
        DtGrid.DataSource = dt;
        DtGrid.DataBind();

    }


    /// <summary>
    /// In this function, we are going to write an query expression: List the sales order details 
    /// group by the cutomers , who has the number of sales orders count is >4.
    /// 
    /// Simple SQL Query : select [Order].["CustomerID"] from [[Order]]
    ///                    group by [Order].["CustomerID"]
    ///                    Having Count([Order].["CustomerID"]) >4
    ///                    
    /// LINQ Expression : 
    /// 
    /// [QueryExpression]      = from [Object] in <IEnumerable>DataSource
    ///                          group [Object] by [group conditioning column] in to [ResultObject]
    ///                          where [ResultObject].Count>4
    ///                          Select [ResultObject]
    ///                          
    /// Example :
    /// var groupbyhaving = from dr in objClsDB.MyDataSet.Tables["Order"].AsEnumerable()
    ///                     group dr by dr["CustomerID"] into newdr
    ///                     where newdr.Count() > 4
    ///                     select newdr;
    /// 
    ///   
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnGroupbyhaving_Click(object sender, EventArgs e)
    {

        //set Labels text to display the query string on web page.....
        
        lblCaption.Text = "Details of the Orders from Order table , that has more than 4 sales orders per customer.  <BR> Here LINQ is on a Single Table";
        lblSQL.Text = "Simple SQL Query : <BR> select [Order].[CustomerID] from [[Order]] <BR> group by [Order].[CustomerID] <BR>  Having Count([Order].[CustomerID]) >4 ";
        lblLINQ.Text = "LINQ Expression : <BR> var groupbyhaving = from dr in objClsDB.MyDataSet.Tables[Order].AsEnumerable() <BR> group dr by dr[CustomerID] into newdr <BR> Select [ResultObject]";


        // Step 1: Define the Query Expression 
        var groupbyhaving = from dr in objClsDB.MyDataSet.Tables["Order"].AsEnumerable()
                            group dr by dr["CustomerID"] into newdr
                            where newdr.Count() > 4
                            select newdr;
        
        DataTable dt = objClsDB.MyDataSet.Tables["Order"].Clone();

        // Step 2: Execute the Query and store the results in to temp table, 
        foreach (var x in groupbyhaving)
            x.CopyToDataTable(dt, LoadOption.OverwriteChanges);

        
        // Step 3 : Temp tables is binded to the Dat Grid. To display the query results.
        DtGrid.DataSource = dt;
        DtGrid.DataBind();
    }

    /// <summary>
    /// In this function, we are going to write an query expression: that sorts the order table based on the bill date.
    ///  
    /// Simple SQL Query : Select [Orders].* from [Orders]
    ///                    Order by [Orders].["BillDate"] Ascending
    ///                    
    /// LINQ Expression:
    /// 
    /// [QueryExpression] = from [Object] in <IEnumerable>DataSource
    ///                     Orderby ["ColumnName"] ascending
    ///                     Select [Object]
    ///                     
    /// Example : var orderByDate = from dr in objClsDB.MyDataSet.Tables["Order"].AsEnumerable()
    ///                             orderby dr["BillDate"] ascending
    ///                             select dr;
    /// 
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnOrderby_Click(object sender, EventArgs e)
    {
        //set Labels text to display the query string on web page.....
      
        lblCaption.Text = "Displays all the records in Order table, based on the Sorting of Bill Date. <BR> In this LINQ is applied on a Single Table";
        lblSQL.Text = "Simple SQL Query : <BR> Select [Orders].* from [Orders] <BR> Order by [Orders].[BillDate] Ascending";
        lblLINQ.Text = "LINQ Expression : <BR> var orderByDate = from dr in objClsDB.MyDataSet.Tables[\"Orders\"].AsEnumerable() <BR> orderby dr[\"BillDate\"] ascending <BR> select dr";

        // Step 1: Define the Query Expression 

        var orderByDate = from dr in objClsDB.MyDataSet.Tables["Order"].AsEnumerable()
                            orderby dr["BillDate"] ascending
                            select dr;

        DataTable dt = objClsDB.MyDataSet.Tables["Order"].Clone();

        // Step 2: Execute the Query and store the results in to temp table, 
        foreach (var x in orderByDate)
            dt.Rows.Add(dt.NewRow().ItemArray = x.ItemArray);

        // Step 3 : Temp tables is binded to the Dat Grid. To display the query results.
        DtGrid.DataSource = dt;
        DtGrid.DataBind();
    }



    /// <summary>
    ///   Here we are going to list the Custmoner names and their total purchased amount.  In this function we have written
    ///   a LINQ Expression that works on multiple tables.
    ///  
    ///  SQL Query : Select  [Customer].["CustomerName"],Sum([Orderstable].["BillAmount"]) 
    ///              from [Customer],[Orderstable]
    ///               where [Customer].["CustomerID"]=[Order].["CustomerID"]
    ///               group by [Order].["OrderID"]
    ///               
    ///  LINQ Example : 
    ///  
    ///  var Query = from dr in objClsDB.MyDataSet.Tables["Order"].AsEnumerable()
    ///              group dr by dr["CustomerID"] into newdr
    ///              select new
    ///                   {
    ///                            GrandTotal= 
    ///                               newdr.Sum( tempRec => Convert.ToDouble(tempRec["BillAmount"])) ,
    ///                               CustomerName =
    ///                                 from dr2 in objClsDB.MyDataSet.Tables["Customers"].AsEnumerable()
    ///                                 where Convert.ToInt32(dr2["CustomerID"].ToString()) == Convert.ToInt32(newdr.Key.ToString()) 
    ///                                 select dr2["CustomerName"]
    ///                    };
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>

    protected void btnPurchaseDet_Click(object sender, EventArgs e)
    {

        //set Labels text to display the query string on web page.....

        lblCaption.Text = "List the customer Name and their Total purcahse amount.  <BR>  LINQ on a Multiple Tables";
        lblSQL.Text = "SQL Query :<BR> Select  [Customer].[CustomerName],Sum([Orderstable].[BillAmount]) <BR> from [Customer],[Orderstable] <BR> where [Customer].[CustomerID]=[Order].[CustomerID] <BR>     group by [Order].[OrderID]";
        lblLINQ.Text = "LINQ Expression : <BR> from dr in objClsDB.MyDataSet.Tables[Orders].AsEnumerable()" +
                        "<BR> group dr by dr[CID] into newdr " + "<BR>" +
                            "select new { GrandTotal=  newdr.Sum( tempRec => Convert.ToDouble(tempRec[BillAmount])) , " + "<BR>" +
                            "CustomerName = from dr2 in objClsDB.MyDataSet.Tables[Customers].AsEnumerable() "+"<BR>" +
                            "where Convert.ToInt32(dr2[CID].ToString()) == Convert.ToInt32(newdr.Key.ToString()) " + "<BR>" +
                            "select dr2[CustomerName]";

        // Step 1: Define the Query Expression 

        var groupbyhaving = from dr in objClsDB.MyDataSet.Tables["Order"].AsEnumerable()
                            group dr by dr["CustomerID"] into newdr
                            select new
                            {
                                GrandTotal= 
                                     newdr.Sum( tempRec => Convert.ToDouble(tempRec["BillAmount"])) ,
                                
                                CustomerName =
                                    from dr2 in objClsDB.MyDataSet.Tables["Customer"].AsEnumerable()
                                     where Convert.ToInt32(dr2["CustomerID"]) == Convert.ToInt32(newdr.Key) 
             
                                    select dr2["CustomerName"]
                            };


        List<result> resultCollection = new List<result>();

        // Step 2: Execute the Query and store the results in to List Collection 

        foreach (var x in groupbyhaving)
        {
            resultCollection.Add(new result { CustomerName = x.CustomerName.First().ToString(),Amount = x.GrandTotal });

        }


        // Step 3 : Temp tables is binded to the Dat Grid. To display the query results.
        DtGrid.DataSource = resultCollection;
        DtGrid.DataBind();
    }
}


// List structure of result ..to hold the vallues for query execution...
struct result
{
    public string CustomerName{ get; set; }
    public double Amount{get;set; }
}

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
Web02 | 2.8.141223.1 | Last Updated 26 Jul 2008
Article Copyright 2008 by Sunil Yenugudati
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid