Skip to main content
 首页 » 编程设计

C# 记一次使用dapper的坑(数据库与类映射)(转)

2022年07月19日146zdz8207

本来是测试dapper转换真正的mysql语句后是否能真正使用索引的,不过被一个地方卡住了,在查询的时候一直不能映射强类型的值。找了大概1小时才找到处理的方式,记录下来免得忘记。

先看表设计


 
image.png

类定义,因为程序如果使用下划线感觉会很突兀,不符合C#类命名规范,所以统一使用了大写开头的驼峰式命名,找了一个通过Attribute的映射到数据库字段的方式,代码在最后贴上

public class PersonInfo 
    { 
        [Column(Name = "mainid")] public long MainId { get; set; } [Column(Name = "id_card_number")] public string IdCardNumber { get; set; } [Column(Name = "name")] public string Name { get; set; } [Column(Name = "created_datetime")] public DateTime CreatedDateTime { get; set; } } 

先插入数据,通过dapper插入数据是没啥问题的,看看数据


 
image.png

代码也是正常,即使不指定列,只要多传入一个MainId也是能正常插入数据的

static void Main(string[] args) { string connectionString = @"server=localhost;port=3306;database=tor_db_test;user=root;Password=123456;CharSet=utf8mb4;Pooling=true;Min Pool Size=0;Max Pool Size=100;"; using (IDbConnection connection = new MySqlConnection(connectionString)) { var resultInt = connection.Execute("INSERT INTO person_info VALUES (@MainId,@IdCardNumber,@Name,@CreatedDateTime)", new PersonInfo() { MainId = 2, IdCardNumber = "440684200001010000", Name = "呜呜呜", CreatedDateTime = DateTime.Now }); Console.WriteLine(resultInt); } Console.WriteLine(); Console.ReadLine(); } 

但是查询就有问题了

static void Main(string[] args) { string connectionString = @"server=localhost;port=3306;database=tor_db_test;user=root;Password=123456;CharSet=utf8mb4;Pooling=true;Min Pool Size=0;Max Pool Size=100;"; using (IDbConnection connection = new MySqlConnection(connectionString)) { //var p = connection.Query<PersonInfo>("SELECT * FROM person_info "); var p = connection.Query<PersonInfo>("SELECT mainid,id_card_number,name,created_datetime FROM person_info "); Console.WriteLine(JsonConvert.SerializeObject(p, Formatting.Indented)); } Console.WriteLine(); Console.ReadLine(); } 

查询结果如下,强类型查询无法自动匹配值了


 
image.png

试一下动态类型,正常的,所以问题定位在dapper强类型转换没能匹配上


 
image.png

最后发现有这个配置,主要是忽略下划线再进行匹配

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true; 

加上之后就正常了


 
image.png

代码如下

static void Main(string[] args) { string connectionString = @"server=localhost;port=3306;database=tor_db_test;user=root;Password=123456;CharSet=utf8mb4;Pooling=true;Min Pool Size=0;Max Pool Size=100;"; using (IDbConnection connection = new MySqlConnection(connectionString)) { Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true; var p = connection.Query<PersonInfo>("SELECT mainid,id_card_number,name,created_datetime FROM person_info "); Console.WriteLine(JsonConvert.SerializeObject(p, Formatting.Indented)); } Console.WriteLine(); Console.ReadLine(); } 

通过Attribute映射代码,网上找的,么得注释

/// <summary> 
    /// Uses the Name value of the <see cref="ColumnAttribute"/> specified to determine 
    /// the association between the name of the column in the query results and the member to 
    /// which it will be extracted. If no column mapping is present all members are mapped as 
    /// usual. /// </summary> /// <typeparam name="T">The type of the object that this association between the mapper applies to.</typeparam> public class ColumnAttributeTypeMapper<T> : FallbackTypeMapper { public ColumnAttributeTypeMapper() : base(new SqlMapper.ITypeMap[] { new CustomPropertyTypeMap( typeof(T), (type, columnName) => type.GetProperties().FirstOrDefault(prop => prop.GetCustomAttributes(false) .OfType<ColumnAttribute>() .Any(attr => attr.Name == columnName) ) ), new DefaultTypeMap(typeof(T)) }) { } } [AttributeUsage(AttributeTargets.Property, AllowMultiple = true)] public class ColumnAttribute : Attribute { public string Name { get; set; } } public class FallbackTypeMapper : SqlMapper.ITypeMap { private readonly IEnumerable<SqlMapper.ITypeMap> _mappers; public FallbackTypeMapper(IEnumerable<SqlMapper.ITypeMap> mappers) { _mappers = mappers; } public ConstructorInfo FindConstructor(string[] names, Type[] types) { foreach (var mapper in _mappers) { try { ConstructorInfo result = mapper.FindConstructor(names, types); if (result != null) { return result; } } catch (NotImplementedException) { } } return null; } public SqlMapper.IMemberMap GetConstructorParameter(ConstructorInfo constructor, string columnName) { foreach (var mapper in _mappers) { try { var result = mapper.GetConstructorParameter(constructor, columnName); if (result != null) { return result; } } catch (NotImplementedException) { } } return null; } public SqlMapper.IMemberMap GetMember(string columnName) { foreach (var mapper in _mappers) { try { var result = mapper.GetMember(columnName); if (result != null) { return result; } } catch (NotImplementedException) { } } return null; } public ConstructorInfo FindExplicitConstructor() { return _mappers .Select(mapper => mapper.FindExplicitConstructor()) .FirstOrDefault(result => result != null); } } 

 
 

链接:https://www.jianshu.com/p/ea588d837014


本文参考链接:https://www.cnblogs.com/xihong2014/p/15469743.html