/// <summary>
/// 지정된 Command를 실행하여, DataTable을 빌드합니다.
/// </summary>
/// <param name="cmd">실행할 Select용 Command</param>
/// <param name="firstResult">첫번째 레코드의 인덱스 (0부터 시작)</param>
/// <param name="maxResults">최대 레코드 수 (0 이면 무시하고, 마지막 레코드까지 가져온다</param>
/// <param name="parameters">DbCommand의 Parameter 정보</param>
/// <returns>instance of <see cref="DataTable"/></returns>
public virtual DataTable ExecuteDataTable(DbCommand cmd, int firstResult, int maxResults,
params IAdoParameter[] parameters)
{
cmd.ShouldNotBeNull("cmd");
if (IsDebugEnabled)
{
log.Debug("Command를 수행합니다. CommandText=[{0}], firstResult=[{1}], maxResults=[{2}], parameters=[{3}]",
cmd.CommandText, firstResult, maxResults, parameters.CollectionToString());
}
var loadAllRecord = (firstResult <= 0 && maxResults <= 0);
var dataTable = new DataTable(AdoTool.DefaultTableName)
{
Locale = CultureInfo.InvariantCulture
};
using (var reader = ExecuteReader(cmd, parameters)) {
// 모든 레코드
if (loadAllRecord)
{
dataTable.Load(reader);
}
else
{
using (var adoAdapter = new AdoDataAdapter(this.GetDataAdapter()))
adoAdapter.Fill(new[] { dataTable }, reader, firstResult, maxResults);
}
}
return(dataTable);
}
public void Load_MultipleResultSet()
{
var tables = new List <DataTable>();
var sqlString = string.Concat(SQL_ORDER_SELECT, ";", SQL_ORDER_DETAIL_SELECT);
using (var adapter = new AdoDataAdapter(NorthwindAdoRepository.GetDataAdapter()))
using (var reader = NorthwindAdoRepository.ExecuteReaderBySqlString(sqlString)) {
do
{
var table = new DataTable();
adapter.Fill(new[] { table }, reader, 0, 0);
tables.Add(table);
} while(reader.IsClosed == false && reader.NextResult());
}
Assert.AreEqual(2, tables.Count);
tables.All(table => table.HasErrors == false);
tables.RunEach(table => {
Assert.Greater(table.Rows.Count, 0);
if (IsDebugEnabled)
{
log.Debug(table.CreateDataReader().ToString(true));
}
});
}
public void Load_CustomerOrderHistory() {
using(var adapter = new AdoDataAdapter(NorthwindAdoRepository.GetDataAdapter()))
using(var reader = CustomerOrderDetailDataReader()) {
Assert.IsNotNull(reader);
Assert.Greater(reader.FieldCount, 0);
var dataTable = new DataTable();
adapter.Fill(new[] { dataTable }, reader, 5, 10);
Assert.IsFalse(dataTable.HasErrors);
Assert.AreEqual(10, dataTable.Rows.Count);
}
}
public void Load_Nullable() {
var spName = NorthwindAdoRepository.QueryProvider.GetQuery("TenMostExpensiveProduct");
using(var adapter = new AdoDataAdapter(NorthwindAdoRepository.GetDataAdapter()))
using(var reader = NorthwindAdoRepository.ExecuteReaderByProcedure(spName)) {
Assert.IsNotNull(reader);
var dataTable = new DataTable();
adapter.Fill(new[] { dataTable }, reader, 2, 2);
Assert.IsFalse(dataTable.HasErrors);
Assert.AreEqual(2, dataTable.Rows.Count);
}
}
public void Load_CustomerOrderHistory()
{
using (var adapter = new AdoDataAdapter(NorthwindAdoRepository.GetDataAdapter()))
using (var reader = CustomerOrderDetailDataReader()) {
Assert.IsNotNull(reader);
Assert.Greater(reader.FieldCount, 0);
var dataTable = new DataTable();
adapter.Fill(new[] { dataTable }, reader, 5, 10);
Assert.IsFalse(dataTable.HasErrors);
Assert.AreEqual(10, dataTable.Rows.Count);
}
}
/// <summary>
/// 여러 ResultSet을 반환할 수 있으므로, DataTable의 컬렉션으로 반환합니다.
/// </summary>
/// <param name="cmd"></param>
/// <param name="firstResult"></param>
/// <param name="maxResults"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public virtual IList <DataTable> ExecuteDataTableAsList(DbCommand cmd, int firstResult, int maxResults,
params IAdoParameter[] parameters)
{
cmd.ShouldNotBeNull("cmd");
if (IsDebugEnabled)
{
log.Debug("DbCommand를 수행하여 DataTable 컬렉션으로 반환합니다... " +
"CommandText=[{0}], firstResult=[{1}], maxResults=[{2}], parameters=[{3}]",
cmd.CommandText, firstResult, maxResults, parameters.CollectionToString());
}
var tables = new List <DataTable>();
var adoAdapter = new AdoDataAdapter(this.GetDataAdapter());
if (firstResult <= 0)
{
firstResult = 0;
}
if (maxResults <= 0)
{
maxResults = int.MaxValue;
}
using (var reader = ExecuteReader(cmd, parameters)) {
var tableCount = 0;
do
{
var tableName = AdoTool.DefaultTableName + ++tableCount;
var dataTable = new DataTable(tableName)
{
Locale = CultureInfo.InvariantCulture
};
if (IsDebugEnabled)
{
log.Debug("DataReader로부터 정보를 읽어 DataTable[{0}]을 빌드합니다...", tableName);
}
adoAdapter.Fill(new DataTable[] { dataTable }, reader, firstResult, maxResults);
tables.Add(dataTable);
} while(reader.IsClosed == false && reader.NextResult());
}
return(tables);
}
public void Load_Nullable()
{
var spName = NorthwindAdoRepository.QueryProvider.GetQuery("TenMostExpensiveProduct");
using (var adapter = new AdoDataAdapter(NorthwindAdoRepository.GetDataAdapter()))
using (var reader = NorthwindAdoRepository.ExecuteReaderByProcedure(spName)) {
Assert.IsNotNull(reader);
var dataTable = new DataTable();
adapter.Fill(new[] { dataTable }, reader, 2, 2);
Assert.IsFalse(dataTable.HasErrors);
Assert.AreEqual(2, dataTable.Rows.Count);
}
}
/// <summary>
/// <paramref name="reader"/>의 내용을 읽어, <see cref="DataTable"/> 로 빌드합니다. <b>다 읽은 DataReader는 닫아버립니다.</b>
/// </summary>
/// <param name="db">DAAB Database instance</param>
/// <param name="reader">읽을 IDataReader</param>
/// <param name="dataTableFactory">DataTable 생성용 델리게이트</param>
/// <param name="firstResult">첫번째 인덱스 (0부터 시작)</param>
/// <param name="maxResults">읽을 최대 레코드 수</param>
/// <returns>DataReader의 내용으로 채워진 DataTable</returns>
/// <seealso cref="AdoDataAdapter"/>
public static DataTable BuildDataTableFromDataReader(this Database db, IDataReader reader,
Func <DataTable> dataTableFactory = null, int firstResult = 0,
int maxResults = 0)
{
if (IsDebugEnabled)
{
log.Debug("AdoDataAdapter를 이용하여, IDataReader 내용을 읽어, DataTable로 빌드합니다");
}
if (dataTableFactory == null)
{
dataTableFactory = () => new DataTable {
Locale = CultureInfo.InvariantCulture
}
}
;
var dataTable = dataTableFactory();
if (reader != null)
{
var adapter = new AdoDataAdapter(db.GetDataAdapter());
try {
adapter.Fill(new[] { dataTable }, reader, firstResult, maxResults);
}
catch (Exception ex) {
if (log.IsErrorEnabled)
{
log.Error("DataReader로부터 DataTable로 Fill하는 동안 예외가 발생했습니다.");
log.Error(ex);
}
throw;
}
finally {
With.TryAction(reader.Dispose);
With.TryAction(adapter.Dispose);
}
if (IsDebugEnabled)
{
log.Debug("DataReader를 읽어 DataTable에 Load 했습니다!!! firstResult=[{0}], maxResults=[{1}]", firstResult, maxResults);
}
}
return(dataTable);
}
}
/// <summary>
/// 지정된 DataAdapter를 통해 얻은 정보를 DataSet으로 빌드한다.
/// </summary>
/// <param name="adapter">DataAdapter</param>
/// <param name="tableName">Table name</param>
/// <param name="targetDataSet">저장할 Dataset</param>
/// <param name="firstResult">첫번째 레코드의 인덱스 (0부터 시작)</param>
/// <param name="maxResults">최대 레코드 수 (0 이면 무시하고, 마지막 레코드까지 가져온다</param>
public virtual void LoadDataSet(DbDataAdapter adapter, string tableName, DataSet targetDataSet,
int firstResult = 0, int maxResults = 0)
{
adapter.ShouldNotBeNull("adapter");
targetDataSet.ShouldNotBeNull("targetDataSet");
Guard.Assert(adapter.SelectCommand != null, "adapter.SelectCommand is null.");
if (IsDebugEnabled)
{
log.Debug(
"DataAdatpter를 이용하여 정보를 DataSet에 로드합니다... adapter.SelectCommand=[{0}], tableName=[{1}], firstResult=[{2}], maxResults=[{3}]",
adapter.SelectCommand.CommandText, tableName, firstResult, maxResults);
}
var resultCount = 0;
var adoAdapter = new AdoDataAdapter(adapter);
IDataReader reader = null;
try {
reader = ExecuteReaderInternal(adapter.SelectCommand);
do
{
var name = tableName.IsWhiteSpace() ? AdoTool.DefaultTableName + ++resultCount : tableName;
adoAdapter.Fill(targetDataSet, name, reader, firstResult, maxResults);
} while(!reader.IsClosed && reader.NextResult());
}
catch (Exception ex) {
if (log.IsErrorEnabled)
{
log.ErrorException("DataReader로부터 Data를 로드하는데 실패했습니다.", ex);
}
}
finally {
if (reader != null)
{
reader.Dispose();
}
//! 이거 하면 안된다!!! 호출한 놈이 해야한다.
// adoAdapter.Dispose();
}
}
public void Load_TenMostExpensiveProduct() {
var spName = NorthwindAdoRepository.QueryProvider.GetQuery("TenMostExpensiveProduct");
Assert.IsNotEmpty(spName);
if(IsDebugEnabled)
log.Debug("Execute Procedure... spName=[{0}]", spName);
using(var adapter = new AdoDataAdapter(NorthwindAdoRepository.GetDataAdapter()))
using(var reader = NorthwindAdoRepository.ExecuteReaderByProcedure(spName)) {
Assert.IsNotNull(reader);
Assert.Greater(reader.FieldCount, 0);
var dataTable = new DataTable();
adapter.Fill(new[] { dataTable }, reader, 2, 2);
Assert.IsFalse(dataTable.HasErrors);
Assert.AreEqual(2, dataTable.Rows.Count);
}
}
public void Load_MultipleResultSet() {
var tables = new List<DataTable>();
var sqlString = string.Concat(SQL_ORDER_SELECT, ";", SQL_ORDER_DETAIL_SELECT);
using(var adapter = new AdoDataAdapter(NorthwindAdoRepository.GetDataAdapter()))
using(var reader = NorthwindAdoRepository.ExecuteReaderBySqlString(sqlString)) {
do {
var table = new DataTable();
adapter.Fill(new[] { table }, reader, 0, 0);
tables.Add(table);
} while(reader.IsClosed == false && reader.NextResult());
}
Assert.AreEqual(2, tables.Count);
tables.All(table => table.HasErrors == false);
tables.RunEach(table => {
Assert.Greater(table.Rows.Count, 0);
if(IsDebugEnabled)
log.Debug(table.CreateDataReader().ToString(true));
});
}
public void Load_TenMostExpensiveProduct()
{
var spName = NorthwindAdoRepository.QueryProvider.GetQuery("TenMostExpensiveProduct");
Assert.IsNotEmpty(spName);
if (IsDebugEnabled)
{
log.Debug("Execute Procedure... spName=[{0}]", spName);
}
using (var adapter = new AdoDataAdapter(NorthwindAdoRepository.GetDataAdapter()))
using (var reader = NorthwindAdoRepository.ExecuteReaderByProcedure(spName)) {
Assert.IsNotNull(reader);
Assert.Greater(reader.FieldCount, 0);
var dataTable = new DataTable();
adapter.Fill(new[] { dataTable }, reader, 2, 2);
Assert.IsFalse(dataTable.HasErrors);
Assert.AreEqual(2, dataTable.Rows.Count);
}
}
/// <summary>
/// 여러 ResultSet을 반환할 수 있으므로, DataTable의 컬렉션으로 반환합니다.
/// </summary>
/// <param name="cmd"></param>
/// <param name="firstResult"></param>
/// <param name="maxResults"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public virtual IList<DataTable> ExecuteDataTableAsList(DbCommand cmd, int firstResult, int maxResults,
params IAdoParameter[] parameters) {
cmd.ShouldNotBeNull("cmd");
if(IsDebugEnabled)
log.Debug("DbCommand를 수행하여 DataTable 컬렉션으로 반환합니다... " +
"CommandText=[{0}], firstResult=[{1}], maxResults=[{2}], parameters=[{3}]",
cmd.CommandText, firstResult, maxResults, parameters.CollectionToString());
var tables = new List<DataTable>();
var adoAdapter = new AdoDataAdapter(this.GetDataAdapter());
if(firstResult <= 0)
firstResult = 0;
if(maxResults <= 0)
maxResults = int.MaxValue;
using(var reader = ExecuteReader(cmd, parameters)) {
var tableCount = 0;
do {
var tableName = AdoTool.DefaultTableName + ++tableCount;
var dataTable = new DataTable(tableName) { Locale = CultureInfo.InvariantCulture };
if(IsDebugEnabled)
log.Debug("DataReader로부터 정보를 읽어 DataTable[{0}]을 빌드합니다...", tableName);
adoAdapter.Fill(new DataTable[] { dataTable }, reader, firstResult, maxResults);
tables.Add(dataTable);
} while(reader.IsClosed == false && reader.NextResult());
}
return tables;
}
/// <summary>
/// 지정된 Command를 실행하여, DataTable을 빌드합니다.
/// </summary>
/// <param name="cmd">실행할 Select용 Command</param>
/// <param name="firstResult">첫번째 레코드의 인덱스 (0부터 시작)</param>
/// <param name="maxResults">최대 레코드 수 (0 이면 무시하고, 마지막 레코드까지 가져온다</param>
/// <param name="parameters">DbCommand의 Parameter 정보</param>
/// <returns>instance of <see cref="DataTable"/></returns>
public virtual DataTable ExecuteDataTable(DbCommand cmd, int firstResult, int maxResults,
params IAdoParameter[] parameters) {
cmd.ShouldNotBeNull("cmd");
if(IsDebugEnabled)
log.Debug("Command를 수행합니다. CommandText=[{0}], firstResult=[{1}], maxResults=[{2}], parameters=[{3}]",
cmd.CommandText, firstResult, maxResults, parameters.CollectionToString());
var loadAllRecord = (firstResult <= 0 && maxResults <= 0);
var dataTable = new DataTable(AdoTool.DefaultTableName) { Locale = CultureInfo.InvariantCulture };
using(var reader = ExecuteReader(cmd, parameters)) {
// 모든 레코드
if(loadAllRecord) {
dataTable.Load(reader);
}
else {
using(var adoAdapter = new AdoDataAdapter(this.GetDataAdapter()))
adoAdapter.Fill(new[] { dataTable }, reader, firstResult, maxResults);
}
}
return dataTable;
}
/// <summary>
/// 지정된 DataAdapter를 통해 얻은 정보를 DataSet으로 빌드한다.
/// </summary>
/// <param name="adapter">DataAdapter</param>
/// <param name="tableName">Table name</param>
/// <param name="targetDataSet">저장할 Dataset</param>
/// <param name="firstResult">첫번째 레코드의 인덱스 (0부터 시작)</param>
/// <param name="maxResults">최대 레코드 수 (0 이면 무시하고, 마지막 레코드까지 가져온다</param>
public virtual void LoadDataSet(DbDataAdapter adapter, string tableName, DataSet targetDataSet,
int firstResult = 0, int maxResults = 0) {
adapter.ShouldNotBeNull("adapter");
targetDataSet.ShouldNotBeNull("targetDataSet");
Guard.Assert(adapter.SelectCommand != null, "adapter.SelectCommand is null.");
if(IsDebugEnabled)
log.Debug(
"DataAdatpter를 이용하여 정보를 DataSet에 로드합니다... adapter.SelectCommand=[{0}], tableName=[{1}], firstResult=[{2}], maxResults=[{3}]",
adapter.SelectCommand.CommandText, tableName, firstResult, maxResults);
var resultCount = 0;
var adoAdapter = new AdoDataAdapter(adapter);
IDataReader reader = null;
try {
reader = ExecuteReaderInternal(adapter.SelectCommand);
do {
var name = tableName.IsWhiteSpace() ? AdoTool.DefaultTableName + ++resultCount : tableName;
adoAdapter.Fill(targetDataSet, name, reader, firstResult, maxResults);
} while(!reader.IsClosed && reader.NextResult());
}
catch(Exception ex) {
if(log.IsErrorEnabled)
log.ErrorException("DataReader로부터 Data를 로드하는데 실패했습니다.", ex);
}
finally {
if(reader != null)
reader.Dispose();
//! 이거 하면 안된다!!! 호출한 놈이 해야한다.
// adoAdapter.Dispose();
}
}