具有用户定义表类型的SQL Server存储过程

Question

我在SQL Server中有一个存储过程，参数@Files是一个用户定义的表类型。我得到一个错误，而执行它从c#代码:

System.Data.SqlClient。传入的表格数据流(TDS)远程过程调用(RPC)协议流不正确。表值参数3 ("@Files")，第0行，第0列:数据类型0xF3(用户定义的表类型)指定了一个非零长度的数据库名称。表值参数不允许使用数据库名，只有模式名和类型名是有效的。

这是我的c#代码:

DataTable dt = new DataTable();
dt.Columns.Add("ComputerId", typeof(int));
dt.Columns.Add("FolderId", typeof(int));
dt.Columns.Add("FilePath", typeof(string));
dt.Columns.Add("FileName", typeof(string));
dt.Columns.Add("Size", typeof(long));
dt.Columns.Add("LastWriteTimeUtc", typeof(DateTime));
dt.Columns.Add("ReadOnly", typeof(bool));
foreach (var item in listFileInformation)
{
dt.Rows.Add(1, 1, item.FilePath, item.FileName, item.Size, item.LastWriteUTC, item.ReaddOnly);
}
databaseHelper.AddParameter("@ComputerId", 1);
databaseHelper.AddParameter("@FolderId", 1);
SqlParameter tableTypeParam = new SqlParameter("@Files", SqlDbType.Structured)
{
TypeName = "Inbox.dbo.EngComputerFileTableType",
Value = dt
};
databaseHelper.AddParameter(tableTypeParam);
databaseHelper.ExecuteNonQuery("InsertEngComputerFiles", System.Data.CommandType.StoredProcedure, System.Data.ConnectionState.Open);
databaseHelper.connection.Close();

这是存储过程:

CREATE PROCEDURE [dbo].[InsertEngComputerFiles]
@ComputerId BIGINT,
@FolderId BIGINT,
@Files [dbo].[EngComputerFileTableType] READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO
[Inbox].[dbo].[EngComputerFile] (
[ComputerId], [FolderId], [FilePath], [FileName],
[Size], [LastWriteTimeUtc], [ReadOnly])
SELECT
@ComputerId, @FolderId, [FilePath], [FileName],
[Size], [LastWriteTimeUtc], [ReadOnly]
FROM
@Files;
UPDATE [Inbox].[dbo].[EngComputer]
SET [LastScanTimeStampUtc] = SYSUTCDATETIME()
WHERE [ComputerId] = @ComputerId;
END

Answer 1

留言说明了一切你需要修改这一行

SqlParameter tableTypeParam = new SqlParameter("@Files", SqlDbType.Structured)
{
//TypeName = "Inbox.dbo.EngComputerFileTableType",
TypeName = "dbo.EngComputerFileTableType",
Value = dt
};