c# Linq to sql 基本查询例子

c# Linq to sql 基本查询例子

分类： .net 2010-11-18 10:06 801人阅读 评论(1) 收藏 举报

准备工作：1.使用微软的例子数据库-Northwind,没有的可以到微软挂官网去下,附件到sql server2005数据库中

               2.使用微软O/R设计器，将Northwind映射到项目中，以下实例在此环境下进行,使用vs2008

以下为代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBClass;//此处为O/R映射Northwind
using System.Data.Linq;
using System.Data.Linq.SqlClient;



namespace Linq_to_Sql_Demo1
{
    class Program
    {
        static void Main(string[] args)
        {
            LinqForLike();
        }

        #region linq中select语句的使用
        public static void SqlFroSelectOne()
        {
            NorthwindDataContext db = new NorthwindDataContext();

            var q = from c in db.Customers where c.City == "London" select new { c.CompanyName, c.City };
            //上下两句表达意思是一样的，上面为标准查询语句，下面为级连表达式
            // var q = db.Customers.Where(c => c.City == "London").Select(c => c.ContactName);

            /* 手动填写数据库对象后的方法
            DataContext dc = new DataContext("server=.//sql2005;database=Northwind;uid=sa;pwd=king_860119");
             var q = from c in dc.GetTable<Customers>() where c.City == "London" select c.CompanyName;
            */
            foreach (var c in q)
            {
                Console.WriteLine(c);
            }

            Console.ReadLine();
        }
        #endregion

        #region linq中select语句的使用2
        public static void SqlFroSelectTwo()
        {
            NorthwindDataContext dc = new NorthwindDataContext();

            var q=from s in dc.Customers select new{城市名称=s.City};
            //在这里相当于声明一个匿名类q,q中存在属性“城市名称”,而其中S为一条条的dc.Customers对象
            //其中“城市名称”这个属性当当与对S.city的重命名一样
            foreach(var a in q)
            {
                Console.WriteLine(a);
            }

            Console.Read();
        }
        #endregion

        #region linq中select语句的使用3
        public static void SqlFroSelectThree()
        {
            NorthwindDataContext dc = new NorthwindDataContext();

            var q=from s in dc.Products select new {s.ProductName,我的条件=s.UnitsInStock-s.UnitsOnOrder<0?"out":"in"};

            foreach(var a in q)
            {
                Console.WriteLine(a);
            }
            Console.Read();

        }
        #endregion

        #region linq中where语句的使用1
        public static void SqlFroWhereOne()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var q =
               from p in db.Products
               where p.UnitPrice > 10m || p.Discontinued
               select p;


            foreach (var a in q)
            {
                Console.WriteLine(a.Categories.CategoryID);
            }
            Console.Read();

        }
        #endregion

        #region linq中order语句的使用1
        public static void SqlFroOrderOne()
        {
            NorthwindDataContext db = new NorthwindDataContext();

            var q = from s in db.Employees orderby s.EmployeeID descending select new { s.EmployeeID,s.FirstName,s.LastName};
            //orderby子句在select子句前面，与where等子句位置无要求
            foreach (var s in q)
            {
                Console.WriteLine(s.ToString());
            }
            Console.Read();

        }
        #endregion

        #region Linq中GroupBy语句的使用1
        public static void SqlFroGroupByOne()
        {
            NorthwindDataContext db = new NorthwindDataContext();

            var q = from p in db.Products group p by p.CategoryID into g select g;

            foreach (var gp in q)
            {
                if (gp.Key == 7)
                {
                    foreach (var p in gp)
                    {
                        //do something
                    }
                }
            }
            Console.ReadKey();


        }
        #endregion

        #region Linq中GroupBy语句的使用2
        public static void SqlForGroupByTwo()
        {
            NorthwindDataContext db = new NorthwindDataContext();

            var q = from p in db.Products group p by p.CategoryID into g select new { CategoryID = g.Key, g };

            foreach (var gp in q)
            {
                //do something
            }
        }
        #endregion

        #region Linq中GroupBy语句的使用30-聚合函数的使用
        public static void SqlForGroupByThree()
        {
            NorthwindDataContext db = new NorthwindDataContext();

            var q = from p in db.Products group p by p.CategoryID into g select new { g.Key, TotalPrice = g.Sum(p => p.UnitPrice) };

            foreach (var a in q)
            {
                Console.WriteLine(a);
            }
            Console.Read();

        }
        #endregion

