Dapper table valued parameter as a property?

Question

I have a stored proc like this:

CREATE PROCEDURE [dbo].[Organisation_Insert]
 @OrganisationXId uniqueidentifier
,@Enabled bit
,@Timezone nvarchar(50)
,@MinimumValue float
,@Rules ReminderRuleType READONLY ...

ReminderRuleType is a user defined type.

In my app I have this:

class OrganisationDTO
    {
        private readonly IOrganisationDocument _orgDoc;
        public long OrganisationId { get { return _orgDoc.OrganisationId; } }
        public Guid OrganisationXId { get { return _orgDoc.OrganisationXId; } }
        public string TimeZone { get { return _orgDoc.TimeZone; } }
        public bool Enabled { get { return _orgDoc.Enabled; } }
        public decimal MinimumValue { get { return _orgDoc.MinimumValue; } }
        public RuleTableValuedParameters Rules { get; private set; }

        public OrganisationDTO(IOrganisationDocument orgDoc)
        {
            _orgDoc = orgDoc;
            Rules = new RuleTableValuedParameters("@Rules", _orgDoc.Rules);
        }
    }

RuleTableValuedParameters implements SqlMapper.IDynamicParameters which has an AddParameters method.

When I execute the query, the @Rules parameter is never passed (using SQLProfiler). I can also see that AddParameters is never called.

Is this possible to do?

Thanks

Answer 1

Here's a simplified example based on your code that shows it working just fine; AddParameters is invoked correctly, and the values are conveyed to the stored procedure. As a side note: if you are using DataTable for your TVPs, the library supports that directly with no additional code needed.

public void SO29596645_TvpProperty()
{
    try { connection.Execute("CREATE TYPE SO29596645_ReminderRuleType AS TABLE (id int NOT NULL)"); }
    catch { }
    connection.Execute(@"create proc #SO29596645_Proc (@Id int, @Rules SO29596645_ReminderRuleType READONLY)
                        as begin select @Id + ISNULL((select sum(id) from @Rules), 0); end");
    var obj = new SO29596645_OrganisationDTO();
    int val = connection.Query<int>("#SO29596645_Proc", obj.Rules, commandType: CommandType.StoredProcedure).Single();

    // 4 + 9 + 7 = 20
    val.IsEqualTo(20);

}

class SO29596645_RuleTableValuedParameters : Dapper.SqlMapper.IDynamicParameters {
    private string parameterName;

    public SO29596645_RuleTableValuedParameters(string parameterName)
    {
        this.parameterName = parameterName;
    }


    public void AddParameters(IDbCommand command, Dapper.SqlMapper.Identity identity)
    {
        Console.WriteLine("> AddParameters");
        SqlCommand lazy = (SqlCommand)command;
        lazy.Parameters.AddWithValue("Id", 7);
        DataTable table = new DataTable {
            Columns = {{"Id", typeof(int)}},
            Rows = {{4}, {9}}
        };
        lazy.Parameters.AddWithValue("Rules", table);
        Console.WriteLine("< AddParameters");
    }
}
class SO29596645_OrganisationDTO
{
    public SO29596645_RuleTableValuedParameters Rules { get; private set; }

    public SO29596645_OrganisationDTO()
    {
        Rules = new SO29596645_RuleTableValuedParameters("@Rules");
    }
}