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 + ".", "")
呼~問題解除，何解? 先筆記~
