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