【转载】C# 中如何实现LINQ查询数据库表信息

声明：本博客转载来源于李天平老师的《项目中的.NET》一书，推荐大家阅读，收获一定是大大的。

LINQ定义了统一的语法，可以从不同的数据源或数据格式中获取数据，从而减少开发人员学习的负担。所有的LINQ查询操作都可以由以下3个不同的操作组成：

【1】获取数据源

【2】创建查询

【3】执行查询

以下我们将各个步骤慢慢分析一下：

using System.Xml.Linq;
using System.Xml;

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data.SqlClient;

△如果数据源是SQL数据库，则第一步要先建立数据库和实例之间的关系映射。第二步为实例对象写查询语句。第三步，LINQ会在运行时保持SQL与实例对象中交互。

        protected void Button4_Click(object sender, EventArgs e)
        {
            //获取数据源
            string connectionString = "data source=VQJREZV7DVSK2QA;Database=codematic;user id=sa;password=admin@123456";
            DataContext db = new DataContext(connectionString);
            Table<Product> prolist = db.GetTable<Product>();

            //创建查询
            var pros = from p in prolist
                       where p.Price > 1
                       orderby p.ID
                       select p;
            //执行查询
            foreach (Product pro in pros)
            {
                Response.Write(pro.Name + "," + pro.Price.ToString() + "<br>");
            }
            //GridView1.DataSource =pros;
        }

△如果数据源是XML文档，则必须将它加工为支持泛型IEnumerable(Of T)接口的对象形式。在.NET FrameWork中，XElement 术语查询类型，将XML文档转化成XElement类型对象即可。

 protected void Button6_Click(object sender, EventArgs e)
        {
            // 获取数据源
            XmlDocument doc = new XmlDocument();
            string path = Server.MapPath("sampledata.xml");
            XElement stulist = XElement.Load(path);

            //创建查询
            var stus = from r in stulist.Elements("学生")
                       where r.Element("性别").Value == "男"
                       orderby r.Element("姓名")
                       select r;

            foreach (XElement node in stus)
            {
                Response.Write(node);
            }

            //foreach (XAttribute att in stulist.Attributes())
            //{
            //    stulist.Add(new XElement(att.Name, (string)att));
            //}
            //stulist.Attributes().Remove();
            //Response.Write(stulist);
        }

△如果采用对象列表进行查询

 protected void Button5_Click(object sender, EventArgs e)
        {
            //获取数据源
            List<Man> manlist = new List<Man>();
            manlist.Add(new Man("李天平", "男"));
            manlist.Add(new Man("李嘉诚", "男"));
            manlist.Add(new Man("徐静蕾", "女"));

            //创建查询
            var mans = from r in manlist
                       where r.Sex == "男"
                       orderby r.Name descending
                       select r;

            //执行查询
            foreach (Man man in mans)
            {
                Response.Write(man.Name + "," + man.Sex + "<br>");
            }
        }

00000000000000000000000000000000000000000000000000000000000000

    /// <summary>
    /// 示例对象
    /// </summary>
    public class Man
    {
        private string name;
        private string sex;
        /// <summary>
        /// 姓名
        /// </summary>
        public string Name
        {
            set { name = value; }
            get { return name; }
        }
        /// <summary>
        /// 性别：男，女
        /// </summary>
        public string Sex
        {
            set { sex = value; }
            get { return sex; }
        }
        public Man(string Name, string Sex)
        {
            name = Name;
            sex = Sex;
        }

    }

    //映射类
    [Table(Name = "P_Product")]//将某个类指定为数据库表相关联的实体类
    public class Product
    {
        [Column(IsPrimaryKey = true)]//将类与数据库表的列相关联
        public int ID { get; set; }

        [Column(Name = "Name")]
        public string Name { get; set; }

        [Column(Name = "Descn")]
        public string Descn { get; set; }

        [Column(Name = "Price")]
        public decimal Price { get; set; }
    }

    public class Student
    {
        private string name;
        private string sex;
        /// <summary>
        /// 姓名
        /// </summary>
        public string Name
        {
            set { name = value; }
            get { return name; }
        }
        /// <summary>
        /// 性别：男，女
        /// </summary>
        public string Sex
        {
            set { sex = value; }
            get { return sex; }
        }
        public Student(string Name, string Sex)
        {
            name = Name;
            sex = Sex;
        }
    }