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 INSERTBULK INSERT 很可能會發生塞入錯誤資料不會發現的情況,而且較不穩定;UDT 就相對較穩。

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


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





留言

  1. 可以問一下,如果用這方式跟Dapper有什麼差異嗎?

    Dapper也可以Insert批次資料做到一次DB Connection,想知道此方法的優勢在哪

    謝謝您 : )


    回覆刪除
    回覆
    1. Dapper雖然有提供Insert多筆的方式,但它其實內部是幫你轉換成許多Insert的statement執行,這效果當然就沒有比DB上直接執行快囉,因為每個statement都會在DB與程式間來回,筆數一多就會漸漸看出差異。而在DB上執行的話,DB和程式只有來回一次,也就是程式將一批資料送給DB,接著DB反覆執行Insert statement,最後再將結果返回,這就減少了溝通的次數了。你可以試試看用程式Insert幾十幾百萬筆資料到DB與在SQL Server Management Studio上執行一個Insert同樣數目資料的Script,比較兩者的時間你就能了解了。

      刪除
    2. 好的,有時間來測試看看,感謝您限量 : )

      另,設計模式寫得很不錯,學習很多,謝謝。

      刪除
  2. 您好,我想請問一個問題
    現在我執行到要將資料對應到UDT的環節
    要是Decimal跟bigint 的型態
    都會出現System.InvalidCastException: Specified cast is not valid.這樣的錯誤
    model型態也都對應到
    設定metadata時也用 SqlDbType.Decimal跟SqlDbType.BigInt去接
    但還是出錯,想請問大大 有沒有辦法找出我的盲點!!感謝

    回覆刪除
    回覆
    1. 附上我的範例
      model.cs
      public class Head
      {
      public decimal Amount { get; set; }
      public long time { get; set; }

      }

      var HeadData = new SqlMetaData[]
      {

      new SqlMetaData("Amount ", SqlDbType.Decimal),
      new SqlMetaData("time ", SqlDbType.BigInt)

      };

      var Hrecord = new SqlDataRecord(HeadData);
      Hrecord.SetDecimal(1, Head.Amount);
      Hrecord.SetInt64(2, Head.time);

      刪除
    2. 你在設定SqlDataRecord的順序錯誤了,
      SetDecimal與SetInt64的第一個參數為ordinal(序數),
      序數從0開始,
      但是你的Amount是設定為1,
      所以對應到的是UDT是time

      刪除
    3. 喔喔 了解
      感謝回答!!

      刪除

張貼留言