Dapper 单表 Object-Relation Mapping

Dapper 是.Net 中非常优秀的ORM 框架, 功能强大, 容易掌握, 性能好.

Dapper 为 IDbConnection 对象增加了非常扩展方法, 我们直接使用 IDbConnection 实例来查询/更新SQL接口.

=====================================

为SQL 参数传参

=====================================

设计一个简单的 eqp 表, 插入一条测试数据.

INSERT INTO oee.dbo.Eqp
(Sys_Id, Eqp_Name, Vendor, Eqp_Group)
VALUES(N'111', N'2222', N'vonder1', N'new group');

通常SQL中要包含参数, 如何为这些sql 参数传值呢, 最简单的方法是通过匿名对象, 匿名对象中的属性和SQL参数同名即可, 大小写不敏感. 

为SQL参数传参的示例代码:

        [HttpGet("UpdateTest")]
        public string UpdateTest()
        {
            string eqpName = "2222";
            using (IDbConnection conn = new SqlConnection(_connectionString))
            {
                string sql = @"update eqp set  Eqp_Group=@eqpgrp 
                from eqp 
                where 1=1
                and Eqp_Name=@name";
                conn.Execute(sql, new { Name = eqpName, eqpgrp ="new group"});
            }
            return "ok";
        }

=====================================

查询标量的示例

=====================================

public string SelectTestScalar()
{
    string eqpName = "2222";
    using (IDbConnection conn = new SqlConnection(_connectionString))
    {
        string sql = @"select count(*) from eqp
        where 1=1
        and Eqp_Name=@name";
       var count= conn.ExecuteScalar(sql, new { Name = eqpName });                
    }
    return "ok";
}

=====================================

查询单独一列的示例

=====================================

public string SelectTestStringList()
{
    string eqpName = "2222";
    using (IDbConnection conn = new SqlConnection(_connectionString))
    {
        string sql = @"select  'aaaa' from eqp
        where 1=1
        and Eqp_Name=@name";
        var listString= conn.Query<string>(sql, new { Name = eqpName });
    }
    return "ok";
}

=====================================

查询结果的Mapping 之简单模式

=====================================

dapper 针对查询结果集, 会自动为每行记录生成一个对象, 并按照字段名找同名的属性名, 完成属性赋值, 字段名和属性名大小写不敏感.  

如果我们的对象属性名和SQL字段名正好一致, 直接使用 conn.Query<T>() 即可完成 O-R Mapping工作.

Model 类代码:

public class EqpWithUnderLine
{
    public string Sys_id { get; set; } = string.Empty;
    public string Eqp_Name { get; set; } = string.Empty;
    public string Vendor { get; set; } = string.Empty;
    public string Eqp_group { get; set; } = string.Empty;
}

SQL查询代码:

public string SelectTest0()
{
    string eqpName = "2222";
    using (IDbConnection conn = new SqlConnection(_connectionString))
    {
        string sql = @"select SYS_ID, EQP_NAME, VENDOR, EQP_GROUP, EQP_TYPE, MOVE_IN_DATE, PLC_DATA_ID, 
        DASHBOARD_SHOW_FLAG, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME 
        from eqp 
        where 1=1
        and Eqp_Name=@name";
        var eqpList = conn.Query<EqpWithUnderLine>(sql, new { Name = eqpName });
    }
    return "ok";
}

=====================================

查询结果的Mapping 之使用Dictionary维护column -> property 配对

=====================================

如果类的Property 和 SQL 结果字段不完全一致, 需要手工建立 column -> property 的关系, 这里使用了一个 dictionary 来保存对应关系.

这一做法优点是, 代码清晰简单;  缺点是,  重构代码需要同时更新dictionary中的名字, 维护成本较高.

Model 类代码:

/// <summary>
/// 类的property 和 SQL 结果字段不完全一致
/// </summary>
public class Eqp
{ 
    public string SysId { get; set; } = string.Empty;
    public string EqpName { get; set; } = string.Empty;
    public string Vendor { get; set; } = string.Empty;
    public string EqpGroup { get; set; } = string.Empty;


    /// <summary>
    /// 维护一个 column -> property 的配对关系
    /// </summary>
    public static void RegisterTypeMapByNameDictionary()
    {
        var columnPropertyMap = new Dictionary<string, string>();
        columnPropertyMap.Add("sys_id", "SysId");
        columnPropertyMap.Add("Eqp_Name", "EqpName");
        columnPropertyMap.Add("vendor", "Vendor");
        columnPropertyMap.Add("eqp_group", "EqpGroup"); 
        DapperHelper.RegisterTypeMapByNameDictionary(columnPropertyMap, typeof(Eqp));
    }
}

