ORM-Dapper学习<五>.Dapper的进阶用法

1.    联合查询

A.    写法一

public user_info_model get_user_info__group_info(int id)
        {
            using (conn)
            {
                string query = @"SELECT * FROM dbo.user_info U 
                                 LEFT JOIN user_group UG 
                                 ON U.id=UG.user_info_id 
                                 WHERE U.id = @id";
                user_info_model userinfo = null;
                var b = conn.Query<user_info_model, user_group_model, user_info_model>(query,
                    (user, group) =>
                    {
                        if (userinfo == null || userinfo.id != user.id)
                            userinfo = user;
                        if (group != null)
                            userinfo.Reviews.Add(group);
                        return userinfo;
                    }, new { id },splitOn: "user_info_id").Distinct().SingleOrDefault();
                return b;
            }
        }
附:关键字Reviews在model的属性
public virtual user_group_model usergroup { get; set; }

B.    写法二

注一：连表主键都是唯一且都为id/ID时，示例如下：

var sql = @"SELECT * FROM dbo.user_info U INNER JOIN group_info G ON U.id = G.id";
var data = conn.Query<user_info_model, group_info_model, user_info_model>(sql, (user_info, group_info) => { user_info.groupinfo = group_info; return user_info; });

注二：Dapper 假定你的 ID 列被命名为“ID”或“id”，如果你的主键是不同的，或者你想在点上拆分宽行点，而不是“ID”，可以使用可选的“splitOn”参数。不选会出现这个异常：“When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id.”

splitOn的用法示例：

var sql = @"SELECT * FROM dbo.user_info U LEFT JOIN user_group UG ON U.id = UG.user_info_id LEFT JOIN group_info G ON UG.group_info_id = G.id ";
var data = conn.Query<user_info_model, user_group_model, user_info_model>(sql, (user_info, user_group) => { user_info.usergroup = user_group; return user_info; }, splitOn: "user_info_id,group_info_id");

2.    返回多个结果

public void get_moredb(int id)
        {
            var sql = @"SELECT * FROM dbo.user_info WHERE id=@id
                        SELECT * FROM dbo.group_info WHERE id=@id";

            using (var multi = conn.QueryMultiple(sql, new { id = id }))
            {
                var user = multi.Read<user_info_model>().Single();
                var group = multi.Read<group_info_model>().ToList();
            }
        }

3.    执行存储过程

A.    简单写法

public void execute_user_info_withpms(user_info_model user)
       {
            var ur = conn.Query<user_info_model>("p_get_user_info_by_id", 
new { id = user.id }, commandType: CommandType.StoredProcedure).First();
｝

B.    灵活写法

注：返回OUTPUT/RETURN等结果

public void execute_user_info_withpms(user_info_model user)
        {
            int result = 0;
            var result2 = 0;
            DynamicParameters pms = new DynamicParameters();
            pms.Add("@name", user.name);
            pms.Add("@pwd", user.pwd);
            pms.Add("@loginDate", user.loginDate);
            //返回SP中Output的值
            pms.Add("@result", null, DbType.Int32, ParameterDirection.Output);
            //返回SP中Return的值
            pms.Add("@result2", null, DbType.Int32, ParameterDirection.ReturnValue);
            using (conn)
            {
                conn.Execute("dbo.p_insert_user_info", pms, null, null, CommandType.StoredProcedure);
                result = pms.Get<int>("@result");
                result2 = pms.Get<int>("@result2");
            }
        }

4.    Ansi String 和 Varchar

注：在 SQL Server 上，当查询非 Unicode 时，查询 Unicode 和 ANSI 时需要使用 Unicode。Dapper 支持 varchar 参数，如果你在一个 varchar 列上执行一个 where 语句，确保下面方式传递参数：

public IEnumerable<user_info_model> ansiStrings()
        {
            var str = conn.Query<user_info_model>("select * from user_info where name = @name", new { name = new DbString { Value = "Test1", IsFixedLength = true, Length = 10, IsAnsi = true } });
            return str;
        }

5.    事务处理

public int transaction(user_info_model user)
        {
            //继承SQL的链接以保证事务在同一个链接中执行
            using (IDbConnection _conn = conn)
            {
                //开始事务
                IDbTransaction transaction = _conn.BeginTransaction();
                try
                {
                    string query = @"DELETE FROM user_info WHERE id = @id";
                    string query2 = @"DELETE FROM user_group WHERE user_info_id = @id ";
                    int row = _conn.Execute(query, new { id = user.id }, transaction);
                    row += _conn.Execute(query2, new { id = user.id }, transaction);

                    //提交事务
                    transaction.Commit();
                    return row;
                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    transaction.Rollback();
                    throw new Exception(ex.Message);
                }
            }