/// <summary>
/// 在指定的事务下,执行一个SQL命令并返回一个数据集
/// </summary>
/// <param name="pTransaction">事务</param>
/// <param name="pCommandType">SQL命令的类别(存储过程,SQL语句等)</param>
/// <param name="pCommandText">存储过程名称或者是SQL语句</param>
/// <param name="pCommandParameters">SQL命令的参数</param>
/// <returns>数据集</returns>
public DataTable ExecuteDataTable(SqlTransaction pTransaction, CommandType pCommandType, string pCommandText, params SqlParameter[] pCommandParameters)
{
//参数校验
if (pTransaction == null)
{
throw new ArgumentNullException("pTransaction");
}
if (pTransaction != null && pTransaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "pTransaction");
}
//创建SQL命令
SqlCommand cmd = new SqlCommand();
this.PrepareCommand(cmd, pTransaction.Connection, pTransaction, pCommandType, pCommandText, pCommandParameters);
//触发事件
if (this.BeforeExecute != null)
{
SqlCommandExecutionEventArgs arg = new SqlCommandExecutionEventArgs();
arg.Command = cmd;
arg.UserInfo = this.CurrentUserInfo;
this.BeforeExecute(this, arg);
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
//开启SQL语句执行时间监控
Stopwatch sw = new Stopwatch();
sw.Start();
//执行SQL命令
da.Fill(dt);
//停止监控
sw.Stop();
//触发事件
if (this.OnExecuted != null)
{
SqlCommandExecutionEventArgs arg = new SqlCommandExecutionEventArgs();
arg.Command = cmd;
arg.ExecutionTime = sw.Elapsed;
arg.UserInfo = this.CurrentUserInfo;
this.OnExecuted(this, arg);
}
//清理资源
cmd.Parameters.Clear();
//返回
return(dt);
}
}
/// <summary>
/// 执行一个SQL命令并返回一个数据集
/// </summary>
/// <param name="pCommandType">SQL命令的类别(存储过程,SQL语句等)</param>
/// <param name="pCommandText">存储过程名称或者是SQL语句</param>
/// <param name="pCommandParameters">SQL命令的参数</param>
/// <returns>数据集</returns>
public DataTable ExecuteDataTable(CommandType pCommandType, string pCommandText, params SqlParameter[] pCommandParameters)
{
//创建SQL命令
using (var connection = this.GetConnection())
{
SqlCommand cmd = new SqlCommand();
this.PrepareCommand(cmd, connection, (SqlTransaction)null, pCommandType, pCommandText, pCommandParameters);
//触发事件
if (this.BeforeExecute != null)
{
SqlCommandExecutionEventArgs arg = new SqlCommandExecutionEventArgs();
arg.Command = cmd;
arg.UserInfo = this.CurrentUserInfo;
this.BeforeExecute(this, arg);
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
//开启SQL语句执行时间监控
Stopwatch sw = new Stopwatch();
sw.Start();
//执行SQL命令
da.Fill(dt);
//停止监控
sw.Stop();
//触发事件
if (this.OnExecuted != null)
{
SqlCommandExecutionEventArgs arg = new SqlCommandExecutionEventArgs();
arg.Command = cmd;
arg.ExecutionTime = sw.Elapsed;
arg.UserInfo = this.CurrentUserInfo;
this.OnExecuted(this, arg);
}
//清理资源
cmd.Parameters.Clear();
//返回
return(dt);
}
}
}
/// <summary>
/// 执行一个SQL命令并返回一个向前的只读数据读取器
/// </summary>
/// <param name="pCommandType">SQL命令的类别(存储过程,SQL语句等)</param>
/// <param name="pCommandText">存储过程名称或者是SQL语句</param>
/// <param name="pCommandParameters">SQL命令的参数</param>
/// <returns>向前的只读数据读取器</returns>
public SqlDataReader ExecuteReader(CommandType pCommandType, string pCommandText, params SqlParameter[] pCommandParameters)
{
//创建SQL命令
var connection = this.GetConnection();
SqlCommand cmd = new SqlCommand();
this.PrepareCommand(cmd, connection, (SqlTransaction)null, pCommandType, pCommandText, pCommandParameters);
//触发事件
if (this.BeforeExecute != null)
{
SqlCommandExecutionEventArgs arg = new SqlCommandExecutionEventArgs();
arg.Command = cmd;
arg.UserInfo = this.CurrentUserInfo;
this.BeforeExecute(this, arg);
}
//开启SQL语句执行时间监控
Stopwatch sw = new Stopwatch();
sw.Start();
//执行SQL命令
SqlDataReader reader = null;
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//停止监控
sw.Stop();
//触发事件
if (this.OnExecuted != null)
{
SqlCommandExecutionEventArgs arg = new SqlCommandExecutionEventArgs();
arg.Command = cmd;
arg.ExecutionTime = sw.Elapsed;
arg.UserInfo = this.CurrentUserInfo;
this.OnExecuted(this, arg);
}
//清理资源
cmd.Parameters.Clear();
//返回
return(reader);
}