Wednesday 22 January 2014

HOw to implement LINQ in C#.NET to Perform Aggregate Function as in SQL Server

First Add the namespace - System.Collections.Generic and System.Linq

Now we get data into datatable : 

DataTable dtt = new DataTable();
string _cquery = "Select * from table1";
dtt = du.getdatatable(_cquery);

Now apply group by clause on datatable dtt using LINQ :

var query = from row in dtt.AsEnumerable()
                                group row by row.Field<string>("col1") into grp
                                select new { AfterDistinct = grp.Key};
                    DataTable ddldt = new DataTable();
                    ddldt.Columns.Add("col1");
                    foreach (var row in query)
                    {
                        int i = 0;
                        DataRow dr = ddldt.NewRow();
                        dr["col1"] = row.AfterDistinct.ToString();
                        ddldt.Rows.Add(dr);
                        i++;
                    }
                    ddl1.Items.Clear();
                    ddl1.DataSource = ddldt;
                    ddl1.DataTextField = "col1";
                    ddl1.DataValueField = "col1";
                    ddl1.DataBind();
                    ddl1.Items.Insert(0, new ListItem("--Select--", "0"));


in this example we get data with group by clause from DataTable and bind this data to DropdownList ddl1.

No comments:

Post a Comment