Linq To DataSet总结

• 使用LINQ技术查询DATASET中的单个表

    private void Form1_Load(object sender, EventArgs e)

        {undefined

            string ConnectionString = "Data Source=DESKTOP-M4PUOAS\\SQLEXPRESS;Initial Catalog=db_TomeTwo;User ID=sa;Password=wh1090220084";//声明连接字符串

            using (SqlConnection conn = new SqlConnection(ConnectionString))//创建数据库连接对象

            {undefined

                string sqlstr = "select * from tb_Bookinfo";//定义查询语句

                SqlDataAdapter da = new SqlDataAdapter(sqlstr, conn);//创建数据桥接器对象

                DataSet ds = new DataSet();//创建数据对象

                da.Fill(ds, "tb_Bookinfo");//填充数据集

                //查找3月份出版的图书

                var result = from b in ds.Tables["tb_Bookinfo"].AsEnumerable()

                             where b.Field<DateTime>("b_pub_date").Month == 3

                             select new

                             {undefined

                                 bookname = b["b_name"].ToString(),

                                 author = b["b_pub_date"].ToString()

                             };

                foreach (var item in result)//遍历图书的书名和出版日期

                {undefined

                   richTextBox1.Text +="书名：" + item.bookname + ";出版日期：" + item.author+"\n";

                }

            }

        }

二、使用LINQ技术对DataSet进行投影操作

本例只要用到了DataRowExtensions类的Field<T>泛型方法

Field<T>方法用来提供对指定行中的每个列值的强类型访问，其语法格式如下：

 Public static T Filed<T>(this DataRow row,string columnName)

   参数说明：

1. row：表示当前访问的列名称。
2. columnName::表示访问的列名称。
3. 返回值：由columnName指定的DataColumn的值，类型为T

       private void Frm_Main_Load(object sender, EventArgs e)

        {undefined

            string ConnectionString = "Data Source=DESKTOP-M4PUOAS\\SQLEXPRESS;Initial Catalog=db_TomeTwo;User ID=sa;Password=wh1090220084";//声明连接字符串

            using (SqlConnection Conn = new SqlConnection(ConnectionString))//创建数据库连接对象

            {undefined

                string sqlstr = "select top 5* from tb_Bookinfo";//定义查询语句

                SqlDataAdapter da = new SqlDataAdapter(sqlstr, Conn);//创建数据桥接器对象

                DataSet ds = new DataSet();//创建数据对象

                da.Fill(ds, "tb_Bookinfo");//填充数据集

                //查询图书信息表前3行数据的图书名称和作者

                var result = from b in ds.Tables["tb_Bookinfo"].AsEnumerable()

                             select new

                             {undefined

                                 b_name = b.Field<string>("b_name"),

                                 b_author = b.Field<string>("b_author")

                             };

                foreach (var item in result)//遍历输出查询结果

                {undefined

                    richTextBox1.Text +="图书名称：" + item.b_name + "  ******  作者：" + item.b_author+"\n";

                }

            }

        }

三、使用LINQ技术查询DataSet 中的多个表

     private void Frm_Main_Load(object sender, EventArgs e)

        {undefined

            string ConnectionString = "Data Source=DESKTOP-M4PUOAS\\SQLEXPRESS;Initial Catalog=db_TomeTwo;User ID=sa;Password=wh1090220084";//声明连接字符串

            using (SqlConnection Conn = new SqlConnection(ConnectionString))//创建数据库连接对象

            {undefined

                string sqlstr = "select * from tb_Register";//定义查询语句

                SqlDataAdapter da = new SqlDataAdapter(sqlstr, Conn);//创建数据桥接器对象

                DataSet ds = new DataSet();//创建数据对象

                da.Fill(ds, "register");//填充第一个数据表数据到DataSet

                sqlstr = "select * from tb_Sale";//定义查询语句

                da.SelectCommand.CommandText = sqlstr;//指定第二条查询语句

                da.Fill(ds, "sale");//填充第二个数据表数据到DataSet

                //查询有销售记录的药品信息

                var result = from r in ds.Tables["register"].AsEnumerable()

                             join s in ds.Tables["sale"].AsEnumerable()

                             on r.Field<string>("药品编号") equals s.Field<string>("药品编号")

                             select new

                             {undefined

                                 drug_name = r["药品名称"].ToString(),

                                 drug_factory = r["生产厂家"].ToString(),

                                 drug_sale = s["销售额"].ToString()

                             };

                foreach (var item in result)                     //遍历输出查询结果

                {undefined

                    richTextBox1.Text += "药品名称：" + item.drug_name + "******生产厂家：" + item.drug_factory + "******销售额：" + item.drug_sale + "\n";

                }

            }

        }