        #region Linq中Join语句的使用1
        public static void LinqForJoinOne()
        {
            NorthwindDataContext db = new NorthwindDataContext();

            var q = from p in db.Customers join o in db.Orders on p.CustomerID equals o.CustomerID select new { p.CustomerID, o.EmployeeID };
           //下面注释的语句为此句的lambda的写法
           // var q = db.Customers.Join(db.Orders, p => p.CustomerID, o => o.CustomerID, (p, o) => new { p.CustomerID, o.EmployeeID });
            foreach (var s in q)
            {
                Console.WriteLine(s);
            }
            Console.Read();
        }
        #endregion

        #region Linq中In语句的使用
        public static void LinqForInOne()
        {
            NorthwindDataContext db = new NorthwindDataContext();

            string[] customerID_Set = new string[] { "AROUT", "BOLID", "FISSA" };

            var q = from s in db.Orders where customerID_Set.Contains(s.CustomerID) select s.EmployeeID;
            //相当于sql中的in，意思就是从这个in的集合中取出，如果not in 就在集合的前面加上！

            foreach (var a in q)
            {
                Console.WriteLine(a);
            }
            Console.Read();


        }
        #endregion

        #region Linq中Like语句的使用
        public static void LinqForLike()
        {
            NorthwindDataContext db = new NorthwindDataContext();

            //中间部分包含And的
            var q = from s in db.Customers where s.ContactName.Contains("And") select s.ContactName;
            //开头部分包含A的
            var p = from s in db.Customers where s.ContactName.StartsWith("A") select s.ContactName;
            //结束部分包含g的
            var a = from s in db.Customers where s.ContactName.EndsWith("g") select s.ContactName;
            //另外的一种写法，需要命名空间System.Data.Linq.SqlClient
            var c = from s in db.Customers where SqlMethods.Like(s.ContactName, "%g") select s.ContactName;

            foreach (var b in c)
            {
                Console.WriteLine(b);
            }
            Console.Read();
        }
        #endregion





    }
}

using System; using System.Collections.Generic; using System.Linq; using System.Text; using DBClass;//此处为O/R映射Northwind using System.Data.Linq; using System.Data.Linq.SqlClient; namespace Linq_to_Sql_Demo1 { class Program { static void Main(string[] args) { LinqForLike(); } #region linq中select语句的使用 public static void SqlFroSelectOne() { NorthwindDataContext db = new NorthwindDataContext(); var q = from c in db.Customers where c.City == "London" select new { c.CompanyName, c.City }; //上下两句表达意思是一样的，上面为标准查询语句，下面为级连表达式 // var q = db.Customers.Where(c => c.City == "London").Select(c => c.ContactName); /* 手动填写数据库对象后的方法 DataContext dc = new DataContext("server=.//sql2005;database=Northwind;uid=sa;pwd=king_860119"); var q = from c in dc.GetTable<Customers>() where c.City == "London" select c.CompanyName; */ foreach (var c in q) { Console.WriteLine(c); } Console.ReadLine(); } #endregion #region linq中select语句的使用2 public static void SqlFroSelectTwo() { NorthwindDataContext dc = new NorthwindDataContext(); var q=from s in dc.Customers select new{城市名称=s.City}; //在这里相当于声明一个匿名类q,q中存在属性“城市名称”,而其中S为一条条的dc.Customers对象 //其中“城市名称”这个属性当当与对S.city的重命名一样 foreach(var a in q) { Console.WriteLine(a); } Console.Read(); } #endregion #region linq中select语句的使用3 public static void SqlFroSelectThree() { NorthwindDataContext dc = new NorthwindDataContext(); var q=from s in dc.Products select new {s.ProductName,我的条件=s.UnitsInStock-s.UnitsOnOrder<0?"out":"in"}; foreach(var a in q) { Console.WriteLine(a); } Console.Read(); } #endregion #region linq中where语句的使用1 public static void SqlFroWhereOne() { NorthwindDataContext db = new NorthwindDataContext(); var q = from p in db.Products where p.UnitPrice > 10m || p.Discontinued select p; foreach (var a in q) { Console.WriteLine(a.Categories.CategoryID); } Console.Read(); } #endregion #region linq中order语句的使用1 public static void SqlFroOrderOne() { NorthwindDataContext db = new NorthwindDataContext(); var q = from s in db.Employees orderby s.EmployeeID descending select new { s.EmployeeID,s.FirstName,s.LastName}; //orderby子句在select子句前面，与where等子句位置无要求 foreach (var s in q) { Console.WriteLine(s.ToString()); } Console.Read(); } #endregion #region Linq中GroupBy语句的使用1 public static void SqlFroGroupByOne() { NorthwindDataContext db = new NorthwindDataContext(); var q = from p in db.Products group p by p.CategoryID into g select g; foreach (var gp in q) { if (gp.Key == 7) { foreach (var p in gp) { //do something } } } Console.ReadKey(); } #endregion #region Linq中GroupBy语句的使用2 public static void SqlForGroupByTwo() { NorthwindDataContext db = new NorthwindDataContext(); var q = from p in db.Products group p by p.CategoryID into g select new { CategoryID = g.Key, g }; foreach (var gp in q) { //do something } } #endregion #region Linq中GroupBy语句的使用30-聚合函数的使用 public static void SqlForGroupByThree() { NorthwindDataContext db = new NorthwindDataContext(); var q = from p in db.Products group p by p.CategoryID into g select new { g.Key, TotalPrice = g.Sum(p => p.UnitPrice) }; foreach (var a in q) { Console.WriteLine(a); } Console.Read(); } #endregion #region Linq中Join语句的使用1 public static void LinqForJoinOne() { NorthwindDataContext db = new NorthwindDataContext(); var q = from p in db.Customers join o in db.Orders on p.CustomerID equals o.CustomerID select new { p.CustomerID, o.EmployeeID }; //下面注释的语句为此句的lambda的写法 // var q = db.Customers.Join(db.Orders, p => p.CustomerID, o => o.CustomerID, (p, o) => new { p.CustomerID, o.EmployeeID }); foreach (var s in q) { Console.WriteLine(s); } Console.Read(); } #endregion #region Linq中In语句的使用 public static void LinqForInOne() { NorthwindDataContext db = new NorthwindDataContext(); string[] customerID_Set = new string[] { "AROUT", "BOLID", "FISSA" }; var q = from s in db.Orders where customerID_Set.Contains(s.CustomerID) select s.EmployeeID; //相当于sql中的in，意思就是从这个in的集合中取出，如果not in 就在集合的前面加上！ foreach (var a in q) { Console.WriteLine(a); } Console.Read(); } #endregion #region Linq中Like语句的使用 public static void LinqForLike() { NorthwindDataContext db = new NorthwindDataContext(); //中间部分包含And的 var q = from s in db.Customers where s.ContactName.Contains("And") select s.ContactName; //开头部分包含A的 var p = from s in db.Customers where s.ContactName.StartsWith("A") select s.ContactName; //结束部分包含g的 var a = from s in db.Customers where s.ContactName.EndsWith("g") select s.ContactName; //另外的一种写法，需要命名空间System.Data.Linq.SqlClient var c = from s in db.Customers where SqlMethods.Like(s.ContactName, "%g") select s.ContactName; foreach (var b in c) { Console.WriteLine(b); } Console.Read(); } #endregion } }

使用O/R映射的数据库实例代码（均为系统自动生成）,连接字符在app.config中

附加上增删改的使用

#region linq中insert语句的使用
       public static void LinqForInsert()
       {
           NorthwindDataContext db = new NorthwindDataContext();

           var rgdata = new Region { RegionID = 5, RegionDescription = "test" };

           try
           {
               db.Region.InsertOnSubmit(rgdata);//插入数据
               db.SubmitChanges();//提交更改
           }
           catch (Exception err)
           {
               Console.WriteLine(err.Message);
           }
           Console.Read();
       }
       #endregion

       #region Linq中update语句的使用
       public static void LinqForUpdata()
       {
           NorthwindDataContext db = new NorthwindDataContext();

           var o = from c in db.Region where c.RegionID == 5 select c;

           foreach (var item in o)
           {
               if(item.RegionID==5)
                   item.RegionDescription = "test改过了";
           }


           db.SubmitChanges();

       }
       #endregion

       #region Linq中del语句的使用
       public static void LinqForDel()
       {
           NorthwindDataContext db = new NorthwindDataContext();

           var q = from c in db.Region where c.RegionID == 5 select c;

           foreach (var item in q)
           {
               db.Region.DeleteOnSubmit(item);
           }
           db.SubmitChanges();
       }
       #endregion

#region linq中insert语句的使用 public static void LinqForInsert() { NorthwindDataContext db = new NorthwindDataContext(); var rgdata = new Region { RegionID = 5, RegionDescription = "test" }; try { db.Region.InsertOnSubmit(rgdata);//插入数据 db.SubmitChanges();//提交更改 } catch (Exception err) { Console.WriteLine(err.Message); } Console.Read(); } #endregion #region Linq中update语句的使用 public static void LinqForUpdata() { NorthwindDataContext db = new NorthwindDataContext(); var o = from c in db.Region where c.RegionID == 5 select c; foreach (var item in o) { if(item.RegionID==5) item.RegionDescription = "test改过了"; } db.SubmitChanges(); } #endregion #region Linq中del语句的使用 public static void LinqForDel() { NorthwindDataContext db = new NorthwindDataContext(); var q = from c in db.Region where c.RegionID == 5 select c; foreach (var item in q) { db.Region.DeleteOnSubmit(item); } db.SubmitChanges(); } #endregion