Dapper使用笔记

[fengzhbo]

基本操作

1，执行查询映射到一个强类型的列表

public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

2，映射到动态类型的列表

public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

3，返回受影响行数

public static int Execute(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

4，执行多次

connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",

new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }

)

5，public static IDataReader ExecuteReader

6，public static T ExecuteScalar

参数化

1，DynamicParameters 集合

创建集合对象，通过Add添加项，然后传递给调用方法

var p = new DynamicParameters();

p.Add("@PriceIds", "123", DbType.String);

输出参数和返回值

var p = new DynamicParameters();

p.Add("@a", 11);

p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);

p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure); 

int b = p.Get<int>("@b");

int c = p.Get<int>("@c"); 

2，匿名类型

connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = 1, Id = 2 });

3，列表：

connection.Query<int>("select id from X where Id in @Ids", new { Ids = new int[] { 1, 2, 3 });

高级

1，映射多个对象

public static IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(

this IDbConnection cnn, string sql, Func<TFirst, TSecond, TReturn> map, object param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null

)

还有其它更多对象的重载

示例：

var sql = 

@"select * from #Posts p 

left join #Users u on u.Id = p.OwnerId 

Order by p.Id";

var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});

2，返回多个结果

public static GridReader QueryMultiple(

this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null

)

示例：

var sql = 

@"

select * from Customers where CustomerId = @id

select * from Orders where CustomerId = @id

select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))

{

var customer = multi.Read<Customer>().Single();

var orders = multi.Read<Order>().ToList();

var returns = multi.Read<Return>().ToList();

} 

存储过程

cnn.Query<User>("spGetUser", new {Id = 1}, commandType: CommandType.StoredProcedure);