Dapper在Oracle中QueryMultiple无效的解决方法.

Dapper在Oracle中QueryMultiple无效的解决方法.

• 项目版本: .Net4.5.2, Dapper=>1.60.6, Oracle.ManagedDataAccess=>19.6.0,Oracle==>12C
• 问题: 在Dapper中使用QueryMultiple,只能查询单SQL,如果是多条SQL,那么会提示SQL语句异常;
• 分析:
  • Dapper对MSSQLSERVER支持最好,使用QueryMultiple没有任何问题;
  • Oracle和MSSQLSERVER对SQL的解析不同;

解决方法:

Dapper中有SqlMapper.IDynamicParameters,它是Dapper处理参数进行动态绑定的接口;

我们需要解决Oracle参数绑定的问题,

直接给出示例:

using Dapper;
using Oracle.ManagedDataAccess.Client;
using System.Collections.Generic;
using System.Data;

namespace LayIM.OracleServer
{
    public class OracleDynamicParameters : SqlMapper.IDynamicParameters
    {
        private readonly DynamicParameters dynamicParameters = new DynamicParameters();

        private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>();

        public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = null, int? size = null)
        {
            OracleParameter oracleParameter;
            if (size.HasValue)
            {
                oracleParameter = new OracleParameter(name, oracleDbType, size.Value, value, direction);
            }
            else
            {
                oracleParameter = new OracleParameter(name, oracleDbType, value, direction);
            }

            oracleParameters.Add(oracleParameter);
        }

        public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)
        {
            var oracleParameter = new OracleParameter(name, oracleDbType, direction);
            oracleParameters.Add(oracleParameter);
        }

        public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);

            var oracleCommand = command as OracleCommand;

            if (oracleCommand != null)
            {
                oracleCommand.Parameters.AddRange(oracleParameters.ToArray());
            }
        }
    }
}

然后给出QueryMultiple使用示例:

public override CommonResult GetInitInfo(long userId)
        {
            return _storage.UseConnection<CommonResult>(connection =>
            {

                string sql = @"
BEGIN
OPEN :rslt1 FOR SELECT T.ID, T.USERNAME, T.SIGNIMAGE SIGN, NVL(T.AVATAR, '/AVATARS/user.jpg') AVATAR FROM S_USER T WHERE T.ID = :userId;
OPEN :rslt2 FOR SELECT T.ID, GROUP_NAME GROUPNAME FROM IM_FRIEND_GROUP T WHERE T.USER_ID = :userId;
OPEN :rslt3 FOR SELECT A.ID GID, C.ID, C.USERNAME, C.SIGNIMAGE SIGN, C.AVATAR FROM IM_FRIEND_GROUP A INNER JOIN IM_FRIEND_GROUP_DETAIL B ON A.ID = B.GROUP_ID LEFT JOIN S_USER C ON B.USER_ID = C.ID WHERE A.USER_ID = :userId;
OPEN :rslt4 FOR SELECT B.ID, B.NAME GROUPNAME, B.AVATAR FROM IM_BIG_GROUP_DETAIL A LEFT JOIN IM_BIG_GROUP B ON A.GROUP_ID = B.ID WHERE A.USER_ID = :userId;
END;
";
                OracleDynamicParameters dynParams = new OracleDynamicParameters();
                dynParams.Add(":rslt1", OracleDbType.RefCursor, ParameterDirection.Output);
                dynParams.Add(":rslt2", OracleDbType.RefCursor, ParameterDirection.Output);
                dynParams.Add(":rslt3", OracleDbType.RefCursor, ParameterDirection.Output);
                dynParams.Add(":rslt4", OracleDbType.RefCursor, ParameterDirection.Output);
                dynParams.Add(":userId", OracleDbType.Int32, ParameterDirection.Input, userId);

                SqlMapper.GridReader reader = connection.QueryMultiple(sql, param: dynParams);

                var result = new BaseListResult();
                result.mine = reader.ReadFirstOrDefault<UserModel>();
                //处理friend逻辑 start
                var friend = reader.Read<FriendGroupModel>();
                var groupUsers = reader.Read<GroupUserModel>();

                friend.ToList().ForEach(f =>
                {
                    //每一组的人分配给各个组
                    f.list = groupUsers?.Where(x => x.gid == f.id);
                });
                result.friend = friend;
                //处理friend逻辑 end
                //读取用户所在群
                result.group = reader.Read<BigGroupModel>();

                _storage.ReleaseConnection(connection);
                return new CommonResult { code = result.mine == null ? 1 : 0, msg = result.mine == null ? "用户不存在" : "", data = result };
            });
        }

给出结果截图:

希望对大家有帮助.