学习Linq To Sql

Linq To Sql 是一个非常神奇的东西

　　他可以让你把精力都放在业务逻辑上面,而用同一个语句操作多种关系型数据库

下面就让我们来看看他的用法..........

　　数据库字段

use master
go
create database Learn_Linq_to_Sql
go
use Learn_Linq_to_Sql
go
create table Class
(
    CId int primary key  identity(1,1),
    CName nvarchar(20) not null
)
go
create table Student
(
    Id int primary key identity(1,1),
    Name nvarchar(10) not null,
    ShuXue int ,
    Yuwen int,
    CId int foreign key references Class(CId)
)
go
insert into Class values
('122'),
('123'),
('124'),
('125')
go
insert into Student values
('小白',67,78,1),
('小菜',34,15,2),
('小芳',32,87,4),
('小李',36,59,3),
('小张',56,69,3),
('小天',45,63,3),
('小杨',78,87,2),
('小青',77,81,1),
('小黄',76,74,4),
('小吴',98,53,1)

　　有了数据库以后我们就需要在项目中创建一个Linq to Sql

　　生成的实体导航属性如下:

可以看到编译器自动帮我们生产了类

然后我们就可以尝试者来使用了

　　

 实现查询....

 除此之外还可以使用CLR的一些功能

            #region Linq 连接
            //var clas = dc.GetTable<Class>();
            //var student = dc.GetTable<Student>();
            //var result = from cl in clas
            //             join s in student on cl.CId equals s.CId into temp
            //             from t in temp.DefaultIfEmpty()
            //             select new {CName=cl.CName,Name=t.Name };
            //foreach (var item in result)
            //{
            //    Console.WriteLine("班级:"+item.CName);
            //    Console.WriteLine(item.Name);
            //}
            #endregion
            #region 关系
            //var result = dc1.GetTable<Class>();
            //foreach (Class cl in result)
            //{
            //    Console.WriteLine("班级:" + cl.CName);
            //    foreach (Student item in cl.Student)
            //    {
            //        Console.WriteLine("	" + item.Name);
            //    }
            //}
            #endregion
            #region 设置加载参数
            //DataLoadOptions ds = new DataLoadOptions();
            //ds.LoadWith<Class>(d =>d.Student);//指定加载
            //dc1.LoadOptions = ds;
            //var result = dc1.GetTable<Class>();
            //foreach (Class cl in result)
            //{
            //    Console.WriteLine("班级:" + cl.CName);
            //    foreach (Student item in cl.Student)
            //    {
            //        Console.WriteLine("	" + item.Name);
            //    }
            //}
            #endregion
            #region 修改
            //var result = dc1.GetTable<Student>();
            //foreach (var item in result)
            //{
            //    item.Yuwen -= 5;
            //}
            //dc1.SubmitChanges();
            #endregion
            #region 删除和添加
            //var students = dc1.GetTable<Student>();
            ////Student s = new Student();
            ////s.Yuwen = 88;
            ////s.ShuXue = 79;
            ////s.Name = "我是sb";
            ////s.CId = 2;
            ////students.InsertOnSubmit(s);
            ////dc1.SubmitChanges();
            //students.DeleteOnSubmit(students.Where(d => d.Name == "我是sb").FirstOrDefault());
            //dc1.SubmitChanges();
            #endregion