SQL Server 2008 利用 DataTable 作為預存程序參數的完整實作指南

前言

在早期的 SQL Server 2000 時代，若要將前端集合資料傳遞給預存程序，往往得先將資料拆解成字串（例如以逗號分隔），再在預存程序中自行解析，不僅麻煩且易出錯。SQL Server 2008 引入了資料表值參數，讓我們能直接將 DataTable 作為參數傳遞，極大簡化了開發流程。本文將以實際範例，完整示範從資料庫物件建立、預存程序撰寫，到 C# 端呼叫的完整步驟。

測試環境

• Windows Server 2008 R2 DataCenter
• Visual Studio 2008 Team System SP1
• SQL Server 2008 Enterprise Edition SP1

此功能為 SQL Server 2008 新增特性，故需在 2008 或更新版本上執行。

資料庫物件準備

1. 建立使用者資料表

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'Users') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
    CREATE TABLE dbo.Users (
        UserID INT IDENTITY(-1, -1) NOT NULL,
        UserName VARCHAR(20) NOT NULL,
        UserPass VARCHAR(20) NOT NULL,
        Sex BIT NULL,
        Age SMALLINT NULL,
        CONSTRAINT PK_Users_UserID PRIMARY KEY (UserID)
    )
END

2. 建立使用者定義資料表類型

IF NOT EXISTS (SELECT * FROM sys.table_types WHERE name = N'UserTable' AND is_user_defined = 1)
BEGIN
    CREATE TYPE UserTable AS TABLE (
        UserName VARCHAR(20) NOT NULL,
        UserPass VARCHAR(20) NOT NULL,
        Sex BIT NULL,
        Age SMALLINT NULL
    )
END

3. 建立預存程序

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sp_InsertSingleUser') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE dbo.sp_InsertSingleUser
GO

CREATE PROCEDURE dbo.sp_InsertSingleUser
    @User UserTable READONLY
AS
BEGIN
    SET XACT_ABORT ON
    BEGIN TRANSACTION
    INSERT INTO dbo.Users (UserName, UserPass, Sex, Age)
    SELECT UserName, UserPass, Sex, Age FROM @User
    COMMIT TRANSACTION
    SET XACT_ABORT OFF
END
GO

前端呼叫範例

在 ASP.NET 網頁中放置表單，使用者輸入資料後，後端將資料整理成 DataTable 並傳遞給預存程序：

public void fnInsertSingleUser(DataTable v_dt)
{
    try
    {
        using (SqlConnection cn = new SqlConnection(CONN))
        {
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = @"sp_InsertSingleUser";
            SqlParameter p = cmd.Parameters.AddWithValue("@User", v_dt);
            p.SqlDbType = SqlDbType.Structured; // 明確指定為結構化類型
            cn.Open();
            cmd.ExecuteNonQuery();
        }
    }
    catch (Exception ex)
    {
        // 記錄錯誤日誌後再拋出
        throw;
    }
}

關鍵點總結

• 資料表值參數必須先在資料庫建立使用者定義資料表類型。
• 預存程序參數宣告時需加上 READONLY 關鍵字。
• C# 端呼叫時，將 DataTable 直接傳給 SqlParameter，並設定 SqlDbType.Structured。
• 此做法避免了字串拼接與解析，效能與可維護性皆大幅提升。