Dapper是一个轻型的ORM类。它有啥优点、缺点相信很多朋友都知道了,园里也有很多朋友都有相关介绍,这里就不多废话。
如果玩过Oracle都知道,存储过程基本都是通过游标返回数据的,但是dapper原生操作游标会报异常,具体异常信息因为现在没有环境就不截图了。
publicFactoryPRiceComparisonPublishItem GetTodayFactoryBasePricePushInfo(stringweiXinId)
{using(varcnn = Database.Connection("ERPDataBase"))
{varp =newOracleDynamicParameters();
p.Add("V_WEIXINID", weiXinId);
p.Add("RetCursor", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);returncnn.Query<FactoryPriceComparisonPublishItem>("PKG_M_STEELMILL.GetFactoryPCPublishItem", param: p, commandType: CommandType.StoredProcedure).SingleOrDefault();
}
}
以上是实际项目的代码片段,通过游标获取查询数据。
那如何去解决这个游标问题呢?就是这个OracleDynamicParameters类,全部内容如下:
usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Linq;usingDapper;usingOracle.Dataaccess.Client;publicclassOracleDynamicParameters : SqlMapper.IDynamicParameters
{privatestaticDictionary<SqlMapper.Identity, Action<IDbCommand,object>> paramReaderCache =newDictionary<SqlMapper.Identity, Action<IDbCommand,object>>();privateDictionary<string, ParamInfo> parameters =newDictionary<string, ParamInfo>();privateList<object>templates;privateclassParamInfo
{publicstringName {get;set; }publicobjectValue {get;set; }publicParameterDirection ParameterDirection {get;set; }publicOracleDbType? DbType {get;set; }publicint? Size {get;set; }publicIDbDataParameter AttachedParam {get;set; }
}///<summary>///construct a dynamic parameter bag///</summary>publicOracleDynamicParameters()
{
}///<summary>///construct a dynamic parameter bag///</summary>///<param name="template">can be an anonymous type or a DynamicParameters bag</param>publicOracleDynamicParameters(objecttemplate)
{
AddDynamicParams(template);
}///<summary>///Append a whole object full of params to the dynamic///EG: AddDynamicParams(new {A = 1, B = 2}) // will add property A and B to the dynamic///</summary>///<param name="param"></param>publicvoidAddDynamicParams(#ifCSHARP30objectparam#elsedynamicparam#endif)
{varobj = paramasobject;if(obj !=null)
{varsubDynamic = objasOracleDynamicParameters;if(subDynamic ==null)
{vardictionary = objasIEnumerable<KeyValuePair<string,object>>;if(dictionary ==null)
{
templates= templates ??newList<object>();
templates.Add(obj);
}else{foreach(varkvpindictionary)
{#ifCSHARP30Add(kvp.Key, kvp.Value,null,null,null);#elseAdd(kvp.Key, kvp.Value);#endif}
}
}else{if(subDynamic.parameters !=null)
{foreach(varkvpinsubDynamic.parameters)
{
parameters.Add(kvp.Key, kvp.Value);
}
}if(subDynamic.templates !=null)
{
templates= templates ??newList<object>();foreach(vartinsubDynamic.templates)
{
templates.Add(t);
}
}
}
}
}///<summary>///Add a parameter to this dynamic parameter list///</summary>///<param name="name"></param>///<param name="value"></param>///<param name="dbType"></param>///<param name="direction"></param>///<param name="size"></param>publicvoidAdd(#ifCSHARP30stringname,objectvalue, DbType? dbType, ParameterDirection? direction,int?size#elsestringname,objectvalue =null, OracleDbType? dbType =null, ParameterDirection? direction =null,int? size =null#endif)
{
parameters[Clean(name)]=newParamInfo() { Name = name, Value = value, ParameterDirection = direction ?? ParameterDirection.Input, DbType = dbType, Size =size };
}privatestaticstringClean(stringname)
{if(!string.IsNullOrEmpty(name))
{switch(name[0])
{case'@':case':':case'?':returnname.Substring(1);
}
}returnname;
}voidSqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
AddParameters(command, identity);
}///<summary>///Add all the parameters needed to the command just before it executes///</summary>///<param name="command">The raw command prior to execution</param>///<param name="identity">Information about the query</param>protectedvoidAddParameters(IDbCommand command, SqlMapper.Identity identity)
{if(templates !=null)
{foreach(vartemplateintemplates)
{varnewIdent =identity.ForDynamicParameters(template.GetType());
Action<IDbCommand,object>appender;lock(paramReaderCache)
{if(!paramReaderCache.TryGetValue(newIdent,outappender))
{
appender= SqlMapper.CreateParamInfoGenerator(newIdent,false,false);
paramReaderCache[newIdent]=appender;
}
}
appender(command, template);
}
}foreach(varparaminparameters.Values)
{stringname =Clean(param.Name);booladd = !((OracleCommand)command).Parameters.Contains(name);
OracleParameter p;if(add)
{
p=((OracleCommand)command).CreateParameter();
p.ParameterName=name;
}else{
p=((OracleCommand)command).Parameters[name];
}varval =param.Value;
p.Value= val ??DBNull.Value;
p.Direction=param.ParameterDirection;vars = valasstring;if(s !=null)
{if(s.Length <=4000)
{
p.Size=4000;
}
}if(param.Size !=null)
{
p.Size=param.Size.Value;
}if(param.DbType !=null)
{
p.OracleDbType=param.DbType.Value;
}if(add)
{
command.Parameters.Add(p);
}
param.AttachedParam=p;
}
}///<summary>///All the names of the param in the bag, use Get to yank them out///</summary>publicIEnumerable<string>ParameterNames
{get{returnparameters.Select(p =>p.Key);
}
}///<summary>///Get the value of a parameter///</summary>///<typeparam name="T"></typeparam>///<param name="name"></param>///<returns>The value, note DBNull.Value is not returned, instead the value is returned as null</returns>publicT Get<T>(stringname)
{varval =parameters[Clean(name)].AttachedParam.Value;if(val ==DBNull.Value)
{if(default(T) !=null)
{thrownewapplicationException("Attempting to cast a DBNull to a non nullable type!");
}returndefault(T);
}return(T)val;
}
}
希望对大家有所帮忙,谢谢!