How to use Dapper to query and insert DataTable?

c# 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.

1
0
4/2/2020 12:28:22 AM

Accepted Answer

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;
    }
2
3/31/2020 11:06:54 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow