[.NET] SqlCommandBuilder.DeriveParameters 擷取預存程序參數與資料表值參數衝突問題

ADO.NET呼叫帶入資料表值參數的預存程序失敗,錯誤訊息:

資料表值參數不可以使用資料庫名稱,只有結構描述名稱名稱是有效的。
Database name is not allowed with a table-valued parameter, only schema name and type name are valid

 

ADO.NET操作預存程序時往往會使用SqlCommandBuilder.DeriveParameters自動取得參數資訊,但執行預存程序時,卻在Table Value Variable的輸入型態上出現(使用者定義資料表類型) 指定了長度不為零的資料庫名稱訊息

先建立一個使用者資料表類型
CREATE TYPE UTYPE_POINT AS TABLE
(                     
       [ID] [varchar](10) ,                
       [CurrentPoint] [INT] ,                
       [UpdateUser] [VARCHAR](10) ,
       [UpdateTime] [datetime] 
)
建立使用UTYPE_POINT的預存程序
CREATE PROCEDURE [dbo].[usp_BulkUpdate] 
	@Table           UTYPE_POINT READONLY,--輸入值: 資料表
	@UpdateCnt       int  output,         --回傳值: 總更新筆數
	@Rsp             varchar(1) output,   --回傳值: 正常0/錯誤1
        @Error           varchar(500) output  --回傳值: 錯誤訊息
AS
--區域變數
Declare @rows int = 0;
SET @UpdateCnt  = 0;
SET NOCOUNT ON;
BEGIN TRY
	    UPDATE TEST_POINT 
		   SET CurrentPoint = B.CurrentPoint,
		       UpdateUser = B.UpdateUser,
			   UpdateTime = B.UpdateTime
		FROM TEST_POINT A JOIN @Table B
		  ON A.ID = B.ID 
		SET @rows = @@ROWCOUNT
        SET @UpdateCnt += @rows
		SET @Rsp = 0;
END TRY
BEGIN CATCH
    SET @Error = ERROR_MESSAGE();
    SET @Rsp = 1;
    RETURN
END CATCH
從ADO.NET呼叫預存程序
DataTable dt = BuildDataTable();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CN"].ConnectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("usp_BulkUpdate", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        SqlCommandBuilder.DeriveParameters(cmd);
        //輸入數引
        cmd.Parameters["@Table"].Value = dt;
        //回傳值
        cmd.Parameters["@UpdateCnt"].Direction = ParameterDirection.Output;
        cmd.Parameters["@Rsp"].Direction = ParameterDirection.Output;
        cmd.Parameters["@Error"].Direction = ParameterDirection.Output;

        cmd.ExecuteNonQuery();

        //顯示回傳值
        Console.WriteLine("Update{0} RSP:{1} ERROR:{2}",
            cmd.Parameters["@UpdateCnt"].Value, cmd.Parameters["@Rsp"].Value, cmd.Parameters["@Error"].Value);

    }
}

出現了以下錯誤

關鍵的這句話:(使用者定義資料表類型) 指定了長度不為零的資料庫名稱
從訊息看起來是不能輸入資料庫名稱,來偵錯一下資料表值參數:

果然是多了資料庫名稱: 程式碼加上這行,將資料庫名稱還有1點剃除

cmd.Parameters["@Table"].TypeName = 
cmd.Parameters["@Table"].TypeName.Replace(conn.Database + ".", "")

 

呼~問題解除,何解? 先筆記~