Oracle DBHelper 第二版

  public static class OracleDBHelper
    {
        public static OracleCommand cmd = null;
        public static OracleConnection conn = null;
        public static string connstr = ConfigurationManager.ConnectionStrings["DbInsured_oracle"].ConnectionString;

        #region 获取Guid方法
        public static string GetGuid()
        {
            string guid = Guid.NewGuid().ToString();
            return guid;
        }
        #endregion


        #region 建立数据库连接对象
        /// <summary>
        /// 建立数据库连接
        /// </summary>
        /// <returns>返回一个数据库的连接OracleConnection对象</returns>
        public static OracleConnection init()
        {
            try
            {
                conn = new OracleConnection(connstr);
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            return conn;
        }
        #endregion

        #region 设置OracleCommand对象
        /// <summary>
        /// 设置OracleCommand对象 
        /// </summary>
        /// <param name="cmd">OracleCommand对象 </param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdParms">参数集合</param>
        private static void SetCommand(OracleCommand cmd, string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
        {
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                cmd.Parameters.AddRange(cmdParms);
            }
        }
        #endregion

        #region 执行相应的sql语句，返回相应的DataSet对象
        /// <summary>
        /// 执行相应的sql语句，返回相应的DataSet对象
        /// </summary>
        /// <param name="sqlstr">sql语句</param>
        /// <returns>返回相应的DataSet对象</returns>
        public static DataSet GetDataSet(string sqlstr)
        {
            DataSet set = new DataSet();
            try
            {
                init();
                OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
                adp.Fill(set);
                conn.Close();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            return set;
        }
        #endregion

        #region 执行相应的sql语句，返回相应的DataSet对象
        /// <summary>
        /// 执行相应的sql语句，返回相应的DataSet对象
        /// </summary>
        /// <param name="sqlstr">sql语句</param>
        /// <param name="tableName">表名</param>
        /// <returns>返回相应的DataSet对象</returns>
        public static DataSet GetDataSet(string sqlstr, string tableName)
        {
            DataSet set = new DataSet();
            try
            {
                init();
                OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
                adp.Fill(set, tableName);
                conn.Close();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            return set;
        }
        #endregion

        #region 执行不带参数sql语句，返回所影响的行数
        /// <summary>
        /// 执行不带参数sql语句，返回所影响的行数
        /// </summary>
        /// <param name="cmdstr">增，删，改sql语句</param>
        /// <returns>返回所影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText)
        {
            int count;
            try
            {
                init();
                cmd = new OracleCommand(cmdText, conn);
                count = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return count;
        }
        #endregion

        #region 执行带参数sql语句或存储过程，返回所影响的行数
        /// <summary>
        /// 执行带参数sql语句或存储过程，返回所影响的行数
        /// </summary>
        /// <param name="cmdText">带参数的sql语句和存储过程名</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdParms">参数集合</param>
        /// <returns>返回所影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
        {
            int count;
            try
            {
                init();
                cmd = new OracleCommand();
                SetCommand(cmd, cmdText, cmdType, cmdParms);
                count = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return count;
        }
        #endregion

        #region 执行不带参数sql语句，返回一个从数据源读取数据的OracleDataReader对象
        /// <summary>
        /// 执行不带参数sql语句，返回一个从数据源读取数据的OracleDataReader对象
        /// </summary>
        /// <param name="cmdstr">相应的sql语句</param>
        /// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>
        public static OracleDataReader ExecuteReader(string cmdText)
        {
            OracleDataReader reader;
            try
            {
                init();
                cmd = new OracleCommand(cmdText, conn);
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return reader;
        }
        #endregion

        #region 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的OracleDataReader对象
        /// <summary>
        /// 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的OracleDataReader对象
        /// </summary>
        /// <param name="cmdText">sql语句或存储过程名</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdParms">参数集合</param>
        /// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>
        public static OracleDataReader ExecuteReader(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
        {
            OracleDataReader reader;
            try
            {
                init();
                cmd = new OracleCommand();
                SetCommand(cmd, cmdText, cmdType, cmdParms);
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return reader;
        }
        #endregion


        #region 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的DataSet对象
        /// <summary>
        /// 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的OracleDataReader对象
        /// </summary>
        /// <param name="cmdText">sql语句或存储过程名</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdParms">参数集合</param>
        /// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>
        public static DataSet ExecuteReaderByDataSet(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
        {
            DataSet set = new DataSet();
            try
            {
                init();
                OracleDataAdapter adp = new OracleDataAdapter(cmdText, conn);
                adp.SelectCommand.Parameters.AddRange(cmdParms);
                adp.Fill(set, "personInfo");
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return set;
        }

        /// <summary>
        /// 不带参数DataSet
        /// </summary>
        /// <returns></returns>
        public static DataSet ExecuteReaderByDataSet(string cmdText)
        {
            DataSet set = new DataSet();
            try
            {
                init();
                OracleDataAdapter adp = new OracleDataAdapter(cmdText, conn);
                adp.Fill(set, "Ds");
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return set;
        }
        #endregion

        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="cmdText"></param>
        ///  OracleDataAdapter读取数据
        /// <returns></returns>
        public static DataSet ExecuteDataReader(string cmdText)
        {
            OracleDataAdapter odataAdapter;
            DataSet ds = new DataSet();
            try
            {
                init();
                odataAdapter = new OracleDataAdapter(cmdText, conn);
                odataAdapter.Fill(ds, "personInfo");
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return ds;
        }



        #region 执行不带参数sql语句,返回结果集首行首列的值object
        /// <summary>
        /// 执行不带参数sql语句,返回结果集首行首列的值object
        /// </summary>
        /// <param name="cmdstr">相应的sql语句</param>
        /// <returns>返回结果集首行首列的值object</returns>
        public static object ExecuteScalar(string cmdText)
        {
            object obj;
            try
            {
                init();
                cmd = new OracleCommand(cmdText, conn);
                obj = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return obj;
        }
        #endregion

        #region 执行带参数sql语句或存储过程,返回结果集首行首列的值object
        /// <summary>
        /// 执行带参数sql语句或存储过程,返回结果集首行首列的值object
        /// </summary>
        /// <param name="cmdText">sql语句或存储过程名</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdParms">返回结果集首行首列的值object</param>
        /// <returns></returns>
        public static object ExecuteScalar(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
        {
            object obj;
            try
            {
                init();
                cmd = new OracleCommand();
                SetCommand(cmd, cmdText, cmdType, cmdParms);
                obj = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            return obj;
        }
        #endregion


        #region DataSet装换为泛型集合
        /// <summary> 
        /// DataSet装换为泛型集合 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="p_DataSet">DataSet</param> 
        /// <param name="p_TableIndex">待转换数据表索引</param> 
        /// <returns></returns> 
        /// 2008-08-01 22:46 HPDV2806 
        public static IList<T> DataSetToIList<T>(DataSet p_DataSet, int p_TableIndex)
        {
            if (p_DataSet == null || p_DataSet.Tables.Count < 0)
                return null;
            if (p_TableIndex > p_DataSet.Tables.Count - 1)
                return null;
            if (p_TableIndex < 0)
                p_TableIndex = 0;

            DataTable p_Data = p_DataSet.Tables[p_TableIndex];
            // 返回值初始化 
            IList<T> result = new List<T>();
            for (int j = 0; j < p_Data.Rows.Count; j++)
            {
                T _t = (T)Activator.CreateInstance(typeof(T));
                PropertyInfo[] propertys = _t.GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    for (int i = 0; i < p_Data.Columns.Count; i++)
                    {
                        // 属性与字段名称一致的进行赋值 
                        if (pi.Name.ToLower().Equals(p_Data.Columns[i].ColumnName.ToLower()))
                        {
                            try
                            {
                                // 数据库NULL值单独处理
                                if (p_Data.Rows[j][i].GetType() == typeof(DateTime))
                                {
                                    pi.SetValue(_t, p_Data.Rows[j][i], null);
                                }else if (p_Data.Rows[j][i] != DBNull.Value)
                                    pi.SetValue(_t, p_Data.Rows[j][i].ToString(), null);
                                else
                                    pi.SetValue(_t, null, null);
                                break;
                            }
                            catch (Exception ex)
                            {
                                throw;
                            }
                        }
                    }
                }
                result.Add(_t);
            }
            return result;
        }
        #endregion

        #region 防止SQL注入方法
        public static string FilterSql(string s)
        {
            if (string.IsNullOrEmpty(s)) return string.Empty;
            s = s.Trim().ToLower();
            s = s.Replace("=", "");
            s = s.Replace("'", "");
            s = s.Replace(";", "");
            s = s.Replace(" or ", "");
            s = s.Replace("select", "");
            s = s.Replace("update", "");
            s = s.Replace("insert", "");
            s = s.Replace("delete", "");
            s = s.Replace("declare", "");
            s = s.Replace("exec", "");
            s = s.Replace("drop", "");
            s = s.Replace("create", "");
            s = s.Replace("%", "");
            s = s.Replace("--", "");
            return s;
        }
        #endregion
    }