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

posted @ 2021-11-29 21:52 harrychinese 閱讀(403) 評論(0) 收藏舉報

刷新頁面返回頂部

harrychinese

Dapper 單表 Object-Relation Mapping

公告