Dapper记录

List Support

Dapper允许您传入IEnumerable<int>，并将自动参数化查询

例如:

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

将转化为:

select * from (select 1 as Id union all select 2 union all select 3) as X where Id in (@Ids1, @Ids2, @Ids3)" // @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3

Multi Mapping

Dapper允许您将一行映射到多个对象。如果您想避免无关的查询和急切的加载关联，这是一个关键特性。

例如：

两个类Post和User

class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public User Owner { get; set; }
}

class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

现在让我们说，我们想要映射一个连接POST和User表的查询。到目前为止，如果我们需要组合两个查询的结果，我们就需要一个新的对象来表示它，但是在这种情况下，将用户对象放在Post对象中更有意义。这是多个映射的用例。您告诉Dapper，查询返回一个Post和一个User对象，然后给它一个函数，描述包含一个Post和一个User对象的每一行要做什么。在我们的示例中，我们希望获取用户对象并将其放入POST对象中。所以我们编写函数：

(post, user) => { post.Owner = user; return post; }

查询方法的3种类型参数指定Dapper应该使用哪些对象来反序列化该行，以及将返回哪些对象。我们将这两行解释为Post和User的组合，并返回一个Post对象。因此类型声明变成

<Post, User, Post>

完整列子：

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;});
var post = data.First();

当您的Id列被命名为Id或id时，Dapper能够拆分返回的行。如果主键不同，或者您要在除Id以外的点拆分行，请使用可选的splitOn参数。

 例如本地例子

 public class StudentModel
    {
        public int StudentId { get; set; }

        public int TeacherId { get; set; }

        public int ClassId { get; set; }
        public string StudentName { get; set; }

        public int StudentAge { get; set; }

        public TeacherModel Teacher { get; set; }

        public ClassModel Class { get; set; }
    }


  public class ClassModel
    {
        public int ClassId { get; set; }

        public string ClassName { get; set; }
    }

 public class TeacherModel
    {
        public int TeacherId { get; set; }

        public string TeacherName { get; set; }
    }

      {
                //两表关联查询
                string sql = "select * from [Student] left join [Class] on [Student].ClassId=[Class].ClassId";
                using (IDbConnection con = GetConnection())
                {
                    var data = con.Query<StudentModel, ClassModel, StudentModel>(sql, (s, c) => { s.Class = c; return s; }, splitOn: "StudentId,ClassId");
                    var post = data.First();
                }
        }


            {
                //三表联合
                string sql = "select * from [Student] left join [Class] on [Student].ClassId=[Class].ClassId left join [Teacher] on [Student].TeacherId=[Teacher].TeacherId";
                using (IDbConnection con = GetConnection())
                {
                    var data = con.Query<StudentModel, ClassModel, TeacherModel, StudentModel>(sql, (s, c, t) => { s.Class = c; s.Teacher = t; return s; }, splitOn: "StudentId,ClassId,TeacherId");
                    var post = data.First();
                }
            }

Multiple Results

Dapper允许您在一个查询中处理多个结果。

例如：

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

Stored Procedures

Dapper完全支持存储：

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

如果你想要更漂亮的东西，你可以：

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

实例：

  {

                //调用存储过程并传入参数
                using (IDbConnection con = GetConnection())
                {
                    var p = new DynamicParameters();
                    p.Add("@TableName", "Student");
                    p.Add("@PrimaryKey", "StudentId");
                    p.Add("@Fields", "*");
                    p.Add("@Condition", "1=1");
                    p.Add("@CurrentPage", 1);
                    p.Add("@PageSize", 5);
                    p.Add("@Sort", "");
                    p.Add("@RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
                    var data = con.Query("ProcGetPageData", p, commandType: CommandType.StoredProcedure).ToList();
                    int dataCount = p.Get<int>("@RecordCount");
                }


            }