LINQ To DateSet 学习一

  datatable使用linq时 必需先转化成IEnumerable<T>数据集合，然后针对IEnumerable<T>进行操作
转化方法：dt.AsEnumerable()

 

//单表查询 var query1 = from p1 in dt.AsEnumerable() select p1; //查询表dt的全部数据 foreach(var item in query1) { item.Field<string>("xingbie"); //xingbie为列名 item.Field<int>("age"); item.Field<string>("Name"); } var query2 = from p1 in dt.AsEnumerable() select p1.Field<string>("Name"); //查询表dt的Name数据 var query2 = from p1 in dt.AsEnumerable() where p1.Field<int>("age") > 22 //查询表dt的年龄大于22岁的 小于50的 where p1.Field<int>("age") < 50 select p1;

多表联合查询

DataTable dtStu = new DataTable(); DataTable dtScore = new DataTable(); var query1 = from stu in dtStu.AsEnumerable() from score in dtScore.AsEnumerable() where stu.Field<int>("ScoreID") == score.Field<int>("ScoreID")//获取成绩编号相等的 select new { Name = stu.Field<string>("Name"), Maths = stu.Field<int>("Math"), Chinese = stu.Field<int>("Chinese"), English = stu.Field<string>("English") }; //读取 foreach (var item in query1) { Console.WriteLine("Name:{0} Maths:{1} Chinese:{2} English:{3}", item.Name.ToString(),item.Maths.ToString(),item.Chinese.ToString(),item.English.ToString()); }

//CopyToDataTable()复制表方法

dataGridView.DataSource = bindingSource; DataSet ds = new DataSet(); ds.Locale = CultureInfo.InvariantCulture; FillDataSet(ds); DataTable orders = ds.Tables["SalesOrderHeader"]; IEnumerable<DataRow> query = from order in orders.AsEnumerable() where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1) select order;. DataTable boundTable = query.CopyToDataTable<DataRow>(); bindingSource.DataSource = boundTable;

//SetField（）修改表中字段数据

foreach (var row in dtStu.AsEnumerable()) { int age = row.Field<int>("age"); row.SetField<int>("age", age + 2);//年龄加2 修改 }

 

//使用数据视图DataView  只读形式数据保存形式

 

DataView dv = dtStu.AsDataView(); //表直接转dataview dv.RowFilter = "Age > 20"; dv.RowFilter = string.Empty; // dv.RowFilter = null; dv.Sort = "Age asc,Name desc"; dv.Sort = string.Empty; //dv.Sort = null; DataTable orders = dataSet.Tables["SalesOrderHeader"]; EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable() where order.Field<bool>("OnlineOrderFlag") == true orderby order.Field<decimal>("TotalDue") select order; DataView view = query.AsDataView(); bindingSource1.DataSource = view;