首页 > 代码库 > 使用Dapper读取Oracle多个结果集
使用Dapper读取Oracle多个结果集
Dapper对SQL Server支持很好，但对于Oracle有些用法不一样，需要自己进行特殊处理。
1、首先要自定义一个Oracle参数类
1 public class OracleDynamicParameters : SqlMapper.IDynamicParameters 2 { 3 private readonly DynamicParameters dynamicParameters = new DynamicParameters(); 4 5 private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>(); 6 7 public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = http://www.mamicode.com/null, int? size = null) 8 { 9 OracleParameter oracleParameter;10 if (size.HasValue)11 {12 oracleParameter = new OracleParameter(name, oracleDbType, size.Value, value, direction);13 }14 else15 {16 oracleParameter = new OracleParameter(name, oracleDbType, value, direction);17 }18 19 oracleParameters.Add(oracleParameter);20 }21 22 public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)23 {24 var oracleParameter = new OracleParameter(name, oracleDbType, direction);25 oracleParameters.Add(oracleParameter);26 }27 28 public void AddParameters(IDbCommand command, SqlMapper.Identity identity)29 {30 ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);31 32 var oracleCommand = command as OracleCommand;33 34 if (oracleCommand != null)35 {36 oracleCommand.Parameters.AddRange(oracleParameters.ToArray());37 }38 }39 }
2、对于一个sql语句中的多个结果集处理
string sql = @"beginopen :rslt1 for select * from t_um_event where rownum<10and c_fid>:fid;open :rslt2 for select count(*) as count from t_um_event;end; "; OracleDynamicParameters dynParams = new OracleDynamicParameters(); dynParams.Add(":rslt1", OracleDbType.RefCursor, ParameterDirection.Output); dynParams.Add(":rslt2", OracleDbType.RefCursor, ParameterDirection.Output); dynParams.Add(":fid", OracleDbType.Int64, ParameterDirection.Input, 1000); using (var muti = cn.QueryMultiple(sql, param: dynParams)) { List<TestClass> firstRes = muti.Read<TestClass>().ToList(); RecordCount secondRes = muti.ReadFirstOrDefault<RecordCount>(); }
class TestClass { public string C_ID { get; set; } public DateTime? C_REG_TIME { get; set; } public string C_Title { get; set; } } class RecordCount { public int Count { get; set; } }
2、对于oracle存储过程的多个结果集处理，存储过程定义
create or replace package pkg_test_dapper is TYPE t_cursor IS REF CURSOR; procedure p_get_list(cur_out1 out t_cursor, p_fid int, cur_out2 out t_cursor); procedure p_get_count(cur_out out t_cursor);end pkg_test_dapper;/create or replace package body pkg_test_dapper is procedure p_get_list(cur_out1 out t_cursor, p_fid int, cur_out2 out t_cursor) as begin open cur_out1 for select * from t_um_event where rownum < 10 and c_fid > p_fid; open cur_out2 for select count(*) as count from t_um_event; end; procedure p_get_count(cur_out out t_cursor) as begin open cur_out for select count(*) as count from t_um_event; end;end pkg_test_dapper;/
c#中用dapper调用存储过程返回oracle多个结果集
using (IDbConnection cn = new OracleConnection(ConfigurationManager.ConnectionStrings["ZWGCDB"].ConnectionString)) { TestClass Evt = cn.QueryFirstOrDefault<TestClass>("select * from t_um_event where c_id=:Id", new { id = "1BA2BF30-658A-4A79-A179-05A77C527150" }); //int cnt = cn.Execute("update t_um_event set c_reg_time=:reg_time where c_id=:id", new { id = "CEA00DA2-79D2-48CC-A9E1-D3CBB3842E54", reg_time = DateTime.Now }); OracleDynamicParameters dynParams = new OracleDynamicParameters(); dynParams.Add("cur_out1", OracleDbType.RefCursor, ParameterDirection.Output); dynParams.Add("cur_out2", OracleDbType.RefCursor, ParameterDirection.Output); dynParams.Add("p_fid", OracleDbType.Int64, ParameterDirection.Input, 1000); using (var muti = cn.QueryMultiple("pkg_test_dapper.p_get_list", dynParams, commandType: CommandType.StoredProcedure)) { List<TestClass> firstRes = muti.Read<TestClass>().ToList(); RecordCount secondRes = muti.ReadFirstOrDefault<RecordCount>(); } }
参考：http://stackoverflow.com/questions/18772781/using-dapper-querymultiple-in-oracle
使用到的包文件如下：
<?xml version="1.0" encoding="utf-8"?><packages> <package id="Dapper" version="1.50.2" targetFramework="net40" /> <package id="Oracle.ManagedDataAccess" version="12.1.24160419" targetFramework="net40" /></packages>
自：http://weiweictgu.cnblogs.com/
使用Dapper读取Oracle多个结果集
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。