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();