下面是代码。程序偶尔会报数据库的错误,我怀疑是SqlHelper类有些地方不够完善。 public class MSSQLDAL:IDataAccess
{
private SqlConnection _con;
private SqlTransaction _tran; public MSSQLDAL(string constr)
{
this._con = new SqlConnection(constr);
}
#region IDataAccess 成员
/// <summary>
/// 打开数据库连接
/// </summary>
public void Open()
{
try
{
if (this._con == null || this._con.State == ConnectionState.Closed)
{
this._con.Open();
}
else
{
this._con.Close();
this._con.Open();
}
}
catch
{
this._con.Close();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (this._con != null || this._tran != null)
{
this._con.Close();
}
}
/// <summary>
/// 增删改操作
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">参数</param>
/// <returns>影响行数</returns>
public int ExecuteNonQuery(string sql, params QueryParameter[] param)
{
int i = 0;
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, sql, CommandType.Text, param);
i = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch
{
this._con.Close();
}
return i;
}
/// <summary>
/// 返回单个值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">参数</param>
/// <returns>查询结果的第一行值</returns>
public object GetScalar(string sql, params QueryParameter[] param)
{
object obj = null;
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, sql, CommandType.Text, param);
obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
}
catch
{
this._con.Close();
} return obj;
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">参数</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql, params QueryParameter[] param)
{
DataTable dt = new DataTable(); try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, sql, CommandType.Text, param);
SqlDataAdapter da = new SqlDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
cmd.Parameters.Clear();
}
catch
{
this._con.Close();
}
return dt;
}
/// <summary>
/// 返回DataReader
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">参数</param>
/// <returns>DataReader</returns>
public IDataReader GetDataReader(string sql, params QueryParameter[] param)
{
SqlDataReader dr = null;
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, sql, CommandType.Text, param);
dr = cmd.ExecuteReader();
cmd.Parameters.Clear();
}
catch
{
this._con.Close();
} return dr;
}
/// <summary>
/// 开启事务
/// </summary>
public void BeginTran()
{
_tran = this._con.BeginTransaction();
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTran()
{
this._tran.Commit();
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollBackTran()
{
this._tran.Rollback();
} #endregion /// <summary>
/// 连接初始化
/// </summary>
/// <param name="cmd">SqlCommand</param>
/// <param name="commandText">SQL语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="commandParam">参数</param>
private void PrepareCommand(SqlCommand cmd, string commandText, CommandType commandType, params QueryParameter[] commandParam)
{
cmd.CommandType = commandType;
cmd.CommandText = commandText;
cmd.Connection = this._con;
cmd.Transaction = this._tran;
if (commandParam != null && commandParam.Length > 0)
{
for (int i = 0; i < commandParam.Length; i++)
{
cmd.Parameters.AddWithValue(commandParam[i].Name, commandParam[i].Value);
}
}
} }
{
private SqlConnection _con;
private SqlTransaction _tran; public MSSQLDAL(string constr)
{
this._con = new SqlConnection(constr);
}
#region IDataAccess 成员
/// <summary>
/// 打开数据库连接
/// </summary>
public void Open()
{
try
{
if (this._con == null || this._con.State == ConnectionState.Closed)
{
this._con.Open();
}
else
{
this._con.Close();
this._con.Open();
}
}
catch
{
this._con.Close();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (this._con != null || this._tran != null)
{
this._con.Close();
}
}
/// <summary>
/// 增删改操作
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">参数</param>
/// <returns>影响行数</returns>
public int ExecuteNonQuery(string sql, params QueryParameter[] param)
{
int i = 0;
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, sql, CommandType.Text, param);
i = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch
{
this._con.Close();
}
return i;
}
/// <summary>
/// 返回单个值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">参数</param>
/// <returns>查询结果的第一行值</returns>
public object GetScalar(string sql, params QueryParameter[] param)
{
object obj = null;
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, sql, CommandType.Text, param);
obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
}
catch
{
this._con.Close();
} return obj;
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">参数</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql, params QueryParameter[] param)
{
DataTable dt = new DataTable(); try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, sql, CommandType.Text, param);
SqlDataAdapter da = new SqlDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
cmd.Parameters.Clear();
}
catch
{
this._con.Close();
}
return dt;
}
/// <summary>
/// 返回DataReader
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">参数</param>
/// <returns>DataReader</returns>
public IDataReader GetDataReader(string sql, params QueryParameter[] param)
{
SqlDataReader dr = null;
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, sql, CommandType.Text, param);
dr = cmd.ExecuteReader();
cmd.Parameters.Clear();
}
catch
{
this._con.Close();
} return dr;
}
/// <summary>
/// 开启事务
/// </summary>
public void BeginTran()
{
_tran = this._con.BeginTransaction();
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTran()
{
this._tran.Commit();
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollBackTran()
{
this._tran.Rollback();
} #endregion /// <summary>
/// 连接初始化
/// </summary>
/// <param name="cmd">SqlCommand</param>
/// <param name="commandText">SQL语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="commandParam">参数</param>
private void PrepareCommand(SqlCommand cmd, string commandText, CommandType commandType, params QueryParameter[] commandParam)
{
cmd.CommandType = commandType;
cmd.CommandText = commandText;
cmd.Connection = this._con;
cmd.Transaction = this._tran;
if (commandParam != null && commandParam.Length > 0)
{
for (int i = 0; i < commandParam.Length; i++)
{
cmd.Parameters.AddWithValue(commandParam[i].Name, commandParam[i].Value);
}
}
} }
我引用了SqlHelper,最近程序偶尔会报数据库的错误,点一下确定程序就退出了,但是没有记录错误日志。。
{
SqlDataReader dr = null;
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, sql, CommandType.Text, param);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
}
catch
{
this._con.Close();
} return dr;
}
这个地方可能会出错
看看楼主是怎么记录错误日志的啊?
{
AppDomain.CurrentDomain.UnhandledException += new UnhandledExceptionEventHandler(CurrentDomain_UnhandledException);
} static void CurrentDomain_UnhandledException(object sender, UnhandledExceptionEventArgs e)
{
string strException = string.Format("{0}发生系统异常。\r\n{1}\r\n\r\n\r\n", DateTime.Now, e.ExceptionObject.ToString());
File.AppendAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SystemException.log"), strException);
}
多谢指教。但是我程序里没有一个地方用到DataReader。我个人也不喜欢用dataReader,一般都用DataTable代替。
{
try
{
if (this._con == null || this._con.State == ConnectionState.Closed)
{
this._con.Open();
}
else
{
this._con.Close();
this._con.Open();
}
}
catch
{
this._con.Close();
}
}this._con等于null,this._con.Open();就报错了吧public void Close()
{
if (this._con != null || this._tran != null)
{
try
{
this._con.Close();
}catch{}
}
}其余地方this._con.Close();我觉得换成Close()比较好
if (this._con.State == ConnectionState.Closed)
{
this._con.Open();
}
另外Open函数和Close函数是不是写在GetDataTable,GetScalar和GetDataReader里会更好一些?
这样就不用每次都手工调用Open函数和Close函数了。
IDataAccess data = DataAccessFactory.CreateDataAccess(); 然后
data.Open();
string strsql = ".....";
DataTable dt = data.GetDataTable(strsql);
data.Close();每次都要显式的去调用Open和Close方法。
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}/// <summary>
/// 向数据库里插入图像格式的字段
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}