CODEWITHSUNDEEP
c#sql-serverdappertable-valued-parameters.net-standard-1.6
Flasher64
Flasher64

Reputation: 53

Dapper / SQL Server : passing TVP to stored procedure with needed fields only

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)?

Upvotes: 3

Views: 2338

Answers (1)

Flasher64
Flasher64

Reputation: 53

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.

Upvotes: 2

Related Questions