关联表的查询

一对一的关系

var conn = new MySqlConnection(connectionString);

var query  = connection.Query<Customer, CustomerExt , Customer>(
		@"select c.customerId, c.customernick ,
				e.taobaonick , e.jdnick from customers as c 
							join customerExt as e 
							on c.customerId = e.customerId where e.customerId = 3",
							(customer,ext)=>{
								customer.CustomerExtProperty = ext;
								return customer;
							},splitOn:",taobaonick");

一对多的关系

多splitOn下的三表查询

 var query = connection.Query<Customer, Article, CustomerExt, Customer>
                        (@"select c.customerid,c.customernick,
												a.title,a.published,
												e.jdnick,e.taobaonick 
												from customers as c
												join articles as a
												on c.customerid=a.customerid
												join customerext as e
												on c.customerid=e.customerid
												where a.customerid=3843",
                                 (customer, article, ext) =>
                                 {
                                     if (!dict.ContainsKey(customer.CustomerID))
                                     {
                                         customer.ArticleList.Add(article);
                                         customer.CustomerExtProperty = ext;

                                         dict[customer.CustomerID] = customer;

                                     }
                                     else
                                     {
                                         dict[customer.CustomerID].ArticleList.Add(article);
                                     }

                                     return customer;

                                 }, splitOn: "title,jdnick");

多条查询

 var query = connection.QueryMultiple(@"select * from customers where customernick='一线码农';
				select * from customers where customernick='博客园团队'");

var customer1 = query.Read<Customer>();
var customer2 = query.ReadFirstOrDefault<Customer>();

buffer

buffer 模式是false，按需执行

return command.Buffered ? data.ToList() : data;

var query = conn.query<Customer>(@"select * from customers where customerLink 
					 in @customerLinks",
					new { customersLinks = new string[]{"xx","yy"} },
					buffer:true);

自定义属性的使用

CustomPropertyTypeMap

这里面讲的又好几种方法，更详细 https://stackoverflow.com/questions/40703015/custom-mapping-in-dapper

public class t_SoDtl
{
    [System.ComponentModel.Description("KeySeq")]
    public Guid ID { get; set; } //数据库中的列名KeySeq

    [System.ComponentModel.Description("SoKeySeq")]
    public Guid SoKeySeq { get; set; }

    [System.ComponentModel.Description("ItemId")]
    public string ItemNo { get; set; }

    [System.ComponentModel.Description("ItemName")]
    public string ItemName { get; set; }

    [System.ComponentModel.Description("Price")]
    public decimal Price { get; set; }

    [System.ComponentModel.Description("Qty")]
    public decimal Quality { get; set; }//数据库中的列名Qty

    [System.ComponentModel.Description("Amount")]
    public decimal Amount { get; set; }
}

//下面就是CustomPropertyTypeMap的用法
//找出Decsription = Column的属性
var map = new CustomPropertyTypeMap(typeof(t_SoDtl),
(type, columnName) => type.GetProperties()
    .Where(prop => prop.GetCustomAttributes(false)
		.OfType<DescriptionAttribute>().Any(attr => attr.Description == columnName)) 
    .FirstOrDefault());

Dapper.SqlMapper.SetTypeMap(typeof(t_SoDtl), map);


DbConnection con = this.GetConnection();
return con.Query<t_SoDtl>(@"select * from dbo.t_SoDtl 
					where SoKeySeq = @SoKeySeq", new {SoKeySeq = soKeySeq}).ToList();

//插入 (注意参数的名称与实体属性的对应)
string strSQL = @"
INSERT INTO dbo.t_so(KeySeq,SoNo,SoDate,CustomerName,Remark)
VALUES(@KeySeq,@SoNo,@SoDate,@CustomerName,@Remark)";

DbConnection con = this.GetConnection();
//con.Execute(strSQL, new {KeySeq = so.KeySeq, SoNo = so.SoNo, 
						SoDate = so.SoDate, CustomerName = so.CustomerName, Remark = so.Remark});
con.Execute(strSQL, so);