. Net/C #/Oracle Database Operations

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> // query the database and return 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 lines 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 {preparecommand (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> /// <para M 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, struct 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 </param> /// <p Aram 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 lines 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 ("the 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> /// query the database, returns the oracledatareader type memory result set /// </Summary> /// <para M 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 reader = 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 = "comm Andparameters "> command parameter set </param> // <returns> result set of the dataset type returned by the current query operation </returns> internal static dataset executedataset (string plain text, params oracleparameter [] commandparameters) {oraclecommand command = new oraclecommand (); oracleconnection connection = new oracleconnection (connstr); dataset = NULL; try {preparecommand (command, connection, null, commandtype. text, plain text, commandparameters ); 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 parameter text, params oracleparameter [] commandparameters) {oraclecommand command = new oraclecommand (); oracleconnection connection = new oracleconnection (connstr); datatable table = NULL; try {preparecommand (command, connection, null, commandtype. text, plain text, Co Mmandparameters); 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 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 located at the first part of the first line. value of the object type in the column </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, null, commandt Ype. 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 name = "command Text "> Oracle stored procedure name or PL/SQL command </param> // <Param name =" commandparameters "> command parameter set </param> /// <returns> value 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, string commandtext, Params oracleparameter [] commandparameters) {If (transaction = NULL) throw new argumentnullexception ("the current database transaction does not exist"); oracleconnection connect Ion = 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 (); command. 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> internal static Object executescalar (oracleconnection connection, commandtype parameter type, string parameter 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 (); command. para Meters. 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 plain 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 ();}}}}