public class SqlDB
{
private static SqlConnection connection; //构建连接字符串
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["sql"].ToString();//读取配置文件
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 DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds); //到这里是 报错 “已有打开的与此命令相关联的 DataReader,必须首先将它关闭 ” return ds.Tables[0];
}
//执行(增、删、改)返回受影响的行数
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
}web.config 是这样写的 <add name="sql" connectionString="server=.;database=ProjectManage;uid=sa;pwd=webtba;Max Pool Size=512"/>
我用的 是 SqlServer 2000 vs2005 我所有 的查询都用这个方法完成GetDataSet(string safeSql) safeSql是sql语句
我在同一台电脑上运行 当一个用户在查询大量数据时 后面的人在查询就报“已有打开的与此命令相关联的 DataReader,必须首先将它关闭。 两个用户都是调用 GetDataSet()方法
{
private static SqlConnection connection; //构建连接字符串
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["sql"].ToString();//读取配置文件
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 DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds); //到这里是 报错 “已有打开的与此命令相关联的 DataReader,必须首先将它关闭 ” return ds.Tables[0];
}
//执行(增、删、改)返回受影响的行数
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
}web.config 是这样写的 <add name="sql" connectionString="server=.;database=ProjectManage;uid=sa;pwd=webtba;Max Pool Size=512"/>
我用的 是 SqlServer 2000 vs2005 我所有 的查询都用这个方法完成GetDataSet(string safeSql) safeSql是sql语句
我在同一台电脑上运行 当一个用户在查询大量数据时 后面的人在查询就报“已有打开的与此命令相关联的 DataReader,必须首先将它关闭。 两个用户都是调用 GetDataSet()方法
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
加上CommandBehavior.CloseConnection这个枚举
在一个用户ip访问中,一定要关闭后,再DataReader检查DataReader打开没关的地方
using (SqlConnection cn = new SqlConnection(“”))
{
SqlCommand cmd1 = new SqlCommand("", cn);
cn.Open();
using (SqlDataReader dr1 = cmd1.ExecuteReader())
{
while (dr1.Read())
{
string sql= "";
SqlConnection conn2 = new SqlConnection("");
SqlCommand cmd2 = new SqlCommand(sql, conn2 );
cmd2.ExecuteNonQuery();
}
} }
我这 跟本就没 SqlDataReader
using System.Collections.Generic;
using System.Text;using System.Data.SqlClient;
using System.Configuration;
using System.Data;namespace Pj_DAL.Conn
{
public class SqlDB
{
// public static SqlConnection conn; //隐藏这一行
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["sql"].ToString(); SqlConnection conn = new SqlConnection(connectionString);//在这里新建一个
if (conn.State == System.Data.ConnectionState.Closed)//增加一个判断语句
{
conn.Close();
conn.Open();
}
if (conn == null)
{
conn.Open();
}
else if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
else if (conn.State == System.Data.ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
return conn;
}
}
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
{
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
return ds.Tables[0];
}
//执行(增、删、改)返回受影响的行数
public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
}
}