公司做了个小系统,可这几天我发现在执行一些最简单的更改和插入sql,如update table1 set state=1 where id=1(且只有几条测试数据)经常会出错:"超时时间已到.在操作完成之前超时时间已过或服务器未响应",但当按确定按钮后再次执行就一切正常,非常快就执行完了.查了数据库的操作语句都有把连接关掉.数据库操作方法如下:
public static int ExecuteNonQuery(string commandText) {
SqlCommand command = new SqlCommand();
command.Connection=new SqlConnection(connectionString);
command.CommandText = commandText;
int result=-1;
try{
if(command.Connection.State ==System.Data.ConnectionState.Closed)
{
command.Connection.Open();
}
result=command.ExecuteNonQuery(); }
catch (Exception e)
{
command.Connection.Close();
throw e;
}
finally
{
command.Connection.Close();
command=null;
}
return result;
}
public static int ExecuteNonQuery(string commandText) {
SqlCommand command = new SqlCommand();
command.Connection=new SqlConnection(connectionString);
command.CommandText = commandText;
int result=-1;
try{
if(command.Connection.State ==System.Data.ConnectionState.Closed)
{
command.Connection.Open();
}
result=command.ExecuteNonQuery(); }
catch (Exception e)
{
command.Connection.Close();
throw e;
}
finally
{
command.Connection.Close();
command=null;
}
return result;
}
{
command.Connection.Close();
throw e;
}
finally
{
command.Connection.Close();
command=null;
}
若有异常发生,command.Connection.Close();会执行两次!
我跟踪了一下,执行到result=command.ExecuteNonQuery(); }就停住了,完后就出异常,出完异常,再点同样的按钮,执行同样的代码,就一切正常了.
private SqlCommand command = new SqlCommand();
command.Connection=new SqlConnection(connectionString);
if(command.Connection.State ==System.Data.ConnectionState.Closed)
{
command.Connection.Open();
}
command.CommandText = commandText;
int result=-1;
try{
result=command.ExecuteNonQuery(); }
catch (Exception e)
{
throw e;
}
finally
{
command.Connection.Close();
}
return result;
}如果还有问题,检查你的数据库连接串(你是通过ip访问的?)以及网络dns等的效率。
SqlCommand command = new SqlCommand();
command.Connection=new SqlConnection(connectionString);
command.Connection.Open();
command.CommandText = commandText;
try{
return command.ExecuteNonQuery(); }
finally
{
command.Connection.Close();
}
}
<add key="SQLConnString" value="Persist Security Info=False;data source=localhost;user id=sa;password=sa;initial catalog=Bingo;Connect Timeout=3600"/>
网络我是要本机测试的.
{
command.Connection.Close();
throw e;
}
finally
{
command.Connection.Close();
command=null;
}
这段有问题,这样的话会执行两次关闭;
另外你最好看看是不是在触发按钮事件的时候代码出的错,把这段代码也贴出来看看。
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;namespace BingoBusiness.dbAccess
{
/// <summary>
/// dbAccess 的摘要说明。
/// </summary>
public class dbAccess
{
private static readonly string connectionString=ConfigurationSettings.AppSettings["SQLConnString"].ToString(); public dbAccess()
{
//
// TODO: 在此处添加构造函数逻辑
//
} #region private method public static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
} private static void AssignParameterValues(SqlParameter[]
commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
return;
} if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
} for (int i = 0, j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
}
private static void PrepareCommand(SqlCommand command, SqlConnection connection,SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
if (connection==null)
command.Connection=new SqlConnection(connectionString);
else
command.Connection = connection; command.CommandText = commandText; if (transaction != null)
{
command.Transaction = transaction;
} command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
} #endregion #region ExecuteNonQuery
//增加此方法为执行一插入语句并返回插入记录的自增长的Id值
public static int ExecuteAndReturnId(string commandText)
{
int result=-1;
SqlCommand cmd = new SqlCommand();
cmd.Connection = new SqlConnection(connectionString);
try
{
if(cmd.Connection.State ==System.Data.ConnectionState.Closed)
{
cmd.Connection.Open();
}
cmd.CommandText =commandText + "; SELECT @ID = @@identity ";
cmd.Parameters.Add("@ID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
result = int.Parse(cmd.Parameters["@ID"].Value.ToString());
}
catch (Exception e)
{
cmd.Connection.Close();
throw e;
}
finally
{
cmd.Connection.Close();
cmd.Parameters.Clear();
cmd=null;
}
return result;
}
public static int ExecuteNonQuery(string commandText)
{
SqlCommand command = new SqlCommand();
command.Connection=new SqlConnection(connectionString);
command.CommandText = commandText;
//command.Transaction = transaction; int result=-1;
try
{
if(command.Connection.State ==System.Data.ConnectionState.Closed)
{
command.Connection.Open();
}
//command.Connection.Open();
result=command.ExecuteNonQuery();
}
catch (Exception e)
{
command.Connection.Close();
throw e;
}
finally
{
command.Connection.Close();
command=null;
}
return result; //return ExecuteNonQuery(CommandType.Text, commandText);
}
public static int ExecuteNonQuery(CommandType commandType, string commandText)
{
return ExecuteNonQuery(commandType, commandText, (SqlParameter[])null);
} public static int ExecuteNonQuery( CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, null, null, commandType, commandText, commandParameters);
int result=-1;
try
{
if(cmd.Connection.State ==System.Data.ConnectionState.Closed)
{
cmd.Connection.Open();
}
//cmd.Connection.Open();
result=cmd.ExecuteNonQuery();
}
catch (Exception e)
{
cmd.Connection.Close();
throw e;
}
finally
{
cmd.Connection.Close();
cmd.Parameters.Clear();
cmd=null;
}
return result;
} #endregion #region ExecuteDataset public static DataSet ExecuteDataset(string commandText)
{
return ExecuteDataset(CommandType.Text, commandText);
} public static DataSet ExecuteDataset(CommandType commandType, string commandText)
{
return ExecuteDataset(commandType, commandText,
(SqlParameter[])null);
} public static DataSet ExecuteDataset(CommandType commandType, string commandText, params SqlParameter[]
commandParameters)
{
SqlCommand cmd = new SqlCommand();DataSet ds = new DataSet();
try
{
PrepareCommand(cmd, null,null, commandType,
commandText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch(Exception e)
{
cmd.Connection.Close();
throw e;
}
finally
{
cmd.Connection.Close();
cmd=null;
}
return ds;
} #endregion #region ExecuteQueryByPage /// <summary>
/// 实现按页查询
/// </summary>
{
SqlParameter[] commandParams= dbParams.GetSpParameterSet(connectionString, "P_Sys_QueryByPage");
AssignParameterValues(commandParams,new object[]{pageNumber,pageSize,commandText});
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd,null,null,CommandType.StoredProcedure,
"P_Sys_QueryByPage", commandParams);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch(Exception e)
{
cmd.Connection.Close();
throw e;
}
finally
{
cmd.Connection.Close();
cmd=null;
}
return ds;
}
#endregion #region ExecuteScalar /// <summary>
/// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
/// </summary>
public static object ExecuteScalar(string commandText)
{
return ExecuteScalar(CommandType.Text,
commandText, (SqlParameter[])null);
}
/// <summary>
/// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
/// </summary>
public static object ExecuteScalar(CommandType commandType, string commandText, params
SqlParameter[] commandParameters)
{
object result;SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd,null, null, commandType,
commandText, commandParameters);
//execute the command & return the results
if(cmd.Connection.State ==System.Data.ConnectionState.Closed)
{
cmd.Connection.Open();
}
//cmd.Connection.Open();
result=cmd.ExecuteScalar();
}
catch(Exception e)
{
cmd.Connection.Close();
throw e;
}
finally
{
cmd.Connection.Close();
cmd.Parameters.Clear();
cmd=null;
}
return result; } #endregion
#region ExecuteChange /// <summary>
/// 通过存储过程执行返回输出的值
/// </summary>
public static int ExecuteChange(string spName,DataRow drParams)
{
SqlParameter[] ps = dbParams.GetSpParameterSet(connectionString, spName);
for(int i=0;i<ps.Length;i++)
{
if (ps[i].Direction==ParameterDirection.Input)
{
int l=ps[i].ParameterName.Length-1;
string rowname=ps[i].ParameterName.Substring(1,l);
if ((ps[i].SqlDbType==SqlDbType.Bit) &&
(drParams[rowname].ToString()!=string.Empty))
ps[i].Value=Convert.ToBoolean(drParams[rowname].ToString());
else if (drParams[rowname].ToString()!=string.Empty)
ps[i].Value=drParams[rowname].ToString();
else
ps[i].Value=DBNull.Value;
}
}
return ExecuteNonQuery(CommandType.StoredProcedure,spName,ps);
} /// <summary>
/// 通过存储过程执行增、删、改的操作
/// </summary>
public static string ExecuteChange(string ProName)
{
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand=new SqlCommand(ProName,myConnection);
myCommand.CommandType =CommandType.StoredProcedure;
try
{
if(myCommand.Connection.State ==System.Data.ConnectionState.Closed)
{
myCommand.Connection.Open();
}
//myCommand.Connection.Open();
return (string)myCommand.ExecuteScalar();
}
catch//(Exception w)
{
return "wrong";
}
finally
{
myConnection.Close();
myConnection.Dispose();
System.GC.Collect();
}
} /// <summary>
/// 通过存储过程执行增、删、改的操作
/// </summary>
public static bool ExecuteChange(string spName,DataSet dsParams)
{
// SqlConnection connection=new SqlConnection(connectionString);
// connection.Open();
// SqlTransaction trans=connection.BeginTransaction();
SqlParameter[] ps = dbParams.GetSpParameterSet(connectionString, spName);
foreach(DataRow drParams in dsParams.Tables[0].Rows)
{
for(int i=0;i<=ps.Length-1;i++)
{
if (ps[i].Direction==ParameterDirection.Input)
{
ps[i].Value=DBNull.Value;
int l=ps[i].ParameterName.Length-1;
string rowname=ps[i].ParameterName.Substring(1,l);
if ((ps[i].SqlDbType==SqlDbType.Bit) &&
(drParams[rowname].ToString()!=string.Empty))
ps[i].Value=Convert.ToBoolean(drParams[rowname].ToString());
else if (drParams[rowname].ToString()!=string.Empty)
ps[i].Value=drParams[rowname].ToString();
else
ps[i].Value=DBNull.Value;
}
}
//ExecuteNonQuery(connection,trans,CommandType.StoredProcedure,spName,ps);
ExecuteNonQuery(CommandType.StoredProcedure,spName,ps);
}
return true;
} /// <summary>
/// 通过存储过程执行增、删、改的操作
/// </summary>
public static int ExecuteChange(string spName,params object[] parameterValues)
{
SqlParameter[] ps = dbParams.GetSpParameterSet(connectionString, spName);
AssignParameterValues(ps,parameterValues);
return ExecuteNonQuery(CommandType.StoredProcedure,spName,ps);
} #endregion #region ExecuteQuery /// <summary>
/// 执行Sql语句进行查询
/// </summary>
public static DataSet ExecuteQuery(string sqlText)
{
return ExecuteDataset(sqlText);
} /// <summary>
/// 执行存储过程进行查询
/// </summary>
public static DataSet ExecuteQuery(string spName,params object[] parameterValues)
{
if (parameterValues==null)
return ExecuteDataset(CommandType.StoredProcedure,spName);
SqlParameter[] ps = dbParams.GetSpParameterSet(connectionString, spName);
AssignParameterValues(ps,parameterValues);
return ExecuteDataset(CommandType.StoredProcedure,spName,ps);
}
#endregion }
}
1、
public static int ExecuteChange(string spName,DataRow drParams){
SqlParameter[] ps = dbParams.GetSpParameterSet(connectionString, spName);
for(int i=0;i<ps.Length;i++){
if (ps[i].Direction==ParameterDirection.Input){
int l=ps[i].ParameterName.Length-1;
string rowname=ps[i].ParameterName.Substring(1,l);
if ((ps[i].SqlDbType==SqlDbType.Bit) && (drParams[rowname].ToString()!=string.Empty))
ps[i].Value=Convert.ToBoolean(drParams[rowname].ToString());
else if (drParams[rowname].ToString()!=string.Empty)
ps[i].Value=drParams[rowname].ToString();
else
ps[i].Value=DBNull.Value;} }
return ExecuteNonQuery(CommandType.StoredProcedure,spName,ps);}
2、
public static int ExecuteNonQuery( CommandType commandType, string commandText, params SqlParameter[] commandParameters){
int result=-1;
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, null, null, commandType, commandText, commandParameters);
try{
if(cmd.Connection.State ==System.Data.ConnectionState.Closed){
cmd.Connection.Open();}
result=cmd.ExecuteNonQuery(); }
catch (Exception e){
cmd.Connection.Close();
throw e; }finally{
cmd.Connection.Close();
cmd.Parameters.Clear();
cmd=null; }
return result;}
3、
private static void PrepareCommand(SqlCommand command, SqlConnection connection,SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters){
if (connection==null)
command.Connection=new SqlConnection(connectionString); else
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null){
command.Transaction = transaction;}
command.CommandType = commandType;
if (commandParameters != null){
AttachParameters(command, commandParameters); }
return;}
private SqlCommand command = new SqlCommand();command.CommandTimeout = 100;
command.Connection=new SqlConnection(connectionString);
if(command.Connection.State ==System.Data.ConnectionState.Closed)
{
command.Connection.Open();
}
command.CommandText = commandText;
int result=-1;
try{
result=command.ExecuteNonQuery(); }
catch (Exception e)
{
throw e;
}
finally
{
command.Connection.Close();
}
return result;
}
加上这句:command.CommandTimeout = 100;
试试吧!
2、调试过程中查看参数赋值情况,很可能首次执行获取的值为空
3、去掉TRY,让真实错误报告到网页上来!你发布的这些代码虽然不尽如人意,但是执行起来也不会有问题的
"听起来像是数据库被锁了,执行互斥操作"怎么判断呢?