数据访问基础类(基于Oracle)

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
using System.Configuration;


namespace DBUtility
{
    public abstract class c
    {
        public static readonly string mStrConStr = "Data Source= ;Persist Security Info=True;User ID= ;Password= ;Unicode=True";
                private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());


        /// <summary>
        /// Reture an oracle database connection
        /// </summary>
        /// <returns></returns>
        public static OracleConnection GetConnection()
        {
            OracleConnection connection = new OracleConnection(mStrConStr);
            return connection;
        }

        /// <summary>
        /// Execute a database query which does not include a select
        /// </summary>
        /// <param name="cmdType">Command type either stored procedure or SQL</param>
        /// <param name="cmdText">Acutall SQL Command</param>
        /// <param name="commandParameters">Parameters to bind to the command</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            // Create a new Oracle command
            OracleConnection connection = null;            
            try
            {
                //Create a connection
                connection = new OracleConnection(mStrConStr);
                //Prepare the command
                OracleCommand cmd = new OracleCommand();
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

                //Execute the command
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                connection.Close();
                return val;
            }
            catch (Exception ex)
            {
                connection.Close();
                throw ex;
            }
        }

        /// <summary>
        /// Execute an OracleCommand (that returns no resultset) against an existing database transaction 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing database transaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute an OracleCommand (that returns no resultset) against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {

            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a select query that will return a result set
        /// </summary>
        /// <param name="connString">Connection string</param>
        //// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns></returns>
        public static OracleDataReader ExecuteReader(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {

            //Create the command and connection            
            OracleConnection conn = null;
            OracleDataReader rdr = null;
            OracleCommand cmd = null;
            try
            {
                conn = new OracleConnection(mStrConStr);
                //Prepare the command to execute
                cmd = new OracleCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);                
                //Execute the query, stating that the connection should close when the resulting datareader has been read
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;

            }
            catch(Exception ex)
            {
                //If an error occurs close the connection as the reader will not be used and we expect it to close the connection
                //rdr.Close();
                cmd.Parameters.Clear();
                conn.Close();
                throw ex;
            }
        }

        public static DataTable GetDataTableBySql(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleDataReader DrdTableFiller=null;
            try
            {
                DrdTableFiller = ExecuteReader(cmdType, cmdText, commandParameters);
                DataTable DtbResultTable = new DataTable();
                DtbResultTable.Load(DrdTableFiller);
                DrdTableFiller.Close();
                return DtbResultTable;
            }
            catch (Exception ex)
            { 
                DrdTableFiller.Close();
                throw ex;
            }

        }

        public static DataTable GetDataTableBySql(CommandType cmdType, string cmdText, long pLngStartPos, int pIntPageSize, params OracleParameter[] commandParameters)
        {
            OracleDataReader DrdTableFiller = null;
            DataTable DtbResultTable = new DataTable();
            
            OracleConnection conn = null; 


            try
            {
                conn=new OracleConnection(mStrConStr);
                OracleCommand cmd = new OracleCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                DrdTableFiller = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();

                //Get table schema
                DataTable dtSchema = DrdTableFiller.GetSchemaTable();
                List<DataColumn> listCols = new List<DataColumn>();
                if (dtSchema != null)
                {
                    foreach (DataRow drow in dtSchema.Rows)
                    {
                        string columnName = System.Convert.ToString(drow["ColumnName"]);
                        DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
                        //column.Unique = (bool)drow["IsUnique"];
                        //column.AllowDBNull = (bool)drow["AllowDBNull"];
                        //column.AutoIncrement = (bool)drow["IsAutoIncrement"];
                        listCols.Add(column);
                        DtbResultTable.Columns.Add(column);
                    }
                }

                // Read rows from DataReader and populate the DataTable

                for (int i = 1; i <pLngStartPos + pIntPageSize; i++)
                {
                    if (DrdTableFiller.Read() && i >= pLngStartPos)
                    {
                        DataRow dataRow = DtbResultTable.NewRow();
                        for (int j = 0; j < listCols.Count; j++)
                        {
                            dataRow[((DataColumn)listCols[j])] = DrdTableFiller[j];
                        }
                        DtbResultTable.Rows.Add(dataRow);
                    }

                }


            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DrdTableFiller.Close();
                conn.Close();
            }
            return DtbResultTable;

        }


        /// <summary>
        /// Execute an OracleCommand that returns the first column of the first record against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SqlConnection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleConnection conn = null;
            OracleCommand cmd = null;

            try
            {
                conn = new OracleConnection(mStrConStr);
                cmd = new OracleCommand();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
                return val;
            }
            catch (Exception ex)
            {                
                cmd.Parameters.Clear();
                conn.Close();
                throw ex;
            }

 
        }

        ///    <summary>
        ///    Execute    a OracleCommand (that returns a 1x1 resultset)    against    the    specified SqlTransaction
        ///    using the provided parameters.
        ///    </summary>
        ///    <param name="transaction">A    valid SqlTransaction</param>
        ///    <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        ///    <param name="commandText">The stored procedure name    or PL/SQL command</param>
        ///    <param name="commandParameters">An array of    OracleParamters used to execute the command</param>
        ///    <returns>An    object containing the value    in the 1x1 resultset generated by the command</returns>
        public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null)
                throw new ArgumentException("The transaction was rollbacked    or commited, please    provide    an open    transaction.", "transaction");

            // Create a    command    and    prepare    it for execution
            OracleCommand cmd = new OracleCommand();

            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);

            // Execute the command & return    the    results
            object retval = cmd.ExecuteScalar();

            // Detach the SqlParameters    from the command object, so    they can be    used again
            cmd.Parameters.Clear();
            return retval;
        }

        public static DataTable GetDataTableBySql(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null)
                throw new ArgumentException("The transaction was rollbacked    or commited, please    provide    an open    transaction.", "transaction");

            // Create a    command    and    prepare    it for execution
            OracleCommand cmd = new OracleCommand();            
            PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
            OracleDataAdapter DaTableFiller = new OracleDataAdapter(cmd);
            DataTable DtbResultTable = new DataTable();
            DaTableFiller.Fill(DtbResultTable);
            return DtbResultTable;
        }

        /// <summary>
        /// Execute an OracleCommand that returns the first column of the first record against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or PL/SQL command</param>
        /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();

            PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        public static DateTime GetDatabaseTime()
        {
            return Convert.ToDateTime(OraDBUtility.ExecuteScalar(CommandType.Text,"select sysdate from dual"));
        }

        /// <summary>
        /// Add a set of parameters to the cached
        /// </summary>
        /// <param name="cacheKey">Key value to look up the parameters</param>
        /// <param name="commandParameters">Actual parameters to cached</param>
        public static void CacheParameters(string cacheKey, params OracleParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// Fetch parameters from the cache
        /// </summary>
        /// <param name="cacheKey">Key to look up the parameters</param>
        /// <returns></returns>
        public static OracleParameter[] GetCachedParameters(string cacheKey)
        {
            OracleParameter[] cachedParms = (OracleParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            // If the parameters are in the cache
            OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];

            // return a copy of the parameters
            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }


        /// <summary>
        /// Internal function to prepare a command for execution by the database
        /// </summary>
        /// <param name="cmd">Existing command object</param>
        /// <param name="conn">Database connection object</param>
        /// <param name="trans">Optional transaction object</param>
        /// <param name="cmdType">Command type, e.g. stored procedure</param>
        /// <param name="cmdText">Command test</param>
        /// <param name="commandParameters">Parameters for the command</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {

            //Open the connection if required
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //Set up the command
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //Bind it to the transaction if it exists
            if (trans != null)
                cmd.Transaction = trans;

            // Bind the parameters passed in
            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// Converter to use boolean data type with Oracle
        /// </summary>
        /// <param name="value">Value to convert</param>
        /// <returns></returns>
        public static string OraBit(bool value)
        {
            if (value)
                return "Y";
            else
                return "N";
        }

        /// <summary>
        /// Converter to use boolean data type with Oracle
        /// </summary>
        /// <param name="value">Value to convert</param>
        /// <returns></returns>
        public static bool OraBool(string value)
        {
            if (value.Equals("Y"))
                return true;
            else
                return false;
        }

        public static string GetSystemID(OracleConnection pConn, string pStrTableName)
        {
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = pConn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GET_SYSID";
            cmd.Parameters.Add("v_name", OracleType.VarChar).Value = pStrTableName;
            cmd.Parameters.Add("V_STR", OracleType.VarChar,255).Direction = ParameterDirection.ReturnValue;
            cmd.ExecuteNonQuery();
            return (string) cmd.Parameters["V_STR"].Value;
        }

         
        public static DataColumn[] GetTableSchema(string pStrSQL)
        {
            OracleDataReader drd = ExecuteReader(CommandType.Text, pStrSQL);
            DataTable dtSchema = drd.GetSchemaTable();
            DataColumn[] ResultCols = new DataColumn[dtSchema.Rows.Count];
            for (int i = 0; i < dtSchema.Rows.Count; i++)
            {
                string columnName = Convert.ToString(dtSchema.Rows[i]["ColumnName"]);
                ResultCols[i] = new DataColumn(columnName, (Type)(dtSchema.Rows[i]["DataType"]));
            }
            drd.Close();
            return ResultCols;
        }
    }
}