How to use Dapper to query and insert DataTable?

c# dapper

Question

I have a model enclosed by another model, such as:

Initial Model:

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

Next Model:

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

I build a table-valued argument in SQL and pass it into my stored procedure as:

 @AndroidResultTableType [HELPER].[PushNotificationTestTableType] READONLY

I transmit the result as follows using Dapper:

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;
    }
}

However, it makes one exception:

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

How do I succeed?AndroidResultTableType if it's not a list, as datatable. The row is always one.

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

Accepted Answer

It is necessary to build a DataTable and populate its fields rather than supplying a straight object.

    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