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

• Download source - 8.12 KB

Introduction

In this article, I am going to explain the implementation of group by functionality on a data set object by using the LINQ technology.

What Is LINQ

LINQ stands for “Language Integrated Query”. This is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages using a syntax reminiscent of SQL. By using the basic query expressions & patterns, we can query and transform the data from any LINQ enabled data source, like ADO.NET Datasets, XML documents and streams, and .NET collections.

When working with LINQ, the following three steps are required:

1. Create the Data Source; those will be used in LINQ expressions.
2. Create the LINQ expressions.
3. Execute the query expressions to get the result of the query.

Sample Code

In this sample project, I had done the first step that is creating the data source, by using the class file clsDB. In this class, I created the two data tables Customer, Orders and inserted few sample records into them. Constructor of the clsDB class returns a DataSet object which holds these sample data tables.

/// <summary>
/// STEP 1 :  In the page load event, we are initializing the clsDB class object.
/// ClsDB class creates and inserts the data into two data tables Customer 
/// and Orders. By using the MyDataSet public property of clsDB, we can access  
/// these two tables.
/// 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 dataset in memory 
/// to avoid DB connections.  
/// Instead of this, you can write the standard SQL connection 
/// code to get the data and fill the dataset from back end.
/// 
/// </summary>
/// <paramname="sender"></param>
/// <paramname="e"></param>

protected void Page_Load(objectsender, EventArgs e)
{
    lblCaption.Text = "Orders Table <BR> With out using LINQ";
 
    if(!IsPostBack)
    {
        //STEP 1:
        objClsDB = newClsDB();
        Session["DBObject"]= objClsDB;

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

Second Step is Writing the Query Expression

What is a Query Expression

Query expression is a query expressed in query syntax.

A query expression must start with “from” clause and end with “select” or “group” clause.
In this sample project, I have created four different types of query expressions.
One of them is: Display all the records from order table by grouping the customerIds.

/// Simple SQl Query : 
select [Order].* from [Order]                    
group by [Order].["CustomerID"]      

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

Third Step is Executing the Query Expression

The query expression we have created in step 2 contains only query. It does not contain the result of the query. To get the result, we should execute it.

In this example, the query expression variable “groupbyfilter” does not contain the result of the query. In order to get the result, we should execute the query. Here I have executed the query expression variable against the foreach loop and copied the result in a table.

Query variable should always produce sequence of elements when iterated through foreach loop or IEnumerable.movenext().

foreach(var x in groupbyfilter)
      x.CopyToDataTable(dt, LoadOption.OverwriteChanges);

Query Expressions that are Used in this Project

“Group by” Button Functionality

Display all the records in Order table, by grouping the CustomerIds in order table.

protected void btnGroupby_Click(object sender, EventArgs e)
    {
        // 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 into temp table
        foreach(var x in groupbyfilter)
            x.CopyToDataTable(dt, LoadOption.OverwriteChanges);
        
        //Step 3: Temp tables is binded to the DatsGrid. To display the query results.
        DtGrid.DataSource = dt;
        DtGrid.DataBind();
 }

“Group by Having” Button Functionality

It gives the records from Order table that has more than 4 sales orders per customer.

protected void btnGroupbyhaving_Click(object sender, EventArgs e)
    {
        // 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 into temp table
        foreach (var x in groupbyhaving)
           x.CopyToDataTable(dt, LoadOption.OverwriteChanges);

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

“OrderBy “ Button Functionality

Displays all the records in Order table, based on the sorting of BillDate:

protected void btnOrderby_Click(object sender, EventArgs e)
{
    // 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 into temp table 
    foreach (var x in orderByDate)
        dt.Rows.Add(dt.NewRow().ItemArray =x.ItemArray);
 
    // Step 3 : Temp tables is binded to the DataGrid. To display the query results.
    DtGrid.DataSource = dt;
    DtGrid.DataBind();
}

“Purchase Order Details“ Button Functionality

List the customer Name and their Total purchase amount. Here LINQ is applied on multiple tables.

protected void btnPurchaseDet_Click(object sender, EventArgs e)
    {
       // 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 into 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 DataGrid. To display the query results.
        DtGrid.DataSource = resultCollection;
        DtGrid.DataBind();
}

History

• 26^th July, 2008: Initial post