Oracle会话过多问题！

Question

今天oracle会话突然到100多了。然后老大测试，在网站上面每点击一下带有查询数据库的链接，会话就会多一个，我想也是，如果每个客户端的每个点击都会增加一个会话的那么数据库是不是吃不消呢？（网上看到oracle会话最多可以容纳几百个吧）我的数据库操作都是基于SQLHelper.cs类的。请问该如何确保oracle会话在一个适当的范围内？
问题描述与http://topic.csdn.net/u/20070904/18/c97723a8-4b4a-4a88-920b-6376dc8abc92.html极其相似

Answer 1

貌似你的会话并没有关闭，如果用datareader读取数据需要注意用CommandBehavior.CloseConnection来关闭连接。另外最好是用using(...){}来打开数据库连接

Answer 2

正式你这样的。给你看看sqlhelper类的代码。
using System;
using System.Data.OracleClient;
using System.Data;
using System.Configuration;namespace DBUtility
{
    public class SQLHelper
    {
        //获取数据库连接字符串
        public static string GetConnectionString()
        {
            string cfgManage = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
            return cfgManage;
        }
        /// <summary>
        /// 执行简单的SQL语句
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="cmdType">command类型</param>
        /// <param name="cmdText">command命名的名字或者SQl语句</param>
        /// <param name="oracleParames">command命令参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] oracleParames) 
        {
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection conn = new OracleConnection(connectionString)) 
            {
                try
                {
                    PrepareCommand(cmd, conn, cmdType, cmdText, oracleParames);
                    int result = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return result;
                }
                finally 
                {
                    conn.Close();
                }
            }            
        }
        /// <summary>
        /// 执行SQL语句，返回DataReader对象
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="cmdType">command命令类型</param>
        /// <param name="cmdText">command存储过程名字或者SQL语句</param>
        /// <param name="parames">command命令参数</param>
        /// <returns></returns>
        public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] parames)
        {
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(connectionString);
            try
            {
                PrepareCommand(cmd, conn, cmdType, cmdText, parames);
                OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
            catch 
            {
                conn.Close();
                throw;
            }
        }        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="connectionString">connection连接字符串</param>
        /// <param name="cmdType">command命令类型</param>
        /// <param name="cmdText">command存储过程名字或者SQl语句</param>
        /// <param name="commandParameters">command命令参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection conn = new OracleConnection(connectionString)) 
            {
                try
                {
                    PrepareCommand(cmd, conn, cmdType, cmdText, commandParameters);
                    object result = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return result;
                }
                finally 
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 执行命令返回DataSet对象
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="cmdType">command命令类型</param>
        /// <param name="cmdText">command存储过程名字或者SQl语句</param>
        /// <param name="commandParameters">command命令参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDateSet(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            DataSet ds = new DataSet();
            OracleCommand cmd = new OracleCommand();
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                try
                {
                    PrepareCommand(cmd, conn, cmdType, cmdText, commandParameters);
                    OracleDataAdapter caChe = new OracleDataAdapter();
                    caChe.SelectCommand = cmd;
                    caChe.Fill(ds);
                    cmd.Parameters.Clear();
                }
                finally 
                {
                    conn.Close();
                }
            }
            return ds;
        }        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string connectionString, string SQLString)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                }
                return ds;
            }
        }        /// <summary>
        /// 执行command命令之前的准备工作
        /// </summary>
        /// <param name="cmd">command命令对象</param>
        /// <param name="conn">connection连接对象</param>
        /// <param name="cmdType">执行命令的类型</param>
        /// <param name="cmdText">执行命名的名字或者SQL命令语句</param>
        /// <param name="cmdParams">cmd命令的参数</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, CommandType cmdType, string cmdText, OracleParameter[] cmdParams)
        {
            if (conn.State != ConnectionState.Open) 
            {
                conn.Open();
            }            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;            if (cmdParams != null) 
            {
                foreach (OracleParameter param in cmdParams) 
                {
                    cmd.Parameters.Add(param);
                }
            }
        }        
    }
}

Answer 3

对了，还有一个问题。using语句里面如果产生了异常，那么资源也会自动释放吗？换个说法，using语句里面用try语句来捕捉异常是否属于画蛇添足？如下：            using (OracleConnection conn = new OracleConnection(connectionString)) 
            {
                try
                {
                    PrepareCommand(cmd, conn, cmdType, cmdText, oracleParames);
                    int result = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return result;
                }
                finally 
                {
                    conn.Close();
                }
            }      

Answer 4

好像是using 最后产生的也会有try，不懂乱猜的