~楓花雪岳~: [SQL] 資料表值參數 (Table Value Parameters)

星期日, 10月 11, 2020

[SQL] 資料表值參數 (Table Value Parameters)

SQL Server 2008 功能 - 資料表值參數 (Table Value Parameters)，簡稱 TVP，紀錄 MS SQL Table Type 建立和三種程式呼叫方式，基本上程式端都是透過對 Store Procedure 傳入 DataTable 來執行

官方文件內的資料表值參數的限制

無法將資料表值參數傳遞至 CLR 使用者定義函式。
資料表值參數索引只支援 UNIQUE 或 PRIMARY KEY 條件約束。 SQL Server 不會維護資料表值參數的統計資料。
資料表值參數在 Transact-SQL 程式碼中處於唯讀狀態。您無法更新資料表值參數資料列中的資料行值，也無法插入或刪除資料列。若要在資料表值參數中修改傳遞至預存程序或參數化陳述式的資料，您必須將資料插入至暫存資料表或資料表變數中。
無法使用 ALTER TABLE 陳述式來修改資料表值參數的設計

MS SQL Table Type 和 Store Procedure 設定方式

------ Step1：建立 Table Type
CREATE TYPE dbo.ProductCategoryTableType AS TABLE  
    ([Name] nvarchar(50)) 

------ Step2：建立 Store Procedure
-- 建立 Insert 相關
CREATE PROCEDURE uspProductCategories_Insert
    (@tvpProductCategories dbo.ProductCategoryTableType READONLY)  
AS
	BEGIN
		INSERT INTO [Production].[ProductCategory] 
			(
				[Name] , 
				rowguid  , 
				ModifiedDate
			)
		SELECT 
			[Name] , 
			NEWID()  , 
			GETDATE()
		FROM @tvpProductCategories ;
	END

-- 建立 Delete 相關
-- [ProductCategoryID] 才是 PK，方便記錄就用 Name 來進行 JOIN 
CREATE PROCEDURE uspProductCategories_Delete
    (@tvpProductCategories dbo.ProductCategoryTableType READONLY)  
AS
	BEGIN
		DELETE P
		FROM [Production].[ProductCategory] AS P
			JOIN @tvpProductCategories AS tvp ON P.[Name] = tvp.[Name]
	END

透過 Console 來記錄三種呼叫方式，方別為 ADO.NET、Dapper 和 EF

using System;
using System.Data;
using System.Data.SqlClient;
using Dapper;

namespace TVPDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = @"Data Source=.\SQL2017;Initial Catalog=AdventureWorks2017;Integrated Security=True;";
            DataTable dtProductCategory = GetDataTableProductCategory();
            // 透過變更 CRUDMode 來決定 insert 或 delete
            string crudModel = CRUDMode.Insert.ToString();

            string spName = $"uspProductCategories_{crudModel}";

            #region ADO.NET 呼叫 TVP
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(spName, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpProductCategories", dtProductCategory);
                tvpParam.SqlDbType = SqlDbType.Structured;

                conn.Open();
                cmd.ExecuteNonQuery();
            }
            #endregion

            #region Dapper 呼叫 TVP
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                conn.Execute
                    (
                        spName,
                        // tvpProductCategories 為 SP 參數，不用前面的 @ 符號
                        // ProductCategoryTableType 為 Table Type
                        new { tvpProductCategories = dtProductCategory.AsTableValuedParameter("ProductCategoryTableType") },
                        commandType: CommandType.StoredProcedure
                    );
            }
            #endregion

            #region EF 呼叫 TVP
            using (AdventureWorksContext context = new AdventureWorksContext())
            {
                // @tvpProductCategories 為 SP 參數
                string cmdText = $"EXEC {spName} @tvpProductCategories";
                SqlParameter tvpParam = new SqlParameter("@tvpProductCategories" , SqlDbType.Structured);
                // ProductCategoryTableType 為 Table Type
                tvpParam.TypeName = "dbo.ProductCategoryTableType";
                tvpParam.Value = dtProductCategory;

                context.Database.ExecuteSqlCommand(cmdText, tvpParam);
            } 
            #endregion
        }

        enum CRUDMode
        {
            Insert,
            Delete
        }

        private static DataTable GetDataTableProductCategory()
        {
            DataTable dtProductCategory = new DataTable();
            dtProductCategory.Columns.Add("Name", typeof(string));
            dtProductCategory.Rows.Add("分類1");
            dtProductCategory.Rows.Add("分類2");
            dtProductCategory.Rows.Add("分類3");
            dtProductCategory.Rows.Add("分類4");
            dtProductCategory.Rows.Add("分類5");
            return dtProductCategory;
        }
    }
}

~楓花雪岳~

星期日, 10月 11, 2020

[SQL] 資料表值參數 (Table Value Parameters)

沒有留言:

張貼留言