数据库实用工具类

经常用到数据库，于是总结了一个较实用的数据库工具类，oracle外其他数据库也是可以借鉴的。

主要分为两个部分:
1.创建数据库对象。调用本类其他方法都需要用到这个对象。
2.操作数据。为数据安全，有事务处理，为节省资源，每次操作都有开、闭连接，高并发情况需要自行优化修改


    
public static class DBUtil
    {

        #region 创建数据库对象
        /// <summary>
        /// 有参oracle数据库连接
        /// </summary>
        /// <param name="connBean"></param> 
        /// <returns>OracleConnection实例或null</returns>
        public static OracleConnection OpenOracleConn(DbConnBean connBean)
        {
            if (connBean == null)
                return null;
            OracleConnection conn = null;
            try{
            String connStr = string.Format(
            "Data Source=(DESCRIPTION="
            + "(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1}))"
            + "(CONNECT_DATA=(SERVICE_NAME={2})));"
            + "User Id={3};Password={4};", connBean.Host, connBean.Port, connBean.Servername, connBean.User, connBean.Pass);
             conn = new OracleConnection(connStr);
            }
            catch (Exception e)
            {
                Console.WriteLine("失败连接oracle：" + e); return null;
            }
            return conn;
        }
       #endregion

        #region 数据操作
        /// <summary>
        /// 查询 oracle数据库共用方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable selectOracle(String sql, OracleConnection oracleconn)
        {
            DataTable dt = null;
            try
            {
                if (OpenOracleConn(oracleconn) == 1)
                {
                    OracleCommand cmd = new OracleCommand(sql, oracleconn);
                    cmd.CommandType = CommandType.Text;
                    DataSet ds = new DataSet();
                    OracleDataAdapter da = new OracleDataAdapter();
                    da.SelectCommand = cmd;
                    da.Fill(ds);//将结果集填入
                    dt = ds.Tables[0];
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("oracle查询失败："+e);
            }
            finally {
                oracleconn.Close();
            }
            return dt;
        }
        /// <summary>
        /// 更新 oracle数据库共用方法（事务）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="oracleconn"></param>
        /// <returns>
        /// 0执行失败 
        /// 1执行成功
        /// </returns>
        public static int insertUpdateOracle(String sql, OracleConnection oracleconn, List<OracleParameter> plist)
        {
            OracleCommand cmd = null;
            try
            {
                cmd = new OracleCommand(sql, oracleconn);
                foreach (OracleParameter op in plist)
                    cmd.Parameters.Add(op);
                if (OpenOracleConn(oracleconn) == 1)
                {
                    cmd.Transaction = oracleconn.BeginTransaction();
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cmd.Transaction.Commit();
                    return 1;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("oracle更新失败:" + e);
                cmd.Transaction.Rollback();
            }
            finally
            {
                oracleconn.Close();
            }
            return 0;
        }
               #endregion

        #region 打开连接
        /// <summary>
        /// 打开oracle数据库
        /// </summary>
        /// <param name="conn"></param>
        /// <returns>
        /// 0失败
        /// 1成功
        /// </returns>
        public static int OpenOracleConn(OracleConnection conn) {
            try
            {
                //打开连接
                conn.Open();
                //如果当前状态打开，在控制台输出
                if (conn.State == ConnectionState.Open)
                {
                    Console.WriteLine("成功连接oracledb:" + conn.ConnectionString);
                    return 1;
                }
                else
                    return 0;
            }
            catch (Exception e) {
                Console.WriteLine("打开oracle数据库失败："+e);
                return 0;
            }
        }

            #endregion

        #region 关闭连接
        /// <summary>
        /// 关闭oracle数据库连接
        /// </summary>
        /// <param name="conn"></param>
        public static void CloseOracleConn(OracleConnection conn)
        {
            if (conn == null) { return; }
            try
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Dispose();
            }
        }
              #endregion
    }