sql-server - TVP 不符合表类型

Question

下面是插入我的数据的函数。

                using (SqlCommand insSwipeDataCommand = connection.CreateCommand())
                {
                    insSwipeDataCommand.Transaction = transaction;
                    insSwipeDataCommand.CommandType = CommandType.StoredProcedure;
                    insSwipeDataCommand.CommandText = "dbo.insSwipeData_sp";

                    SqlParameter attendeeTableParam = insSwipeDataCommand.Parameters.Add("@AttendeeTable", SqlDbType.Structured);
                    attendeeTableParam.Value = this.dataTable;
                    attendeeTableParam.TypeName = "AttendeeTableType";

                    // add orgid parameter
                    insSwipeDataCommand.Parameters.Add("@orgId", SqlDbType.UniqueIdentifier).Value = this.organizationId;
                    insSwipeDataCommand.ExecuteNonQuery();
                }

insSwipeData_sp

create PROC dbo.insSwipeData_sp
(@AttendeeTable         AttendeeTableType READONLY
,@orgId                 UNIQUEIDENTIFIER
)

AS

BEGIN
    SET NOCOUNT ON


    DECLARE @enteredUserId  UNIQUEIDENTIFIER
    SET @enteredUserId = 'xxxxxxxxx-xxxx-xxx-xxxx-xxxxxxxxxx'

    -- Delete old Swipe records
    DELETE FROM dbo.swipeData_tbl
    WHERE orgId = @orgId

    -- CREATE Swipe Records
    INSERT INTO dbo.swipeData_tbl
    (orgId, sdid, rawData, enteredUserId, enteredUtc, manualEntry)
    SELECT @orgId, attendeeId, barcode
          ,@enteredUserId, GETUTCDATE(), 0 -- Consider ( datepart , date ) if date here is needed as NVARCHAR
    FROM @AttendeeTable
    WHERE barcode IS NOT NULL and LTRIM(RTRIM(barcode)) <> '';
END

这是我的 AttendeeTableType 架构的图像。

这是我用于 attendeeTableParam 的 this.datatable 的图像 在 insSwipeDataCommand.ExecuteNonQuery(); 行上，我收到以下错误。

表值参数“@AttendeeTable”的数据不符合参数的表类型。

Answer 1

根据错误，您的数据不完全符合表类型。注意“完全”——如果你没有指定列的类型，它们将会被推断出来，而且很容易被错误地推断出来。这里最好的方法是创建一个您知道与表类型定义匹配的表:

var dt = new DataTable();
dt.Columns.Add("firstName", typeof(string)).MaxLength = 100;
dt.Columns.Add("lastName", typeof(string)).MaxLength = 100;
dt.Columns.Add("studentId", typeof(string)).MaxLength = 10;
dt.Columns.Add("email", typeof(string)).MaxLength = 100;
dt.Columns.Add("barcode", typeof(string)).MaxLength = 100;
dt.Columns.Add("dob", typeof(string)).MaxLength = 200;
dt.Columns.Add("major", typeof(string)).MaxLength = 200;
dt.Columns.Add("gender", typeof(string)).MaxLength = 200;
dt.Columns.Add("classCode", typeof(string)).MaxLength = 15;
dt.Columns.Add("currentclassCode", typeof(string)).MaxLength = 15;
dt.Columns.Add("entranceCode", typeof(string)).MaxLength = 15;
dt.Columns.Add("attendeeId", typeof(Guid));

然后在需要插入数据时使用.Clone()创建具有正确架构的新表。这样，如果类型或长度不匹配，客户端就会捕获它。

您可以采用另一种方法，该方法不依赖于将表定义嵌入到应用程序中，而是从数据库中获取表定义。这样做有利有弊——它需要额外的数据库往返，并且如果类型或列不匹配，则不容易发现应用程序逻辑中的错误，但它确实为您提供了更改类型的额外灵 active 无需更改应用程序(例如添加新的可为空的列)。

var dt = new DataTable();
using (var connection = new SqlConnection(...)) {
    connection.Open();
    using (var command = new SqlCommand()) {
        command.Connection = connection;
        command.CommandText = "DECLARE @t dbo.AttendeeTableType; SELECT * FROM @t;"
        using (var reader = command.ExecuteReader()) {
            dt.Load(reader);
        }
    }
}

显然，您可能希望缓存此操作和 .Clone() 的结果，而不是对涉及表类型参数的每个命令都执行此操作。