NET Dapper 基于 Oracle 参数化查询,返回多个结果集
1.添加Nuget包:
Dapper
Oracle.ManagedDataAccess
2.添加帮助类:OracleDynamicParameters
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using Dapper; using Oracle.ManagedDataAccess.Client; using System.Data; using System.Collections.Generic; namespace xOracleDal.CustomEntitys { public class OracleDynamicParameters : SqlMapper.IDynamicParameters { private readonly DynamicParameters dynamicParameters = new DynamicParameters(); private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>(); public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = null, int? size = null) { OracleParameter oracleParameter; if (size.HasValue) { oracleParameter = new OracleParameter(name, oracleDbType, size.Value, value, direction); } else { oracleParameter = new OracleParameter(name, oracleDbType, value, direction); } oracleParameters.Add(oracleParameter); } public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction) { var oracleParameter = new OracleParameter(name, oracleDbType, 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()); } } } }
3.后端Sql
BEGIN OPEN :RSLT1 FOR SELECT COUNT(1) FROM Tabl1 WHERE Pk = :Pk; OPEN :RSLT2 FOR SELECT COUNT(1) FROM Tabl2 WHERE AddUser = :AddUser; OPEN :RSLT3 FOR SELECT COUNT(1) FROM Tabl3 WHERE Uk = :Uk; END;
4.构建:Mssql关键字是[@],Oracle关键字是[:]
var dynParams = new OracleDynamicParameters(); // 返回值:添加的顺序决定取值的顺序 dynParams.Add(":rslt1", OracleDbType.RefCursor, ParameterDirection.Output); dynParams.Add(":rslt2", OracleDbType.RefCursor, ParameterDirection.Output); dynParams.Add(":rslt3", OracleDbType.RefCursor, ParameterDirection.Output); // 参数 dynParams.Add(":Pk", OracleDbType.Varchar2, ParameterDirection.Input, do.Pk); dynParams.Add(":AddUser", OracleDbType.Varchar2, ParameterDirection.Input, do.AddUser); dynParams.Add(":Uk", OracleDbType.Varchar2, ParameterDirection.Input, do.Uk);
5.调用
public void QueryMultiple(string sql, OracleDynamicParameters param) {
using (IDbConnection connection = new OracleConnection("User Id=AAA;Password=BBB;Connect Timeout=120; Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=CCC)(PORT=DDD)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=EEE)))")) { SqlMapper.GridReader reader = connection.QueryMultiple(sql, param); var num1 = reader.Read<int>().FirstOrDefault(); var num2 = reader.Read<int>().FirstOrDefault(); var lnum3 = reader.Read<int>().FirstOrDefault(); } }