好用的SQL TVP~~独家赠送[增-删-改-查]的例子

 以前总是追求新东西,发现基础才是最重要的,今年主要的目标是精通SQL查询和SQL性能优化。

 

一、什么是TVP?

表值参数Table-Value Parameter (TVP) 提供一种将客户端应用程序中的多行数据封送到 SQL Server 的简单方式,而不需要多次往返或特殊服务器端逻辑来处理数据。 您可以使用表值参数来包装客户端应用程序中的数据行,并使用单个参数化命令将数据发送到服务器。 传入的数据行存储在一个表变量中,然后您可以通过使用 Transact-SQL 对该表变量进行操作。

可以使用标准的 Transact-SQL SELECT 语句来访问表值参数中的列值。 

简单点说就是当想传递aaaa,bbbb,cccc,dddd给存储过程时,可以先将aaa,bbb,ccc,dddd存到一张表中:

aaaa

bbbb

cccc

dddd

然后将这张表传递给存储过程。

如:当我们需要查询指定产品的信息时,通常可以传递一串产品ID到存储过程里面,如"1,2,3,4",然后查询出ID=1或ID=2或ID=3或ID=4的产品信息。

可以先将"1,2,3,4"存到一张表中,然后将这张表传给存储过程。

1

2

3

4

那么这种方法有什么优势呢?请接着往下看。

二、早期版本是怎么在 SQL Server 中传递多行的?

在 SQL Server 2008 中引入表值参数之前,用于将多行数据传递到存储过程或参数化 SQL 命令的选项受到限制。 开发人员可以选择使用以下选项,将多个行传递给服务器:

  • 使用一系列单个参数表示多个数据列和行中的值。 使用此方法传递的数据量受所允许的参数数量的限制。 SQL Server 过程最多可以有 2100 个参数。 必须使用服务器端逻辑才能将这些单个值组合到表变量或临时表中以进行处理。
  • 将多个数据值捆绑到分隔字符串或 XML 文档中,然后将这些文本值传递给过程或语句。 此过程要求相应的过程或语句包括验证数据结构和取消捆绑值所需的逻辑。
  • 针对影响多个行的数据修改创建一系列的单个 SQL 语句,例如通过调用 SqlDataAdapter 的 Update 方法创建的内容。 可将更改单独提交给服务器,也可以将其作为组进行批处理。 不过,即使是以包含多个语句的批处理形式提交的,每个语句在服务器上还是会单独执行。
  • 使用 bcp 实用工具程序或 SqlBulkCopy 对象将很多行数据加载到表中。 尽管这项技术非常有效,但不支持服务器端处理,除非将数据加载到临时表或表变量中。

三、例子

当我们需要查询指定产品的信息时,通常可以传递一串产品ID到存储过程里面,如"1,2,3,4",然后查询出ID=1或ID=2或ID=3或ID=4的产品信息。

我们可以先将“1,2,3,4”存到一张表中,然后作为参数传给存储过程。在存储过程里面操作这个参数。

1.使用TVP 查询产品

查询产品ID=1,2,3,4,5的产品

public static void TestGetProductsByIDs()
{
    Collection<int> productIDs = new Collection<int>();
    Console.WriteLine();
    Console.WriteLine("----- Get Product ------");
    Console.WriteLine("Product IDs: 1,2,3,4,5");
    productIDs.Add(1);
    productIDs.Add(2);
    productIDs.Add(3);
    productIDs.Add(4);
    productIDs.Add(5);

    Collection<Product> dtProducts = GetProductsByIDs(productIDs);
    foreach (Product product in dtProducts)
    {
        Console.WriteLine("{0}   {1}", product.ID, product.Name);
    }
}

查询的方法:

/// <summary>
/// Data access layer. Gets products by the collection of the specific product' ID.
/// </summary>
/// <param name="conn"></param>
/// <param name="productIDs"></param>
/// <returns></returns>
public static Collection<Product> GetProductsByIDs(SqlConnection conn, Collection<int> productIDs)
{
    Collection<Product> products = new Collection<Product>();
    DataTable dtProductIDs = new DataTable("Product");
    dtProductIDs.Columns.Add("ID", typeof(int));

    foreach (int id in productIDs)
    {
        dtProductIDs.Rows.Add(
            id
        );
    }

    SqlParameter tvpProduct = new SqlParameter("@ProductIDsTVP", dtProductIDs);
    tvpProduct.SqlDbType = SqlDbType.Structured;
    //SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procGetProducts", tvpProduct);

    using (SqlDataReader dataReader = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "procGetProductsByProductIDsTVP", tvpProduct))
    {
        while (dataReader.Read())
        {
            Product product = new Product();
            product.ID = dataReader.IsDBNull(0) ? 0 : dataReader.GetInt32(0);
            product.Name = dataReader.IsDBNull(1) ? (string)null : dataReader.GetString(1).Trim();

            products.Add(product);
        }
    }
    return products;
} 

创建以产品ID作为列名的TVP:

