/// <summary>
/// 获取查询结果得行序列。
/// </summary>
/// <param name="sql">查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0表示不指定，此时套用默认的超时设置。</param>
/// <returns>查询结果得行序列。</returns>
public virtual IEnumerable <IDataRecord> Rows(string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
ArgAssert.NotNullOrEmpty(sql, nameof(sql));
DbConnection connection = null;
DbDataReader reader = null;
DbCommand cmd = null;
try
{
connection = CreateAndOpenConnection();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
reader = cmd.ExecuteReader();
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
cmd?.Parameters.Clear();
//获取reader失败时，关闭连接
if (reader == null && connection != null)
{
CloseConnection(connection);
}
}
return(YieldRows(connection, reader));
}
/// <summary>
/// 执行非查询SQL语句，并断言所影响的行数。若影响的函数不正确，抛出异常。
/// </summary>
/// <param name="sql">非查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0为不指定。</param>
/// <exception cref="IncorrectResultSizeException">当影响的行数不正确。</exception>
public int Execute(string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
ArgAssert.NotNullOrEmpty(sql, "sql");
DbConnection connection = null;
DbCommand cmd = null;
try
{
connection = CreateAndOpenConnection();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
}
if (connection != null)
{
CloseConnection(connection);
}
}
}
public override int GetOrdinal(string name)
{
ArgAssert.NotNullOrEmpty(name, nameof(name));
//根据列的数量选择是遍历列，还是构造列的哈希表进行检索
return(_fieldCount > 8 ? GetOrdinalByNameMap(name) : GetOrdinalByIteration(name));
}
/// <summary>
/// 返回查询语句对应查询结果的<see cref="System.Data.DataSet"/>。
/// </summary>
/// <param name="sql">查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0表示不指定，此时套用默认的超时设置。</param>
/// <returns>表示查询结果的<see cref="System.Data.DataSet"/>。</returns>
/// <exception cref="ArgumentNullException">当<paramref name="sql"/>为<c>null</c>。</exception>
/// <exception cref="ArgumentException">当<paramref name="sql"/>长度为0。</exception>
public virtual DataSet DataSet(string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
ArgAssert.NotNullOrEmpty(sql, nameof(sql));
DbConnection connection = null;
DbCommand cmd = null;
try
{
connection = CreateAndOpenConnection();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
return(FillDataSet(cmd));
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
cmd?.Parameters.Clear();
if (connection != null)
{
CloseConnection(connection);
}
}
}
/// <summary>
/// 执行非查询SQL语句，并断言所影响的行数。若影响的函数不正确，抛出异常。
/// 这是一个异步操作。
/// </summary>
/// <param name="sql">非查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0表示不指定，此时套用默认的超时设置。</param>
/// <exception cref="IncorrectResultSizeException">当影响的行数不正确。</exception>
public virtual async Task <int> ExecuteAsync(string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
if (!AsyncEnabled)
{
return(Execute(sql, parameters, commandType, timeout));
}
ArgAssert.NotNullOrEmpty(sql, nameof(sql));
DbConnection connection = null;
DbCommand cmd = null;
try
{
connection = await CreateAndOpenConnectionAsync();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
return(await cmd.ExecuteNonQueryAsync());
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
cmd?.Parameters.Clear();
if (connection != null)
{
CloseConnection(connection);
}
}
}
/// <summary>
/// 返回查询语句对应查询结果的<see cref="System.Data.DataSet"/>。
/// </summary>
/// <param name="sql">查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0为不指定。</param>
/// <returns>表示查询结果的<see cref="System.Data.DataSet"/>。</returns>
/// <exception cref="ArgumentNullException">当<paramref name="sql"/>为<c>null</c>。</exception>
/// <exception cref="ArgumentException">当<paramref name="sql"/>长度为0。</exception>
public DataSet DataSet(string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
ArgAssert.NotNullOrEmpty(sql, "sql");
var ds = new DataSet();
FillDataSet(ds, sql, parameters, commandType, timeout);
return(ds);
}
/// <summary>
/// 获取查询结果得行序列。
/// </summary>
/// <param name="sql">查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0为不指定。</param>
/// <returns>查询结果得行序列。</returns>
public IEnumerable <IDataRecord> Rows(string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
ArgAssert.NotNullOrEmpty(sql, "sql");
DbConnection connection = null;
DbDataReader reader = null;
DbCommand cmd;
try
{
connection = CreateAndOpenConnection();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
reader = cmd.ExecuteReader();
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
//获取reader失败时，关闭连接
if (reader == null && connection != null)
{
CloseConnection(connection);
}
}
try
{
cmd.Parameters.Clear();
while (reader.Read())
{
yield return(reader);
}
}
finally
{
if (!reader.IsClosed)
{
reader.Close();
}
if (connection != null)
{
CloseConnection(connection);
}
}
}
/// <summary>
/// 使用<see cref="IMapper{T}"/>查询指定对象的集合。
/// </summary>
/// <typeparam name="T">查询的目标类型。</typeparam>
/// <param name="mapper"><see cref="IMapper{T}"/>的实例。</param>
/// <param name="sql">查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0为不指定。</param>
/// <returns>目标类型的实例的集合。若查询命中的行数为0，返回空集合。</returns>
public IList <T> List <T>(IMapper <T> mapper,
string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
ArgAssert.NotNullOrEmpty(sql, "sql");
ArgAssert.NotNull(mapper, "mapper");
var results = new List <T>();
DbConnection connection = null;
DbCommand cmd = null;
IDataReader reader = null;
try
{
connection = CreateAndOpenConnection();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
reader = cmd.ExecuteReader();
var rowCount = 0;
while (reader.Read())
{
var row = mapper.MapRow(reader, ++rowCount);
results.Add(row);
}
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
}
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
if (connection != null)
{
CloseConnection(connection);
}
}
return(results);
}
/// <summary>
/// 使用<see cref="IMapper{T}"/>查询指定对象。
/// SQL命中的记录必须为1行，否则抛出异常。
/// </summary>
/// <typeparam name="T">查询的目标类型。</typeparam>
/// <param name="mapper"><see cref="IMapper{T}"/>的实例。</param>
/// <param name="sql">查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0表示不指定，此时套用默认的超时设置。</param>
/// <returns>目标类型的实例。</returns>
/// <exception cref="IncorrectResultSizeException">当SQL命中的记录行数不为 1。</exception>
public virtual T ForceGet <T>(IMapper <T> mapper,
string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
ArgAssert.NotNullOrEmpty(sql, nameof(sql));
ArgAssert.NotNull(mapper, nameof(mapper));
DbConnection connection = null;
IDataReader reader = null;
DbCommand cmd = null;
try
{
connection = CreateAndOpenConnection();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
reader = cmd.ExecuteReader();
int rowCount;
T result;
if (DbClientHelper.TryMapUniqueRow(reader, mapper, out result, out rowCount))
{
return(result);
}
throw new IncorrectResultSizeException(sql, CommandType.Text, parameters, 1, rowCount);
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
cmd?.Parameters.Clear();
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
if (connection != null)
{
CloseConnection(connection);
}
}
}
/// <summary>
/// 使用<see cref="IMapper{T}"/>查询指定对象的集合。
/// 若查询未命中纪录，返回空集（长度为0，不是null）。
/// 这是一个异步操作。
/// </summary>
/// <typeparam name="T">查询的目标类型。</typeparam>
/// <param name="mapper"><see cref="IMapper{T}"/>的实例。</param>
/// <param name="sql">查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0表示不指定，此时套用默认的超时设置。</param>
/// <returns>目标类型的实例的集合。若查询命中的行数为0，返回空集合。</returns>
public virtual async Task <IList <T> > ListAsync <T>(IMapper <T> mapper,
string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
if (!AsyncEnabled)
{
return(List(mapper, sql, parameters, commandType, timeout));
}
ArgAssert.NotNullOrEmpty(sql, nameof(sql));
ArgAssert.NotNull(mapper, nameof(mapper));
DbConnection connection = null;
DbCommand cmd = null;
IDataReader reader = null;
try
{
connection = await CreateAndOpenConnectionAsync();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
reader = await cmd.ExecuteReaderAsync();
return(MapRowsToList(reader, mapper));
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
cmd?.Parameters.Clear();
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
if (connection != null)
{
CloseConnection(connection);
}
}
}
/// <summary>
/// 返回查询语句对应查询结果的<see cref="System.Data.DataTable"/>。
/// 这是一个异步操作。
/// </summary>
/// <param name="sql">查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0表示不指定，此时套用默认的超时设置。</param>
/// <returns>表示查询结果的<see cref="System.Data.DataTable"/>。</returns>
/// <exception cref="ArgumentNullException">当<paramref name="sql"/>为<c>null</c>。</exception>
/// <exception cref="ArgumentException">当<paramref name="sql"/>长度为0。</exception>
public virtual async Task <DataTable> DataTableAsync(string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
if (!AsyncEnabled)
{
return(DataTable(sql, parameters, commandType, timeout));
}
ArgAssert.NotNullOrEmpty(sql, nameof(sql));
DbConnection connection = null;
DbCommand cmd = null;
try
{
connection = await CreateAndOpenConnectionAsync();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
// TODO 目前还没有找到适当的异步填充 DataTable 的方法，填充部分目前以非异步方式执行。
// dataTable.Load(reader) 在 mysql 的部分类型字段（比如longtext）将出现异常，必须通过 mysql
// 的 connector library 提供的 MySqlDataAdapter 填充，解决方案尚不清晰。
return(FillDataTable(cmd));
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
cmd?.Parameters.Clear();
if (connection != null)
{
CloseConnection(connection);
}
}
}
/// <summary>
/// 返回查询语句对应查询结果的<see cref="System.Data.DataSet"/>。
/// 这是一个异步操作。
/// </summary>
/// <param name="sql">查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0表示不指定，此时套用默认的超时设置。</param>
/// <returns>表示查询结果的<see cref="System.Data.DataSet"/>。</returns>
/// <exception cref="ArgumentNullException">当<paramref name="sql"/>为<c>null</c>。</exception>
/// <exception cref="ArgumentException">当<paramref name="sql"/>长度为0。</exception>
public virtual async Task <DataSet> DataSetAsync(string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
if (!AsyncEnabled)
{
return(DataSet(sql, parameters, commandType, timeout));
}
ArgAssert.NotNullOrEmpty(sql, nameof(sql));
DbConnection connection = null;
DbCommand cmd = null;
try
{
connection = await CreateAndOpenConnectionAsync();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
// TODO 目前还没有找到适当的异步填充 DataSet 的方法，填充部分目前以非异步方式执行。
// 主要因为 DbDataAdaper 没有提供异步的方法，而如何从 IDataReader 获得 DataSet还没搞清楚。
return(FillDataSet(cmd));
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
cmd?.Parameters.Clear();
if (connection != null)
{
CloseConnection(connection);
}
}
}
/// <summary>
/// 使用<see cref="IMapper{T}"/>查询指定对象。
/// 若满足条件的记录不存在，返回目标类型的默认值（对于引用类型为<c>null</c>）。
/// </summary>
/// <typeparam name="T">查询的目标类型。</typeparam>
/// <param name="sql">查询SQL。</param>
/// <param name="parameters">参数序列。空序列或null表示没有参数。</param>
/// <param name="commandType">命令的类型。</param>
/// <param name="timeout">命令的超时时间，单位毫秒。0表示不指定，此时套用默认的超时设置。</param>
/// <param name="mapper"><see cref="IMapper{T}"/>的实例。</param>
/// <returns>目标类型的实例。</returns>
public virtual T Get <T>(IMapper <T> mapper, string sql, IEnumerable <DbParameter> parameters = null,
CommandType commandType = CommandType.Text, int timeout = 0)
{
ArgAssert.NotNullOrEmpty(sql, nameof(sql));
ArgAssert.NotNull(mapper, nameof(mapper));
DbConnection connection = null;
IDataReader reader = null;
DbCommand cmd = null;
try
{
connection = CreateAndOpenConnection();
cmd = CreateCommand(sql, connection, parameters, commandType, timeout);
reader = cmd.ExecuteReader();
return(reader.Read() ? mapper.MapRow(reader, 1) : default(T));
}
catch (Exception ex)
{
throw new SqlExecutingException(sql, commandType, parameters, ex);
}
finally
{
cmd?.Parameters.Clear();
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
if (connection != null)
{
CloseConnection(connection);
}
}
}
