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<DtoEmployee>(Dbs.His,"select * from role.Employee where EmployeeId=:id", new { id = 100 }); /// Query<int>(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<DtoEmployee>(cnn,"select * from role.Employee where EmployeeId=:id", new { id = 100 }).ToList(); /// Query<int>(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); } } }
