. NET/C #/Oracle database operation instance code,. net

. NET/C #/Oracle database operation instance code,. net
. NET/C #/Oracle database operation instance code

Using System; using System. data; using System. collections. generic; using System. configuration; using System. data. oracleClient; using System. text; using System. IO; // <summary> /// Oracle database operation class /// </summary> internal static class OracleHelper {// database connection string private readonly static string connstr = ConfigurationManager. connectionStrings ["ConnectionStrings"]. connectionString; // <summary> // execute the database query Operation, returns the number of affected rows /// </summary> /// <param name = "plain text"> Oracle stored procedure name or PL/SQL command </param> /// <param name = "commandParameters"> command parameter set </param> // <returns> Number of data rows affected by the current query operation </returns> internal static int ExecuteNonQuery (string parameter text, params OracleParameter [] commandParameters) {OracleCommand command = new OracleCommand (); OracleConnection connection = new OracleConnection (connstr); int result = 0; try {Pre PareCommand (command, connection, null, CommandType. text, plain Text, commandParameters); result = command. executeNonQuery (); command. parameters. clear () ;}catch {throw;} finally {command. dispose (); connection. close (); connection. dispose () ;}return result ;}/// <summary> /// execute the Database Transaction query operation, returns the number of affected rows /// </summary> /// <param name = "transaction"> Database transaction object </param> /// <param name = "transaction type"> command type </param> /// <Param name = "plain text"> Oracle stored procedure name or PL/SQL command </param> /// <param name = "commandParameters"> command parameter set </param> // <returns> Number of data rows affected by the current transaction query operation </returns> internal static int ExecuteNonQuery (OracleTransaction transaction, commandType parameter type, string parameter text, params OracleParameter [] commandParameters) {OracleCommand command = new OracleCommand (); OracleConnection = transaction. connection; int Result = 0; try {PrepareCommand (command, connection, transaction, primitive type, plain text, commandParameters); result = command. executeNonQuery (); command. parameters. clear () ;}catch {throw;} finally {transaction. dispose (); command. dispose (); connection. close (); connection. dispose () ;}return result ;}/// <summary> /// query the database, returns the number of affected rows /// </summary> /// <param name = "connection"> Oracle database connection object </pa Ram> /// <param name = "cmdType"> Command type </param> /// <param name = "cmdText"> Oracle stored procedure name or PL/SQL Command </param> /// <param name = "commandParameters"> command parameter set </param> /// <returns> Number of data rows affected by the current query operation </returns> internal static int ExecuteNonQuery (OracleConnection connection, commandType primitive type, string plain text, params OracleParameter [] commandParameters) {if (connection = null) throw new ArgumentNullException ("Current Database connection does not exist "); OracleCommand command = new OracleCommand (); int result = 0; try {PrepareCommand (command, connection, null, primitive type, plain text, commandParameters); result = command. executeNonQuery (); command. parameters. clear () ;}catch {throw;} finally {command. dispose (); connection. close (); connection. dispose () ;}return result ;}/// <summary> /// execute the database query operation and return the OracleDataReader type memory result set /// </summary> /// <Param name = "plain text"> Oracle stored procedure name or PL/SQL command </param> /// <param name = "commandParameters"> command parameter set </param> // <returns> memory result set of the OracleDataReader type returned by the current query operation </returns> internal static OracleDataReader ExecuteReader (string plain text, params OracleParameter [] commandParameters) {OracleCommand command = new OracleCommand (); OracleConnection connection = new OracleConnection (connstr); OracleDataReader re Ader = null; try {PrepareCommand (command, connection, null, CommandType. text, plain Text, commandParameters); reader = command. executeReader (CommandBehavior. closeConnection); command. parameters. clear (); return reader;} catch {command. dispose (); connection. close (); throw ;}/// <summary> /// execute the database query operation, return the DataSet result set /// </summary> /// <param name = "plain text"> Oracle stored procedure name or PL/SQL command </param> /// <param Name = "commandParameters"> command parameter set </param> // <returns> result set of the DataSet type returned by the current query operation </returns> internal static DataSet ExecuteDataSet (string parameter text, params OracleParameter [] commandParameters) {OracleCommand command = new OracleCommand (); OracleConnection connection = new OracleConnection (connstr); DataSet dataset = null; try {PrepareCommand (command, connection, null, commandType. text, plain Text, commandP Arameters); OracleDataAdapter adapter = new OracleDataAdapter (); adapter. selectCommand = command; dataset = new DataSet (); adapter. fill (dataset); command. parameters. clear () ;}catch {throw;} finally {command. dispose (); connection. close (); connection. dispose () ;}return dataset ;}/// <summary> /// query the database, returns the result set of the DataTable type /// </summary> /// <param name = "plain text"> Oracle stored procedure name or PL/SQL command </param> /// <Param name = "commandParameters"> command parameter set </param> /// <returns> result set of the DataTable type returned by the current query operation </returns> internal static DataTable executeDataTable (string plain text, params OracleParameter [] commandParameters) {OracleCommand command = new OracleCommand (); OracleConnection connection = new OracleConnection (connstr); DataTable table = null; try {PrepareCommand (command, connection, null, commandType. text, Optional text, commandParameters); OracleDataAdapter adapter = new OracleDataAdapter (); adapter. selectCommand = command; table = new DataTable (); adapter. fill (table); command. parameters. clear () ;}catch {throw;} finally {command. dispose (); connection. close (); connection. dispose () ;}return table ;}/// <summary> /// query the database, value of the Object type in the first column of the First row in the returned result set /// </summary> /// <param name = "plain text"> Oracle Storage Procedure name or PL/SQL command </param> /// <param name = "commandParameters"> command parameter set </param> /// <returns> the result returned by the current query operation set the value of the Object type in the first column of the First row </returns> internal static object ExecuteScalar (string plain text, params OracleParameter [] commandParameters) {OracleCommand command = new OracleCommand (); OracleConnection connection = new OracleConnection (connstr); object result = null; try {PrepareCommand (command, connection, nu Ll, CommandType. text, plain Text, commandParameters); result = command. executeScalar (); command. parameters. clear () ;}catch {throw;} finally {command. dispose (); connection. close (); connection. dispose () ;}return result ;}/// <summary> /// execute the Database Transaction query operation, value of the Object type in the first column of the First row in the returned result set /// </summary> /// <param name = "transaction"> an existing database transaction Object </param> /// <param name = "commandType"> command type </param> /// <param na Me = "commandText"> Oracle stored procedure name or PL/SQL command </param> // <param name = "commandParameters"> command parameter set </param> /// <returns> values of the Object type in the first column of the First row in the result set returned by the current transaction query operation </returns> internal static object ExecuteScalar (OracleTransaction transaction, commandType commandType, string commandText, params OracleParameter [] commandParameters) {if (transaction = null) throw new ArgumentNullException ("the current database transaction does not exist"); OracleConnect Ion connection = transaction. connection; if (connection = null) throw new ArgumentException ("the current transaction's database Connection does not exist"); OracleCommand command = new OracleCommand (); object result = null; try {PrepareCommand (command, connection, transaction, commandType, commandText, commandParameters); result = command. executeScalar (); command. parameters. clear () ;}catch {throw;} finally {transaction. dispose (); comma Nd. dispose (); connection. close (); connection. dispose () ;}return result ;}/// <summary> /// query the database, value of the Object type in the first column of the First row in the returned result set /// </summary> /// <param name = "connection"> database connection Object </param>/ // <param name = "param type"> Command type </param> // <param name = "param text"> Oracle stored procedure name or PL/SQL Command </param> /// <param name = "commandParameters"> command parameter set </param> /// <returns> the result set returned by the current query operation is in the Object type in the first column of the First row value </returns> inte Rnal static object ExecuteScalar (OracleConnection connection, CommandType primitive type, string plain text, params OracleParameter [] commandParameters) {if (connection = null) throw new ArgumentException ("the current database connection does not exist "); oracleCommand command = new OracleCommand (); object result = null; try {PrepareCommand (command, connection, null, primitive type, plain text, commandParameters); result = command. executeScalar (); c Ommand. parameters. clear () ;}catch {throw;} finally {command. dispose (); connection. close (); connection. dispose ();} return result ;} /// <summary> /// preparations before running database commands /// </summary> /// <param name = "command"> Command object </param> /// <param name = "connection"> database connection object </param> /// <param name = "trans"> transaction object </param> /// <param name = "cmdType"> Command type </param> // <param name = "cmdText"> Oracle stored procedure name or PL/SQL Command </Param> /// <param name = "commandParameters"> command parameter set </param> private static void PrepareCommand (OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType argument type, string parameter text, OracleParameter [] commandParameters) {if (connection. state! = ConnectionState. Open) connection. Open (); command. Connection = connection; command. CommandText = plain text; command. CommandType = parallel type; if (trans! = Null) command. Transaction = trans; if (commandParameters! = Null) {foreach (OracleParameter parm in commandParameters) command. parameters. add (parm) ;}/// <summary> // set. NET date and Time type to Oracle compatible date and Time Format String /// </summary> /// <param name = "date">. NET Date and Time type object </param> // <returns> Oracle-compatible Date and Time Format String (for example, this string: TO_DATE ('2017-12-1 ', 'yyyy-MM-DD ') </returns> internal static string GetOracleDateFormat (DateTime date) {return "TO_DATE ('" + date. toString ("yyyy-M-dd") + "', 'Yyyy-MM-DD ') ";} // <summary> // set. NET date and Time type to Oracle compatible date Format String // </summary> /// <param name = "date">. NET Date and Time type object </param> /// <param name = "format"> Oracle Date and Time Type format qualifier </param> /// <returns> Oracle compatible date time Format String (for example, this string: TO_DATE ('1970-12-1 ', 'yyyy-MM-DD') </returns> internal static string GetOracleDateFormat (DateTime date, string format) {if (format = null | format. trim () = "") format = "YYYY-MM-DD "; Return" TO_DATE ('"+ date. toString ("yyyy-M-dd") + "','" + format + "')";} /// <summary> /// process the specified keyword as the valid parameter value for fuzzy search. /// </summary> /// <param name = "source"> keywords to be processed </param> // <returns> filtered query keywords </returns> internal static string HandleLikeKey (string source) {if (source = null | source. trim () = "") return null; source = source. replace ("[", "[]"); source = source. replace ("_", "[_]"); source = Source. replace ("%", "[%]"); return ("%" + source + "% ");} /// <summary> /// write text content to the CLOB field of the database (unavailable: An exception is reported when the connection is closed) /// </summary> /// <param name = "connectionString"> database connection string </param> /// <param name = "table"> database table name </param> /// <param name = "where"> specified WHERE Condition Statement </param> /// <param name = "clobField"> CLOB field name </ param> // <param name = "content"> content of the text to be written </param> internal static void WriteCLOB (string table, s Tring where, string clobField, string content) {if (String. isNullOrEmpty (connstr) | String. isNullOrEmpty (table) | String. isNullOrEmpty (clobField) return; using (OracleConnection connection = new OracleConnection (connstr) {OracleCommand command = null; try {connection. open (); command = connection. createCommand (); command. commandText = "SELECT" + clobField + "FROM" + table + "WHERE" + wh Ere + "for update"; OracleDataReader reader = command. ExecuteReader (); if (reader! = Null & reader. hasRows) {reader. read (); command. transaction = command. connection. beginTransaction (); OracleLob lob = reader. getOracleLob (0); byte [] buffer = Encoding. unicode. getBytes (content); if (lob! = OracleLob. Null) lob. Erase (); lob. Write (buffer, 0, (buffer. Length % 2 = 0 )? Buffer. length: (buffer. length-1); command. transaction. commit (); reader. close () ;}} catch {command. transaction. rollback (); throw;} finally {command. dispose (); connection. close (); connection. dispose ();}}} /// <summary> /// read the content of the CLOB field from the database and output it in parallel /// </summary> /// <param name = "connectionString"> database connection string </param> /// <param name = "table"> database table name </param> /// <param name = "where"> specified WHERE Condition Statement </ p Aram> /// <param name = "clobField"> name of the CLOB field </param> /// <param name = "output"> Save the string variable output by the content </ param> internal static void ReadCLOB (string connectionString, string table, string where, string clobField, ref string output) {if (String. isNullOrEmpty (connectionString) | String. isNullOrEmpty (table) | String. isNullOrEmpty (clobField) return; using (OracleConnection connection = new OracleConnection (conn EctionString) {OracleCommand command = null; StreamReader stream = null; try {connection. open (); command = connection. createCommand (); command. commandText = "SELECT" + clobField + "FROM" + table + "WHERE" + where; OracleDataReader reader = command. executeReader (); if (reader! = Null & reader. hasRows) {reader. read (); command. transaction = command. connection. beginTransaction (); OracleLob lob = reader. getOracleLob (0); if (lob! = OracleLob. null) {stream = new StreamReader (lob, Encoding. unicode); output = stream. readToEnd (). trim (); command. transaction. commit (); reader. close () ;}} catch {command. transaction. rollback (); throw;} finally {stream. close (); command. dispose (); connection. close (); connection. dispose ();}}}}