那天有个小孩跟我说LINQ（五）转载

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:linqlinq_Ch5DBSalesDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'SalesDB_log', FILENAME = N'D:linqlinq_Ch5DBSalesDB_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 检测数据库是否存在

     代码如下：

   string conStr = @"Data Source=.;Initial Catalog=SalesDB;Integrated Security=True"; //复制于app.config文件中的
            //1.检测数据库是否存在
            SalesDBDataContext db = new SalesDBDataContext(conStr);
            if (db.DatabaseExists())
            {
                Console.WriteLine("数据库存在！");
            }
            else {
                Console.WriteLine("可惜啊，数据库不存在！");
            }

效果图：

 

2.2 插入数据（我们创建后面例子的基础）

为了不影响后面的代码，我们将2.1例子的代码注释掉，然后项目中新建一个Enum文件夹

①向Products表中插入几条数据，然后Users表中创建几个用户，然后添加几条Sales信息和ReturnSales信息

    我们使用 InsertOnSubmit 方法

    产品类型枚举（ProductTypeEnum.cs）如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace linq_Ch5.Enum
{
    public enum ProductTypeEnum
    {
        HuaZhuang=1,
        Books=2,
        Computers=3,
        Phones=4,
        Bags=5,
        Clothes=6
    }
}

    Program.cs代码如下（先Products产品）：

         SalesDBDataContext db = new SalesDBDataContext();
            Products pro1 = new Products
            {
                ProductName = "相宜本草男士专用洗面奶",
                ProductCode = "XYBCNSZYXMN",
                ProductType = (int)ProductTypeEnum.HuaZhuang,
                ProductDescription = "祛痘效果很好",
                ProductUnitPrice = 39.9M
            };
            Products pro2 = new Products
            {
                ProductName = "相宜本草男士专用爽肤水",
                ProductCode = "XYBCNSZYSFS",
                ProductType = (int)ProductTypeEnum.HuaZhuang,
                ProductDescription = "去除倦容",
                ProductUnitPrice = 80.0M
            };
            Products pro3 = new Products
            {
                ProductName = "Windows Phone7 程序设计",
                ProductCode = "WP7CXSJ",
                ProductType = (int)ProductTypeEnum.Books,
                ProductDescription = "wp7开发必备",
                ProductUnitPrice = 99.00M
            };
            Products pro4 = new Products
            {
                ProductName = "ASP.NET MVC4 IN ACTION",
                ProductCode = "ANMVC4IA",
                ProductType = (int)ProductTypeEnum.Books,
                ProductDescription = "最新的ASP.NET MVC入门资料",
                ProductUnitPrice = 198.00M
            };
            Products pro5 = new Products
            {
                ProductName = "韩国最新迷你小夹克",
                ProductCode = "HGZXMNXJK",
                ProductType = (int)ProductTypeEnum.Clothes,
                ProductDescription = "男士修身的",
                ProductUnitPrice = 298.00M
            };
            Products pro6 = new Products
            {
                ProductName = "达普斯女生气质包包",
                ProductCode = "DPSNSQZBB",
                ProductType = (int)ProductTypeEnum.Bags,
                ProductDescription = "目前只有海蓝色的",
                ProductUnitPrice = 98.00M
            };
            Products pro7 = new Products
            {
                ProductName = "海澜之家新款A8484",
                ProductCode = "HNZJA8484",
                ProductType = (int)ProductTypeEnum.Clothes,
                ProductDescription = "最显男人气质的衣服",
                ProductUnitPrice = 498.00M
            };
            Products pro8 = new Products
            {
                ProductName = "Iphone5 国行版",
                ProductCode = "IPHONE5",
                ProductType = (int)ProductTypeEnum.Phones,
                ProductDescription = "美版的4688人民币",
                ProductUnitPrice = 5498.00M
            };
            List<Products> products = new List<Products> {
                pro1,
                pro2,
                pro3,
                pro4,
                pro5,
                pro6,
                pro7,
                pro8,
            };
            foreach (Products itemPro in products)
            {
                db.Products.InsertOnSubmit(itemPro);
            }
            db.SubmitChanges();
            Console.WriteLine("product表数据添加成功！");

效果图：

Program.cs代码如下（再Users用户表，使用InsertAllOnSubmit）：

  List<Users> users = new List<Users> {
                new Users{UserName="茗洋"},
                new Users{UserName="芳竹"},
                new Users{UserName="清新空气"}
            };
            db.Users.InsertAllOnSubmit(users);
            db.SubmitChanges();
            Console.WriteLine("用户信息添加成功");

效果图：

同理，完成Sales和ReturnSales表的数据

  List<Sales> sales = new List<Sales> {
              new Sales{ProductCode="XYBCNSZYXMN",Quantity=10,UserId=1,CreateDate=DateTime.Now},
              new Sales{ProductCode="XYBCNSZYSFS",Quantity=8,UserId=1,CreateDate=DateTime.Now},
              new Sales{ProductCode="XYBCNSZYSFS",Quantity=8,UserId=2,CreateDate=DateTime.Now},
              new Sales{ProductCode="XYBCNSZYXMN",Quantity=1,UserId=2,CreateDate=DateTime.Now},

              new Sales{ProductCode="WP7CXSJ",Quantity=1,UserId=3,CreateDate=DateTime.Now},
              new Sales{ProductCode="WP7CXSJ",Quantity=100,UserId=2,CreateDate=DateTime.Now},
              new Sales{ProductCode="ANMVC4IA",Quantity=1,UserId=3,CreateDate=DateTime.Now},
              new Sales{ProductCode="HGZXMNXJK",Quantity=1,UserId=2,CreateDate=DateTime.Now},

              new Sales{ProductCode="DPSNSQZBB",Quantity=1,UserId=2,CreateDate=DateTime.Now},
              new Sales{ProductCode="HNZJA8484",Quantity=1,UserId=1,CreateDate=DateTime.Now},
              new Sales{ProductCode="ANMVC4IA",Quantity=1,UserId=1,CreateDate=DateTime.Now},
              new Sales{ProductCode="IPHONE5",Quantity=1,UserId=3,CreateDate=DateTime.Now}
            };

            List<ReturnSales> returnSales = new List<ReturnSales> {
              new ReturnSales{ProductCode="XYBCNSZYXMN",Quantity=1,UserId=1,CreateDate=DateTime.Now},
              new ReturnSales{ProductCode="XYBCNSZYSFS",Quantity=8,UserId=1,CreateDate=DateTime.Now},
              new ReturnSales{ProductCode="XYBCNSZYSFS",Quantity=8,UserId=2,CreateDate=DateTime.Now},
              new ReturnSales{ProductCode="WP7CXSJ",Quantity=50,UserId=2,CreateDate=DateTime.Now}
            };
            db.Sales.InsertAllOnSubmit<Sales>(sales);
            db.ReturnSales.InsertAllOnSubmit<ReturnSales>(returnSales);
            db.SubmitChanges();
            Console.WriteLine("添加 sale表和returnSales表信息成功！");

 

2.3 修改数据

    获得数据后,修改其中的属性,然后SubmitChanges()，就修改了

    代码如下:

Products pro9 = new Products
           {
               ProductName = "开心的哇哇",
               ProductCode = "KXDWW",
               ProductType = (int)ProductTypeEnum.Clothes,
               ProductDescription = "能听懂人说话",
               ProductUnitPrice = 148M
           };
           db.Products.InsertOnSubmit(pro9);
           db.SubmitChanges();
           Products getWaWa = db.Products.FirstOrDefault(x => x.ProductCode == "KXDWW");
           Console.WriteLine("添加成功！商品名为" + getWaWa.ProductName);
           Console.WriteLine("正在修改名称...");
           getWaWa.ProductName = "开心的娃娃";
           db.SubmitChanges();
           Products getWaWaUpdate = db.Products.FirstOrDefault(x => x.ProductCode == "KXDWW");
           Console.WriteLine("修改成功！修改后的商品名为" + getWaWaUpdate.ProductName);

效果图：

 

2.4 删除数据

