LINQ to DataSet的DataTable操作

LINQ to DataSet需要使用System.Core.dll、System.Data.dll和System.Data.DataSetExtensions.dll，在项目中添加引用System.Data和System.Data.DataSetExtensions。

1. DataTable读取列表

DataSet ds = new DataSet(); // 省略ds的Fill代码 DataTable products = ds.Tables["Product"]; IEnumerable<DataRow> rows = from p in products.AsEnumerable() select p; foreach (DataRow row in rows) { Console.WriteLine(row.Field<string>("ProductName")); } DataSet ds = new DataSet(); // 省略ds的Fill代码 DataTable products = ds.Tables["Product"]; var rows = products.AsEnumerable() .Select(p => new { ProductID = p.Field<int>("ProductID"), ProductName = p.Field<string>("ProductName"), UnitPrice = p.Field<decimal>("UnitPrice") }); foreach (var row in rows) { Console.WriteLine(row.ProductName); } var products = ds.Tables["Product"].AsEnumerable(); var query = from p in products select p.Field<string>("ProductName");

2. DataTable查询

var rows = products.AsEnumerable() .Where(p => p.Field<decimal>("UnitPrice") > 10m) .Select(p => new { ProductID = p.Field<int>("ProductID"), ProductName = p.Field<string>("ProductName"), UnitPrice = p.Field<decimal>("UnitPrice") });

3. DataTable数据排序

var rows = products.AsEnumerable() .Where(p => p.Field<decimal>("UnitPrice") > 10m) .OrderBy(p => p.Field<int>("SortOrder")) .Select(p => new { ProductID = p.Field<int>("ProductID"), ProductName = p.Field<string>("ProductName"), UnitPrice = p.Field<decimal>("UnitPrice") }); var expr = from p in products.AsEnumerable() orderby p.Field<int>("SortOrder") select p; IEnumerable<DataRow> rows = expr.ToArray(); foreach (var row in rows) { Console.WriteLine(row.Field<string>("ProductName")); } var expr = from p in ds.Tables["Product"].AsEnumerable() orderby p.Field<int>("SortOrder"), p.Field<string>("ProductName") descending select p;

4. 多个DataTable查询

var query = from p in ds.Tables["Product"].AsEnumerable() from c in ds.Tables["Category"].AsEnumerable() where p.Field<int>("CategoryID") == c.Field<int>("CategoryID") && p.Field<decimal>("UnitPrice") > 10m select new { ProductID = p.Field<int>("ProductID"), ProductName = p.Field<string>("ProductName"), CategoryName = c.Field<string>("CategoryName") };

5. DataTable分组

var query = from p in ds.Tables["Product"].AsEnumerable() group p by p.Field<int>("CategoryID") into g select new { CategoryID = g.Key, Products = g }; foreach (var item in query) { Console.WriteLine(item.CategoryID); foreach (var p in item.Products) { Console.WriteLine(p.Field<string>("ProductName")); } }

查询Product中每个CategoryID的数目：

var expr = from p in ds.Tables["Product"].AsEnumerable() group p by p.Field<int>("CategoryID") into g select new { CategoryID = g.Key, ProductsCount = g.Count() };

 

DataTable与Linq相互转换

DataTable通过dt.AsEnumerable()方法转换可用Linq查询，反之，Linq也可以转化为DataTableDataTable newDt = query1.CopyToDataTable<DataRow>();var query1 =    from stu in dtStu.AsEnumerable()    from score in dtScore.AsEnumerable()    where stu.Field<int>("ScoreID") == score.Field<int>("ScoreID")    where (int)stu["Age"] > 20    select stu;

//通过CopyToDataTable()方法创建新的副本DataTable newDt = query1.CopyToDataTable<DataRow>(); foreach (var item in newDt.AsEnumerable())   {       System.Console.WriteLine(item["Name"]);  }

转载于:https://www.cnblogs.com/siyunianhua/p/8334191.html

相关资源：JAVA上百实例源码以及开源项目