C #: verify the correctness of PLSQL statements with parameters in Oracle databases,

C #: verify the correctness of PLSQL statements with parameters in Oracle databases,

In the Oracle database C # project, there is a requirement that PLSQL statements with parameters are configured on the interface, but the correctness must be verified by a program. How can this problem be achieved? This is the content to be discussed in this article.

1. Use the ExecuteNonQuery method of the OracleCommand object

This method uses Oracle. ManagedDataAccess. dll. First, you must copy the file in the oracle client directory and add references. You can add a public class to verify the SQL statement and use it in the project. (Example: ValidateSQL. cs)

using Oracle.ManagedDataAccess.Client;

Next, you can write a general SQL verification method in this public class:

/// <Summary> /// verify the SQL statement /// </summary> /// <param name = "SQL"> </param> /// <returns> </returns> public bool ValidateSQL (string SQL, out string strmsg) {bool bResult; using (OracleConnection connection = new OracleConnection (this. connectionString) {OracleCommand cmd = connection. createCommand (); connection. open (); cmd. commandText = "set autotrace traceonly"; // cmd. executeNonQuery (); try {cmd. com MandText = SQL; cmd. ExecuteNonQuery (); bResult = true; strmsg = "SQL statement verification passed! ";} Catch (Exception ex) {if (ex. message. toString (). contains ("ORA-01008") {// If the ORACLE error number is not bound to a variable, it indicates that the statement is correct bResult = true; strmsg = "SQL statement verification passed! ";}Else {bResult = false; strmsg =" verification error: "+ ex. message ;}} finally {// cmd. commandText = "set autotrace off"; // cmd. executeNonQuery () ;}} return bResult ;}

In general, if the SQL statement does not contain parameters, it can be verified directly. If a parameter exists, you need to judge the error number in catch.

The definition of error numbers in Oracle Data is fixed and is not affected by the version number. In addition, during the execution of PLSQL scripts, half of the order of parameters is determined by the backend.

If an error occurs in the statement, it is directly thrown out. If the error code is "ORA-01008", it means the variable is not bound. The SQL statement must be correct.

This method uses a data connection string and re-creates an OracleCommand object through the link string to verify the statement.

The following problem occurs: If the string is encrypted, the connection cannot be established normally. What should I do?

 

Ii. database connection and SQL verification of encrypted strings

If the link string is an encrypted string, you must decrypt it before determining the SQL script. If the above method is called at this time, a "invalid link string" error occurs.

For example, if my link string is stored in the web. config configuration, I configured a node that is encrypted or not. If the value is true, it indicates encryption and an encrypted link string is provided.

<add key="ConStringEncrypt" value="true" /><add key="ConnectionString" value="F4107C21837F889831795CA2637F77F119EE4F3108F882C0A8F27BE676B57C78B7AB9D8BFC406475A027E1FF2F2FF69626FCB0003F2D8BB99481EFC760A48FA6A2798764FE9D94E4" />

In this case, decryption is required when the statement is verified using the ExecuteNonQuery method of the OracleCommand object. The Code is as follows:

/// <Summary> /// obtain the connection string /// </summary> public static string ConnectionString {get {string _ connectionString = ConfigurationManager. appSettings ["ConnectionString"]; // gets the configuration string ConStringEncrypt = ConfigurationManager for encrypted connection strings. deleetcipher ["ConStringEncrypt"]; if (ConStringEncrypt = "true") {// if encrypted, Decrypt it first (assume that the key is dotnet and the decryption method is Decrypt) _ connectionString = Decrypt (_ connectionString, "dotnet") ;}return _ connectionString ;}}

Add the decrypted connection string to the above method and perform verification. Shows the effect:

 

Note: This article is original in Healer007, with the name: radish. If you need to reprint it, please indicate the source!