Send and receive Datatable using Dapper

Question

I have a model inside another model like:

First Model:

public class ReminderPushNotificationTest
{
    public string UserName { get; set; }
    ....
    public ReminderPushNotificationTableType AndroidResultTableType { get; set; }
}

Second Model:

public class ReminderPushNotificationTableType
{
    public string Headers { get; set; }
    ...
}

In SQL I create a table valued parameter and receive it as a parameter in my stored procedure as:

 @AndroidResultTableType [HELPER].[PushNotificationTestTableType] READONLY

I use Dapper to send the result as:

public async Task<bool> ReminderPushNotificationInsert_Test(ReminderPushNotificationTest model)
{
    try
    {
        async Task<bool> DoReminderPushNotificationInsert_Test()
        {
            using var connection = _connectionManager.GetOpenConnection(_configuration.GetConnectionString(ConnectionString));

            await connection.QueryAsync("[Test].[usp_spName]", param: new {
                model.UserId,
                model.AndroidResultTableType
            }
            , commandType: CommandType.StoredProcedure);

            return true;
        }
        return await DoReminderPushNotificationInsert_Test();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

But it throws an exception:

The member AndroidResultTableType of type Models.Helpers.Test.ReminderPushNotificationTableType cannot be used as a parameter value

How can I pass AndroidResultTableType as datatable if is not a list. It always has 1 row.

score 4 · Accepted Answer · edited Nov 14 '21 at 15:24

Instead of passing direct object you need to construct DataTable and fill its fields

await connection.QueryAsync("[Test].[usp_spName]", new
{
    model.UserId,
    AndroidResultTableType = CreateTableType(model.AndroidResultTableType)
}, commandType: CommandType.StoredProcedure);

private static DataTable CreateTableType(
    ReminderPushNotificationTableType notification)
{
    var t = new DataTable();
    t.SetTypeName("[HELPER].[PushNotificationTestTableType]");
    t.Columns.Add("Headers");
    t.Rows.Add(notification.Headers);
    return t;
}

Send and receive Datatable using Dapper

1 Answers1