3

I need to pass a collection of items to a stored procedure. A stored procedure inserts them into several tables. I use a table value parameter of custom user type declared.

TVP type:

CREATE TYPE [dbo].[Type1] AS TABLE
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Field1] [nvarchar](256) NOT NULL,
    [Field2] [int] NOT NULL,

    PRIMARY KEY NONCLUSTERED ([Id] ASC)
            WITH (IGNORE_DUP_KEY = OFF)
)

Here is the code I use:

var dataTable = new List<SqlDataRecord>();
SqlMetaData[] metaData =
{
    new SqlMetaData("Field1", SqlDbType.NVarChar, 256),
    new SqlMetaData("Field2", SqlDbType.Int),
};

foreach (var item in items)
{
    var record = new SqlDataRecord(metaData);
    record.SetValue(0, item.Field1);
    record.SetValue(1, item.Field2);
    dataTable.Add(record);
}
var parameters = new DynamicParameters(new
{
    Items = dataTable.AsTableValuedParameter()
});

await conn.QueryAsync(
    sql: "DoSomeJob_Sproc",
    param: parameters,
    commandType: CommandType.StoredProcedure);

The problem here is that TVP type has identifier column which cannot (and do not needed actually) be passed when inserting new items here. But if I'm not specifying it in record metadata, I get an error

Trying to pass a table-valued parameter with 2 column(s) where the corresponding user-defined table type requires 3 column(s).

The question is: is there a way to specify the fields I need explicitly (but not using ordinal as shown in the snippet)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Flasher64
  • 53
  • 7
  • 2
    My own experiments with TVPs and Dapper say no. Anyway, a user-defined table type for passing data should be simple - why exactly do you need a primary key and identity column on something you're going to copy data out of in your sproc? KISS. – Ian Kemp Aug 18 '20 at 22:13

1 Answers1

2

It seems that I've found the answer - at least of how to ignore identity column here:

SqlMetaData[] metaData =
{
    new SqlMetaData("IdentityColumn", SqlDbType.Int, true, false, SortOrder.Unspecified, -1),
    new SqlMetaData("Field1", SqlDbType.NVarChar, 256),
    new SqlMetaData("Field2", SqlDbType.Int),
};

foreach (var item in items)
{
    var record = new SqlDataRecord(metaData);
    record.SetValue(1, item.Field1); //starting from 1; 0 is for ident col
    record.SetValue(2, item.Field2);
    dataTable.Add(record);
}

Hope this can help someone else.

Flasher64
  • 53
  • 7