2 LINQ TO SQL(代码下载)
我们以一个简单的销售的业务数据库为例子
表结构很简单：Users（购买者（用户）表），Products（产品信息表），Sales（销售表），ReturnSales（退货信息表）
在Sales表中，我们就知道谁买的了（根据UserId），然后买的时间，买的产品（根据ProductCode），Sales和ReturnSales一样的结构的
接下来我们按照 小孩LINQ（四）中的方法建立好dbml文件，以便好操作数据。
准备工作后完成后如下：
附一（数据库代码）：
USE [master]
GO
/****** Object: Database [SalesDB] Script Date: 04/08/2013 21:44:07 ******/
CREATE DATABASE [SalesDB] ON PRIMARY
( NAME = N'SalesDB', FILENAME = N'D:\linq\linq_Ch5\DB\SalesDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'SalesDB_log', FILENAME = N'D:\linq\linq_Ch5\DB\SalesDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [SalesDB] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SalesDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [SalesDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SalesDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SalesDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SalesDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SalesDB] SET ARITHABORT OFF
GO
ALTER DATABASE [SalesDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SalesDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SalesDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SalesDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SalesDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SalesDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [SalesDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SalesDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SalesDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SalesDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SalesDB] SET DISABLE_BROKER
GO
ALTER DATABASE [SalesDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SalesDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SalesDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SalesDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SalesDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SalesDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [SalesDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [SalesDB] SET READ_WRITE
GO
ALTER DATABASE [SalesDB] SET RECOVERY FULL
GO
ALTER DATABASE [SalesDB] SET MULTI_USER
GO
ALTER DATABASE [SalesDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SalesDB] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'SalesDB', N'ON'
GO
USE [SalesDB]
GO
/****** Object: Table [dbo].[Users] Script Date: 04/08/2013 21:44:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Sales] Script Date: 04/08/2013 21:44:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sales](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductCode] [nvarchar](50) NOT NULL,
[Quantity] [int] NOT NULL,
[UserId] [int] NOT NULL,
[CreateDate] [datetime] NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ReturnSales] Script Date: 04/08/2013 21:44:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ReturnSales](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductCode] [nvarchar](50) NOT NULL,
[Quantity] [int] NOT NULL,
[UserId] [int] NOT NULL,
[CreateDate] [datetime] NOT NULL,
CONSTRAINT [PK_ReturnSales] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Products] Script Date: 04/08/2013 21:44:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductCode] [nvarchar](50) NOT NULL,
[ProductName] [nvarchar](50) NOT NULL,
[ProductUnitPrice] [decimal](18, 2) NOT NULL,
[ProductType] [smallint] NOT NULL,
[ProductDescription] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Default [DF_Sales_CreateDate] Script Date: 04/08/2013 21:44:09 ******/
ALTER TABLE [dbo].[Sales] ADD CONSTRAINT [DF_Sales_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO
/****** Object: Default [DF_ReturnSales_CreateDate] Script Date: 04/08/2013 21:44:09 ******/
ALTER TABLE [dbo].[ReturnSales] ADD CONSTRAINT [DF_ReturnSales_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO
/****** Object: Default [DF_Products_ProductName] Script Date: 04/08/2013 21:44:09 ******/
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [DF_Products_ProductName] DEFAULT ('') FOR [ProductName]
GO
2.1 检测数据库是否存在
代码如下：
1: string conStr = @"Data Source=.;Initial Catalog=SalesDB;Integrated Security=True"; //复制于app.config文件中的
2: //1.检测数据库是否存在
3: SalesDBDataContext db = new SalesDBDataContext(conStr);
4: if (db.DatabaseExists())
5: {
6: Console.WriteLine("数据库存在！");
7: }
8: else {
9: Console.WriteLine("可惜啊，数据库不存在！");
10: }
效果图：
2.2 插入数据（我们创建后面例子的基础）
为了不影响后面的代码，我们将2.1例子的代码注释掉，然后项目中新建一个Enum文件夹
①向Products表中插入几条数据，然后Users表中创建几个用户，然后添加几条Sales信息和ReturnSales信息
我们使用 InsertOnSubmit 方法
产品类型枚举（ProductTypeEnum.cs）如下：
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Text;
5:
6: namespace linq_Ch5.Enum
7: {
8: public enum ProductTypeEnum
9: {
10: HuaZhuang=1,
11: Books=2,
12: Computers=3,
13: Phones=4,
14: Bags=5,
15: Clothes=6
16: }
17: }
Program.cs代码如下（先Products产品）：
1: SalesDBDataContext db = new SalesDBDataContext();
2: Products pro1 = new Products
3: {
4: ProductName = "相宜本草男士专用洗面奶",
5: ProductCode = "XYBCNSZYXMN",
6: ProductType = (int)ProductTypeEnum.HuaZhuang,
7: ProductDescription = "祛痘效果很好",
8: ProductUnitPrice = 39.9M
9: };
10: Products pro2 = new Products
11: {
12: ProductName = "相宜本草男士专用爽肤水",
13: ProductCode = "XYBCNSZYSFS",
14: ProductType = (int)ProductTypeEnum.HuaZhuang,
15: ProductDescription = "去除倦容",
16: ProductUnitPrice = 80.0M
17: };
18: Products pro3 = new Products
19: {
20: ProductName = "Windows Phone7 程序设计",
21: ProductCode = "WP7CXSJ",
22: ProductType = (int)ProductTypeEnum.Books,
23: ProductDescription = "wp7开发必备",
24: ProductUnitPrice = 99.00M
25: };
26: Products pro4 = new Products
27: {
28: ProductName = "ASP.NET MVC4 IN ACTION",
29: ProductCode = "ANMVC4IA",
30: ProductType = (int)ProductTypeEnum.Books,
31: ProductDescription = "最新的ASP.NET MVC入门资料",
32: ProductUnitPrice = 198.00M
33: };
34: Products pro5 = new Products
35: {
36: ProductName = "韩国最新迷你小夹克",
37: ProductCode = "HGZXMNXJK",
38: ProductType = (int)ProductTypeEnum.Clothes,
39: ProductDescription = "男士修身的",
40: ProductUnitPrice = 298.00M
41: };
42: Products pro6 = new Products
43: {
44: ProductName = "达普斯女生气质包包",
45: ProductCode = "DPSNSQZBB",
46: ProductType = (int)ProductTypeEnum.Bags,
47: ProductDescription = "目前只有海蓝色的",
48: ProductUnitPrice = 98.00M
49: };
50: Products pro7 = new Products
51: {
52: ProductName = "海澜之家新款A8484",
53: ProductCode = "HNZJA8484",
54: ProductType = (int)ProductTypeEnum.Clothes,
55: ProductDescription = "最显男人气质的衣服",
56: ProductUnitPrice = 498.00M
57: };
58: Products pro8 = new Products
59: {
60: ProductName = "Iphone5 国行版",
61: ProductCode = "IPHONE5",
62: ProductType = (int)ProductTypeEnum.Phones,
63: ProductDescription = "美版的4688人民币",
64: ProductUnitPrice = 5498.00M
65: };
66: List<Products> products = new List<Products> {
67: pro1,
68: pro2,
69: pro3,
70: pro4,
71: pro5,
72: pro6,
73: pro7,
74: pro8,
75: };
76: foreach (Products itemPro in products)
77: {
78: db.Products.InsertOnSubmit(itemPro);
79: }
80: db.SubmitChanges();
81: Console.WriteLine("product表数据添加成功！");
效果图：
Program.cs代码如下（再Users用户表，使用InsertAllOnSubmit）：
1: List<Users> users = new List<Users> {
2: new Users{UserName="茗洋"},
3: new Users{UserName="芳竹"},
4: new Users{UserName="清新空气"}
5: };
6: db.Users.InsertAllOnSubmit(users);
7: db.SubmitChanges();
8: Console.WriteLine("用户信息添加成功");
效果图：
同理，完成Sales和ReturnSales表的数据
1: List<Sales> sales = new List<Sales> {
2: new Sales{ProductCode="XYBCNSZYXMN",Quantity=10,UserId=1,CreateDate=DateTime.Now},
3: new Sales{ProductCode="XYBCNSZYSFS",Quantity=8,UserId=1,CreateDate=DateTime.Now},
4: new Sales{ProductCode="XYBCNSZYSFS",Quantity=8,UserId=2,CreateDate=DateTime.Now},
5: new Sales{ProductCode="XYBCNSZYXMN",Quantity=1,UserId=2,CreateDate=DateTime.Now},
6:
7: new Sales{ProductCode="WP7CXSJ",Quantity=1,UserId=3,CreateDate=DateTime.Now},
8: new Sales{ProductCode="WP7CXSJ",Quantity=100,UserId=2,CreateDate=DateTime.Now},
9: new Sales{ProductCode="ANMVC4IA",Quantity=1,UserId=3,CreateDate=DateTime.Now},
10: new Sales{ProductCode="HGZXMNXJK",Quantity=1,UserId=2,CreateDate=DateTime.Now},
11:
12: new Sales{ProductCode="DPSNSQZBB",Quantity=1,UserId=2,CreateDate=DateTime.Now},
13: new Sales{ProductCode="HNZJA8484",Quantity=1,UserId=1,CreateDate=DateTime.Now},
14: new Sales{ProductCode="ANMVC4IA",Quantity=1,UserId=1,CreateDate=DateTime.Now},
15: new Sales{ProductCode="IPHONE5",Quantity=1,UserId=3,CreateDate=DateTime.Now}
16: };
17:
18: List<ReturnSales> returnSales = new List<ReturnSales> {
19: new ReturnSales{ProductCode="XYBCNSZYXMN",Quantity=1,UserId=1,CreateDate=DateTime.Now},
20: new ReturnSales{ProductCode="XYBCNSZYSFS",Quantity=8,UserId=1,CreateDate=DateTime.Now},
21: new ReturnSales{ProductCode="XYBCNSZYSFS",Quantity=8,UserId=2,CreateDate=DateTime.Now},
22: new ReturnSales{ProductCode="WP7CXSJ",Quantity=50,UserId=2,CreateDate=DateTime.Now}
23: };
24: db.Sales.InsertAllOnSubmit<Sales>(sales);
25: db.ReturnSales.InsertAllOnSubmit<ReturnSales>(returnSales);
26: db.SubmitChanges();
27: Console.WriteLine("添加 sale表和returnSales表信息成功！");
2.3 修改数据
获得数据后,修改其中的属性,然后SubmitChanges()，就修改了
代码如下:
1: Products pro9 = new Products
2: {
3: ProductName = "开心的哇哇",
4: ProductCode = "KXDWW",
5: ProductType = (int)ProductTypeEnum.Clothes,
6: ProductDescription = "能听懂人说话",
7: ProductUnitPrice = 148M
8: };
9: db.Products.InsertOnSubmit(pro9);
10: db.SubmitChanges();
11: Products getWaWa = db.Products.FirstOrDefault(x => x.ProductCode == "KXDWW");
12: Console.WriteLine("添加成功！商品名为" + getWaWa.ProductName);
13: Console.WriteLine("正在修改名称...");
14: getWaWa.ProductName = "开心的娃娃";
15: db.SubmitChanges();
16: Products getWaWaUpdate = db.Products.FirstOrDefault(x => x.ProductCode == "KXDWW");
17: Console.WriteLine("修改成功！修改后的商品名为" + getWaWaUpdate.ProductName);
效果图：
2.4 删除数据
代码如下:
1: #region 删除数据
2: //获得数据后删除
3: Products delWaWa = db.Products.FirstOrDefault(x => x.ProductCode == "KXDWW");
4: db.Products.DeleteOnSubmit(delWaWa);
5: db.SubmitChanges();
6: Console.WriteLine("删除成功！");
7: #endregion
效果图:
批量删除
1: Products pro10 = new Products
2: {
3: ProductName = "开心的哇哇测试1",
4: ProductCode = "KXDWW1",
5: ProductType = (int)ProductTypeEnum.Clothes,
6: ProductDescription = "能听懂人说话",
7: ProductUnitPrice = 148M
8: };
9: db.Products.InsertOnSubmit(pro10);
10: Products pro11 = new Products
11: {
12: ProductName = "开心的哇哇测试2",
13: ProductCode = "KXDWW2",
14: ProductType = (int)ProductTypeEnum.Clothes,
15: ProductDescription = "能听懂人说话",
16: ProductUnitPrice = 148M
17: };
18: db.Products.InsertOnSubmit(pro11);
19: db.SubmitChanges();
20: Console.WriteLine("添加测试数据成功");
21: //获得数据
22: Console.WriteLine("开始获得数据");
23: IEnumerable<Products> pros = db.Products.Where(p => p.ProductCode == "KXDWW1" || p.ProductCode == "KXDWW2").ToList();
24: Console.WriteLine("获得刚刚添加的数据成功");
25: db.Products.DeleteAllOnSubmit(pros);
26: db.SubmitChanges();
27: Console.WriteLine("删除成功");
效果图:
2.5 差集,交集,并集操作
①差集,取从未有过退货记录的产品信息
代码:
1: var saleInfo = db.Sales.Select(itm => new { itm.ProductCode });
2: var returnSaleInfo = db.ReturnSales.Select(itm => new { itm.ProductCode });
3: var query = saleInfo.Except(returnSaleInfo); //参数的顺序有关，except的左边为总集，右边为在saleInfo中查找匹配的
4: foreach (var item in query)
5: {
6: Console.WriteLine(item.ProductCode+",");
7: }
8: Console.WriteLine();
②交集，取有过退货记录的产品信息
代码:
1: var saleInfo2 = db.Sales.Select(itm => new { itm.ProductCode });
2: var returnSaleInfo2 = db.ReturnSales.Select(itm => new { itm.ProductCode });
3: var query2 = saleInfo.Intersect(returnSaleInfo);
4: foreach (var item in query2)
5: {
6: Console.WriteLine(item.ProductCode + ",");
7: }
8: Console.WriteLine();
③并集，查询每种产品的销售总数
我们先创建两个视图 V_SalesInfo(Products和Sales关联),V_ReturnSalesInfo(Products和ReturnSales关联)
V_SalesInfo
SELECT p.ProductCode, p.ProductName, s.ProductCode AS PRC, p.ProductUnitPrice, p.ProductType, p.ProductDescription, s.Quantity, s.UserId, s.CreateDate
FROM dbo.Products AS p INNER JOIN
dbo.Sales AS s ON p.ProductCode = s.ProductCode
V_ReturnSalesInfo
1: SELECT p.ProductCode, p.ProductName, s.ProductCode AS PRC, p.ProductUnitPrice, p.ProductType, p.ProductDescription, s.Quantity, s.UserId, s.CreateDate
2: FROM dbo.Products AS p INNER JOIN
3: dbo.ReturnSales AS s ON p.ProductCode = s.ProductCode
将视图拖放到dbml文件中
代码:
1: var saInfo = from si in db.V_SalesInfo
2: select new { si.ProductCode, si.ProductName, si.Quantity };
3: var reSaInfo = from rs in db.V_ReturnSalesInfo
4: select new { rs.ProductCode, rs.ProductName, Quantity=rs.Quantity*(-1) };
5: var salesInfo = saInfo.Union(reSaInfo);
6: var queryUnion = from item in salesInfo
7: group item by new { item.ProductCode, item.ProductName } into g
8: select new
9: {
10: 产品代码 = g.Key.ProductCode,
11: 产品名称 = g.Key.ProductName,
12: 销售总数 = g.Sum(i => i.Quantity)
13: };
14: foreach (var unionItem in queryUnion)
15: {
16: Console.WriteLine("产品代码：" + unionItem.产品代码 + " " + "产品名称：" + unionItem.产品名称 + "销售总数：" + unionItem.销售总数);
17: }
效果图：
2.6 执行SQL
①查询
string sql = "select * from V_SalesInfo where ProductCode='" + "IPHONE5" + "'";
var query10 = db.ExecuteQuery<V_SalesInfo>(sql);
foreach (var unionItem in query10)
{
Console.WriteLine("产品代码：" + unionItem.ProductCode + " " + "产品名称：" + unionItem.ProductName + "销售总数：" + unionItem.Quantity);
}
效果图：
②删除
string sql2 = "delete from ReturnSales where ProductCode ='" + "WP7CXSJ" + "'";
db.ExecuteCommand(sql2);
Console.WriteLine("sql方式删除成功");
效果图：
③修改和添加 请参考②练习
④获得已更新的对象 GetChangeSet()
使用LINQ to SQL操作数据库，在调用数据上下文类（DataContext）的SubmitChange()方法将所有的更新提交到数据库之前，有时需要获取已更新的对象，这样做对于日志和调试都是很有用的。
1: foreach (var item in db.Sales)
2: {
3: item.CreateDate = DateTime.Now;
4: }
5: //获得被修改的记录
6: ChangeSet set = db.GetChangeSet();
7: IList<object> sa=set.Updates;
8: foreach (var item in sa)
9: {
10: Sales sal = item as Sales;
11: Console.WriteLine("产品编号："+sal.ProductCode+" "+sal.CreateDate.ToString("yyyy年MM月dd日"));
12: }
效果图：