求优化一个SqlHelper类
下面是代码。程序偶尔会报数据库的错误，我怀疑是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); } } } }
/// <summary>/// 执行多条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();}}}