/// <summary> /// 更新或者登陆多条数据 /// </summary> /// <param name="comm">命令集</param> /// <returns>操作的记录数</returns> public static bool ExecuteTransForList(SqlCommand[] comms) { //获得返回集实例 _result = Utility.Result.GetInstance(); //创建连接 using (SqlConnection conn = new SqlConnection(_connectionString)) { SqlTransaction transaction = null; try { //打开连接 conn.Open(); //开始事务 transaction = conn.BeginTransaction(); //定义操作COUNT int totalCount = 0; //设置参数 if (comms != null && comms.Length > 0) { for (int i = 0; i < comms.Length; i++) { SqlCommand comm = comms[i]; //设置命令数据库连接 comm.Connection = conn; comm.Transaction = transaction; //执行数据库操作 totalCount += comm.ExecuteNonQuery(); } } //提交事务 transaction.Commit(); //设置Error标志 _result.HasError = false; //设置影响记录数 _result.OprateCount = totalCount; } catch (Exception e) { //如果开始了事务,则回滚事务 if (transaction != null) { transaction.Rollback(); } //设置Error标志 _result.HasError = true; //设置Error信息 _result.ErrorMessage = e.Message; //设置影响记录数为0 _result.OprateCount = 0; // throw e; } finally { //连接打开时,关闭连接 if (conn != null) { conn.Close(); } } } return true; }
/// <summary> /// 执行聚会函数 added by jiangym /// </summary> /// <param name="comm"></param> /// <param name="parms"></param> /// <returns></returns> public static object ExecuteScalar(string sql, params SqlParameter[] p) { //获得返回集实例 _result = Utility.Result.GetInstance(); object obj = null; SqlCommand comm = new SqlCommand(); //创建连接 using (SqlConnection conn = new SqlConnection(_connectionString)) { //设置参数 if (p != null && p.Length > 0) { foreach (SqlParameter item in p) comm.Parameters.Add(item); } comm.Connection = conn; //打开连接 conn.Open(); comm.CommandText = sql; obj = comm.ExecuteScalar(); try { //设置查询参数 _result.Parameters = comm.Parameters; //设置Error标志 _result.HasError = false; } catch (Exception e) { //设置Error标志 _result.HasError = true; //设置Error信息 _result.ErrorMessage = e.Message; } finally { //连接打开时,关闭连接 if (conn != null) { conn.Close(); } } } return obj; }
/// <summary> /// 执行数据库操作 /// </summary> /// <param name="comm">执行的命令</param> /// <param name="p">参数集</param> /// <returns>操作的记录数</returns> private static int ExecuteTrans(SqlCommand comm, params SqlParameter[] p) { //获得返回集实例 _result = Utility.Result.GetInstance(); //创建连接 using (SqlConnection conn = new SqlConnection(_connectionString)) { comm.Parameters.Clear(); //设置参数 if (p != null && p.Length > 0) { foreach (SqlParameter item in p) comm.Parameters.Add(item); } //设置命令数据库连接 comm.Connection = conn; //打开连接 conn.Open(); try { //设置事务 comm.Transaction = conn.BeginTransaction(); //执行数据库操作 _result.OprateCount = comm.ExecuteNonQuery(); //提交事务 comm.Transaction.Commit(); //设置查询参数 _result.Parameters = comm.Parameters; //设置Error标志 _result.HasError = false; } catch (Exception e) { //如果开始了事务,则回滚事务 if (comm.Transaction != null) { comm.Transaction.Rollback(); } //设置Error标志 _result.HasError = true; //设置Error信息 _result.ErrorMessage = e.Message; //设置影响记录数为0 _result.OprateCount = 0; throw e; } finally { //连接打开时,关闭连接 if (conn != null) { conn.Close(); } } } return _result.OprateCount; }
/// <summary> /// 执行Sql返回Dependency对象 added by 江贻明 /// </summary> /// <param name="sql">sql语句</param> /// <param name="Dependency">Dependency对象</param> /// <param name="parms">参数</param> /// <returns>DataTable</returns> public static DataTable ExecuteSqlDependency(string sql, ref System.Web.Caching.SqlCacheDependency Dependency, params SqlParameter[] parms) { SqlDependency.Start(_connectionString); //获得返回集实例 _result = Utility.Result.GetInstance(); //创建连接 using (SqlConnection conn = new SqlConnection(_connectionString)) { SqlCommand comm = new SqlCommand(); //设置参数 if (parms != null && parms.Length > 0) { foreach (SqlParameter item in parms) comm.Parameters.Add(item); } comm.CommandText = sql; System.Web.Caching.SqlCacheDependency dependency = new System.Web.Caching.SqlCacheDependency(comm); Dependency = dependency; comm.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(comm); try { //填充数据 da.Fill(_result.DataTable); //设置查询参数 _result.Parameters = comm.Parameters; //设置Error标志 _result.HasError = false; } catch (Exception e) { //设置Error标志 _result.HasError = true; //设置Error信息 _result.ErrorMessage = e.Message; //设置查询信息为空DataTable _result.DataTable = new DataTable(); } finally { //连接打开时,关闭连接 if (conn != null) { conn.Close(); } } } return _result.DataTable; }