Best Practices for Using Dapper to Operate Oracle Stored Procedures in .NET Core, Why is it a best practice? Because I stepped on the pit in the actual development and found that the solutions given in most articles on the Internet can not solve the problem well. Especially when the acquisition type is OracleDbType.
Using Dapper to Operate Oracle Stored Procedures in .NET Core
RefCursor and the output are ParameterDirection. Output data. Online is the same as writing an extension to OracleDynamicParameters. However, the code given in OracleDynamicParameters has no problem with the code for the Get method or the writing of the Get method. This leads to the problem of “OracleDbType cannot be converted to CLR type” if you execute an Oracle stored procedure and get the output value of the OracleDbType.RefCursor type. The specific exception hints are not screenshots here, which is roughly a conversion of OracleDbType to CLR type.
Dapper’s DynamicParameters does not support cursor types
If you use Dapper to perform Oracle’s stored procedure operations, just this stored procedure needs to pass in the output value of a cursor type. As shown below, you will find that the cursor type is not included in DbType.
var p = new DynamicParameters();
p.Add("foo", "bar");
p.Add("baz_cursor", dbType: DbType.?(no cursor type) , direction: ParameterDirection.Output);
Customize OracleDynamicParameters to support cursor types
Using Dapper to manipulate Oracle’s articles in .NET Core, you can click on the link to view the next [translation] ASP.NET Core Web API using Oracle database and Dapper to see this is enough.
This article is translated, there is a code for the extension method of OracleDynamicParameters, the specific code can be clicked on the above link to view, using OracleDynamicParameters for Oracle stored procedure query is not a problem, but also supports the inclusion of OracleDbType Execution of a .RefCursor type of stored procedure. Because it is passed in the Add parameter, the data type is OracleDbType type, as shown below:
So here we can pass in the cursor type when adding parameters. As follows:
But at this time, if the cursor type is an output parameter, then if you directly obtain it in the following way, it will explode the error at the beginning of our article.
The general meaning of the exception is “returning the OracleDbType type, can not be directly converted to CLR type, such as the above int type.”
Solve Exception problems
Now that we know the problem with the exception, then we need to solve the problem. The general solution is to re-implement the Get scheme and perform the conversion of OracleDataType to CLR type when acquiring data. Maybe this is a bit difficult for everyone, but don’t worry, we have GayHub, so I found a ready-made implementation on GitHub. The specific code can be viewed here. The OracleDynamicParameters implemented here are more powerful and more feature-rich than our implementation. At the same time, the conversion of the Get method is also implemented. As shown below:
At the same time, the author also released the Nuget package to keep you away from 996. The usage is as follows:
Then you can introduce Dapper.Oracle’s clear space in the file.
At the same time, the GitHub address of this project is necessary to post: https://github.com/DIPSAS/Dapper.Oracle
As the author puts it: This assembly adds support for writing Oracle-specific SQL that supports all DbTypes used by Oracle Managed Providers for parameters, supports setting various properties for commands (lobfetchsize, arraybindcount, bindbyname), and pairs of parameters. Set the collectiontype. With this package, you can now run a stored procedure that returns a refcursor, or use an array bound count to execute a SQL statement with an array of arguments.
At last
Today I shared using dapper to operate Oracle Stored Procedures in .NET Core with you a pit in our .NET Core that uses Dapper to manipulate Oracle stored procedures and gives a solution that I personally think is the best implementation. I hope to be helpful. Dapper is a great tool for writing high-performance database manipulation code. However, sometimes there may be some shortcomings in the support of Oracle. At this time, a group of programmers who are willing to share and are willing to contribute to share the excellent extensions to keep us away from 996.
Finally, I also appeal to everyone to share and contribute to the .NET Core community.