Dapper - Mapping class names/properties to database fields

Question

I have a class...

public class WireTransferRequest
    {
        public int RequestID { get; set; }
        public DateTime RequestDate { get; set; }
        public string RequesterName { get; set; }
        public string AuthorizerName { get; set; }
        public string AuthorizerSignature { get; set; }
        public DateTime TransferDate { get; set; }
        public string CustomerNames { get; set; }
        public string Reasons { get; set; }
        public string Amounts { get; set; }
        public decimal TotalOfAmounts { get; set; }
    }

I have a table....

CREATE TABLE IF NOT EXISTS `digital_forms`.`wire_transfer_request` (
  `request_id` INT NOT NULL,
  `request_date` DATETIME NOT NULL,
  `requester_name` VARCHAR(64) NOT NULL,
  `authorizer_name` VARCHAR(64) NULL,
  `authorizer_signature` VARCHAR(64) NULL,
  `transfer_date` DATETIME NULL,
  `customer_names` VARCHAR(64) NULL,
  `customer_reasons` VARCHAR(128) NULL,
  `customer_amounts` VARCHAR(128) NULL,
  `total_of_customer_amounts` DECIMAL(19,2) NULL,
  PRIMARY KEY (`request_id`),
  INDEX `idx_request_date` (`request_date` ASC) VISIBLE,
  INDEX `idx_requestor_name` (`requestor_name` ASC) VISIBLE)
ENGINE = InnoDB

and I have a query...

    public override void Add(WireTransferRequest request)
        {

            if (request == null)
                throw new ArgumentNullException(nameof(request));

            request.RequestID = _transaction.Connection.ExecuteScalar<int>(

            "INSERT INTO wire_transfer_request(request_date, requester_name, authorizer_name," +
                                             "authorizer_signature, transfer_date, customer_names," +
                                             "customer_reasons, customer_amounts, total_of_customer_amounts" +

            "VALUES (@request_date, @requester_name, @authorizer_name," +
                                             "@authorizer_signature, @transfer_date, @customer_names," +
                                             "@customer_reasons, @customer_amounts, @total_of_customer_amounts; SELECT SCOPE_IDENTITY()",
                                              new
                                             {
                                                 request_date = request.RequestDate,
                                                 requester_name = request.RequesterName,
                                                 authorizer_name = request.AuthorizerName,
                                                 authorizer_signature = request.AuthorizerSignature,
                                                 transfer_date = request.TransferDate,
                                                 customer_names = request.CustomerNames,
                                                 customer_reasons = request.Reasons,
                                                 customer_amounts = request.Amounts,
                                                 total_of_customer_amounts = request.TotalOfAmounts,
                                             },
                                             _transaction
                                             );
        }

I want Dapper to be able to recognize and map the column and table names appropriately. A quick Google search of this turns up a lot of old, old results so I'm curious as to an updated (if possible) solution/work around for this.

I have tried using the MatchNamesWithUnderscores mapper but it does not achieve the result I desire with insertions.

Answer 1

I'm no Dapper guru, but here are my ideas.

Option 0. It works

There are 2 ) missing in your sql.

The following snippet works.

var sql = "INSERT INTO wire_transfer_request(request_date, requester_name, authorizer_name," +
         "authorizer_signature, transfer_date, customer_names," +
         "customer_reasons, customer_amounts, total_of_customer_amounts)" +

"VALUES (@request_date, @requester_name, @authorizer_name," +
         "@authorizer_signature, @transfer_date, @customer_names," +
         "@customer_reasons, @customer_amounts, @total_of_customer_amounts); SELECT SCOPE_IDENTITY()";

var o = new 
{
    request_date = DateTime.UtcNow,
    requester_name = "X",
    authorizer_name = "Y",
    authorizer_signature = "Sig",
    transfer_date = DateTime.UtcNow,
    customer_names = "Z",
    customer_reasons = "A",
    customer_amounts = "B",
    total_of_customer_amounts = 10m,
};

var inserted = conn.ExecuteScalar(sql, o);

inserted contains the request_id of the newly inserted row.

Option 1. A class for the table

Insert (which is not the core of this answer) is from the official Dapper.Contrib.

[Table("wire_transfer_request")]
public class WireTransferRequest2
{
    public int request_id { get; set; }

    public DateTime request_date { get; set; }

    public string requester_name { get; set; }

    public string authorizer_name { get; set; }

    public string authorizer_signature { get; set; }

    public DateTime transfer_date { get; set; }

    public string customer_names { get; set; }

    public string customer_reasons { get; set; }

    public string customer_amounts { get; set; }

    public decimal total_of_customer_amounts { get; set; }
}

using Dapper.Contrib.Extensions;

(...)

var o = new WireTransferRequest2
{
    request_id = 1,
    request_date = DateTime.UtcNow,
    requester_name = "X",
    authorizer_name = "Y",
    authorizer_signature = "Sig",
    transfer_date = DateTime.UtcNow,
    customer_names = "Z", 
    customer_reasons = "A",
    customer_amounts = "B",
    total_of_customer_amounts = 10m,
};

conn.Insert(o);

Option 2. Use ColumnAttribute

Details at Manually map column names with class properties.