LINQ几个主要操作

先把准备工作做好,这步可以不看,创建一个数据库和两张表,下面是创建语句,很简单,大家都看得懂.

第一步:创建表和初始化数据

--创建数据库Student
create database Student
go
use Student
--创建数据表T_Student
create table T_Student(
ID int not null identity primary key,
Name varchar(20) not null,
EnglishName varchar(20) not null,
Age int,
Birthday date,
Country varchar(50)
)
--创建数据表T_Score,设置主外键关系
create table T_Score(
ID int not null identity primary key,
StudentID int not null foreign key references T_Student(ID),
English varchar(20) not null,
Math varchar(20) not null,
Chinese varchar(20) not null
)
go

--向T_Student插入数据
insert into T_Student
select '李正兴','Black_Li',19,'1991-03-17','江苏盐城'
union
select '罗海','Jason',21,'1991-10-17','江苏南京'
union
select '张士阳','Huk',35,'1991-12-18','安徽阜阳'
union
select '吕佳惠','Lily',23,'1991-06-23','四川'
union
select '金琢','Babylon',23,'1991-03-15','江苏扬州'

--向T_Score插入数据
insert into T_Score
select 1,'90','84','68'
union
select 2,'87','78','78'
union
select 3,'79','87','88'
union
select 4,'95','84','89'
union
select 5,'90','84','58'
go

学生表:

成绩表:

 第二步:创建两张表对应的实体

View Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqDemo
{
    /// <summary>
    /// 学生实体类
    /// </summary>
    public class Student
    {
        #region 属性
        public int ID { get; set; }
        public string Name { get; set; }
        public string EnglishName { get; set; }
        public int Age { get; set; }
        public DateTime Birthday { get; set; }
        public string Country { get; set; }
        #endregion

        #region 方法
        /// <summary>
        /// 重写Object类的ToString()方法
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return string.Format("学号:{0},姓名:{1},英文名:{2},年龄:{3},生日:{4},家乡:{5}",
                this.ID,
                this.Name,
                this.EnglishName,
                this.Age,
                this.Birthday,
                this.Country);
        }
        #endregion
    }
}
View Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqDemo
{
    /// <summary>
    /// 成绩表实体
    /// </summary>
    public class Score
    {
        #region 属性
        public int ID { get; set; }
        public int StudentID { get; set; }
        public string English { get; set; }
        public string Math { get; set; }
        public string Chinese { get; set; }
        #endregion

        #region 方法
        /// <summary>
        /// 重写Object类的ToString()方法
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return string.Format("学号:{0},英语:{1},数学:{2},语文:{3}",
                this.StudentID,
                this.English,
                this.Math,
                this.Chinese);
        }
        #endregion
    }
}

第三步:创建学生管理类(其中用到了自己写的SQLHelper,也贴出来)

View Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace LinqDemo
{
    /// <summary>
    /// 学生管理类
    /// </summary>
    public class StudentManage
    {
        private string commandText = string.Empty;
        DataTable table = null;
        /// <summary>
        /// 获取所有学生信息
        /// </summary>
        /// <returns></returns>
        public List<Student> GetAllStudents()
        {
            //创建一个集合
            List<Student> list = new List<Student>();
            commandText = @"SELECT [ID]
                                  ,[Name]
                                  ,[EnglishName]
                                  ,[Age]
                                  ,[Birthday]
                                  ,[Country]
                                FROM [Student].[dbo].[T_Student]";
            //执行Sql语句,查出所有的学生信息
            table = SQLHelper.ExecuteDataTable(commandText);

            //遍历添加到list集合中
            for (int i = 0; i < table.Rows.Count; i++)
            {
                list.Add(new Student()
                {
                    ID = Convert.ToInt32(table.Rows[i]["ID"]),
                    Name = table.Rows[i]["Name"].ToString(),
                    EnglishName = table.Rows[i]["EnglishName"].ToString(),
                    Age = Convert.ToInt32(table.Rows[i]["Age"]),
                    Birthday = Convert.ToDateTime(table.Rows[i]["Birthday"]),
                    Country = table.Rows[i]["Country"].ToString()
                });
            }
            return list;
        }
        /// <summary>
        /// 获取所有成绩信息
        /// </summary>
        /// <returns></returns>
        public List<Score> GetAllScores()
        {
            //创建一个集合
            List<Score> list = new List<Score>();
            commandText = @"SELECT [ID],
                                [StudentID],
                                [English],
                                [Math],
                                [Chinese]
                              FROM [Student].[dbo].[T_Score]";
            //执行Sql语句,查出所有的学生信息
            table = SQLHelper.ExecuteDataTable(commandText);

            //遍历添加到list集合中
            for (int i = 0; i < table.Rows.Count; i++)
            {
                list.Add(new Score()
                {
                    ID = Convert.ToInt32(table.Rows[i]["ID"]),
                    StudentID = Convert.ToInt32(table.Rows[i]["StudentID"]),
                    English = table.Rows[i]["English"].ToString(),
                    Math = table.Rows[i]["Math"].ToString(),
                    Chinese = table.Rows[i]["Chinese"].ToString()
                });
            }
            return list;
        }
    }
}
View Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace LinqDemo
{
    /// <summary>
    /// 后台数据执行类
    /// </summary>
    public class SQLHelper
    {
        #region  ExecuteNonQuery()方法 返回一个int值
        /// <summary>
        /// 执行ExecuteNonQuery并返回结果,静态函数可由类SQLHelper直接调用
        /// </summary>
        /// <param name="commandText">sql语句命令</param>
        /// <param name="parameters">用户传过来的参数集合</param>
        /// <returns>返回一个int值</returns>
        public static int ExecuteNonQuery(string commandText, params SqlParameter[] parameters)
        {
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;//建立连接字符串

            using (SqlConnection conn = new SqlConnection(connStr))//创建一个连接
            {
                conn.Open();//打开连接

                using (SqlCommand cmd = conn.CreateCommand())//创建命令
                {
                    cmd.CommandText = commandText;//sql命令是用户传过来的命令
                    //遍历传过来的参数
                    foreach (SqlParameter p in parameters)
                    {
                        cmd.Parameters.Add(p);
                    }
                    return cmd.ExecuteNonQuery();//执行命令,并返回执行结果
                }
            }
        }
        #endregion

        #region  ExecuteScalar()方法 返回一个object值
        /// <summary>
        /// 执行ExecuteScalar并返回结果,静态函数可由类SQLHelper直接调用
        /// </summary>
        /// <param name="commandText">sql语句命令</param>
        /// <param name="parameters">用户传过来的参数集合</param>
        /// <returns>返回一个object值</returns>

        /* 举例:cmd.CommandText = "SELECT COUNT(*) FROM T_Persons";
              Int32 count = (Int32)cmd.ExecuteScalar();
         执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
         */

        public static object ExecuteScalar(string commandText, params SqlParameter[] parameters)
        {
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;//建立连接字符串

            using (SqlConnection conn = new SqlConnection(connStr))//创建一个连接
            {
                conn.Open();//打开连接

                using (SqlCommand cmd = conn.CreateCommand())//创建命令
                {
                    cmd.CommandText = commandText;//sql命令是用户传过来的命令
                    //遍历传过来的参数
                    foreach (SqlParameter p in parameters)
                    {
                        cmd.Parameters.Add(p);
                    }
                    return cmd.ExecuteScalar();//执行命令,并返回执行结果
                }
            }
        }

        #endregion

        #region  ExecuteDataTable()方法 返回一个DataTable值
        /// <summary>
        /// 执行ExecuteDataTable并返回结果,静态函数可由类SQLHelper直接调用
        /// </summary>
        /// <param name="commandText">sql语句命令</param>
        /// <param name="parameters">用户传过来的参数集合</param>
        /// <returns>返回一个DataTable值</returns>

        public static DataTable ExecuteDataTable(string commandText, params SqlParameter[] parameters)
        {
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;//建立连接字符串

            using (SqlConnection conn = new SqlConnection(connStr))//创建一个连接
            {
                conn.Open();//打开连接

                using (SqlCommand cmd = conn.CreateCommand())//创建命令
                {
                    cmd.CommandText = commandText;//sql命令是用户传过来的命令

                    DataSet dataset = new DataSet();//新建一个数据集,把查询数据保存到本地数据集中

                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);//创建一个适配器adapter

                    //遍历传过来的参数
                    foreach (SqlParameter p in parameters)
                    {
                        cmd.Parameters.Add(p);
                    }

                    adapter.Fill(dataset);//向数据集中填充数据

                    DataTable table = dataset.Tables[0];//将dataset中的查询结果(一般第一个表)保存起来


                    return table;//执行命令,并返回执行结果
                }
            }
        }

        #endregion



    }
}

第四步:LINQ开始,在Program.cs类中

        static void Main(string[] args)
        {
            DoWork();
            Console.ReadKey();
        }
        /// <summary>
        /// 做某些操作函数
        /// </summary>
        static void DoWork()
        {
            //创建学生管理对象
            StudentManage SM = new StudentManage();
            //获取所有学生存放于集合students中
            List<Student> students = SM.GetAllStudents();
            //获取所有成绩存放于集合scores中
            List<Score> scores = SM.GetAllScores();      
        }
查询
            //查询语句,将所有学生姓名取出来放入集合中
            //IEnumerable<string> names = students.Select(s=>s.Name);
            //或者
            IEnumerable<string> names = from s in students select s.Name;
            foreach (string name in names)
            {
                Console.WriteLine(name);
            } 
结果:
            var stus = students.Select(s=>new { Name = s.Name, Age = s.Age });
            //或者
            //var stus = from s in students select new { Name = s.Name, Age = s.Age };
            foreach (var s in stus)
            {
                Console.WriteLine(s);
            }
结果:

筛选
            //筛选语句,将符合条件的学生姓名取出来
            //IEnumerable<Student> stus = students.Where(stu => string.Equals(stu.Country,"四川")).Select(stu=>stu);
            //或者
            IEnumerable<Student> stus = from stu in students where string.Equals(stu.Country, "四川") select stu;
            foreach (Student stu in stus)
            {
                Console.WriteLine(stu.ToString());
            }
结果:
排序
            //根据学号降序,默认升序
            //var stus = students.OrderByDescending(stu => stu.ID).Select(stu => new {Number=stu.ID,Name=stu.Name});
            //或者
            var stus = from stu in students orderby stu.ID descending select new { Number = stu.ID, Name = stu.Name };
            foreach (var stu in stus)
            {
                Console.WriteLine(stu);
            }
结果:
分组
            //根据年龄分组
            //var stusGroup = students.GroupBy(stu=>stu.Age);
            //或者
            var stusGroup = from stu in students group stu by stu.Age;
            //遍历组信息
            foreach (var grop in stusGroup)
            {
                Console.WriteLine("这是年龄为"+grop.Key+"");
                Console.WriteLine("该组成员如下:");
                //遍历组成员信息
                foreach(var stu in grop)
                {
                    Console.WriteLine("姓名:"+stu.Name+" 年龄:"+stu.Age);
                }
                Console.WriteLine();
            }
结果:
结果集去除重复与计数
            //取出所有年龄,过滤掉重复,统计个数
            int number = students.Select(stu => stu.Age).Distinct().Count();
联合查询
            //创建学生管理对象
            StudentManage SM = new StudentManage();
            //获取所有学生存放于集合students中
            List<Student> students = SM.GetAllStudents();
            //获取所有成绩存放于集合scores中
            List<Score> scores = SM.GetAllScores();
            //联合学生和成绩表
            //var StuScores = students.Select(stu => stu).Join
        (scores, stus => stus.ID, scors => scors.StudentID, (stus, scors) =>
        new {stus.Name,stus.Age,scors.Chinese,scors.English});
//输出 //或者 var StuScores = from stu in students join score in scores
       on stu.ID equals score.StudentID
       select new { stu.Name, stu.Age, score.Chinese, score.English }; foreach (var stuscore in StuScores) { Console.WriteLine(stuscore); }
结果:

未完待续……

作者:李正兴     英文名:black_li
出处:http://www.cnblogs.com/longteng1991/
如果,您喜欢这篇文章的话,您可以分享给您的好友,请点击按钮【推荐】
如果,您想更快的阅读我的最新文章,您可以关注我,请点击按钮【关注】
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
posted @ 2013-01-22 08:57  极客黑李  阅读(1912)  评论(10编辑  收藏  举报