|
|
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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip