IT技術互動交流平臺

好用的SQL TVP~~獨家贈送[增刪改查]的例子

作者:jackson0714  來源:IT165收集  發布日期:2016-12-16 20:35:43

 以前總是追求新東西,發現基礎才是最重要的,今年主要的目標是精通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/

Tag標簽: 好用   例子  
  • 專題推薦

About IT165 - 廣告服務 - 隱私聲明 - 版權申明 - 免責條款 - 網站地圖 - 網友投稿 - 聯系方式
本站內容來自于互聯網,僅供用于網絡技術學習,學習中請遵循相關法律法規
香港最快开奖现场直播结果