Dapper is perfectly compatible with Oracle for executing stored procedures and returning results

Dapper is perfectly compatible with Oracle for executing stored procedures and returning results
This problem has plagued me for two days. When I used Dapper, I felt very cool. In particular, in combination with the new. net 4.0 dynamic, let me generate a generic set, and then convert it to json in one breath. However, various ORM types are always confusing... For example, I used to write tests on SQL Server and perform encapsulation without any problems. CURD, batch operations, stored procedures, and transactions. It can be switched to Oracle, and a problem occurs ~ Isn't it good to support Oracle?] There is no problem in the early stage of Dapper + Oracle unit test, that is to say, there is no problem in normal SQL operations. Then, when I wrote to the unit test of the stored procedure, it hurt. Because DbType data type enumeration is used in the original version. No output cursor is returned by Sqlserver. However, the cursor is required for the Oracle output result set. So here the problem comes. Set the parameter type for OracleParameter. DbType does not have the Cursor type. There are not many documents about Dapper, and most of them are concentrated on SqlServer, which may serve the. Net platform and focus more on the supporting databases of Microsoft. Okay. If the problem arises, solve it. It is open-source. Source code. Search by problem first [I don't like Baidu, because Baidu found a lot of irrelevant things, and the copper smell is too heavy. Google is inaccessible in China, so I chose Bing, and the result is not bad .] After online collection, we found that Dapper does support Oracle, but there is no content for calling the Oracle stored procedure. Well, if not, analyze it by yourself. Since the parameter type is not supported, it cannot be changed to the supported one? The original version is as follows: 1 DynamicParameters dp = new DynamicParameters (); 2 dp. add ("RoleId", "1"); 3 dp. add ("RoleName", "", DbType. string, ParameterDirection. output); this is the part that declares parameter in the original Dapper version. The red part of the code above specifies the parameter type. In system. data. oracleclient, The OracleType enumeration has the Cursor type. Then, check the DynamicParameters class. For example, you can see that this class implements an interface. Note: The original author reserved an interface for us to implement other content by ourselves. Take a look at the interface: The interface content is very simple, it is an AddParameters method. So, you can be sure that the above guess is correct. We can directly extend and implement this interface. Create a class OracleDynamicParameters that implements IDynamicParameters. Then, follow the DynamicParameters class provided by the original author to implement this interface. The final modified version is as follows (the code is too large and expanded and directly pasted into your file): View Codeok. After the extension is completed, let's take a unit test and give it a try:

1 /// <summary> 2 /// execute the stored procedure with parameters and return result 3 /// </summary> 4 public static void ExectPro () 5 {6 var p = new OracleDynamicParameters (); 7 p. add ("beginTime", 201501); 8 p. add ("endTime", 201512); 9 p. add ("targetColumn", "tax"); 10 p. add ("vCur", OracleDbType. refCursor, ParameterDirection. output); 11 using (IDbConnection conn = new OracleConnection (SqlConnOdp) 12 {13 conn. open (); 14 var aa = conn. query ("p_123c", param: p, commandType: CommandType. storedProcedure ). toList (); 15 aa. forEach (m => Console. writeLine (m. c_NAME); 16} 17 Console. readLine (); 18}

 

The result is successfully executed, and all values in the first column are printed. Then, the simple expansion of Dapper is complete. The Oracle driver I use is ODP. NET ,. net is 4.0 this ODP.. NET Oracle. dataAccess. we recommend that you copy the dll from your target server. Do not use the local one. If I use the local one, an external program error is prompted. It is a version issue or a number of digits.