Dapper - Insert using nested object

Question

I have a class User with a nested class Bank.

class User{
        int Id;
        string username;
        Bank bank;

    }

    Class Bank{
        int id;
        string name;
    }

I need to create an Insert function for User. Is there a way in Dapperto execute a query and binding the parameters from a nested a object ?

Answer 1

For such a scenario where not just a User object but a List<User> might also need a db insertion, you can consider using table valued parameters. For your question it would be:

• Use IEnumerable<User> and IEnumerable<Bank>, even if it is just one object

• Make the TVP with appropriate schema and same order of columns as in IEnumerable collection for the stored procedure, else it will lead to error

• You can use dynamic parameters to bind the parameters, where for TVP, IEnumerable can be supplied using extension method AsTableValuedParameters in case you are using anonymous type parameters not dynamic parameters, then use the ObjectReader from FastMember in Nuget to convert IEnuemrable<T> to Datatable, which is mandatory for TVP. Even custom code can be used for IEnuemrable<T> to Datatable conversion, in case few columns need to be omitted, following is the code snippet:

---

public static DataTable CreateTable<TDataTable>(this IEnumerable<TDataTable> collection)
{
    // Fetch the type of List contained in the ParamValue
    var tableType = typeof(TDataTable);

    // Create DataTable which will contain data from List<T>
    var dataTable = new DataTable();

    // Fetch the Type fields count
    var columnCount = tableType.GetProperties().Count();

    var columnNameMappingDictionary = new Dictionary<string, string>();

    // Create DataTable Columns using table type field name and their types
    // Traversing through Column Collection
    for (var counter = 0; counter < columnCount; counter++)
    {
        var propertyInfo = tableType.GetProperties()[counter]; 

        var columnName = propertyInfo.Name;

        columnNameMappingDictionary.Add(propertyInfo.Name,
            propertyInfo.Name);

        // Fetch the current type of a property and check whether its nullable type before adding a column
        var currentType = tableType.GetProperties()[counter].PropertyType;

        dataTable.Columns.Add(columnName, Nullable.GetUnderlyingType(currentType) ?? currentType);
    }

    // Return parameter with null value
    if (collection == null)
        return dataTable;

    // Traverse through number of entries / rows in the List
    foreach (var item in collection)
    {
        // Create a new DataRow
        var dataRow = dataTable.NewRow();

        foreach (var columnName in columnNameMappingDictionary.Select(propertyinfo => propertyinfo.Value))
        {
            dataRow[columnName] = item.GetType().GetProperty(columnName).GetValue(item) ?? DBNull.Value;
        }
        // Add Row to Table
        dataTable.Rows.Add(dataRow);
    }

    return (dataTable);
}

Answer 2

You can write a custom mapper for Dapper using DapperExtensions:

public sealed class UserMapper : ClassMapper<User>
{
    public UserMapper()
    {
        Map(x => x.bank.id).Column("BankId");
        Map(x => x.bank.name).Column("BankName");

        AutoMap();
    }
}

https://github.com/tmsmith/Dapper-Extensions/wiki/Customized-mapping-for-a-class

Make sure you register the assembly containing the mapper:

DapperExtensions.SetMappingAssemblies(new[] { typeof(UserMapper).Assembly });