.NET/C#/Oracle資料庫操作類

using System;using System.Data;using System.Collections.Generic;using System.Configuration;using System.Data.OracleClient;using System.Text;using System.IO;/// <summary>/// Oracle資料庫操作類/// </summary>internal static class OracleHelper{    //資料庫連接字串    private readonly static string connstr = ConfigurationManager.ConnectionStrings["ConnectionStrings"].ConnectionString;    /// <summary>    /// 執行資料庫查詢操作,返回受影響的行數    /// </summary>    /// <param name="cmdText">Oracle預存程序名稱或PL/SQL命令</param>    /// <param name="commandParameters">命令參數集合</param>    /// <returns>當前查詢操作影響的資料行數</returns>    internal static int ExecuteNonQuery(string cmdText, params OracleParameter[] commandParameters)    {        OracleCommand command = new OracleCommand();        OracleConnection connection = new OracleConnection(connstr);        int result = 0;        try        {            PrepareCommand(command, connection, null,CommandType.Text, cmdText, commandParameters);            result = command.ExecuteNonQuery();            command.Parameters.Clear();        }        catch        {            throw;        }        finally        {            command.Dispose();            connection.Close();            connection.Dispose();        }        return result;    }    /// <summary>    /// 執行資料庫事務查詢操作,返回受影響的行數    /// </summary>    /// <param name="transaction">資料庫事務對象</param>    /// <param name="cmdType">Command類型</param>    /// <param name="cmdText">Oracle預存程序名稱或PL/SQL命令</param>    /// <param name="commandParameters">命令參數集合</param>    /// <returns>當前事務查詢操作影響的資料行數</returns>    internal static int ExecuteNonQuery(OracleTransaction transaction, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)    {        OracleCommand command = new OracleCommand();        OracleConnection connection = transaction.Connection;        int result = 0;        try        {            PrepareCommand(command, connection, transaction, cmdType, cmdText, commandParameters);            result = command.ExecuteNonQuery();            command.Parameters.Clear();        }        catch        {            throw;        }        finally        {            transaction.Dispose();            command.Dispose();            connection.Close();            connection.Dispose();        }        return result;    }    /// <summary>    /// 執行資料庫查詢操作,返回受影響的行數    /// </summary>    /// <param name="connection">Oracle資料庫連接對象</param>    /// <param name="cmdType">Command類型</param>    /// <param name="cmdText">Oracle預存程序名稱或PL/SQL命令</param>    /// <param name="commandParameters">命令參數集合</param>    /// <returns>當前查詢操作影響的資料行數</returns>    internal static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)    {        if (connection == null) throw new ArgumentNullException("當前資料庫連接不存在");        OracleCommand command = new OracleCommand();        int result = 0;        try        {            PrepareCommand(command, connection, null, cmdType, cmdText, commandParameters);            result = command.ExecuteNonQuery();            command.Parameters.Clear();        }        catch        {            throw;        }        finally        {            command.Dispose();            connection.Close();            connection.Dispose();        }        return result;    }    /// <summary>    /// 執行資料庫查詢操作,返回OracleDataReader類型的記憶體結果集    /// </summary>    /// <param name="cmdText">Oracle預存程序名稱或PL/SQL命令</param>    /// <param name="commandParameters">命令參數集合</param>    /// <returns>當前查詢操作返回的OracleDataReader類型的記憶體結果集</returns>    internal static OracleDataReader ExecuteReader(string cmdText, params OracleParameter[] commandParameters)    {        OracleCommand command = new OracleCommand();        OracleConnection connection = new OracleConnection(connstr);        OracleDataReader reader = null;        try        {            PrepareCommand(command, connection, null,CommandType.Text, cmdText, commandParameters);            reader = command.ExecuteReader(CommandBehavior.CloseConnection);            command.Parameters.Clear();            return reader;        }        catch        {            command.Dispose();            connection.Close();            throw;        }    }    /// <summary>    /// 執行資料庫查詢操作,返回DataSet類型的結果集    /// </summary>    /// <param name="cmdText">Oracle預存程序名稱或PL/SQL命令</param>    /// <param name="commandParameters">命令參數集合</param>    /// <returns>當前查詢操作返回的DataSet類型的結果集</returns>    internal static DataSet ExecuteDataSet(string cmdText, params OracleParameter[] commandParameters)    {        OracleCommand command = new OracleCommand();        OracleConnection connection = new OracleConnection(connstr);        DataSet dataset = null;        try        {            PrepareCommand(command, connection, null,CommandType.Text, cmdText, 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>    /// 執行資料庫查詢操作,返回DataTable類型的結果集    /// </summary>    /// <param name="cmdText">Oracle預存程序名稱或PL/SQL命令</param>    /// <param name="commandParameters">命令參數集合</param>    /// <returns>當前查詢操作返回的DataTable類型的結果集</returns>    internal static DataTable ExecuteDataTable(string cmdText, params OracleParameter[] commandParameters)    {        OracleCommand command = new OracleCommand();        OracleConnection connection = new OracleConnection(connstr);        DataTable table = null;        try        {            PrepareCommand(command, connection, null,CommandType.Text, cmdText, 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>    /// 執行資料庫查詢操作,返回結果集中位於第一行第一列的Object類型的值    /// </summary>    /// <param name="cmdText">Oracle預存程序名稱或PL/SQL命令</param>    /// <param name="commandParameters">命令參數集合</param>    /// <returns>當前查詢操作返回的結果集中位於第一行第一列的Object類型的值</returns>    internal static object ExecuteScalar(string cmdText, params OracleParameter[] commandParameters)    {        OracleCommand command = new OracleCommand();        OracleConnection connection = new OracleConnection(connstr);        object result = null;        try        {            PrepareCommand(command, connection, null,CommandType.Text, cmdText, commandParameters);            result = command.ExecuteScalar();            command.Parameters.Clear();        }        catch        {            throw;        }        finally        {            command.Dispose();            connection.Close();            connection.Dispose();        }        return result;    }    ///    <summary>    ///    執行資料庫事務查詢操作,返回結果集中位於第一行第一列的Object類型的值    ///    </summary>    ///    <param name="transaction">一個已存在的資料庫事務對象</param>    ///    <param name="commandType">命令類型</param>    ///    <param name="commandText">Oracle預存程序名稱或PL/SQL命令</param>    ///    <param name="commandParameters">命令參數集合</param>    ///    <returns>當前事務查詢操作返回的結果集中位於第一行第一列的Object類型的值</returns>    internal static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)    {        if (transaction == null) throw new ArgumentNullException("當前資料庫事務不存在");        OracleConnection connection = transaction.Connection;        if (connection == null) throw new ArgumentException("當前事務所在的資料庫連接不存在");        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>    /// 執行資料庫查詢操作,返回結果集中位於第一行第一列的Object類型的值    /// </summary>    /// <param name="connection">資料庫連接對象</param>    /// <param name="cmdType">Command類型</param>    /// <param name="cmdText">Oracle預存程序名稱或PL/SQL命令</param>    /// <param name="commandParameters">命令參數集合</param>    /// <returns>當前查詢操作返回的結果集中位於第一行第一列的Object類型的值</returns>    internal static object ExecuteScalar(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)    {        if (connection == null) throw new ArgumentException("當前資料庫連接不存在");        OracleCommand command = new OracleCommand();        object result = null;        try        {            PrepareCommand(command, connection, null, cmdType, cmdText, commandParameters);            result = command.ExecuteScalar();            command.Parameters.Clear();        }        catch        {            throw;        }        finally        {            command.Dispose();            connection.Close();            connection.Dispose();        }        return result;    }    /// <summary>    /// 執行資料庫命令前的準備工作    /// </summary>    /// <param name="command">Command對象</param>    /// <param name="connection">資料庫連接對象</param>    /// <param name="trans">事務對象</param>    /// <param name="cmdType">Command類型</param>    /// <param name="cmdText">Oracle預存程序名稱或PL/SQL命令</param>    /// <param name="commandParameters">命令參數集合</param>    private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)    {        if (connection.State != ConnectionState.Open) connection.Open();        command.Connection = connection;        command.CommandText = cmdText;        command.CommandType = cmdType;        if (trans != null) command.Transaction = trans;        if (commandParameters != null)        {            foreach (OracleParameter parm in commandParameters)                command.Parameters.Add(parm);        }    }    /// <summary>    /// 將.NET日期時間類型轉化為Oracle相容的日期時間格式字串    /// </summary>    /// <param name="date">.NET日期時間類型對象</param>    /// <returns>Oracle相容的日期時間格式字串（如該字串：TO_DATE('2007-12-1','YYYY-MM-DD')）</returns>    internal static string GetOracleDateFormat(DateTime date)    {        return "TO_DATE('" + date.ToString("yyyy-M-dd") + "','YYYY-MM-DD')";    }    /// <summary>    /// 將.NET日期時間類型轉化為Oracle相容的日期格式字串    /// </summary>    /// <param name="date">.NET日期時間類型對象</param>    /// <param name="format">Oracle日期時間類型格式化限定符</param>    /// <returns>Oracle相容的日期時間格式字串（如該字串：TO_DATE('2007-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>    /// 將指定的關鍵字處理為模糊查詢時的合法參數值    /// </summary>    /// <param name="source">待處理的查詢關鍵字</param>    /// <returns>過濾後的查詢關鍵字</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>    /// 將常值內容寫入到資料庫的CLOB欄位中（不可用：報串連被關閉的異常）    /// </summary>    /// <param name="connectionString">資料庫連接字串</param>    /// <param name="table">資料庫表名稱</param>    /// <param name="where">指定的WHERE條件陳述式</param>    /// <param name="clobField">CLOB欄位的名稱</param>    /// <param name="content">要寫入的常值內容</param>    internal static void WriteCLOB(string table, string 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 " + where + " 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>    /// 從資料庫中讀取CLOB欄位的內容並進行輸出    /// </summary>    /// <param name="connectionString">資料庫連接字串</param>    /// <param name="table">資料庫表名稱</param>    /// <param name="where">指定的WHERE條件陳述式</param>    /// <param name="clobField">CLOB欄位的名稱</param>    /// <param name="output">儲存內容輸出的字串變數</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(connectionString))        {            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();            }        }    }}