如何让dapper支持oracle游标呢?

王朝学院·作者佚名  2016-08-28
窄屏简体版  字體: 小  |  中  |  大  |  超大  

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;

}

}

希望对大家有所帮忙,谢谢!

 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
© 2005- 王朝網路 版權所有 導航