/// <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;
}
