本來(lái)是測(cè)試dapper轉(zhuǎn)換真正的mysql語(yǔ)句后是否能真正使用索引的，不過(guò)被一個(gè)地方卡住了，在查詢的時(shí)候一直不能映射強(qiáng)類型的值。找了大概1小時(shí)才找到處理的方式，記錄下來(lái)免得忘記。
先看表設(shè)計(jì)
image.png
類定義，因?yàn)槌绦蛉绻褂孟聞澗€感覺(jué)會(huì)很突兀，不符合C#類命名規(guī)范，所以統(tǒng)一使用了大寫(xiě)開(kāi)頭的駝峰式命名，找了一個(gè)通過(guò)Attribute的映射到數(shù)據(jù)庫(kù)字段的方式，代碼在最后貼上
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; }
}
先插入數(shù)據(jù)，通過(guò)dapper插入數(shù)據(jù)是沒(méi)啥問(wèn)題的，看看數(shù)據(jù)
image.png
代碼也是正常，即使不指定列，只要多傳入一個(gè)MainId也是能正常插入數(shù)據(jù)的
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();
}
但是查詢就有問(wèn)題了
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();
}
查詢結(jié)果如下，強(qiáng)類型查詢無(wú)法自動(dòng)匹配值了
image.png
試一下動(dòng)態(tài)類型，正常的，所以問(wèn)題定位在dapper強(qiáng)類型轉(zhuǎn)換沒(méi)能匹配上
image.png
最后發(fā)現(xiàn)有這個(gè)配置，主要是忽略下劃線再進(jìn)行匹配
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();
}
通過(guò)Attribute映射代碼,網(wǎng)上找的，么得注釋
/// <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);
}
}
完
