LINQ to SQL:创建你的第一个程序
0.参考文献
http://tech.it168.com/msoft/2007-12-21/200712211034451.shtml
1. 概述
在LINQ to SQL系列之一基础篇中,我介绍了学习LINQ to SQL的一些基础知识的准备,为了让大家对LINQ to SQL有一个直观的认识和了解,在本文中,我将以Step By Step的形式来创建一个LINQ to SQL的程序,实现基本的增删改查。
2. 环境准备
我的开发环境
A. Visual Studio 2010
B. SQL Server 2012
3. 准备数据库
第一步,我们先准备相关的数据表结构(在下篇文章中我会写到如何使用DataContext来直接创建数据库)。这里创建一个Customers数据表,它具有姓名、年龄、地址、城市、电话等这样一些字段:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CREATE TABLE [dbo].[Customers] ( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [Age] [int] NULL, [Address] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NULL, [City] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [Phone] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
4. 建立示例程序
第二步,建立Web Site。在Default.aspx界面添加GridView的控件,使其看起来如下图所示:
第三步,在website目录下添加App_Code文件夹,然后在App_Code文件夹下添加LINQ to SQL Classes类,然后添加数据库连接,如下图所示:
如上图所示,在Tables中找到我们第一步创建的Customers表到设计界面,如下图所示:
经过了上面的操作之后,在新建的LINQ to SQL类中做了什么?打开刚才所建的LINQ to SQL类设计文件(.designer.cs),可以看到,首先定义了一个DataClassesDataContext类,并为它配置了名为Database的特性,DataContext(数据上下文)类是实体类和数据库之间的一个桥梁,Database特性配置了该DataContext与哪个数据库所对应:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="TSQL2012")] public partial class DataClassesDataContext : System.Data.Linq.DataContext { private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource(); #region Extensibility Method Definitions partial void OnCreated(); partial void InsertCustomer(Customer instance); partial void UpdateCustomer(Customer instance); partial void DeleteCustomer(Customer instance); #endregion public DataClassesDataContext() : base(global::System.Configuration.ConfigurationManager.ConnectionStrings["TSQL2012ConnectionString"].ConnectionString, mappingSource) { OnCreated(); } public DataClassesDataContext(string connection) : base(connection, mappingSource) { OnCreated(); } public DataClassesDataContext(System.Data.IDbConnection connection) : base(connection, mappingSource) { OnCreated(); } public DataClassesDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base(connection, mappingSource) { OnCreated(); } public DataClassesDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base(connection, mappingSource) { OnCreated(); } public System.Data.Linq.Table<Customer> Customers { get { return this.GetTable<Customer>(); } } }
同时,还定义了一个名为Customer的实体类,该类是对数据库表的描述,通过Table特性来指定它与哪张表映射,通过Column特性来指定属性与数据库表中的字段之间的对应关系,关于DataContext(数据上下文)和实体的映射,后续的文章中我还会专门去讲述。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Customers")] public partial class Customer : INotifyPropertyChanging, INotifyPropertyChanged { private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty); private int _Id; private string _Name; private System.Nullable<int> _Age; private string _Address; private string _City; private string _Phone; #region Extensibility Method Definitions partial void OnLoaded(); partial void OnValidate(System.Data.Linq.ChangeAction action); partial void OnCreated(); partial void OnIdChanging(int value); partial void OnIdChanged(); partial void OnNameChanging(string value); partial void OnNameChanged(); partial void OnAgeChanging(System.Nullable<int> value); partial void OnAgeChanged(); partial void OnAddressChanging(string value); partial void OnAddressChanged(); partial void OnCityChanging(string value); partial void OnCityChanged(); partial void OnPhoneChanging(string value); partial void OnPhoneChanged(); #endregion public Customer() { OnCreated(); } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Id", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)] public int Id { get { return this._Id; } set { if ((this._Id != value)) { this.OnIdChanging(value); this.SendPropertyChanging(); this._Id = value; this.SendPropertyChanged("Id"); this.OnIdChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Name", DbType="NVarChar(50)")] public string Name { get { return this._Name; } set { if ((this._Name != value)) { this.OnNameChanging(value); this.SendPropertyChanging(); this._Name = value; this.SendPropertyChanged("Name"); this.OnNameChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Age", DbType="Int")] public System.Nullable<int> Age { get { return this._Age; } set { if ((this._Age != value)) { this.OnAgeChanging(value); this.SendPropertyChanging(); this._Age = value; this.SendPropertyChanged("Age"); this.OnAgeChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Address", DbType="NVarChar(200)")] public string Address { get { return this._Address; } set { if ((this._Address != value)) { this.OnAddressChanging(value); this.SendPropertyChanging(); this._Address = value; this.SendPropertyChanged("Address"); this.OnAddressChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_City", DbType="NVarChar(50)")] public string City { get { return this._City; } set { if ((this._City != value)) { this.OnCityChanging(value); this.SendPropertyChanging(); this._City = value; this.SendPropertyChanged("City"); this.OnCityChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Phone", DbType="NVarChar(50)")] public string Phone { get { return this._Phone; } set { if ((this._Phone != value)) { this.OnPhoneChanging(value); this.SendPropertyChanging(); this._Phone = value; this.SendPropertyChanged("Phone"); this.OnPhoneChanged(); } } } public event PropertyChangingEventHandler PropertyChanging; public event PropertyChangedEventHandler PropertyChanged; protected virtual void SendPropertyChanging() { if ((this.PropertyChanging != null)) { this.PropertyChanging(this, emptyChangingEventArgs); } } protected virtual void SendPropertyChanged(String propertyName) { if ((this.PropertyChanged != null)) { this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName)); } } }
5. 实现数据的查询
第四步,经过了前面的几步准备之后,就可是实现我们的查询了。先来查询Customers表中所有的记录,并绑定到GridView控件上。在Default.aspx.cs中编写如下代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { DataClassesDataContext db = new DataClassesDataContext(); IEnumerable<Customer> customers = from c in db.Customers select c; this.gv_Customer.DataSource = customers; this.gv_Customer.DataBind(); } }
打开网页如下图所示:
6. 实现带条件的查询
在上一步中,我们查询了所有的记录,接下来看一下如何进行带条件的查询,譬如说显示某一给定ID的客户详细信息。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
IEnumerable<Customer> customers = from c in db.Customers where c.Id == 1 select c;
如下图所示:
7. 实现数据的增加
在LINQ to SQL中,可以很方便的进行数据的操作,可以调用InsertOnSubmit方法,如果需要批量增加的,需要调用泛型的InsertAllOnSubmit()方法。如下所示:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { DataClassesDataContext db = new DataClassesDataContext(); Customer customer = new Customer { Name = "TerryLee", City = "TianJin", Phone = "110119114", Address = "Tianjin Nankai" }; db.Customers.InsertOnSubmit(customer); db.SubmitChanges(); IEnumerable<Customer> customers = from c in db.Customers select c; this.gv_Customer.DataSource = customers; this.gv_Customer.DataBind(); } }
在这段代码中,我们首先构造一个Customer对象,并运行对象初始化器对其进行初始化。调用InsertOnSubmit()方法来增加一条记录,并用SubmitChanges()将其持久化到数据库中。运行Code7中的代码后,可以看到在数据库中增加了一条新的记录:
8. 实现数据的修改
在LINQ to SQL中实现对数据的修改,只需要在查询出数据后,直接调用DataContext方法SubmitChanges()进行更新就可以了。如果涉及到在多个DataContext之间进行数据的更新,需要使用Attach方法(后续文章中会写到)。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { DataClassesDataContext db = new DataClassesDataContext(); Customer customer = db.Customers.Single(p => p.Id == 1); customer.Name = "zhangsan"; ; db.SubmitChanges(); IEnumerable<Customer> customers = from c in db.Customers select c; this.gv_Customer.DataSource = customers; this.gv_Customer.DataBind(); } }
9. 实现数据的删除
在LINQ to SQL中,实现数据的删除,类似与上面所讲的数据的增加,只不过调用的方法相应的变为DeleteOnSubmit()和DeleteAllOnSubmit(),这里就不再细说了,如下代码片段所示:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { DataClassesDataContext db = new DataClassesDataContext(); Customer customer = db.Customers.Single(p => p.Id == 1); db.Customers.DeleteOnSubmit(customer); db.SubmitChanges(); IEnumerable<Customer> customers = from c in db.Customers select c; this.gv_Customer.DataSource = customers; this.gv_Customer.DataBind(); } }
上述代码首先查询出Id为1的记录,并将其删除。
Code1: