/// <summary>
/// 创建数据访问命令。
/// </summary>
/// <param name="sql">SQL语句。</param>
/// <returns>数据访问命令。</returns>
public DataAccessCommand CreateCommand(string sql)
{
DataAccessCommand cmd = new DataAccessCommand(this);
cmd.Sql = sql;
return(cmd);
}
/// <summary>
/// 执行数据访问命令，返回查询结果中第一行第一列的值。
/// </summary>
/// <param name="cmd">要执行的数据访问命令。</param>
/// <returns>查询结果中第一行第一列的值。</returns>
/// <exception cref="ArgumentNullException">如果cmd为空引用，则抛出该异常。</exception>
/// <exception cref="ArgumentException">如果cmd.Sql为空，则抛出该异常。</exception>
public object ExecuteScalar(DataAccessCommand cmd)
{
if (cmd == null)
{
throw new ArgumentNullException("cmd");
}
if (string.IsNullOrEmpty(cmd.Sql))
{
throw new ArgumentException(Properties.Resources.InvalidSql, "cmd");
}
return(ExecuteScalar(cmd.Sql, cmd.Parameters));
}
/// <summary>
/// 执行数据访问命令，返回查询结果。
/// </summary>
/// <param name="cmd">要执行的数据访问命令。</param>
/// <returns>查询结果。</returns>
/// <exception cref="ArgumentNullException">如果cmd为空引用，则抛出该异常。</exception>
/// <exception cref="ArgumentException">如果cmd.Sql为空，则抛出该异常。</exception>
public DataTable ExecuteDataTable(DataAccessCommand cmd)
{
if (cmd == null)
{
throw new ArgumentNullException("cmd");
}
if (string.IsNullOrEmpty(cmd.Sql))
{
throw new ArgumentException(Properties.Resources.InvalidSql, "cmd");
}
return(ExecuteDataTable(cmd.Sql, cmd.Parameters, cmd.ReturnCount));
}
/// <summary>
/// 执行SQL脚本（DML语句或存储过程）。
/// 通过逗号分隔符，可以将多条SQL语句连接为一条语句，在同一个事务中依次执行。
/// </summary>
/// <param name="script">要执行的SQL脚本（DML语句或存储过程，无动态参数）。</param>
/// <param name="ignoreComment">是否忽略SQL脚本中的注释（//、--、/**/）。</param>
/// <param name="multiStatements">是否多条语句（语句之间用半角分号分隔）。</param>
/// <exception cref="ArgumentException">如果script为空，则抛出该异常。</exception>
public void ExecuteNonQuery(string script, bool ignoreComment, bool multiStatements)
{
if (string.IsNullOrEmpty(script))
{
throw new ArgumentException(Properties.Resources.InvalidSql, "script");
}
//去除注释
if (ignoreComment)
{
script = DataAccessCommand.IgnoreScriptComment(script);
}
if (string.IsNullOrEmpty(script))
{
throw new ArgumentException(Properties.Resources.InvalidSql, "script");
}
if (multiStatements)
{
//在同一个事务中执行多条语句
Collection <DataAccessCommand> commands = new Collection <DataAccessCommand>();
foreach (string statement in DataAccessCommand.ParseScriptToStatements(script))
{
DataAccessCommand command = CreateCommand(statement);
if (DataAccessCommand.IsProcedure(statement))
{
command.SqlType = CommandType.StoredProcedure;
}
commands.Add(command);
}
if (commands.Count > 0)
{
ExecuteNonQuery(commands);
}
}
else if (DataAccessCommand.IsProcedure(script))
{
//执行存储过程
ExecuteNonQuery(script, null, CommandType.StoredProcedure);
}
else
{
//执行普通SQL语句
ExecuteNonQuery(script, null, CommandType.Text);
}
}
/// <summary>
/// 执行查询语句，返回查询结果的数量。
/// </summary>
/// <param name="cmd">要执行的数据访问命令。</param>
/// <returns>查询结果的数量。</returns>
/// <exception cref="ArgumentNullException">如果cmd为空引用，则抛出该异常。</exception>
/// <exception cref="ArgumentException">如果cmd.Sql为空，则抛出该异常。</exception>
public object ExecuteCount(DataAccessCommand cmd)
{
if (cmd == null)
{
throw new ArgumentNullException("cmd");
}
if (string.IsNullOrEmpty(cmd.Sql))
{
throw new ArgumentException(Properties.Resources.InvalidSql, "cmd");
}
DataAccessCommand cmd2 = new DataAccessCommand(cmd.Parent);
cmd2.Sql = GetCountSql(cmd.Sql);
foreach (DbParameter param in cmd.Parameters)
{
cmd2.Parameters.Add(CloneParameter(param));
}
return(ExecuteScalar(cmd2));
}
/// <summary>
/// 执行数据访问命令，返回受影响的行数。
/// </summary>
/// <param name="cmd">要执行的数据访问命令。</param>
/// <returns>受影响的行数。</returns>
/// <exception cref="ArgumentNullException">如果cmd为空引用，则抛出该异常。</exception>
/// <exception cref="ArgumentException">如果cmd.Sql为空，则抛出该异常。</exception>
public int ExecuteNonQuery(DataAccessCommand cmd)
{
if (cmd == null)
{
throw new ArgumentNullException("cmd");
}
if (string.IsNullOrEmpty(cmd.Sql))
{
throw new ArgumentException(Properties.Resources.InvalidSql, "cmd");
}
DbProviderFactory factory = DbProviderFactories.GetFactory(Provider);
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
connection.Open();
DbCommand command = connection.CreateCommand();
try
{
command.Transaction = connection.BeginTransaction();
command.CommandText = cmd.Sql;
command.CommandType = cmd.SqlType;
command.Parameters.Clear();
foreach (DbParameter parameter in cmd.Parameters)
{
command.Parameters.Add(CloneParameter(parameter));
}
int result = 0;
//如果参数列表空，或者不需要批量替换参数值，则直接执行
if (cmd.Parameters.Count == 0 || cmd.ValuesList.Count == 0)
{
result = command.ExecuteNonQuery();
}
else
{
//批量替换参数值，实现同一SQL语句使用不同参数值的批量执行
foreach (object[] values in cmd.ValuesList)
{
for (int j = 0; j < command.Parameters.Count; j++)
{
command.Parameters[j].Value = values[j];
}
int tmp = command.ExecuteNonQuery();
if (tmp > 0)
{
result += tmp;
}
}
}
command.Transaction.Commit();
return(result);
}
catch
{
command.Transaction.Rollback();
throw;
}
finally
{
command.Dispose();
}
}
}
/// <summary>
/// 执行数据访问命令，根据分页方案，返回查询结果。<br/>
/// 该方法采用的是通用的分页方案，如果数据量太大会影响性能。所以，如果想获得更高的查询性能，可以针对具体数据库，编写专门的分页SQL。
/// </summary>
/// <param name="cmd">要执行的数据访问命令。</param>
/// <param name="paging">分页方案。</param>
/// <returns>查询结果。</returns>
/// <exception cref="ArgumentNullException">如果cmd或paging为空引用，则抛出该异常。</exception>
/// <exception cref="ArgumentException">如果cmd.Sql为空，则抛出该异常。</exception>
public DataTable ExecuteDataTable(DataAccessCommand cmd, DataAccessPaging paging)
{
if (cmd == null)
{
throw new ArgumentNullException("cmd");
}
if (string.IsNullOrEmpty(cmd.Sql))
{
throw new ArgumentException(Properties.Resources.InvalidSql, "cmd");
}
if (paging == null)
{
throw new ArgumentNullException("paging");
}
//更新记录总数
string sqlCount = GetCountSql(cmd.Sql);
paging.RowsCount = Convert.ToInt32(ExecuteScalar(sqlCount, cmd.Parameters));
//返回所有页的数据
if (paging.PageSize <= 0)
{
return(ExecuteDataTable(cmd));
}
//检查要返回的数据的范围
if (paging.PageIndex >= paging.PagesCount)
{
paging.PageIndex = paging.PagesCount - 1;
}
int startRow = paging.PageIndex * paging.PageSize;
int endRow = startRow + paging.PageSize - 1;
//返回指定页的数据
DataTable dataTable = new DataTable("T");
dataTable.Locale = System.Globalization.CultureInfo.CurrentCulture;
//读取指定页的数据
using (DbDataReader reader = ExecuteDataReader(cmd))
{
//复制表结构
for (int j = 0; j < reader.FieldCount; ++j)
{
dataTable.Columns.Add(reader.GetName(j), reader.GetFieldType(j));
}
//循环读取数据行
int i = 0;
while (reader.Read() && i <= endRow)
{
//限制每页返回的记录数
if (cmd.ReturnCount >= 0 && dataTable.Rows.Count >= cmd.ReturnCount)
{
break;
}
if (i >= startRow)
{
DataRow row = dataTable.NewRow();
for (int j = 0; j < reader.FieldCount; ++j)
{
row[j] = reader[j];
}
dataTable.Rows.Add(row);
}
i++;
}
}
return(dataTable);
}
