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