SQL查询代码:

public string SelectTest2()
{
    //按照 column-property Dictionary , 同一类只需要注册一次, 通常放在程序入口处
    Eqp.RegisterTypeMapByNameDictionary();

    string eqpName = "2222";
    using (IDbConnection conn = new SqlConnection(_connectionString))
    {
        string sql = @"select Sys_Id, Eqp_Name, Vendor, Eqp_Group, Eqp_Type, Move_In_Date, PLC_Data_Id, 
        Dashboard_Show_Flag, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME 
        from eqp 
        where 1=1
        and Eqp_Name=@name";
        var eqpList = conn.Query<Eqp>(sql, new { Name = eqpName });
    }
    return "ok";
}

=====================================

查询结果的Mapping 之使用注解维护column -> property 配对

=====================================

如果类的Property 和 SQL 结果字段不完全一致, 需要手工建立 column -> property 的关系, 这里使用了一个 Description Attribute 来保存对应关系.

这一做法优点是, 代码清晰简单, 因为 Attribute 直接放在 property 前,  即使重构代码通常也会注意到需要修改 Describe Attribute, 维护成本也较低. 

Model 类代码:

/// <summary>
/// 类的property 和 SQL 结果字段不完全一致, 使用 Description Attribute 来保存对应关系
/// </summary>
public class EqpWithAttr
{
    [Description("sys_id")] public string SysId { get; set; } = string.Empty;
    [Description("Eqp_Name")] public string EqpName { get; set; } = string.Empty;
    [Description("vendor")] public string Vendor { get; set; } = string.Empty;
    [Description("eqp_group")] public string EqpGroup { get; set; } = string.Empty;

    public static void RegisterTypeMapByDescriptionAttr()
    {
        DapperHelper.RegisterTypeMapByDescriptionAttr(typeof(Eqp)); 
    } 
}

SQL查询代码:

public string SelectTest1()
{
    //按照 Description Attribute 注册 Dapper TypeMap, 同一类只需要注册一次, 通常放在程序入口处
    EqpWithAttr.RegisterTypeMapByDescriptionAttr();

    string eqpName = "2222";
    using (IDbConnection conn = new SqlConnection(_connectionString))
    {
        string sql= @"select Sys_Id, Eqp_Name, Vendor, Eqp_Group, Eqp_Type, Move_In_Date, PLC_Data_Id, 
        Dashboard_Show_Flag, CREATED_BY, CREATED_TIME, UPDATED_BY, UPDATED_TIME 
        from eqp 
        where 1=1
        and Eqp_Name=@name";  
        var eqpList= conn.Query<EqpWithAttr>(sql, new { name = eqpName });
    }
    return "ok";
}

两种手动Mapping方法, 都用到的 DapperHelper 类代码如下:

public class DapperHelper
{
    public static string? GetDescriptionFromAttribute(MemberInfo member)
    {
        if (member == null) return null;

        Attribute? attrib = Attribute.GetCustomAttribute(member, typeof(DescriptionAttribute), false);
        if (attrib == null) return null;
        DescriptionAttribute attrib2 = (DescriptionAttribute)attrib;
        return (attrib2?.Description ?? member.Name).ToLower();
    }

    public static void RegisterTypeMapByDescriptionAttr(Type objType)
    {
        var map = new CustomPropertyTypeMap(objType,
        (type, columnName) => type.GetProperties()
        .FirstOrDefault(prop => GetDescriptionFromAttribute(prop) == columnName.ToLower()
                        ));
        Dapper.SqlMapper.SetTypeMap(objType, map);            
    }

    public static void RegisterTypeMapByNameDictionary(Dictionary<string, string> columnPropertyMap, Type objType)
    {
        var map = new CustomPropertyTypeMap(
            typeof(Eqp), (type, columnName) =>
            {
                foreach (var pair in columnPropertyMap)
                {
                    if (pair.Key.ToLower() == columnName.ToLower())
                    {
                        return type.GetProperty(pair.Value);
                    }
                }
                return null;
            });
        Dapper.SqlMapper.SetTypeMap(objType, map);
    }
}