Dapper参数化查询使用实例

由于使用SQL字符串查询ORACLE会造成大量的硬解析，建议使用参数化查询，不使用拼接SQL文本，否则无法使用到数据库的缓存查询计划，也造成ORACLE进行硬解析造成解析调用次数暴增而性能剧降。正确范例如下：

public class QcStrucDocQueryHelper
{
    public static IList<StrucDocViewModel> QueryQcStrucDocs(int encounterId)
    {
        IList<StrucDocViewModel> result = new List<StrucDocViewModel>();
        StringBuilder sb = new StringBuilder();
        sb.Append(
@"select d.STRUCDOCID, d.TEMPLATEID, t.TEMPLATEKEY, d.METADATAID, c.METADATAKEY, d.STRUCDOCDATE DocumentDate, d.TASKIDS QuotedTargetItems
from doc.STRUCDOC d, concept.DOCMETADATA c, CONFIG.DOCTEMPLATE t 
where d.TEMPLATEID=t.DOCTEMPLATEID and d.METADATAID=c.METADATAID and d.ISDELETED=0 and d.ENCOUNTERID = :EncounterId"); 
        try
        {
            using (DbConnection cn = DapperHelper.CrateConnection(Dbs.IP))
            {
                result = DapperHelper.Query<StrucDocViewModel>(cn, sb.ToString(), new { EncounterId = encounterId }).OrderBy(a => a.DocumentDate).ThenByDescending(a => a.StrucDocId).ToList();
            }
        }
        catch (Exception ex)
        {
            MessageHelper.Show(ex.Message, "QcStrucDocQueryHelper.QueryQcStrucDocs()查询失败", MessageBoxImage.Error);
        }
        return result;
    }

    public static IList<QcOrderRequestViewModel> QueryQcOrderRequests(int encounterId, string consumableIds)
    {
        IList<QcOrderRequestViewModel> result = new List<QcOrderRequestViewModel>();
        StringBuilder sb = new StringBuilder();
        sb.Append(
$@"select r.ORDERREQUESTID, r.PARENTID, r.ORDERKINDCODEID, r.STATUSCODEID, r.CONSUMABLECODEID, r.CONSUMABLEID, r.STARTDATE, r.ENDDATE, r.EXECTIMESERIES
from poor.ORDERREQUEST r 
where r.STATUSCODEID != 4 and r.ORDERKINDCODEID = 4 and r.ISDELETED = 0 and r.ENCOUNTERID = :EncounterId");
        if (!string.IsNullOrWhiteSpace(consumableIds)) { sb.Append($@" and r.CONSUMABLEID in ({consumableIds}) "); }

        try
        {
            using (DbConnection cn = DapperHelper.CrateConnection(Dbs.IP))
            {
                result = DapperHelper.Query<QcOrderRequestViewModel>(cn, sb.ToString(), new { EncounterId = encounterId }).ToList();
            }
            IList<QcOrderRequestViewModel> parents = result.Where(a => a.ParentId == -1).ToList();
            foreach (var item in result.Where(a => a.ParentId != -1))
            {
                var p = parents.FirstOrDefault(a => a.OrderRequestId == item.ParentId);
                if (p != null)
                {
                    item.StatusCodeId = p.StatusCodeId;
                    item.ExecTimeSeries = p.ExecTimeSeries;
                }
            }
        }
        catch (Exception ex)
        {
            MessageHelper.Show(ex.Message, "QcStrucDocQueryHelper.QueryQcOrderRequests()查询失败", MessageBoxImage.Error);
        }
        return result;
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using JetSun.Infrastructure;
using JetSun.Infrastructure.Advanced;
using JetSun.Infrastructure.ServiceModel;
using Dapper;
using System.Data.Common;
using System.Data;
using Oracle.ManagedDataAccess.Client;

namespace JetSun.Infrastructure.Advanced
{
    public static class DapperHelper
    {
        public static DbConnection CrateConnection(Dbs dbs)
        {
            ServerContext sc = WcfServiceHelper.CreateServerContext(ClientContext.Instance);

            DbsSetting ds = sc.GetDbsSetting(dbs);
            DbConnection connection = null;
            if (ds.Provider == DbsProvider.MsSql)
                connection = new System.Data.SqlClient.SqlConnection(ds.CurrentConnectionString);
            else if (ds.Provider == DbsProvider.Oracle)
                connection = new Oracle.ManagedDataAccess.Client.OracleConnection(ds.CurrentConnectionString);
            else
                throw new Exception(string.Format("不支持的数据库类型{0}", ds.Provider.ToString()));

            connection.Open();
            return connection;
        }
        /// <summary>
        /// 用Dapper查询数据库。多Dapper功能请使用Dapper.SqlMapper类。
        /// 例：
        ///        Query&lt;DtoEmployee&gt;(Dbs.His,"select * from role.Employee where EmployeeId=:id", new { id = 100 });
        ///        Query&lt;int&gt;(Dbs.His,"select EmployeeId from role.Employee where PersonId=:id", new { id = 100 });
        /// </summary>
        public static IEnumerable<T> Query<T>(Dbs dbs, String sql, object param = null)
        {
            using (DbConnection cnn = CrateConnection(dbs))
            {
                return Query<T>(cnn, sql, param);
            }
        }

        /// <summary>
        /// 用Dapper查询数据库。更多Dapper功能请使用Dapper.SqlMapper类。
        /// 例：
        ///    using (DbConnection cnn = DapperHelper.CrateConnection(Dbs.His))
        ///    {
        ///        Query&lt;DtoEmployee&gt;(cnn,"select * from role.Employee where EmployeeId=:id", new { id = 100 }).ToList();
        ///        Query&lt;int&gt;(cnn,"select EmployeeId from role.Employee where PersonId=:id", new { id = 100 }).ToList();
        ///    }
        /// </summary>
        public static IEnumerable<T> Query<T>(DbConnection cnn, String sql, object param = null, int commandTimeout = 60)
        {
            return cnn.Query<T>(sql, param, null, true, commandTimeout);
        }
        /// <summary>
        /// 用Dapper执行sql。更多Dapper功能请使用Dapper.SqlMapper类。
        /// 例：
        ///    using (DbConnection cnn = DapperHelper.CrateConnection(Dbs.His))
        ///    {
        ///        Execute(cnn,"Update role.Employee set isdeleted=0 where EmployeeId=100");
        ///        
        ///        dto = new DtoEmployee { Name = "testName11", Code = "testCode11" };
        ///        int rs=DapperHelper.Execute(cn, "Update role.Employee set name=:name, code=:code where EmployeeId=4804", dto);//自动从dto中读取同名属性赋值
        ///    }
        /// </summary>
        public static int Execute(this IDbConnection cnn, string sql, object param = null, int commandTimeout = 60)
        {
            return Dapper.SqlMapper.Execute(cnn, sql, param, null, commandTimeout);
        }
        /// <summary>
        /// 用Dapper执行sql。更多Dapper功能请使用Dapper.SqlMapper类。
        /// 例：
        ///    using (DbConnection cnn = DapperHelper.CrateConnection(Dbs.His))
        ///    {
        ///        ExecuteSP(cnn,"role.TestSp",new { p1 = "testName2", p2 = "testcode2" } ); //p1 p2为存储过程role.TestSp的参数
        ///    }
        /// </summary>
        /// <param name="cnn"></param>
        /// <param name="spName"></param>
        /// <param name="param"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static int ExecuteSP(this IDbConnection cnn, string spName, object param = null, int commandTimeout = 60)
        {
            return Dapper.SqlMapper.Execute(cnn, spName, param, null, commandTimeout, System.Data.CommandType.StoredProcedure);
        }

        public static IList<T> ExecuteSP<T>(IDbConnection cnn, string spName, OracleDynamicParameters param = null, int commandTimeout = 60) where T : class
        {
            return Dapper.SqlMapper.Query<T>(cnn, spName, param, null, true, commandTimeout, CommandType.StoredProcedure).ToList();
        }
    }

    public class OracleDynamicParameters : SqlMapper.IDynamicParameters
    {
        private readonly DynamicParameters _dynamicParameters = new DynamicParameters();

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

        public void Add(string name, object value = null, DbType dbType = DbType.AnsiString, ParameterDirection? direction = null, int? size = null)
        {
            _dynamicParameters.Add(name, value, dbType, direction, size);
        }

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

        public void Add(string name, OracleDbType oracleDbType, int size, ParameterDirection direction)
        {
            var oracleParameter = new OracleParameter(name, oracleDbType, size) { Direction = 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());
            }
        }

        public T Get<T>(string parameterName)
        {
            return Parse<T>(_oracleParameters.SingleOrDefault(t => t.ParameterName == parameterName));
            //if (parameter != null)
            //    return (T)Convert.ChangeType(parameter.Value, typeof(T));
            //return default(T);
        }

        public T Get<T>(int index)
        {
            return Parse<T>(_oracleParameters[index]);
            //if (parameter != null)
            //    return (T)Convert.ChangeType(parameter.Value, typeof(T));
            //return default(T);
        }

        private T Parse<T>(OracleParameter parameter)
        {
            if (parameter.IsNull()) return default(T);

            try { return (T)Convert.ChangeType(parameter.Value, typeof(T)); }
            catch { return TypeHelper.ConvertTo<T>(parameter.Value.ToString()); }
        }
    }

    public sealed class DbString
    {
        public DbString() { Length = -1; }
        public bool IsAnsi { get; set; }
        public bool IsFixedLength { get; set; }
        public int Length { get; set; }
        public string Value { get; set; }
        public void AddParameter(IDbCommand command, string name)
        {
            if (IsFixedLength && Length == -1)
            {
                throw new InvalidOperationException("If specifying IsFixedLength,  a Length must also be specified");
            }
            var param = command.CreateParameter();
            param.ParameterName = name;
            param.Value = (object)Value ?? DBNull.Value;
            if (Length == -1 && Value != null && Value.Length <= 4000)
            {
                param.Size = 4000;
            }
            else
            {
                param.Size = Length;
            }
            param.DbType = IsAnsi ? (IsFixedLength ? DbType.AnsiStringFixedLength : DbType.AnsiString) : (IsFixedLength ? DbType.StringFixedLength : DbType.String);
            command.Parameters.Add(param);
        }
    }
}