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

悟空聊架构

发布于 2018-06-26 15:41:28

80700

代码可运行

文章被收录于专栏：悟空聊架构 | 公众号悟空聊架构 | 公众号

运行总次数：0

代码可运行

以前总是追求新东西，发现基础才是最重要的，今年主要的目标是精通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事务+隔离级别+阻塞+死锁