第8章 LINQ to SQL.docx
《第8章 LINQ to SQL.docx》由会员分享,可在线阅读,更多相关《第8章 LINQ to SQL.docx(18页珍藏版)》请在冰豆网上搜索。
第8章LINQtoSQL
第8章LINQtoSQL
上一章已经介绍了LINQ语言的基础,这一章将重点讲述利用LINQ访问SQLServer数据库的问题。
同一种数据,例如工厂产品的规格、名称、数量等既能用自定义的方式表示,也可以用数据库来表示。
它们表示的对象相同,但处理方式却大不相同。
用程序语言(如C#、Java等)处理数据时,常首先将数据抽象成类和对象,以达到“安全”、“重用”的目的;而数据库是用表格、行、列和关系来表示数据的。
用LINQ技术来处理数据库中数据的方法就是,先将数据库映射成类和对象。
映射的工具有两种:
SQLMetal和LINQtoSQL.其中SQLMetal是一个命令行工具,而LINQtoSQL是一个O/R可视化设计器.下面将重点介绍后者。
8.1将数据库映射成类和对象
对象/关系设计器(O/R设计器)提供了一个可视化的设计界面,用于建立数据库与实体类之间的映射。
就是说,通过O/R设计器可以在应用程序中创建数据库的对象模型。
目前,O/R设计器只支持对SQLServer2000、SQLServer2005、SQLServer2008和SQLServerExpress数据库的映射。
8.1.1映射的过程
现在结合微软提供的Northwind样板数据库,来讲解使用O/R设计器的方法:
1.在网站的Add_Data目录下将Northwind.mdf数据库调过来;
2.在“添加新项”的窗口中选择“LINQtoSQL类”,并在打开的新窗口中,修改文件名(这里改为“Northwind.dbml”),语言选“VisualC#”。
最后在系统的提示下将映射的类放在App_Code目录下。
此时将打开一个可视化化窗口;
3.窗口分为左、右两部分。
左边可放入数据表及它们之间的关系;右边(按鼠标右键后在“隐藏/显示”之间切换)放入存储过程。
现在打开“数据库资源管理器”,将多个数据表拖到窗口中来,然后将若干存储过程拖入右边窗口。
其情况如图所示;
界面中的方框就代表数据表映射后的实体类,数据库中的字段就映射成实体类中的属性。
实体类之间的连线(associations)代表数据表之间的关系(relationships)。
箭头代表“一对多”的同步关系,箭头的起始端代表“一”端,箭头的终点代表“多”端。
4.通过编译完成映射工作。
8.1.2映射中的对应关系
数据库与实体类之间的映射关系是:
1)每张数据表(Table)映射成一个实体类(Entityclass);
2)数据表中的字段(Column)映射成类中的属性(Classmember);
3)数据库的关系(relationships)映射成类的关联(Association);
4)存储过程(StoredProcedure)映射成类中的方法(Method);
5)除此而外,系统还为数据库生成了一个DataContext类(这里的名字为NorthwindDataContext类),这个类像一座桥梁架设在数据库与实体类之间,具体作用包括以下几个方面:
⏹连接数据库;
⏹从数据库读取数据;
⏹向数据库提交编辑的结果。
[注]:
目前O/R设计器是一个简单的对象关系映射器,它仅支持1:
1的映射关系,不支持复杂映射(例如,将实体类映射到联接表)。
此外,该设计器还是一个单向代码生成器。
这表示代码文件中只反映对设计器视图所做的更改。
在映射过程中可以修改类的属性,其属性窗口如图所示:
属性窗口的上半部是类成员的属性,下半部是数据库成员的属性。
例如:
⏹上面的“名称”是类中的属性名,下面的“源”是对应于数据库中的字段名。
如果将“名称”改用汉字时,网页中将用汉字显示相应的字段名。
⏹如果将属性的“延迟加载”设为true时,该字段只有在以后使用时再加载。
一些容量大的成员(如较大的图片),可以利用这个属性缩短打开网页的时间。
⏹在视图中右击类的属性(字段)后选择“删除”,可以去掉一些应用程序中不用的字段(在数据库中字段并没有消失)。
对于映射的类可以增添“关系”、“继承”等。
。
。
8.1.3映射后的部分代码
打开Northwind.designer.cs设计文件,就可以看到映射后各实体类的代码。
以Products数据表为例,它已经映射成以下的类:
[Table(Name="dbo.Products")]
publicpartialclassProducts:
…
{…}
字段如ProductID、ProductName都已经映射成以下的属性:
privateint_ProductID;
privatestring_ProductName;
publicintProductID
{
get
{
returnthis._ProductID;
}
set
{
if((this._ProductID!
=value))
{
…
}
}
}
[Column(Storage="_ProductName",DbType="NVarChar(40)NOTNULL",CanBeNull=false)]
publicstringProductName
{
get
{
returnthis._ProductName;
}
set
{
if((this._ProductName!
=value))
{
…
}
}
}
8.2数据库显示和查询
8.2.1显示数据表
下面我们将结合GridView控件(或其他控件),用编写LINQ代码的方法来显示数据表中的数据。
如:
protectedvoidPage_Load(objectsender,EventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
varproduct=frompindb.Products
selectnew
{
p.CategoryID,
p.ProductID,
p.ProductName,
p.QuantityPerUnit,
p.UnitPrice
};
GridView1.DataSource=product;
GridView1.DataBind();
}
上述代码可以显示Products数据表中的4个字段。
另一种方法是使用LinqDataSource控件。
LinqDataSource控件提供了一种连接到多种数据源的方法,包括数据库数据、数据源类和内存中集合。
通过使用该控件,可以对这些数据源完成数据库检索(选择、筛选、分组和排序)、编辑(更新、删除和插入)等项任务。
下面我们讲述利用LINQ数据源控件访问数据库的方法。
1.在网站中放入Northwind数据库,和GridView1控件,在控件的属性窗口中可以设置各种属性(如分页等);
2.右击GridView1右上角标签选择LINQ数据源控件,打开“配置数据源”窗口。
情况如图所示;
3.按“下一步”按钮后,选择数据表按“完成”按钮;
4.在GridView1的编辑列中选择“自动生成字段”的复选框;
5.打开LINQDataSource的属性窗口,双击Selecting事件,然后编写LINQ代码:
protectedvoidLinqDataSource1_Selecting(objectsender,LinqDataSourceSelectEventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
varproduct=frompindb.Products
selectnew
{
p.CategoryID,
p.ProductID,
p.ProductName,
p.QuantityPerUnit,
p.UnitPrice
};
e.Result=product;
}
这段代码与上面那段代码基本相同,区别只是最后用e.Result=r;结尾,不另设置与数据的连接和绑定。
另外注意这段代码要写在LinqDataSource1_Selecting事件中。
8.2.2简单查询
下面代码的任务是显示单价(UnitPrice)小于20的产品(只显示4个字段)。
protectedvoidLinqDataSource1_Selecting(objectsender,LinqDataSourceSelectEventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
varr=frompindb.Products
wherep.UnitPrice>20
selectnew
{
p.CategoryID,
p.ProductName,
p.ProductID,
p.UnitPrice,
};
e.Result=r;
}
8.2.3分组显示
protectedvoidButton2_Click(objectsender,EventArgse)
{
varq=frompindb.Products
grouppbyp.CategoryIDintog
//按CategoryID进行分组
selectnew
{
Category=g.Key,
Sum=g.Sum(p=>p.UnitPrice),//显示每组的总金额
MinPrice=g.Min(p=>p.UnitPrice),//显示每组中的最低价
MaxPrice=g.Max(p=>p.UnitPrice)//显示每组中的最高价
};
GridView1.DataSource=q;
GridView1.DataBind();
}
8.2.4跨数据表查询
如果数据表之间已经建立了关系,则可以跨数据表进行查询,将多个数据表中的相关字段显示在同一张表格中。
例如产品类型表(Categories)与产品表(Products)已经建立了“一对多”的同步关系,现在想要将“类型名”与相关的“产品名”显示到同一张表格中。
由于类型名与产品名分别放在不同的数据表中,因此应该使用跨数据表的查询方法。
其代码如下:
protectedvoidButton2_Click(objectsender,EventArgse)
{
varq=fromcindb.Categories
frompinc.Products
wherec.CategoryID==int.Parse(DropDownList1.SelectedValue)
selectnew{c.CategoryName,p.ProductName};
GridView1.DataSource=q;
GridView1.DataBind();
}
代码中用下拉控件中选择项的值(SelectedValue)作为条件,显示“类型名”与对应的“产品名”。
上述代码也可以改写如下。
protectedvoidButton1_Click(objectsender,EventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
varq=fromcindb.Products
wherec.Categories.CategoryID==int.Parse(DropDownList1.SelectedValue)
selectnew{c.Categories.CategoryName,c.ProductName};
GridView1.DataSource=q;
GridView1.DataBind();
}
两者代码不同在什么地方?
在后面这段代码中:
fromcindb.Products
c进入同步的“多”方,通过它可以直接找到“一”方的数据表(类),并调用该表的字段(属性);与此相反,从“多”方不能直接找到“一”方的数据表,只能通过另一个from语句进行设置。
8.3编辑数据库
数据库编辑包括更新(updating)、插入(inserting)、删除(deleting)三方面。
在更新和删除中都需要先定位,再执行编辑操作。
编辑工作都是先在映射的类中进行,完成后再将结果用SubmitChanges()方法送回数据库。
如果没有最后这一句,数据库中的数据都不会改动。
下面将围绕Northwind数据库来讲解数据库的编辑方法。
网页的界面如图所示:
8.3.1更新数据表(Updating)
代码如下:
protectedvoidButton2_Click(objectsender,EventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
varq=frompindb.Products
wherep.产品ID==int.Parse(TextBox8.Text)
selectp;
//先定位需要修改的纪录。
foreach(varcinq)
{
c.单价=decimal.Parse(TextBox3.Text);
}
db.SubmitChanges();
//返回数据库修改
}
可以改用另一种更加简洁的形式,将取得同样的效果。
代码如下:
protectedvoidButton2_Click(objectsender,EventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
ProductsupdatePrice=db.Products.Single(m=>m.产品ID==int.Parse(TextBox8.Text));
//先定位需要修改的纪录。
updatePrice.单价=decimal.Parse(TextBox3.Text);
//指定字段修改的值
db.SubmitChanges();
//返回数据库修改
}
8.3.2插入新记录(Inserting)
增添纪录时需首先创建一条新记录(对象),包括数据表中不能为null的所有字段;然后再将这个对象作为属性成员添加到原有的类中,最后增加到数据库中。
代码如下:
protectedvoidButton3_Click(objectsender,EventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
Productspp=newProducts{类型ID=int.Parse(TextBox1.Text),产品名=TextBox4.Text};
//先生成一条新纪录
db.Products.InsertOnSubmit(pp);
//将新纪录加入到类中
db.SubmitChanges();
//将新纪录增加到数据库中
}
}
8.3.3删除记录(Deleting)
删除记录前必须定位,找到准备删除的记录,然后再执行删除操作。
代码如下:
protectedvoidButton4_Click(objectsender,EventArgse)
{
varq=frompindb.Products
wherep.产品ID==int.Parse(TextBox8.Text)
selectp;
foreach(varcinq)
{
db.Products.DeleteOnSubmit(c);
}
db.SubmitChanges();
}
也可以采用另一种更加简洁的形式,代码如下:
protectedvoidButton4_Click(objectsender,EventArgse)
{
ProductsDD=db.Products.Single(m=>m.产品ID==int.Parse(TextBox8.Text));
db.Products.DeleteOnSubmit(DD);
db.SubmitChanges();
}
8.4调用存储过程(CallStoredProcedure)
下面用一个示例来说明在LINQtoSQL中调用存储过程的方法。
假定在O/R设计视图中放入一个存储过程,名为:
Sales_by_Year。
原来的存储过程的代码如下:
ALTERprocedure"SalesbyYear"
@Beginning_DateDateTime,@Ending_DateDateTimeAS
SELECTOrders.ShippedDate,Orders.OrderID,"OrderSubtotals".Subtotal,DATENAME(yy,ShippedDate)ASYear
FROMOrdersINNERJOIN"OrderSubtotals"ONOrders.OrderID="OrderSubtotals".OrderID
WHEREOrders.ShippedDateBetween@Beginning_DateAnd@Ending_Date
从代码可以看出,这个存储过程将返回在指定时间范围内的订单情况。
经过O/R设计视图的映射,存储过程变成为方法,名字与存储过程相同。
为调用这个方法,在网页界面上先放两个TextBox控件,分别用于输入时间和截止时间。
另外放一个按钮,其Click事件的代码如下:
protectedvoidButton1_Click(objectsender,EventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
varorder=db.Sales_by_Year(DateTime.Parse(TextBox1.Text),DateTime.Parse(TextBox8.Text));
//调用方法
GridView1.DataSource=order;
GridView1.DataBind();
}
8.5数据分析示例
下面列举几个常用的应用示例,来说明LINQ用于管理和数据分析的方法。
示例中准备使用Wizard控件将多种分析综合到一个网页中。
使用的界面如下图所示:
8.5.1销售分析
1.在一定的时间范围内,统计按月销售的金额
界面中先放入两个TextBox以便输入统计的日期范围,格式为:
月/日/年。
再放入一个按钮,其Click事件的代码如下:
protectedvoidButton1_Click(objectsender,EventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
varq=frompindb.Order_Details
wherep.Orders.OrderDate>=DateTime.Parse(TextBox1.Text)&&
p.Orders.OrderDate<=DateTime.Parse(TextBox8.Text)
grouppbyp.Orders.OrderDate.Value.Monthintog
orderbyg.Sum(x=>x.Quantity*x.UnitPrice)descending
selectnew
{
月份=g.Key,
金额=g.Sum(x=>x.Quantity*x.UnitPrice)
};
GridView1.DataSource=q;
GridView1.DataBind();
}
8.5.2对产品销路的分析
1.在一定时间范围内,统计各产品的销售情况,按照销售量的降序排列。
界面中先放入两个TextBox以便输入统计的日期范围,格式为:
月/日/年。
再放入一个按钮,其Click事件的代码如下:
protectedvoidButton1_Click(objectsender,EventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
varq=frompindb.Order_Details
wherep.Orders.OrderDate>=DateTime.Parse(TextBox1.Text)&&
p.Orders.OrderDate<=DateTime.Parse(TextBox8.Text)
grouppbyp.Products.ProductNameintog
orderbyg.Sum(x=>x.Quantity*x.UnitPrice)descending
selectnew
{
产品名=g.Key,
金额=g.Sum(x=>x.Quantity*x.UnitPrice)
};
GridView1.DataSource=q;
GridView1.DataBind();
}
9.5.3职工管理
1.职工的业绩比较
在这里职工业绩用该职工经手的业务金额来很衡量。
其代码如下:
protectedvoidButton1_Click(objectsender,EventArgse)
{
NorthwindDataContextdb=newNorthwindDataContext();
varq=frompindb.Order_Details
wherep.Orders.OrderDate>=DateTime.Parse(TextBox1.Text)&&
p.Orders.OrderDate<=DateTime.Parse(TextBox8.Text)
grouppbyp.Orders.EmployeeIDintog
orderbyg.Sum(x=>x.Quantity*x.UnitPrice)descending
selectnew
{
职工标志=g.Key,
金额=g.Sum(x=>x.Quantity*x.UnitPrice)
};
GridView1.DataSource