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 ?

• juharr: You could just create an anonymous class new { UserId = u.Id, UserName = u.username, BankId = u.bank.id, BankName = u.bank.name }
• SilentRage47: Yeah that's what I'm doing right now, I was wondering is there's a way to do it just by passing an instance of the User class
• Stefano d'Antonio: Maybe you can create a mapper to automate it.

Answer 1

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

• Nick: And then what? This answer is either incorrect or incomplete. When I try to use @BankId in my query it is not using the BankId that is mapped in the ClassMapper. It just throws an SQL exception Must declare the scalar variable "@BankId". How do you actually use that nested class in the query after you map it?

Answer 2

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