SQL TVP 試用紀錄

TVP (table valued parameters) in SQL Server 2019+ 試用紀錄。

TVP(table valued parameters) 說明

官方說明

Use table-valued parameters (Database Engine) - SQL ServerMicrosoftLearn

白話文就是：讓『table』可以像一般的 SqlParemaeter 傳來傳去。

實作上增加一種『使用者定義資料表(User-Defined Tables)』這是自訂型別，有 table 特性的 parameter，也可稱作『table type』，它可以像 SqlParameter 一樣的使用。

應用於傳遞大批量的資料是很方便的選擇。速度可以比美 Bulk Copy 又沒有授權的限制。

開發環境

IDE: Visual Studio 2022 Platform: .NET6 DB: SQL Server 2019 DB ORM: Dapper

試用紀錄　

建立測試資料表。

-- 建立標的資料表
CREATE TABLE [dbo].[MyData](
	[SN] [bigint] IDENTITY(1,1) NOT NULL,
	[IDN] [varchar](10) NOT NULL,
	[Title] [nvarchar](100) NOT NULL,
	[Amount] [decimal](18, 4) NOT NULL,
	[Birthday] [datetime2](7) NULL,
	[Remark] [nvarchar](500) NULL,
 CONSTRAINT [PK_MyData] PRIMARY KEY CLUSTERED ([SN] ASC)
)
GO

-- 建立標的資料表的 TVP 以可以大批量匯入操作
-- ※注意：TVP 也是 type 可以稱作『table type』交流比較不拗口。
CREATE TYPE [dbo].[MyDataTvp] AS TABLE(
	[IDN] [varchar](10) NOT NULL,
	[Title] [nvarchar](100) NOT NULL,
	[Amount] [decimal](18, 4) NOT NULL,
	[Birthday] [datetime2](7) NULL,
	PRIMARY KEY CLUSTERED ([IDN] ASC)
)
GO

在 C# 的對應資料結構

using System;
using System.ComponentModel.DataAnnotations;
using Dapper.Contrib.Extensions;
using KeyAttribute = Dapper.Contrib.Extensions.KeyAttribute;

[Table("MyData")]
public class MyData 
{
  [Key]
  [Required]
  public Int64 SN { get; set; }
  [Required]
  public string IDN { get; set; }
  [Required]
  public string Title { get; set; }
  [Required]
  public Decimal? Amount { get; set; }
  public DateTime? Birthday { get; set; }
  public string Remark { get; set; }
}

public class MyDataTvp 
{
  public string IDN { get; set; }
  public string Title { get; set; }
  public Decimal? Amount { get; set; }
  public DateTime? Birthday { get; set; }
}

透過 TVP 來 INSERT 大批量資料

public String TestTVPImportDB()
{
  //# 準備大批量資料
  List<MyDataTvp> dataList = new();
  dataList.Add(new MyDataTvp { IDN = "A001001", Title = "今天天氣真好", Amount = 123456789m, Birthday = DateTime.Today });
  dataList.Add(new MyDataTvp { IDN = "A002002", Title = "我真的出運了", Amount = 987654321m, Birthday = DateTime.Today.AddDays(1) });
  dataList.Add(new MyDataTvp { IDN = "A003003", Title = "你好嗎我很好", Amount = 1234.5678m, Birthday = DateTime.Today.AddDays(2) });

  //# 上傳大批量資料
  using var conn = DBHelper.CONNLAB.Open(); // 基底為 Dapper 

  var param = new DynamicParameters();
  DataTable dataTable = dataList.AsDataTable(); 
  // ※必需轉成 DataTable 型別。再用 DataTable.AsTableValuedParameter() 函式轉成TVP。
  param.Add("@dataList", dataTable.AsTableValuedParameter(nameof(MyDataTvp)));

  string sql = @"INSERT INTO MyData(IDN,Title,Amount,Birthday) SELECT IDN,Title,Amount,Birthday FROM @dataList; ";
  int rowsAffected = conn.Execute(sql, param);

  return $"新增了{rowsAffected}筆資料。";
}

直接 INSERT 大批量資料(without TVP)

