Using Dapper.TVP TableValueParameter with other parameters

Question

I have a procedure that takes in a table-valued parameter, along with others:

CREATE PROCEDURE [dbo].[Update_Records]
    @currentYear INT,
    @country INT,
    @records Record_Table_Type READONLY
AS

and am trying to call this with Dapper.TVP.

Here is the code I have so far:

        var recordsParameter = new List<SqlDataRecord>();

        // This metadata matches 'Record_Table_Type' in the Database
        var recordsMetaData = new[]
        {
            new SqlMetaData("OriginalValue", SqlDbType.Decimal, 19, 4),
            new SqlMetaData("NewValue", SqlDbType.Decimal, 19, 4),
            new SqlMetaData("NewPercent", SqlDbType.Decimal, 7, 2),
        };

        foreach (var r in records)
        {
            var record = new SqlDataRecord(recordsMetaData);
            record.SetDecimal(0, r.OriginalValue);
            record.SetDecimal(1, r.NewValue);
            record.SetDecimal(2, r.NewPercent);
            recordsParameter.Add(record);
        }

        var spParams = new DynamicParameters(new
        {
            currentYear = filter.currentYear,
            country = filter.country,
        });

        var recordsParam = new TableValueParameter("@records", "Record_Table_Type", recordsParameter);

        using (var connection = ConnectionFactory.GetConnection())
        {
            connection.Execute("Update_Records", ???, commandType: CommandType.StoredProcedure);
        }

My issue is how do I pass both sets of parameters to the procedure in the call to Dapper Execute()?

I have tried:

var spParams = new DynamicParameters(new
{
    currentYear = filter.currentYear,
    country = filter.country,
    records = new TableValueParameter("@records", "Record_Table_Type", recordsParameter);
});

connection.Execute("Update_Records", spParams, commandType: CommandType.StoredProcedure);

and

connection.Execute("Update_Records", new Object[] { spParams, recordsParam }, commandType: CommandType.StoredProcedure);

Both call the procedure, but pass an empty table parameter ( SELECT COUNT(*) FROM @records returns 0 )

I can't seem to find any actual documentation or source for Dapper.TVP, so the whole thing is very confusing, and the 2nd parameter to .Execute() is just a dynamic so that again doesn't tell me what I can and can't pass to it.

Any ideas?

What would be the alternative to the DataTable type, just in case I want to use it in asp.net core as there is no support for DataTable at the moment... — pawan nepal, Sep 20 '16 at 01:01
Did you figure this out? Could you provide a link to the answer if you did? — Jon49, Mar 31 '17 at 17:38
You have to use SqlDataRecords https://github.com/StackExchange/Dapper/blob/61e965eed900355e0dbd27771d6469248d798293/Dapper.Tests/Tests.Parameters.cs#L167 — Jon49, Apr 12 '17 at 22:31

score 17 · Accepted Answer · answered Oct 07 '14 at 22:00

17

I am on mobile and may be misunderstanding the question, but this should be just:

DataTable records = ...
connection.Execute("Update_Records",
    new {
        currentYear = filter.currentYear,
        country = filter.country,
        records
    },
    commandType: CommandType.StoredProcedure
);

answered Oct 07 '14 at 22:00

Marc Gravell

1,026,079
266
2,566
2,900

1

Yep, that's what I ended up doing. My initial searches turned up the Dapper.TVP NuGet package, which doesn't seem to be needed (or maintained?) any more. Thanks! – CodingWithSpike Oct 08 '14 at 01:13
@CodingWithSpike indeed, it shouldn't be needed any more - dapper handles TVPs internally; as for maintained: I don't know - it isn't mine ;p – Marc Gravell Oct 08 '14 at 06:49

score 9 · Answer 2 · edited May 23 '17 at 12:17

Based on an answer from Mark Gravell here: Does Dapper support SQL 2008 Table-Valued Parameters?

I changed my code to no longer use Dapper.TVP and instead just use a DataTable so the code is now:

        var recordsTable = new DataTable();
        recordsTable.Columns.Add("NewValue", typeof(Decimal));
        foreach (var netRevenue in records)
        {
            var row = recordsTable.NewRow();
            row[0] = netRevenue.NewValue;
            recordsTable.Rows.Add(row);
        }
        recordsTable.EndLoadData();

        var spParams = new DynamicParameters(new
        {
            currentYear = filter.currentYear,
            country = filter.country,
            records = recordsTable.AsTableValuedParameter("Record_Table_Type")
        });

        using (var connection = ConnectionFactory.GetConnection())
        {
            connection.Execute("Update_Records", spParams, commandType: CommandType.StoredProcedure);
        }

And this works.

You shouldn't actually need the As... here: because the sproc defines the user-defined type, it should work without specifying it. — Marc Gravell, Oct 07 '14 at 22:01

Using Dapper.TVP TableValueParameter with other parameters

2 Answers2

Linked