Read Oracle Multiple result sets using Dapper

Dapper support for SQL Server is good, but for Oracle some usages are different and require special handling on their own.

1, first to customize an Oracle parameter class

1   Public classOracleDynamicParameters:SqlMapper.IDynamicParameters2     {3         Private ReadOnlyDynamicparameters dynamicparameters =Newdynamicparameters ();4 5         Private ReadOnlyList<oracleparameter> oracleparameters =NewList<oracleparameter>();6 7          Public voidADD (stringName, Oracledbtype Oracledbtype, ParameterDirection direction,ObjectValue =NULL,int? Size =NULL)8         {9 OracleParameter OracleParameter;Ten             if(size. HasValue) One             { AOracleParameter =NewOracleParameter (name, Oracledbtype, size. Value, value, direction); -             } -             Else the             { -OracleParameter =NewOracleParameter (name, Oracledbtype, value, direction); -             } -  + Oracleparameters.add (oracleparameter); -         } +  A          Public voidADD (stringname, Oracledbtype oracledbtype, parameterdirection direction) at         { -             varOracleParameter =NewOracleParameter (name, oracledbtype, direction); - Oracleparameters.add (oracleparameter); -         } -  -          Public voidaddparameters (IDbCommand command, sqlmapper.identity Identity) in         { - ((sqlmapper.idynamicparameters) dynamicparameters). AddParameters (Command, identity); to  +             varOracleCommand = command asOracleCommand; -  the             if(OracleCommand! =NULL) *             { $ OracleCommand.Parameters.AddRange (Oracleparameters.toarray ());Panax Notoginseng             } -         } the}

oracledynamicparameters

2. For multiple result set processing in one SQL statement

 stringsql =@"BEGINOPEN:RSLT1 for SELECT * from T_um_event where Rownum<10and c_fid>:fid;open:rslt2 for select COUNT (*) as C                    Ount from T_um_event;end; "; Oracledynamicparameters Dynparams=Neworacledynamicparameters (); Dynparams.add (": Rslt1", Oracledbtype.refcursor, ParameterDirection.Output); Dynparams.add (": Rslt2", Oracledbtype.refcursor, ParameterDirection.Output); Dynparams.add (": FID", Oracledbtype.int64, ParameterDirection.Input, +); using(varMuti =CN. Querymultiple (SQL, Param:dynparams)) {List<TestClass> firstres = Muti. Read<testclass>().                    ToList (); RecordCount Secondres= Muti. Readfirstordefault<recordcount>(); }

    classTestClass { Public stringc_id {Get; Set; }         PublicDatetime?C_reg_time {Get; Set; }         Public stringC_title {Get; Set; }    }    classRecordCount { Public intCount {Get;Set; } }

2. For multiple result set processing for Oracle stored procedures, the stored procedure definition

Create or ReplacePackage Pkg_test_dapper isTYPE t_cursor isREFCURSOR; procedurep_get_list (cur_out1 out t_cursor, P_fidint, cur_out2 out t_cursor); procedureP_get_count (cur_out out t_cursor);EndPkg_test_dapper;/Create or ReplacePackage Body Pkg_test_dapper is  procedurep_get_list (cur_out1 out t_cursor, P_fidint, cur_out2 out T_cursor) as  begin    OpenCur_out1 for      Select *         fromt_um_eventwhereRowNum< Ten          andC_fid>P_fid; OpenCur_out2 for      Select Count(*) as Count  fromt_um_event; End; procedureP_get_count (Cur_out out T_cursor) as  begin    OpenCur_out for      Select Count(*) as Count  fromt_um_event; End;EndPkg_test_dapper;/

Calling stored procedures in C # with Dapper to return multiple result sets from Oracle

   using(IDbConnection cn =NewOracleConnection (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", R Eg_time = DateTime.Now});oracledynamicparameters Dynparams=Neworacledynamicparameters (); Dynparams.add ("CUR_OUT1", Oracledbtype.refcursor, ParameterDirection.Output); Dynparams.add ("Cur_out2", Oracledbtype.refcursor, ParameterDirection.Output); Dynparams.add ("P_fid", Oracledbtype.int64, ParameterDirection.Input, +); using(varMuti = CN. Querymultiple ("pkg_test_dapper.p_get_list", Dynparams, commandType:CommandType.StoredProcedure)) {List<TestClass> firstres = Muti. Read<testclass>().                    ToList (); RecordCount Secondres= Muti. Readfirstordefault<recordcount>(); }            }

Reference: Http://stackoverflow.com/questions/18772781/using-dapper-querymultiple-in-oracle

The package file to use is as follows:

<?XML version= "1.0" encoding= "Utf-8"?><Packages>  < PackageID= "Dapper"version= "1.50.2"targetframework= "NET40" />  < PackageID= "Oracle.manageddataaccess"version= "12.1.24160419"targetframework= "NET40" /></Packages>

From: http://weiweictgu.cnblogs.com/

Read Oracle Multiple result sets using Dapper