/// <summary>
/// 直接用 INSERT INTO 上傳大量資料到DB。
/// 此例未使用TVP。
/// SQL Server 較新的版本也可以大批量 INSERT 。
/// </summary>
public String TestInsertMultiData()
{
  //# 準備大批量資料
  List<MyData> dataList = new();
  dataList.Add(new MyData { IDN = "A004001", Title = "今天天氣真好", Amount = 123456789m, Birthday = DateTime.Today, Remark = "就是這麼簡單" });
  dataList.Add(new MyData { IDN = "A005002", Title = "我真的出運了", Amount = 987654321m, Birthday = DateTime.Today.AddDays(1), Remark = "就是這麼簡單" });
  dataList.Add(new MyData { IDN = "A006003", Title = "你好嗎我很好", Amount = 1234.5678m, Birthday = DateTime.Today.AddDays(2), Remark = "就是這麼簡單" });

  //# 上傳大批量資料
  using var conn = DBHelper.CONNLAB.Open(); // 基底為 Dapper 
  string insertSql = "INSERT INTO MyData (IDN,Title,Amount,Birthday,Remark) VALUES (@IDN,@Title,@Amount,@Birthday,@Remark) ";
  int rowsAffected = conn.Execute(insertSql, dataList);

  return $"新增了{rowsAffected}筆資料。";
}

透過 Procedure 就要用 TVP 來溝通

CREATE PROCEDURE [dbo].[prBatchInsertMyData]
	@dataList AS dbo.MyDataTvp READONLY
AS
BEGIN
  -- KEY 不存在時才新增
  MERGE dbo.MyData AS T
  USING (SELECT * FROM @dataList) AS S
    ON (T.IDN = S.IDN )
  WHEN NOT MATCHED BY TARGET
  THEN
    INSERT (IDN,Title,Amount,Birthday)
	VALUES (S.IDN,S.Title,S.Amount,S.Birthday);

  -- 回應
  SELECT RowsAffected = @@ROWCOUNT; 
END
GO

主程式

/// <summary>
/// 測試用 TVP 上傳大量資料到 DB。
/// </summary>
public String TestTVPCallProc()
{
  //## 準備大批量資料
  List<MyDataTvp> dataList = new();
  dataList.Add(new MyDataTvp { IDN = "A00P001", Title = "今天天氣真好", Amount = 123456789m, Birthday = DateTime.Today });
  dataList.Add(new MyDataTvp { IDN = "A00P002", Title = "我真的出運了", Amount = 987654321m, Birthday = DateTime.Today.AddDays(1) });
  dataList.Add(new MyDataTvp { IDN = "A00P003", Title = "你好嗎我很好", Amount = 1234.5678m, Birthday = DateTime.Today.AddDays(2) });

  //## 上傳大批量資料
  using var conn = DBHelper.CONNLAB.Open(); // 基底為 Dapper

  var args = new prBatchInsertMyDataArgs
  {
    dataList = dataList
  };

  var resultList = conn.CallprBatchInsertMyData(args);
  int rowsAffected = resultList[0].RowsAffected;
  return $"新增了{rowsAffected}筆資料。";
}

包裝成函式

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using Dapper;
using Microsoft.Data.SqlClient;

public class prBatchInsertMyDataResult
{
  public int RowsAffected { get; set; }
}

public class prBatchInsertMyDataArgs
{
  public List<MyDataTvp> dataList { get; set; }
}

static partial class DBHelperClassExtensions
{
  public static List<prBatchInsertMyDataResult> CallprBatchInsertMyData(this SqlConnection conn, prBatchInsertMyDataArgs args, SqlTransaction txn = null)
  {
    var param = new DynamicParameters();
    param.Add("@dataList", args.dataList.AsDataTable().AsTableValuedParameter(nameof(MyDataTvp)));

    var resultList = conn.Query<prBatchInsertMyDataResult>("dbo.prBatchInsertMyData", param,
      transaction: txn,
      commandType: System.Data.CommandType.StoredProcedure
      ).AsList();
    return resultList;
  }
}

/// <summary>
/// 轉換 List<T> 成 DataTable。
/// for TVP(table value parameter) 參數傳遞
/// </summary>
public static DataTable AsDataTable<TTableType>(this List<TTableType> infoList)
{
  var table = new DataTable();
  var properties = typeof(TTableType).GetRuntimeProperties();
    
  foreach (var prop in properties)
  {
    table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
  }

  foreach (var info in infoList)
  {
    table.Rows.Add(properties.Select(property => property.GetValue(info)).ToArray());
  }

  return table;
}

(EOF)