配置LINQ TO SQL
首先添加一个Linq to sql文件,以.dbml结尾的文件。无法把表拖拽到.dbml文件中,提示“所选对象使用不支持的数据提供程序”
解决方案
在服务器资源管理器中右键单击连接,选择"修改连接",将数据源由"用于 OLE DB 的 .NET Framework 数据提供程序"
修改为"用于 SQL Server 的 .NET Framework 数据提供程序"即可:
var result = from c in db.Categorys select c 被编译器编译为:
Table<Category> table = db.GetTable<Category>();
var result = from c in table select c;
示例:
添加一个实体类Student.cs
添加命名空间:using System.Data.Linq.Mapping;
[Table(Name = "Student")]
public class Student
{
private int stu_Id;
[Column(IsPrimaryKey=true)]
public int Stu_Id
{
get { return stu_Id; }
set { stu_Id = value; }
}
private string stu_Name;
[Column]
public string Stu_Name
{
get { return stu_Name; }
set { stu_Name = value; }
}
private int cla_Id;
[Column]
public int Cla_Id
{
get { return cla_Id; }
set { cla_Id = value; }
}
}
添加WebForm1.aspx
添加命名空间:using System.Data.Linq;
protected void Page_Load(object sender, EventArgs e)
{
DataContext db = new DataContext("Data Source=.;Initial Catalog=Test;Integrated Security=SSPI;");
Table<Student> student= db.GetTable<Student>();
//var result = student.Where(s => s.Stu_Name == "zhangsan").Select(s => s);
var result = student.Select(s => s);
foreach (Student stu in result)
{
Response.Write("学号:" + stu.Stu_Id + " -- " + "姓名:" + stu.Stu_Name +"</br>");
}
}
显示:
二、连接数据库不在使用DataContext,而是创建一个类MSPetShop,然后继承DataContext
public MSPetShop(string connection) :base(connection);
三、表实体与数据库表映射
这是 Category实体类
这是Product实体类
3.1 在实体类中添加映射关系
这是Category实体类
这是Product实体类
3.2 两表联合查询
3.3 添加或修改数据
上图为修改数据
上图增加新增数据
示例:
创建一个实体 Class.cs 文件
[Table(Name = "Class")]
public class Class
{
private int class_Id; //班级号
[Column(IsPrimaryKey=true)]
public int Class_Id
{
get { return class_Id; }
set { class_Id = value; }
}
private string class_Name; //班级名称
[Column]
public string Class_Name
{
get { return class_Name; }
set { class_Name = value; }
} private EntitySet<Student> students;
[Association(Storage = "students",OtherKey = "Class_Id")]
public EntitySet<Student> Students
{
get { return this.students; }
set { this.students.Assign(value); }
}
}
添加实体类Student.cs
[Table(Name = "Student")]
public class Student
{
private int stu_Id; //学号
[Column(IsPrimaryKey=true)]
public int Stu_Id
{
get { return stu_Id; }
set { stu_Id = value; }
}
private string stu_Name; //学生姓名
[Column]
public string Stu_Name
{
get { return stu_Name; }
set { stu_Name = value; }
}
private int cla_Id; //班级号(外键)
[Column]
public int Cla_Id
{
get { return cla_Id; }
set { cla_Id = value; }
} private EntityRef<Class> classes;
[Association(Storage = "classes",ThisKey="Class_Id")]
public Class Classes
{
get { return this.classes.Entity; }
set { this.classes.Entity = value; }
}
}
添加Linq to sql文件,以.dbml结尾的文件
添加一个类MyDBContext.cs文件
public class MyDBContext :DataContext
{
public Table<Class> Class;
public Table<Student> Student;
public MyDBContext(string connection) : base(connection) { }
}
添加WebForm1.aspx
protected void Page_Load(object sender, EventArgs e)
{
MyDBContext db = new MyDBContext("Data Source=.;Initial Catalog=Test;Integrated Security=SSPI;");
var result = from c in db.Student
from s in c.Class
select s; foreach (var item in result)
{
Response.Write("学号:" + item.Student.Stu_Id + " -- " + "姓名:" + item.Student.Stu_Name + " -- " + "班级:" + item.Class_Name + "</br>");
}
}
结果: