vs2010 mysql linq to sql 系列_linq to sql简单使用

1.新建一个winform项目。

2.添加一个Linq to Sql 类，命名为Northwind

3.打开服务器资源管理器，将表拖动到linq to sql 类，实体类就由Vs生成了

4.实例化DataContext类

NorthwindDataContext dc = new NorthwindDataContext();

5.下面就可以使用Linq to Sql啦。

a.查询Employees所有数据

var ee =

from employee in dc.Employees

select employee;

b.加个Where条件，查询Employee表所有City=London

var e2 =

from employee indc.Employeeswhere employee.City == "London"

select employee;

c.查询EmployeeID<5的数据

var e3 =

from employee indc.Employeeswhere employee.EmployeeID < 5

select employee;

d.查询雇佣日期在1993年1月1日以前的人员。

var e4 =

from employee indc.Employeeswhere employee.HireDate < new DateTime(1993, 1, 1)select employee;

e.多个查询条件

var e5 =

from employee indc.Employeeswhere employee.HireDate < new DateTime(1993, 1, 1)where employee.EmployeeID < 3

select employee;

f.like语句

var e6 =

from employee indc.Employeeswhere SqlMethods.Like(employee.FirstName, "%a%")//where employee.FirstName.Contains("a")//等价于 like '%a%'//where employee.FirstName.StartsWith("a")//等价于 like 'a%'//where employee.FirstName.EndsWith("a")//等价于 like '%a'

select employee;

g.查询指定的列

var e7 =(from employee indc.Employees//select new { employee.EmployeeID, employee.FirstName };

select new{

员工编号=employee.EmployeeID,

员工姓名=employee.FirstName,

尊称=employee.TitleOfCourtesy

});

h.子查询

var e8 =

from employee indc.Employeeswhere employee.Orders.Count > 100

select employee;

i.内连接

/**

* 查询位于伦敦的客户的订单

* SELECT *

* FROM [dbo].[Orders]

* INNER JOIN [dbo].[Customers] ON [Customers].[CustomerID] = [Orders].[CustomerID]

* WHERE [Customers].[City] = 'London'*/

var e9 =

from cus indc.Customers

join ordindc.Orders on cus.CustomerID equals ord.CustomerIDwhere cus.City == "London"

select ord;

j.增加

Employees m = newEmployees();

m.LastName= "闪硕";

m.FirstName= "燕";

m.Title= "闪硕标题";

dc.Employees.InsertOnSubmit(m);

dc.SubmitChanges();

k.修改

var e10 = dc.Employees.FirstOrDefault(employee => employee.LastName == "闪硕");

e10.City= "石家庄";

dc.SubmitChanges();

l.删除

var e10 = dc.Employees.FirstOrDefault(employee => employee.LastName == "闪硕");

dc.Employees.DeleteOnSubmit(e10);

dc.SubmitChanges();

练习代码使用Vs2012，微软northwnd示例数据库