IF NOT EXISTS(  SELECT * FROM sys.types WHERE name = 'ProductIDsTVP')
	CREATE TYPE [dbo].[ProductIDsTVP] AS TABLE
	(
		[ID] INT
	)
GO 

查询产品的存储过程:

/****** Object:  StoredProcedure [dbo].[procGetProductsByProductIDsTVP]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procGetProductsByProductIDsTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[procGetProductsByProductIDsTVP]
GO

Create PROCEDURE [dbo].[procGetProductsByProductIDsTVP]
(
	@ProductIDsTVP ProductIDsTVP READONLY
)
AS
            
SELECT p.ID, p.Name
	
FROM Product as p
INNER JOIN @ProductIDsTVP as t on p.ID = t.ID

2.使用TVP 删除产品

 删除产品ID=1,5,6的产品

public static void TestDeleteProductsByIDs()
{
    Collection<int> productIDs = new Collection<int>();
    Console.WriteLine();
    Console.WriteLine("----- Delete Products ------");
    Console.WriteLine("Product IDs: 1,5,6");
    productIDs.Add(1);
    productIDs.Add(5);
    productIDs.Add(6);
    DeleteProductsByIDs(productIDs);
}

 删除的方法:

/// <summary>
/// Deletes products by the collection of the specific product' ID
/// </summary>
/// <param name="conn"></param>
/// <param name="productIDs"></param>
public static void DeleteProductsByIDs(SqlConnection conn, Collection<int> productIDs)
{
    Collection<Product> products = new Collection<Product>();
    DataTable dtProductIDs = new DataTable("Product");
    dtProductIDs.Columns.Add("ID", typeof(int));

    foreach (int id in productIDs)
    {
        dtProductIDs.Rows.Add(
            id
        );
    }

    SqlParameter tvpProduct = new SqlParameter("@ProductIDsTVP", dtProductIDs);
    tvpProduct.SqlDbType = SqlDbType.Structured;
    SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procDeleteProductsByProductIDsTVP", tvpProduct);
}

删除产品的存储过程:

/****** Object:  StoredProcedure [dbo].[procDeleteProductsByIDsByProductIDsTVP]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procDeleteProductsByProductIDsTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[procDeleteProductsByProductIDsTVP]
GO

Create PROCEDURE [dbo].[procDeleteProductsByProductIDsTVP]
(
	@ProductIDsTVP ProductIDsTVP READONLY
)
AS
            
DELETE p FROM Product AS p
INNER JOIN @ProductIDsTVP AS t on p.ID = t.ID

3.使用TVP 增加产品

增加产品

ID=5,Name=bbb

ID=6,Name=abc

public static void TestInsertProducts()
{
    Collection<Product> products = new Collection<Product>();
    Console.WriteLine();
    Console.WriteLine("----- Insert Products ------");
    Console.WriteLine("Product IDs: 5-bbb,6-abc");
    products.Add(
        new Product()
        {
            ID = 5,
            Name = "qwe"
        });

    products.Add(
        new Product()
        {
            ID = 6,
            Name = "xyz"
        });

    InsertProducts(products);
}

增加的方法:

/// <summary>
/// Inserts products by the collection of the specific products.
/// </summary>
/// <param name="conn"></param>
/// <param name="products"></param>
public static void InsertProducts(SqlConnection conn, Collection<Product> products)
{
    DataTable dtProducts = new DataTable("Product");
    dtProducts.Columns.Add("ID", typeof(int));
    dtProducts.Columns.Add("Name", typeof(string));

    foreach (Product product in products)
    {
        dtProducts.Rows.Add(
            product.ID,
            product.Name
        );
    }

    SqlParameter tvpProduct = new SqlParameter("@ProductTVP", dtProducts);
    tvpProduct.SqlDbType = SqlDbType.Structured;
    SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procInsertProductsByProductTVP", tvpProduct);
}

增加产品的存储过程:

/****** Object:  StoredProcedure [dbo].[procInsertProductsByProductTVP]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procInsertProductsByProductTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[procInsertProductsByProductTVP]
GO

Create PROCEDURE [dbo].[procInsertProductsByProductTVP]
(
	@ProductTVP ProductTVP READONLY
)
AS
            
INSERT INTO Product (ID, Name)
SELECT
	t.ID, 
	t.Name
FROM @ProductTVP AS t

GO

4.使用TVP 更新产品

 将ID=2的产品的Name更新为bbb

   将ID=6的产品的Name更新为abc

public static void TestUpdateProducts()
{
    Collection<Product> products = new Collection<Product>();
    Console.WriteLine();
    Console.WriteLine("----- Update Products ------");
    Console.WriteLine("Product IDs: 2-bbb,6-abc");
    products.Add(
        new Product()
        {
            ID = 2,
            Name = "bbb"
        });

    products.Add(
        new Product()
        {
            ID = 6,
            Name = "aaa"
        });

    UpdateProducts(products);
}

 更新的方法:

/// <summary>
/// Updates products by the collection of the specific products
/// </summary>
/// <param name="conn"></param>
/// <param name="products"></param>
public static void UpdateProducts(SqlConnection conn, Collection<Product> products)
{
    DataTable dtProducts = new DataTable("Product");
    dtProducts.Columns.Add("ID", typeof(int));
    dtProducts.Columns.Add("Name", typeof(string));

    foreach (Product product in products)
    {
        dtProducts.Rows.Add(
            product.ID,
            product.Name
        );
    }

    SqlParameter tvpProduct = new SqlParameter("@ProductTVP", dtProducts);
    tvpProduct.SqlDbType = SqlDbType.Structured;
    SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procUpdateProductsByProductTVP", tvpProduct);
}

创建以产品ID和产品Name作为列名的TVP:

IF NOT EXISTS(  SELECT * FROM sys.types WHERE name = 'ProductTVP')

	CREATE TYPE [dbo].[ProductTVP] AS TABLE(
		[ID] [int] NULL,
		[Name] NVARCHAR(100)
	)

GO

增加产品的存储过程:

/****** Object:  StoredProcedure [dbo].[procUpdateProductsByIDs]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procUpdateProductsByProductTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[procUpdateProductsByProductTVP]
GO

Create PROCEDURE [dbo].[procUpdateProductsByProductTVP]
(
	@ProductTVP ProductTVP READONLY
)
AS
            
Update p
SET 
	p.ID = t.ID, 
	p.Name = t.Name
FROM product AS p
INNER JOIN @ProductTVP AS t on p.ID = t.ID

GO

结果:

注意:

(1)无法在表值参数中返回数据。 表值参数是只可输入的参数;不支持 OUTPUT 关键字。

(2)表值参数为强类型,其结构会自动进行验证。 

(3)表值参数的大小仅受服务器内存的限制。

(4)删除表值参数时,需要先删除引用表值参数的存储过程。

四、写在最后

后期会将TVP的性能问题和SQL Bulk Copy的用法补上。

五、参考资料

表值参数 https://msdn.microsoft.com/zh-cn/library/bb675163.aspx

表值参数(数据库引擎)https://msdn.microsoft.com/zh-CN/Library/bb510489(SQL.100).aspx 

推荐阅读:30分钟全面解析-SQL事务+隔离级别+阻塞+死锁

推荐阅读:T-SQL基础博客目录

作  者: Jackson0714 出  处:http://www.cnblogs.com/jackson0714/ 关于作者:专注于微软平台的项目开发。如有问题或建议,请多多赐教! 版权声明:本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接。 特此声明:所有评论和私信都会在第一时间回复。也欢迎园子的大大们指正错误,共同进步。或者直接私信我 声援博主:如果您觉得文章对您有帮助,可以点击文章右下角推荐一下。您的鼓励是作者坚持原创和持续写作的最大动力!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Danny的专栏

System.AccessViolationException”类型的未经处理的异常在 System.Data.dll 中发生。其他信息:尝试读取或写入受保护的内存。这通常指示其他内存已损坏。

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/huyuyang6688/article/...

732
来自专栏跟着阿笨一起玩NET

C# 几种数据库的大数据批量插入

转载:http://www.cnblogs.com/luluping/archive/2012/08/09/2629515.html

641
来自专栏Jackson0714

好用的SQL TVP~~独家赠送[增-删-改-查]的例子

38113
来自专栏木宛城主

SharePoint中在线编辑文档

我一直以为只有在Document Library里面的File才会支持在线编辑。直到今天早上我才发现用IE打开List里面的Attachments也是支持在线...

1926
来自专栏木宛城主

SharePoint下用C#代码上传文档至文档库的子文件夹中

SharePoint的文档库非常方便,可以管理日常的文档,更强大的是可以创建文件夹来分类,通常在UI界面上是非常简单了,点击文件夹,Create a Fold...

1938
来自专栏更流畅、简洁的软件开发方式

【开源】QuickPager ASP.NET2.0分页控件V2.0.0.1——分页控件的源码 (二)

namespace JYK.Controls.PageManage {     /**//// <summary>     /// 生成分页控件需要的SQL语句...

2086
来自专栏猫哥学前班

微信小程序 base64 图片 canvas 画布 drawImage 实现

在微信小程序中 canvas drawImage 传入的第一个参数是 imageResource 图片资源路径,这个参数通常由从相册选择图片 wx.choose...

2125
来自专栏Golang语言社区

如何使用golang实现操作键盘按键

https://godoc.org/github.com/nsf/termbox-go 这个库可以支持 简单示例: package main import...

3245
来自专栏Java3y

移动商城第七篇【购物车增删改查、提交订单】

把商品加入购物车 接下来我们要做的就是将商品加入到购物车中。我们这次使用的是Cookie来将用户的信息存储起来。那为什么要用cookie呢?? 如果将购物车存储...

75413
来自专栏一个会写诗的程序员的博客

使用 Java REPLJava REPL

https://github.com/albertlatacz/java-repl/releases

512

扫码关注云+社区