Linq To Sql

Linq To Sql

Linq To Sql是Linq技术的核心内容，通过它能做到很多意想不到的事情。接下来咱就一一将这些有意思的把戏给写出来。

接下来咱们的案例都将是针对下面这3张表展开的，这里我假设这3张表都存放在数据库Student中。

Student（

ID long primary,

Name NVarChar(10),

Age tinyint

）

Teacher(

ID long primary,

Name NvarChar(10)

Age tinyint

)

Class(

StudentID int,

TeacherID int

Primary key(StudentID,TeacherID)

)

 

案例1如何将类与数据库中的标关联起来

[Table(Name=”Student”)] Public class Student { Private long _ID; Private string _Name; Private byte _Age; [Column(Name=”ID”,isPrimaryKey=true,Storage=”_ID”,isDbGenerated=true)] Public long ID { Get{return _ID;} Set{_ID=value;} } [Column(Name=”Name”,Storage=”_Name”,DbType=”NvarChar(10)”)] Public string Name { Get{return _Name;} Set{_Name=value;} } [Column(Name=”Age”,Storage=”_Age”,DbType=”tinyint”)] Public byte Age { Get{return _Name;} Set{_Name=value;} } } [Table(Name=”Teacher”)] Public class Student { Private long _ID; Private string _Name; Private byte _Age; [Column(Name=”ID”,isPrimaryKey=true,Storage=”_ID”,isDbGenerated=true)] Public long ID { Get{return _ID;} Set{_ID=value;} } [Column(Name=”Name”,Storage=”_Name”,DbType=”NvarChar(10)”)] Public string Name { Get{return _Name;} Set{_Name=value;} } [Column(Name=”Age”,Storage=”_Age”,DbType=”tinyint”)] Public byte Age { Get{return _Name;} Set{_Name=value;} } } [Table(Name=”Class”)] Public class Class { Private long _StudentID; Private long _TeacherID; [Column(Name=”ID”,isPrimaryKey=true,Storage=”_StudentID”)] Public long StudentID { Get{return _ID;} Set{_ID=value;} } [Column(Name=”ID”,isPrimaryKey=true,Storage=”_TeacherID”)] Public long StudentID { Get{return _ID;} Set{_ID=value;} } }

案例2如何将1对多，或多对多关系映射到类中。

多对多关系可以拆分为1对多关系,如下面这个多对多关系，可以拆分为：

Student—》MyClass，Teacher—》MyClass这两个1对多关系。

Student

MyClass

Teacher

它们在Linq中的实现代码如下：

  [Table(Name="Student")] public class Student { private EntitySet<MyClass> _Class; [Association(Name="S_C",Storage="_Class",OtherKey="StudentID")] public EntitySet<MyClass> MyClass { get { return _Class; } set { _Class.Assign(value); } } } [Table(Name = "Teacher")] public class Teacher { private EntitySet<MyClass> _Class; [Association(Name = "T_C", Storage = "_Class", OtherKey = "TeacherID")] public EntitySet<MyClass> MyClass { get { return _Class; } set { _Class.Assign(value); } } } // [Table(Name = "Class")] public class MyClass { private EntityRef<Student> _Student; private EntityRef<Teacher> _Teacher; [Association(Name="S_C",Storage="_Student",ThisKey="StudentID")] public Student Student { get { return _Student.Entity; } set { _Student.Entity = value; } } [Association(Name = "T_C", Storage = "_Teacher", ThisKey = "TeacherID")] public Teacher Teacher { get { return _Teacher.Entity; } set { _Teacher.Entity = value; } } }   

案例3如何将存储过程映射到类里面

public class StudentDB : DataContext { public Table<Student> Student; public Table<Teacher> Teacher; public Table<MyClass> Class; public StudentDB(string connectionString) : base(connectionString) { } public void CreateDataBase() { base.CreateDatabase(); } public void GetStudent() { Student = base.GetTable<Student>(); } public void GetTeacher() { Teacher = base.GetTable<Teacher>(); } public void GetClass() { Class = base.GetTable<MyClass>(); } 这个映射的存储过程返回的是结果集 [Function(Name="GetAllStudent")] public ISingleResult<Student> GetAllStudent() { IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod()); return (ISingleResult<Student>)result.ReturnValue; } 这个映射的存储过程返回的是output参数 [Function(Name="GetAllStudentCount")] [return:Parameter(DbType="bigint")] Public long GetAllStudentCount([Parameter(Name="Count",DbType="bigint")] ref long count) { IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(),count); count=long.Parse(result.GetParameterValue(0).ToString()); return count; } 这个映射的存储过程可能返回为两个类型的结果集合 [Function(Name="GetStudentOrTeacher")] [ResultType(typeof(Student))] [ResultType(typeof(Teacher))] public IMultipleResults GetStudentOrTeacher([Parameter(Name="Type",DbType="tinyint")] byte type) { IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(),type); return (IMultipleResults)result.ReturnValue; } 这个映射的存储过程顺序返回多个结果集合 可惜没能实现，只能得到第1个结果集合的内容 [Function(Name="GetAllInfo")] [ResultType(typeof(Student))] [ResultType(typeof(Teacher))] [ResultType(typeof(MyClass))] public IMultipleResults GetAllInfo() { IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod()); return (IMultipleResults)result.ReturnValue; } }   

案例4如果通过Linq To Sql来向数据库检索信息

StudentDB db=new StudentDB(“***”); Table<Student> student=db.GetTable<Student>(); Table<Teacher> teacher=db.GetTable< Teacher >(); Table<MyClass> myclass=db.GetTable< MyClass >(); Var result=from t in teacher Join c in myclass on t.ID equals c.TeacherID Join s in student on c.StudentID equals s.ID Where t.Name==”苍井空” Select s

呵呵，这个sql语句的意思你懂的。。。

 

案例5通过Linq To Sql想数据库添加新信息

 StudentDB db=new StudentDB(“***”); Student s=new Student{ Name=”李孝丽”, Age=26 } Db.GetTable<Student>().InsertOnSubmit(s); Db.SubmitChanges();

案例6通过Linq To Sql向数据库更新或删除某条信息

StudentDB db=new StudentDB(“***”); //删除名字为苍井空的教师 Db.GetTable<Teacher>().Where(item=>item.Name==”苍井空”) .First().DeleteOnSubmit(); //将名字为河秀丽的教师的年龄更改为28 Db.GetTable<Teacher>().Where(item=>item.Name==”河秀丽”) .First().Age=28; //向数据库提交更改信息 Db.SubmitChanges();