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: }

效果圖：

?

轉載于:https://www.cnblogs.com/kloseking/p/3168040.html

總結

以上是生活随笔為你收集整理的那天有个小孩跟我说LINQ（五）转载的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。