C# - 呼叫 MSSQL Store Procedure 傳入 UDT 參數 (Table-Valued Parameter)

限量在最近的工作中，要把資料倒進資料庫的 Table 裡。先前在 Oracle 上的做法是使用 UDT (User Defined Type) 的方式，先定義好 UDT，寫一隻 Store Procedure 接收 UDT 陣列的參數進行 INSERT 或 UPDATE 的動作。後端程式也要寫與 UDT Mapping 的 Type，這樣才能傳入 SP。但是這次遇到的是 MSSQL，所以限量今天也要用同樣的方式來處理。

Oracle 在處理 C# 與 UDT上比較容易，只要引用提供的 DLL，建立繼承 IOracleCustomType 的 DataType 並對每個 Property 上加入對應的 UDT 欄位名稱就好了。

MSSQL 處理方式就比較麻煩了，有兩種處理方式，第一種是使用 DataTable 來模擬資料表對應 UDT 欄位；第二種方式就是限量要用的方式，簡單來說就是將資料塞到 SqlDataRecord 裏頭再傳入 SP。之所以會用這個方式是因為在 .Net Core 的程式中無法使用 DataTable。不要問我為什麼，因為我也想問 Microsoft 為什麼。所以基於這個理由，在 .Net Core 上的處理方式就只剩 SqlDataRecord 一種了。

廢話不多說，馬上來看下面範例程式如何實作：

建立範例 Table

test_student.sql

CREATE TABLE [dbo].[test_student](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [name] [nvarchar](20) NULL,
 [no] [int] NULL,
 [idno] [varchar](20) NULL,
 [address] [nvarchar](100) NULL,
 [email] [varchar](50) NULL,
 [phone] [varchar](20) NULL,
 [class] [varchar](5) NULL,
 CONSTRAINT [PK_test_student] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

建立範例 UDT

StudentDto.sql

CREATE TYPE [dbo].[StudentDto] AS TABLE(
 [Name] [nvarchar](20) NULL,
 [No] [int] NULL,
 [IDNo] [varchar](20) NULL,
 [Address] [nvarchar](100) NULL,
 [Email] [varchar](50) NULL,
 [Phone] [varchar](20) NULL,
 [Class] [varchar](5) NULL
)

建立範例 Store Procedure

sp_importStudentDto.sql

CREATE PROCEDURE [dbo].[sp_importStudentDto]
 @StudentList [dbo].[StudentDto] READONLY
AS
BEGIN
 SET NOCOUNT ON;

 INSERT INTO test_student(name, no, idno, address, email, phone, class) 
 SELECT Name, No, IDNo, Address, Email, Phone, Class FROM @StudentList
END

C# 端範例程式

Program.cs

class Program
{
 static void Main(string[] args)
 {
  // 組Sql Server ConnectionString
  var connBuilder = new SqlConnectionStringBuilder {
   DataSource = "localhost\\TRUSTSRC",
   InitialCatalog = "SJ_TRUST_SRC",
   PersistSecurityInfo = true,
   UserID = "user",
   Password = "XXXXXXXX"
  };

  // 測試資料
  var studentList = new List<Student>()
  {
   new Student
   {
    Name = "A",
    No = 1,
    Class = "111",
    IDNo = "A123456789"
   },
   new Student
   {
    Name = "B",
    No = 2,
    Class = "222",
    IDNo = "B123456789"
   },
   new Student
   {
    Name = "C",
    No = 3,
    Class = "333",
    IDNo = "C123456789"
   }
  };

  // 定義對應UDT的欄位資料
  var metaData = new SqlMetaData[]
  {
   new SqlMetaData("Name", SqlDbType.NVarChar, 20),
   new SqlMetaData("No", SqlDbType.Int),
   new SqlMetaData("IDNo", SqlDbType.VarChar, 20),
   new SqlMetaData("Address", SqlDbType.NVarChar, 100),
   new SqlMetaData("Email", SqlDbType.VarChar, 50),
   new SqlMetaData("Phone", SqlDbType.VarChar, 20),
   new SqlMetaData("Class", SqlDbType.VarChar, 5)
  };

  var recordList = new List<SqlDataRecord>();

  // 將每筆Student資料轉換成對應的UDT
  studentList.ForEach(x =>
  {
   var record = new SqlDataRecord(metaData);

   if(!string.IsNullOrWhiteSpace(x.Name))
    record.SetString(0, x.Name);
   if(x.No.HasValue)
    record.SetInt32(1, x.No.Value);
   if (!string.IsNullOrWhiteSpace(x.IDNo))
    record.SetString(2, x.IDNo);
   if (!string.IsNullOrWhiteSpace(x.Address))
    record.SetString(3, x.Address);
   if (!string.IsNullOrWhiteSpace(x.Email))
    record.SetString(4, x.Email);
   if (!string.IsNullOrWhiteSpace(x.Phone))
    record.SetString(5, x.Phone);
   if (!string.IsNullOrWhiteSpace(x.Class))
    record.SetString(6, x.Class);

   recordList.Add(record);
  });

  // 執行SP並傳入UDT資料
  using (var conn = new SqlConnection(connBuilder.ConnectionString))
  using (var cmd = conn.CreateCommand())
  {
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = "sp_importStudentDto";
   cmd.Parameters.Add(new SqlParameter
   {
    ParameterName = "@StudentList",
    SqlDbType = SqlDbType.Structured,
    Value = recordList,
    Direction = ParameterDirection.Input
   });

   conn.Open();
   var result = cmd.ExecuteNonQuery();
  }

  Console.ReadLine();
 }
}

C# 端的 DataType

Student.cs

public class Student
{
 public string Name { get; set; }

 public int? No { get; set; }

 public string IDNo { get; set; }

 public string Address { get; set; }

 public string Email { get; set; }

 public string Phone { get; set; }

 public string Class { get; set; }
}

使用 SqlDataRecord 方式有幾個重點：

1. 須建立 SqlMetaData 陣列，讓 SqlDataRecord 能知道 UDT 每個欄位的資訊。
2.  要注意 DB 欄位上的 null 判斷，因為在資料庫中一些像 int, datetime 都可為 null，但在 C# 中這些資料型態是不可為 null 的。
3. SqlDataRecord 在設定欄位值時，要注意設定欄位的順序，順序是依照 UDT 上定義的欄位順序，如果不知道順序可以用用 GetOrdinal 方法，傳入欄位名稱就可以了。
4.  呼叫 SP 時要將 SqlDataRecord 加入 Parameter，SqlDbType 為 Structured。

使用 UDT 的方式雖然比較麻煩，因為不僅在 C# 端要寫程式，DB 端也需要寫一套，但是這還是有很大的好處的，好處在於對於大批資料塞入資料庫的動作會比較快，而且快超多。相較於程式端一筆一筆來回呼叫，用UDT只需來回一次就好了。相較於程式端使用 BULK INSERT，BULK INSERT 很可能會發生塞入錯誤資料不會發現的情況，而且較不穩定；UDT 就相對較穩。

以上分析希望能夠幫助到大家解決大量資料進資料庫的問題。


參考來源：
Passing Table Valued Parameter to Stored Procedure: Part 1
Passing Table Valued Parameter to Stored Procedure: Part 2