学习 linq to ADO.NET 实例笔记（一）

Linq  查询 DataSet  代码实例如下：

      首先要获得 DataSet 数据源，因为DataSet里面存储的为DataTable,所以我们其实查询的是DataTable里面的数据，如果要用linq 必须通过 DataTable.AsEnumerable() 方法转化为IEnumerable<T> 类型，之后就可以对DataTable 进行查询，示例代码如下（注：这只是本人自己的想法，如果有错误希望大家指出）：

 

首先要写个返回DataTable 的方法，下面会说到查询2个DataTable ，所有就直接放两个DataTable ,具体方法如下：

/// <summary>
        /// DemoDt
        /// </summary>
        /// <returns></returns>
        static DataTable DemoDt()
        {
            int[] ids = { 1, 2, 3, 4, 5 };
            string[] names = { "张三", "李四", "王五", "小二", "嘿嘿" };
            string[] sexs = { "男", "女", "男", "男", "女" };
            int[] ages = { 18, 15, 16, 19, 24 };

            DataTable dt = new DataTable();

            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("sex", typeof(string));
            dt.Columns.Add("age", typeof(int));



            for (int i = 0; i < ids.Length; i++)
            {
                DataRow dr = dt.NewRow();
                dr[0] = Convert.ToInt32(ids[i].ToString());
                dr[1] = names[i].ToString();
                dr[2] = sexs[i].ToString();
                dr[3] = Convert.ToInt32(ages[i].ToString());
                dt.Rows.Add(dr);
            }

            return dt;

        }
        /// <summary>
        /// 分数dt
        /// </summary>
        /// <returns></returns>
        static DataTable ScoreDt()
        {

            DataTable dt = new DataTable();
            int[] ids = { 1, 2, 3, 4, 6 };
            //语文
            int[] yuwen = { 10, 20, 30, 40, 50 };
            //数学
            int[] shuxue = { 60, 55, 70, 75, 65 };
            //英语
            int[] yingyu = { 90, 98, 99, 95, 96 };
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("yw", typeof(int));
            dt.Columns.Add("sx", typeof(int));
            dt.Columns.Add("yy", typeof(int));

            for (int i = 0; i < yuwen.Length; i++)
            {
                DataRow dr = dt.NewRow();
                dr[0] = Convert.ToInt32(ids[i]);
                dr[1] = Convert.ToInt32(yuwen[i]);
                dr[2] = Convert.ToInt32(shuxue[i]);
                dr[3] = Convert.ToInt32(yingyu[i]);
                dt.Rows.Add(dr);
            }
            return dt;

        }

具体查询方法如下：

            #region 查询一个dt,  没有添加where 或者其他的查询条件

            //Field<int>("字段") 得到值

            DataTable dt = DemoDt();
            DataTable score = ScoreDt();

            var query = from value in dt.AsEnumerable()
                        select value;



            foreach (var item in query)
            {
                Console.WriteLine("编号：{0},姓名:{1},性别：{2},年龄：{3}", item.Field<int>("id"), item.Field<string>("name"), item.Field<string>("sex"), item.Field<int>("age"));
            }


            #endregion

            #region   添加where 条件

            var query1 = from value in dt.AsEnumerable()
                         where value.Field<int>("id") > 3
                         where value.Field<int>("age") > 20
                         select value;
            foreach (var item in query1)
            {
                Console.WriteLine("编号：{0},姓名:{1},性别：{2},年龄：{3}", item.Field<int>("id"), item.Field<string>("name"), item.Field<string>("sex"), item.Field<int>("age"));
            }

            #endregion



            #region 查询两个dt，获得两个dt 里面id 相等数据


            var query3 = from value in dt.AsEnumerable()
                         from value2 in score.AsEnumerable()
                         where value.Field<int>("id") == value2.Field<int>("id")
                         where value2.Field<int>("yw") > 30
                         select new
                         {

                             id = value.Field<int>("id"),
                             name = value.Field<string>("name"),
                             yw = value2.Field<int>("yw"),
                             sx = value2.Field<int>("sx"),
                             yy = value2.Field<int>("yw")
                         };


            foreach (var item in query3)
            {
                Console.WriteLine("编号：{0},姓名:{1},语文：{2},数学：{3},英语：{4}", item.id.ToString(), item.name.ToString(), item.yw.ToString(), item.sx.ToString(), item.yy.ToString());

            }

            #endregion


            #region 查询DemoDt里面除了 DemoDt 和 ScoreDt Id相等的数据

            var query4 = from value in dt.AsEnumerable()
                         from value2 in score.AsEnumerable()
                         where value.Field<int>("id") == value2.Field<int>("id")
                         select value;

            var query5 = dt.AsEnumerable().Except(query4);


            foreach (var item in query5)
            {

                Console.WriteLine("编号：{0},姓名:{1},性别：{2},年龄：{3}", item.Field<int>("id"), item.Field<string>("name"), item.Field<string>("sex"), item.Field<int>("age"));
            }
            #endregion



            #region 修改 age

            //修改---通过SetField<T>("字段")这个方法
            foreach (var item in dt.AsEnumerable())
            {
                int age = item.Field<int>("age");
                item.SetField<int>("age", age + 1);
            }

            var query6 = from value in dt.AsEnumerable()
                         select value;

            foreach (var item in query6)
            {
                Console.WriteLine("编号：{0},姓名:{1},性别：{2},年龄：{3}", item.Field<int>("id"), item.Field<string>("name"), item.Field<string>("sex"), item.Field<int>("age"));
            }
            #endregion