一个OA系统,只有几个人用,每天都要重启几次,用着用着就不能显示了,登陆也登不进去了,在服务器上登陆也不行,服务器上登陆时显示:"已有打开的与此命令相关联的DataReader,必须首先将它关闭",必须要重新启动服务器才可,想了很久,估计是通用数据访问函数有问题,如果是的话,不知如何找出问题在哪,现贴出数据访问函数所有的代码,望高手费心看一下,找一下问题,或着可能是其他地方:调试的时候,偶尔不能出来正常登陆画面,光标停留在 函数
public static DataSet GetDataSet(string SqlString)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
// SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection);//这二行都不行
using (SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection))
{
DataSet dataset = new DataSet();
adapter.Fill(dataset);
connection.Close();
return dataset;
}
} }这个位置: adapter.Fill(dataset),因登陆时对数据库有很多操作的,估计是GetDataSet有点问题,
下面是所有的代码:namespace lgh.DBUtility
{
/// <summary>
/// 数据访问抽象基础类
/// Copyright (C) 2004-2008 By lgh
/// </summary>
public abstract class DbHelperSQL
{
public DbHelperSQL()
{ }
//自己解密数据库设置字符串
protected static string DecryptDBStr(string Text, string sKey)
{
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
int len;
len = Text.Length / 2;
byte[] inputByteArray = new byte[len];
int x, i;
for (x = 0; x < len; x++)
{
i = Convert.ToInt32(Text.Substring(x * 2, 2), 16);
inputByteArray[x] = (byte)i;
}
des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
System.IO.MemoryStream ms = new System.IO.MemoryStream();
CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
cs.Write(inputByteArray, 0, inputByteArray.Length);
cs.FlushFinalBlock();
return Encoding.Default.GetString(ms.ToArray());
}
//定义连接字符串。
protected static string ConnectionString = DecryptDBStr(ConfigurationManager.AppSettings["SQLConnectionString"], "lgh");
protected static SqlConnection Connection;
protected static SqlConnection Connection1;
//定义数据库的打开和关闭方法
protected static void Open()
{
if (Connection == null)
{
Connection = new SqlConnection(ConnectionString);
}
if (Connection.State.Equals(ConnectionState.Closed))
{
Connection.Open();
}
} protected static void Close()
{
// if (Connection != null)
if (Connection.State!=ConnectionState.Closed )
{
Connection.Close();
}
} //绑定到GridView
public static void BindGridView(string SqlString, GridView MyGvData)
{
MyGvData.DataSource = GetDataSet(SqlString);
MyGvData.DataBind();
}
//绑定到DropDownList,设定Text和value显示
public static void BindDropDownList2(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
{
try
{
SqlDataReader MyReader = GetDataReader(SqlString);
while (MyReader.Read())
{
ListItem MyItem = new ListItem();
MyItem.Text = MyReader[TextStr].ToString();
MyItem.Value = MyReader[ValueStr].ToString();
MyDDL.Items.Add(MyItem);
}
MyReader.Close();
}
catch { }
}
//绑定到DropDownList,设定Text和value显示,先清空DropDownList
public static void BindDropDownList(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
{
try
{
SqlDataReader MyReader = GetDataReader(SqlString);
MyDDL.Items.Clear();
while (MyReader.Read())
{
ListItem MyItem = new ListItem();
MyItem.Text = MyReader[TextStr].ToString();
MyItem.Value = MyReader[ValueStr].ToString();
MyDDL.Items.Add(MyItem);
}
MyReader.Close();
}
catch { }
}
//绑定到DropDownList,设定Text和value显示
public static void BindItemList(string SqlString, ListBox MyDDL, string TextStr, string ValueStr)
{
SqlDataReader MyReader = GetDataReader(SqlString);
MyDDL.Items.Clear();
while (MyReader.Read())
{
ListItem MyItem = new ListItem();
MyItem.Text = MyReader[TextStr].ToString();
MyItem.Value = MyReader[ValueStr].ToString();
MyDDL.Items.Add(MyItem);
}
MyReader.Close();
}
//绑定到DropDownList,设定Text和value显示,先清空DropDownList
public static void BindDropDownListAddEmpty(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
{
SqlDataReader MyReader = GetDataReader(SqlString);
MyDDL.Items.Clear();
ListItem MyItem1 = new ListItem();
MyItem1.Text = "";
MyItem1.Value = "0";
MyDDL.Items.Add(MyItem1);
while (MyReader.Read())
{
ListItem MyItem = new ListItem();
MyItem.Text = MyReader[TextStr].ToString();
MyItem.Value = MyReader[ValueStr].ToString();
MyDDL.Items.Add(MyItem);
}
MyReader.Close();
}
//返回一个用 | 分隔的字符串
public static string GetStringList(string SqlString)
{
string ReturnStr = string.Empty;
SqlDataReader MyReader = GetDataReader(SqlString);
while (MyReader.Read())
{
if (ReturnStr.Length > 0)
{
ReturnStr = ReturnStr + "|" + MyReader[0].ToString();
}
else
{
ReturnStr = MyReader[0].ToString();
}
}
MyReader.Close();
return ReturnStr;
}
//返回当前最大的列值
public static int GetMaxID(string FieldName, string TableName)
{
int MyReturn = 0;
SqlDataReader MyReader = GetDataReader("select max(" + FieldName + ") from " + TableName);
if (MyReader.Read())
{
MyReturn =int.Parse(MyReader[0].ToString());
}
MyReader.Close();
return MyReturn;
}
//判断用Sql查询的数据是否存在,true表示存在,False表示不存在
public static bool Exists(string strSql)
{
object obj = DbHelperSQL.GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
public static bool Exists(string strSql, params SqlParameter[] cmdParms)
{
object obj = DbHelperSQL.GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
//返回SqlDataReader数据集,使用完后记得关闭SqlDataReader
public static SqlDataReader GetDataReader(string SqlString)
{ Open();
SqlCommand cmd = new SqlCommand(SqlString, Connection);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } // 公有方法,获取数据,返回一个DataSet。
public static DataSet GetDataSet(string SqlString)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
// SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection);//这二行都不行
using (SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection))
{
DataSet dataset = new DataSet();
adapter.Fill(dataset);
connection.Close();
return dataset;
}
} }
public static DataSet GetDataSet(string SqlString)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
// SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection);//这二行都不行
using (SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection))
{
DataSet dataset = new DataSet();
adapter.Fill(dataset);
connection.Close();
return dataset;
}
} }这个位置: adapter.Fill(dataset),因登陆时对数据库有很多操作的,估计是GetDataSet有点问题,
下面是所有的代码:namespace lgh.DBUtility
{
/// <summary>
/// 数据访问抽象基础类
/// Copyright (C) 2004-2008 By lgh
/// </summary>
public abstract class DbHelperSQL
{
public DbHelperSQL()
{ }
//自己解密数据库设置字符串
protected static string DecryptDBStr(string Text, string sKey)
{
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
int len;
len = Text.Length / 2;
byte[] inputByteArray = new byte[len];
int x, i;
for (x = 0; x < len; x++)
{
i = Convert.ToInt32(Text.Substring(x * 2, 2), 16);
inputByteArray[x] = (byte)i;
}
des.Key = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
des.IV = ASCIIEncoding.ASCII.GetBytes(System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(sKey, "md5").Substring(0, 8));
System.IO.MemoryStream ms = new System.IO.MemoryStream();
CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
cs.Write(inputByteArray, 0, inputByteArray.Length);
cs.FlushFinalBlock();
return Encoding.Default.GetString(ms.ToArray());
}
//定义连接字符串。
protected static string ConnectionString = DecryptDBStr(ConfigurationManager.AppSettings["SQLConnectionString"], "lgh");
protected static SqlConnection Connection;
protected static SqlConnection Connection1;
//定义数据库的打开和关闭方法
protected static void Open()
{
if (Connection == null)
{
Connection = new SqlConnection(ConnectionString);
}
if (Connection.State.Equals(ConnectionState.Closed))
{
Connection.Open();
}
} protected static void Close()
{
// if (Connection != null)
if (Connection.State!=ConnectionState.Closed )
{
Connection.Close();
}
} //绑定到GridView
public static void BindGridView(string SqlString, GridView MyGvData)
{
MyGvData.DataSource = GetDataSet(SqlString);
MyGvData.DataBind();
}
//绑定到DropDownList,设定Text和value显示
public static void BindDropDownList2(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
{
try
{
SqlDataReader MyReader = GetDataReader(SqlString);
while (MyReader.Read())
{
ListItem MyItem = new ListItem();
MyItem.Text = MyReader[TextStr].ToString();
MyItem.Value = MyReader[ValueStr].ToString();
MyDDL.Items.Add(MyItem);
}
MyReader.Close();
}
catch { }
}
//绑定到DropDownList,设定Text和value显示,先清空DropDownList
public static void BindDropDownList(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
{
try
{
SqlDataReader MyReader = GetDataReader(SqlString);
MyDDL.Items.Clear();
while (MyReader.Read())
{
ListItem MyItem = new ListItem();
MyItem.Text = MyReader[TextStr].ToString();
MyItem.Value = MyReader[ValueStr].ToString();
MyDDL.Items.Add(MyItem);
}
MyReader.Close();
}
catch { }
}
//绑定到DropDownList,设定Text和value显示
public static void BindItemList(string SqlString, ListBox MyDDL, string TextStr, string ValueStr)
{
SqlDataReader MyReader = GetDataReader(SqlString);
MyDDL.Items.Clear();
while (MyReader.Read())
{
ListItem MyItem = new ListItem();
MyItem.Text = MyReader[TextStr].ToString();
MyItem.Value = MyReader[ValueStr].ToString();
MyDDL.Items.Add(MyItem);
}
MyReader.Close();
}
//绑定到DropDownList,设定Text和value显示,先清空DropDownList
public static void BindDropDownListAddEmpty(string SqlString, DropDownList MyDDL, string TextStr, string ValueStr)
{
SqlDataReader MyReader = GetDataReader(SqlString);
MyDDL.Items.Clear();
ListItem MyItem1 = new ListItem();
MyItem1.Text = "";
MyItem1.Value = "0";
MyDDL.Items.Add(MyItem1);
while (MyReader.Read())
{
ListItem MyItem = new ListItem();
MyItem.Text = MyReader[TextStr].ToString();
MyItem.Value = MyReader[ValueStr].ToString();
MyDDL.Items.Add(MyItem);
}
MyReader.Close();
}
//返回一个用 | 分隔的字符串
public static string GetStringList(string SqlString)
{
string ReturnStr = string.Empty;
SqlDataReader MyReader = GetDataReader(SqlString);
while (MyReader.Read())
{
if (ReturnStr.Length > 0)
{
ReturnStr = ReturnStr + "|" + MyReader[0].ToString();
}
else
{
ReturnStr = MyReader[0].ToString();
}
}
MyReader.Close();
return ReturnStr;
}
//返回当前最大的列值
public static int GetMaxID(string FieldName, string TableName)
{
int MyReturn = 0;
SqlDataReader MyReader = GetDataReader("select max(" + FieldName + ") from " + TableName);
if (MyReader.Read())
{
MyReturn =int.Parse(MyReader[0].ToString());
}
MyReader.Close();
return MyReturn;
}
//判断用Sql查询的数据是否存在,true表示存在,False表示不存在
public static bool Exists(string strSql)
{
object obj = DbHelperSQL.GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
public static bool Exists(string strSql, params SqlParameter[] cmdParms)
{
object obj = DbHelperSQL.GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
//返回SqlDataReader数据集,使用完后记得关闭SqlDataReader
public static SqlDataReader GetDataReader(string SqlString)
{ Open();
SqlCommand cmd = new SqlCommand(SqlString, Connection);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } // 公有方法,获取数据,返回一个DataSet。
public static DataSet GetDataSet(string SqlString)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
// SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection);//这二行都不行
using (SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection))
{
DataSet dataset = new DataSet();
adapter.Fill(dataset);
connection.Close();
return dataset;
}
} }
解决方案 »
- 请教.ASPX文件是怎么被注入JS代码的(原理、解决方法、防范措施)
- 求助,IIS不能预览index.aspx页面问题。
- 辞职,找工作,郁闷,散分 ing.....
- 是不是在页面中使用了UpdatePanel 后在点击刷新 提示无法刷新啊
- 求个Excel导入sql的解决方案
- [问题]二进制流转化成对应的文件
- 怎样把DataGrid和对象数组绑定?
- 大批量UPdate如何优化
- [求助] 在repeater添加了一个服务器端的CheckBox,如何知道用户选择了那行?
- 我在VB.NET下调用弹出对话框,可是无论click确定还是取消,结果都一样,我写的有什么错误吗?
- 如何获取 别人的网站的站内搜索结果
- 请教:URL的中文参数传递及其编码的问题
public static DataTable GetDataTable(string SqlString)
{
DataSet dataset = GetDataSet(SqlString);
return dataset.Tables[0];
}
// 公有方法,获取数据,返回首行首列。
public static string GetSHSL(string SqlString)
{
DataSet dataset = GetDataSet(SqlString);
if (dataset.Tables[0].Rows.Count > 0)
{
return Convert.ToString(dataset.Tables[0].Rows[0][0].ToString());
}
else
{
return "";
}
}
// 公有方法,获取数据,返回首行首列的INT值。
public static string GetSHSLInt(string SqlString)
{
DataSet dataset = GetDataSet(SqlString);
if (dataset.Tables[0].Rows.Count > 0)
{
return Convert.ToString(dataset.Tables[0].Rows[0][0].ToString());
}
else
{
return "0";
}
}
// 公有方法,获取数据,返回一个DataRow。
public static DataRow GetDataRow(string SqlString)
{
DataSet dataset = GetDataSet(SqlString);
if (dataset.Tables[0].Rows.Count > 0)
{
return dataset.Tables[0].Rows[0];
}
else
{
return null;
}
}
// 公有方法,执行Sql语句。对Update、Insert、Delete为影响到的行数,其他情况为-1
public static int ExecuteSQL(String SqlString, Hashtable MyHashTb)
{
int count = -1;
Open();
try
{
SqlCommand cmd = new SqlCommand(SqlString, Connection);
foreach (DictionaryEntry item in MyHashTb)
{
string[] CanShu = item.Key.ToString().Split('|');
if (CanShu[1].ToString().Trim() == "string")
{
cmd.Parameters.Add(CanShu[0], SqlDbType.VarChar);
}
else if (CanShu[1].ToString().Trim() == "int")
{
cmd.Parameters.Add(CanShu[0], SqlDbType.Int);
}
else if (CanShu[1].ToString().Trim() == "text")
{
cmd.Parameters.Add(CanShu[0], SqlDbType.Text);
}
else if (CanShu[1].ToString().Trim() == "datetime")
{
cmd.Parameters.Add(CanShu[0], SqlDbType.DateTime);
}
else
{
cmd.Parameters.Add(CanShu[0], SqlDbType.VarChar);
}
cmd.Parameters[CanShu[0]].Value = item.Value.ToString();
}
count = cmd.ExecuteNonQuery();
}
catch
{
count = -1;
}
finally
{
Close();
}
return count;
}
// 公有方法,执行Sql语句。对Update、Insert、Delete为影响到的行数,其他情况为-1
public static int ExecuteSQL(String SqlString)
{
// int count = -1;
// Open();
// try
// {
// SqlCommand cmd = new SqlCommand(SqlString, Connection);
// count = cmd.ExecuteNonQuery();
// }
// catch
// {
// count = -1;
// }
// finally
// {
// Close();
// }
// return count; int count = -1;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand(SqlString, connection)) //这里li加了using
{
try
{
cmd.CommandTimeout = 300;
count = cmd.ExecuteNonQuery();
}
catch
{
count = -1;
}
finally
{
connection.Close();
}
}
}
return count;
}
// 公有方法,执行一组Sql语句。返回是否成功,采用事务管理,发现异常时回滚数据
public static bool ExecuteSQL(string[] SqlStrings)
{
bool success = true;
Open();
SqlCommand cmd = new SqlCommand();
SqlTransaction trans = Connection.BeginTransaction();
cmd.Connection = Connection;
cmd.Transaction = trans;
try
{
foreach (string str in SqlStrings)
{
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch
{
success = false;
trans.Rollback();
}
finally
{
Close();
}
return success;
}
public static object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
public static object GetSingle(string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}
// 执行SQL语句,返回影响的记录数
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}
//执行查询语句,返回DataSet
public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
{
connection.Open();
// SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection);//这二行都不行
using (SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection))
{
DataSet dataset = new DataSet();
adapter.Fill(dataset);
connection.Close();
return dataset;
}
}
=>
using (SqlConnection connection = new SqlConnection(ConnectionString))
{ using (SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection))
{
DataSet dataset = new DataSet();
adapter.Fill(dataset); return dataset;
}
}
{
if (Connection == null)
{
Connection = new SqlConnection(ConnectionString);
}
if (Connection.State.Equals(ConnectionState.Closed))
{
Connection.Open();
}
else
throw new Exception("已有其他人员正在查询,请稍后再试");
}改成这样就可以了。
----
已有打开的与此命令相关联的DataReader,必须首先将它关闭
同样的条件,第一个用户正在查询,还没有执行到Close,第二个已经进来,这时使用的还是同一个DataReader,还是正在打开的,所以会报错,不出问题才怪不要用静态的
检查一下你的Reader对象是不是有static的?打开没关闭?
花钱还不提供技术支持,这是bug
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);你的DataReader用法换成这样看看。
程序买来时getdataset是这样的:
// 公有方法,获取数据,返回一个DataSet。
public static DataSet GetDataSet(string SqlString)
{
Open();
SqlDataAdapter adapter = new SqlDataAdapter(SqlString, Connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Close();
return dataset;
}
程序里有个聊天程序,定时调用这个getdataset查询聊天记录及在线人数,因为如果有二人在线聊天一段时间就会出现提示: 已有打开的与此命令相关联的DataReader,必须首先将它关闭。所以将它改了一下:
public static DataSet GetDataSet(string SqlString)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
// SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection);//这二行都不行
using (SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection))
{
DataSet dataset = new DataSet();
adapter.Fill(dataset);
connection.Close();
return dataset;
}
} }
改了后,用XP做服务器,二台电脑测试,开四五个浏览器,同时运行聊天模块一段时间没有问题。但同时会出现一天需要重启服务器几次的情况,否则不能登陆了。也不能运行。
但改成
using (SqlConnection connection = new SqlConnection(ConnectionString))
{ using (SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection))
{
DataSet dataset = new DataSet();
adapter.Fill(dataset); return dataset;
}
}
后,同样二台电脑测试,只能开二个浏览器了,再多开就显示在线用户多了。
不知改成微软的那个SqlHelper.cs / DataAccessApplicationBlock 是否可行,上面的回帖,说什么的都有,真不知怎么办啊
dr.Read();
或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();
}
} }
using (SqlConnection connection = new SqlConnection(ConnectionString))
{ using (SqlDataAdapter adapter = new SqlDataAdapter(SqlString, connection))
{ adapter.Fill(dataset);
}
}
return dataset;
或者取值后,将记录集赋给table
然后执行其他操作