四、使用LINQ技术对DataSet进行排序操作

private void Frm_Main_Load(object sender, EventArgs e)

        {undefined

            string ConnectionString = "server=WIN-GI7E47AND9R\\LS; database=db_TomeTwo; uid=sa; pwd=";//连接字符串

            using (SqlConnection Conn = new SqlConnection(ConnectionString))//创建数据库连接对象

            {undefined

                string sqlstr = "select * from tb_Bookinfo";//定义查询语句

                SqlDataAdapter da = new SqlDataAdapter(sqlstr, Conn);//创建数据桥接器对象

                DataSet ds = new DataSet();//创建数据对象

                da.Fill(ds, "tb_Bookinfo");//填充数据集

                //按照单价降序排序

                var result = from b in ds.Tables["tb_Bookinfo"].AsEnumerable()

                             orderby b.Field<decimal>("b_price") descending

                             select new

                             {undefined

                                 b_name = b["b_name"].ToString(),

                                 b_price = b["b_price"].ToString()

                             };

                foreach (var item in result)//遍历输出查询结果

                {undefined

                    richTextBox1.Text+="图书名称：" + item.b_name + "  ******  单价：" + item.b_price+"\n";

                }

            }

        }

五、使用TakeWhile提取满足条件的记录

     private void Frm_Main_Load(object sender, EventArgs e)

        {undefined

            string conStr = "Data Source=WIN-GI7E47AND9R\\LS;Database=db_TomeTwo;UID=sa;Pwd=;";//取连接字符串

            string sql = "select * from EmployeeInfo";//构造sql语句

            DataSet ds = new DataSet();//创建数据集

            using (SqlConnection con = new SqlConnection(conStr))//创建数据连接

            {undefined

                SqlCommand cmd = new SqlCommand(sql, con);//创建Command对象

                SqlDataAdapter sda = new SqlDataAdapter(cmd);//创建DataAdapter对象

                sda.Fill(ds, "EmployeeInfo");//填充数据集

            }

            //从头开始提取生日小于2009-7-1之前的员工信息

            IEnumerable<DataRow> query = ds.Tables["EmployeeInfo"].AsEnumerable().TakeWhile(itm => itm.Field<DateTime>("Birthday") < Convert.ToDateTime("2009-7-1"));

            dataGridView1.DataSource = query.CopyToDataTable();//设置dataGridView1数据源

        }

六、使用SkipWhile跳过指定条件的记录

   private void Frm_Main_Load(object sender, EventArgs e)

        {undefined

            string conStr = "Data Source=WIN-GI7E47AND9R\\LS;Database=db_TomeTwo;UID=sa;Pwd=;";//取连接字符串

            string sql = "select * from EmployeeInfo";//构造sql语句

            DataSet ds = new DataSet();//创建数据集

            using (SqlConnection con = new SqlConnection(conStr))//创建数据连接

            {undefined

                SqlCommand cmd = new SqlCommand(sql, con);//创建Command对象

                SqlDataAdapter sda = new SqlDataAdapter(cmd);//创建DataAdapter对象

                sda.Fill(ds, "EmployeeInfo");//填充数据集

            }

            //跳过生日小于2009-7-1的员工信息

            IEnumerable<DataRow> query = ds.Tables["EmployeeInfo"].AsEnumerable().SkipWhile(itm => itm.Field<DateTime>("Birthday") < Convert.ToDateTime("2009-7-1"));

            dataGridView1.DataSource = query.CopyToDataTable();//设置dataGridView1数据源

        }

文章来源: wanghao.blog.csdn.net，作者：AI浩，版权归原作者所有，如需转载，请联系作者。

原文链接：wanghao.blog.csdn.net/article/details/105527242