代码如下:

        #region 删除数据
            //获得数据后删除
             Products delWaWa = db.Products.FirstOrDefault(x => x.ProductCode == "KXDWW");
             db.Products.DeleteOnSubmit(delWaWa);
             db.SubmitChanges();
             Console.WriteLine("删除成功！");
            #endregion

效果图:

批量删除

      Products pro10 = new Products
          {
              ProductName = "开心的哇哇测试1",
              ProductCode = "KXDWW1",
              ProductType = (int)ProductTypeEnum.Clothes,
              ProductDescription = "能听懂人说话",
              ProductUnitPrice = 148M
          };
             db.Products.InsertOnSubmit(pro10);
             Products pro11 = new Products
             {
                 ProductName = "开心的哇哇测试2",
                 ProductCode = "KXDWW2",
                 ProductType = (int)ProductTypeEnum.Clothes,
                 ProductDescription = "能听懂人说话",
                 ProductUnitPrice = 148M
             };
             db.Products.InsertOnSubmit(pro11);
             db.SubmitChanges();
             Console.WriteLine("添加测试数据成功");
            //获得数据
             Console.WriteLine("开始获得数据");
             IEnumerable<Products> pros = db.Products.Where(p => p.ProductCode == "KXDWW1" || p.ProductCode == "KXDWW2").ToList();
             Console.WriteLine("获得刚刚添加的数据成功");
             db.Products.DeleteAllOnSubmit(pros);
             db.SubmitChanges();
             Console.WriteLine("删除成功");

效果图:

 

2.5 差集,交集,并集操作

      ①差集,取从未有过退货记录的产品信息

         代码:

var saleInfo = db.Sales.Select(itm => new { itm.ProductCode });
                var returnSaleInfo = db.ReturnSales.Select(itm => new { itm.ProductCode });
                var query = saleInfo.Except(returnSaleInfo); //参数的顺序有关，except的左边为总集，右边为在saleInfo中查找匹配的
                foreach (var item in query)
                {
                    Console.WriteLine(item.ProductCode+",");
                }
                Console.WriteLine();

       ②交集，取有过退货记录的产品信息

         代码:

var saleInfo2 = db.Sales.Select(itm => new { itm.ProductCode });
               var returnSaleInfo2 = db.ReturnSales.Select(itm => new { itm.ProductCode });
               var query2 = saleInfo.Intersect(returnSaleInfo);
               foreach (var item in query2)
               {
                   Console.WriteLine(item.ProductCode + ",");
               }
               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

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.ReturnSales AS s ON p.ProductCode = s.ProductCode

将视图拖放到dbml文件中        

代码:

   var saInfo = from si in db.V_SalesInfo
                         select new { si.ProductCode, si.ProductName, si.Quantity };
            var reSaInfo = from rs in db.V_ReturnSalesInfo
                         select new { rs.ProductCode, rs.ProductName, Quantity=rs.Quantity*(-1) };
            var salesInfo = saInfo.Union(reSaInfo);
            var queryUnion = from item in salesInfo
                             group item by new { item.ProductCode, item.ProductName } into g
                             select new
                             {
                                 产品代码 = g.Key.ProductCode,
                                 产品名称 = g.Key.ProductName,
                                 销售总数 = g.Sum(i => i.Quantity)
                             };
            foreach (var unionItem in queryUnion)
            {
                Console.WriteLine("产品代码：" + unionItem.产品代码 + "  " + "产品名称：" + unionItem.产品名称 + "销售总数：" + unionItem.销售总数);
            }

效果图：

 

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()方法将所有的更新提交到数据库之前，有时需要获取已更新的对象，这样做对于日志和调试都是很有用的。

     foreach (var item in db.Sales)
            {
                item.CreateDate = DateTime.Now;
            }
            //获得被修改的记录
            ChangeSet set = db.GetChangeSet();
            IList<object> sa=set.Updates;
            foreach (var item in sa)
            {
                Sales sal = item as Sales;
                Console.WriteLine("产品编号："+sal.ProductCode+"  "+sal.CreateDate.ToString("yyyy年MM月dd日"));
            }

效果图：