您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2008 使用 DataTable 作為預存程序參數的實作教學

編輯：關於SqlServer

背景與優勢

在 SQL Server 2000 時代，若要將前端集合資料傳給預存程序，只能將字串拆分組合，過程繁瑣。SQL Server 2008 引入資料表值參數，可直接將 DataTable 作為參數傳遞，大幅簡化開發。

測試環境

Windows Server 2008 R2 Datacenter
Visual Studio 2008 Team System SP1
SQL Server 2008 Enterprise SP1

資料庫物件建立腳本

-- 建立使用者表
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('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

-- 建立使用者定義資料表型別
IF NOT EXISTS (SELECT * FROM sys.table_types WHERE name = '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
GO

-- 建立預存程序
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('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

前端 C# 呼叫範例

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);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
        }
    }
    catch (Exception ex) { throw ex; }
}