ASP.NET读数据库偶尔发生internal connection fatal error 错误

Question

读取数据类是
            string sql = "select top " + tmpPagesize + " " + tmpField + " from " + tableName + " where " + tableId + " not in(select top " + top + " " + tableId + " from " + tableName + " where " + where + " " + tmpOrder + ") and  " + where + " " + tmpOrder + "";
            using (SqlDataReader reader = DBHELPER.GetReader(sql))
            {
                while (reader.Read())
                {
                    list.Add(getListByRows(reader));
                }
                reader.Close();
            }
            return list;        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }

Answer 1

string sql = "select top " + tmpPagesize + " " + tmpField + " from " + tableName + " where " + tableId + " not in(select top " + top + " " + tableId + " from " + tableName + " where " + where + " " + tmpOrder + ") and  " + where + " " + tmpOrder + "";
            using (SqlDataReader reader = DBHELPER.GetReader(sql))
            {
                while (reader.Read())
                {
                    list.Add(getListByRows(reader));
                }
                reader.Close();            }
            return list;
红色的部分去掉，

Answer 2

那会报 reader未关闭之类的错误吧

Answer 3

那会发生reader未关闭的错误吗

Answer 4

ExecuteReader(CommandBehavior.CloseConnection)CommandBehavior.CloseConnection自动关闭

Answer 5

http://blogs.msdn.com/b/bill/archive/2006/08/26/internal-connection-fatal-error-when-trying-to-run-a-union-all-query-or-to-call-the-deriveparameters-method.aspxhttp://support.microsoft.com/kb/913764 

Answer 6

用reader对象要及时关闭，不然挺容易出问题的

Answer 7

我是SERVER2008 英文操作系统 SQL08

Answer 8

reader.close()  不算关闭吗

Answer 9

执行当前Web请求期间触发了一个未处理的异常：
致命的内部连接错误。 using (SqlDataReader reader = DBHELPER.GetReader(sql))
            {
                while (reader.Read())
                {
                    //怀疑是getListByRows(reader)方法内部提前关闭了reader
                    list.Add(getListByRows(reader));
                }
                reader.Close(); //这句话没问题，此时处理就不会等到以后交给托管来清理。
            }
            return list;

Answer 10

string sql = "select top " + tmpPagesize + " " + tmpField + " from " + tableName + " where " + tableId + " not in(select top " + top + " " + tableId + " from " + tableName + " where " + where + " " + tmpOrder + ") and  " + where + " " + tmpOrder + "";
            using (SqlDataReader reader = DBHELPER.GetReader(sql))
            {
                while (reader.Read())
                {
                    //省略
                    list.Add(getListByRows(reader));
                }
                reader.Close();
            }
            return list;DBHELPER
private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
          public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

Answer 11

鉴于楼主说这个问题是偶尔发生的，也就是说一般情况下没问题。我不支持楼主用静态连接的这种方式
public static SqlConnection Connection
可能问题就出现在这里。很难想象1000个人同时使用这个连接是个什么样子。
所以我建议楼主对每个数据库请求都新建一个SqlConnection，即采用如下方式：
using(SqlConnection conn=new SqlConnection(DefaultConnectionString))
{
   //执行你要的操作
}

Answer 12

取消掉STATIC 还是会出现这类问题，一重新启动应用程序池就又好了

Answer 13

http://stackoverflow.com/questions/1155263/what-causes-internal-connection-fatal-errors

Answer 14

 public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }改成
 public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }

Answer 15

  public static SqlConnection Connection
  应该是静态造成的