How to map column names to class properties with Dapper
2020-02-18 #sql#csharp
Last week, I used Dapper instead of NHibernate to perform a simple processing on a table in our Oracle database. Since this is an ancient table, the column names are "old-fashioned" and I don't want to end up with overly convoluted property names, even if it's only for a one-shot process.
Let's say my table has the following structure:
Contact_Clients
---------------
Contact_Cli_ID Number
Clt_ID Number
Contact_Cli_Nom Varchar2(40)
Contact_Cli_Prenom Varchar2(40)
Contact_Tel_Fixe Varchar2(24)
Contact_Cli_Fax Varchar2(24)
Contact_Cli_Mail Varchar2(127)
Contact_Portable Varchar2(24)
...
I have to define the C# class below:
public class Contact
{
public int Contact_Cli_ID { get; set; }
public int Clt_ID { get; set; }
public string Contact_Cli_Nom { get; set; }
public string Contact_Cli_Prenom { get; set; }
public string Contact_Tel_Fixe { get; set; }
public string Contact_Cli_Fax { get; set; }
public string Contact_Cli_Mail { get; set; }
public string Contact_Portable { get; set; }
}
But I'd prefer to use pretty names for its properties:
public class Contact
{
public int Contact_ID { get; set; }
public int Client_ID { get; set; }
public string Nom { get; set; }
public string Prenom { get; set; }
public string Telephone { get; set; }
public string Telecopie { get; set; }
public string Mail { get; set; }
public string Portable { get; set; }
}
Usually I go with SQL aliases to work around this problem. So, instead of doing a regular SELECT * FROM Contact_Clients ...
, I end up writing:
var sql = "SELECT Contact_Cli_ID AS Contact_ID,
Clt_ID AS Client_ID,
Contact_Cli_Nom AS Nom,
Contact_Cli_Prenom AS Prenom,
Contact_Tel_Fixe AS Telephone,
Contact_Cli_Fax AS Telecopie,
Contact_Cli_Mail AS Mail,
Contact_Portable AS Portable
FROM Contact_Clients
...";
Pro: I can limit my code to only useful columns, although in this case almost all columns are necessary.
Con: I've been using Dapper for a few years now, it's time to search for some improvments to redefine the names of the columns.
It's a bit of a sensitive subject and probably not really a priority. However, there is a discussion about the right way to do this, by configuration or with attributes: [Column] and [Table] Attributes .
In the meantime, a Stack Overflow question (Manually map column names with class properties) allowed me to find a quite simple solution to implement. Taken from the Dapper Tests code (and thus tested :), it uses the [Description]
attribute from System.ComponentModel
.
Thanks to this article, I can write the following code:
using System.ComponentModel;
...
class Program
{
static void Main(string[] args)
{
var cnx_string = @"Data Source=XXXX;User ID=YYYY;Password=ZZZZ";
var db = new OracleConnection(cnx_string);
db.Open();
var map = new CustomPropertyTypeMap(typeof(Contact), (type, columnName)
=> type.GetProperties().FirstOrDefault(prop => GetDescriptionFromAttribute(prop) == columnName.ToLower()));
Dapper.SqlMapper.SetTypeMap(typeof(Contact), map);
var sql = "SELECT * FROM Contact_Clients WHERE Contact_Cli_ID = 1234";
var c = db.QueryFirst<Contact>(sql);
Console.WriteLine(c.Nom + " " + c.Prenom);
db.Close();
Console.ReadLine();
}
static string GetDescriptionFromAttribute(MemberInfo member)
{
if (member == null) return null;
var attrib = (DescriptionAttribute)Attribute.GetCustomAttribute(member, typeof(DescriptionAttribute), false);
return (attrib?.Description ?? member.Name).ToLower();
}
}
public class Contact
{
[Description("Contact_Cli_ID")]
public int Contact_ID { get; set; }
[Description("Clt_ID")]
public int Client_ID { get; set; }
[Description("Contact_Cli_Nom")]
public string Nom { get; set; }
[Description("Contact_Cli_Prenom")]
public string Prenom { get; set; }
[Description("Contact_Tel_Fixe")]
public string Telephone { get; set; }
[Description("Contact_Portable")]
public string Portable { get; set; }
[Description("Contact_Cli_Mail")]
public string Mail { get; set; }
}
Thinking about it, I'm not sure it's more elegant than SQL aliases... But new!
Version en français : Comment mapper colonnes et propriétés avec Dapper.