/// <summary>
/// Command를 실행시켜, 결과 셋을 DataSet에 저장한다.<br/>
/// 여러 ResultSet을 반환하는 경우 각각의 ResultSet에 대응하는 TableName을 제공해야 합니다.
/// </summary>
/// <param name="repository">IAdoRepository 인스턴스</param>
/// <param name="cmd">실행할 Command</param>
/// <param name="targetDataSet"></param>
/// <param name="tableNames"></param>
/// <example>
/// <code>
/// // Region 전체 정보 및 갯수를 가져옵니다.
/// string query = "select * from Region; select count(*) from Region";
///
/// DataSet ds = new DataSet();
/// using (DbCommand cmd = Impl.GetSqlStringCommand(query))
/// {
/// Impl.LoadDataSet(cmd, ds, new string[] { "Region", "CountOfRegion" });
/// Assert.AreEqual(2, ds.Tables.Count);
/// Assert.AreEqual(4, ds.Tables[1].Rows[0][0]);
/// }
/// </code>
/// </example>
public static void LoadDataSet(this IAdoRepository repository, DbCommand cmd, DataSet targetDataSet, string[] tableNames) {
cmd.ShouldNotBeNull("cmd");
tableNames.ShouldNotBeEmpty("tableNames");
if(IsDebugEnabled)
log.Debug("DbCommand를 실행하여 DataSet에 로등합니다... cmd.CommandText=[{0}], tableNames=[{1}]",
cmd.CommandText, tableNames.CollectionToString());
for(int i = 0; i < tableNames.Length; i++)
tableNames[i].ShouldNotBeWhiteSpace("index=" + i);
if(repository.IsActiveTransaction)
AdoTool.EnlistCommandToActiveTransaction(repository, cmd);
var newConnectionCreated = false;
if(cmd.Connection == null)
cmd.Connection = AdoTool.CreateTransactionScopeConnection(repository.Db, ref newConnectionCreated);
using(var adapter = repository.GetDataAdapter()) {
for(var j = 0; j < tableNames.Length; j++) {
var sourceTable = (j == 0) ? AdoTool.DefaultTableName : (AdoTool.DefaultTableName + j);
adapter.TableMappings.Add(sourceTable, tableNames[j]);
}
adapter.SelectCommand = cmd;
adapter.Fill(targetDataSet);
if(newConnectionCreated)
AdoTool.ForceCloseConnection(adapter.SelectCommand);
}
}
/// <summary>
/// 지정한 Command를 현재 활성화된 Transaction에 참여시킵니다.
/// </summary>
/// <param name="repository"></param>
/// <param name="cmd"></param>
public static void EnlistCommandToActiveTransaction(this IAdoRepository repository, DbCommand cmd) {
cmd.ShouldNotBeNull("cmd");
if(repository.IsActiveTransaction) {
if(IsDebugEnabled)
log.Debug("DbCommand 객체를 현재 활성화된 Active Transaction에 참가시킵니다...");
cmd.Connection = repository.ActiveTransaction.Connection;
cmd.Transaction = repository.ActiveTransaction;
}
}
/// <summary>
/// <paramref name="cmd"/>를 실행하여, 결과를 DataSet으로 반환합니다.
/// </summary>
/// <param name="cmd">실행할 <see cref="DbCommand"/> instance.</param>
/// <param name="firstResult">첫번째 레코드의 인덱스 (0부터 시작)</param>
/// <param name="maxResults">최대 레코드 수 (0 이면 무시하고, 마지막 레코드까지 가져온다</param>
/// <param name="parameters">collectio of parameters of Command</param>
/// <returns>결과 셋이 담긴 DataSet</returns>
public override DataSet ExecuteDataSet(DbCommand cmd, int firstResult, int maxResults, params IAdoParameter[] parameters) {
cmd.ShouldNotBeNull("cmd");
var result = new DataSet();
var tables = ExecuteDataTableAsList(cmd, firstResult, maxResults, parameters);
if(tables != null)
result.Tables.AddRange(tables.ToArray());
return result;
}
/// <summary>
/// Procedure의 Parameter 정보를 로드합니다.
/// </summary>
/// <param name="discoveryCommand"></param>
protected override void DeriveParameters(DbCommand discoveryCommand) {
discoveryCommand.ShouldNotBeNull("discoveryCommand");
Devart.Data.Oracle.OracleCommandBuilder.DeriveParameters((Devart.Data.Oracle.OracleCommand)discoveryCommand);
}
/// <summary>
/// 지정된 Command의 ExecuteReader 메소드를 실행합니다.
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
protected virtual IDataReader ExecuteReaderInternal(DbCommand cmd) {
cmd.ShouldNotBeNull("cmd");
return (IsActiveTransaction)
? Db.ExecuteReader(cmd, ActiveTransaction)
: Db.ExecuteReader(cmd);
}
/// <summary>
/// Execute specified DbCommand, and return <see cref="AdoDataReader"/>
/// </summary>
/// <param name="cmd">DbCommand to execute</param>
/// <param name="parameters">Command parameters</param>
/// <returns>instance of <see cref="IDataReader"/></returns>
public IDataReader ExecuteReader(DbCommand cmd, params IAdoParameter[] parameters) {
cmd.ShouldNotBeNull("cmd");
if(IsDebugEnabled)
log.Debug("DataReader를 얻습니다... command text=[{0}], parameters=[{1}]",
cmd.CommandText, parameters.CollectionToString());
if(parameters != null)
AdoTool.SetParameterValues(Db, cmd, parameters);
return ExecuteReaderInternal(cmd);
}
/// <summary>
/// 지정된 Command의 ExecuteNonQuery 메소드를 실행합니다.
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
protected virtual int ExecuteNonQueryInternal(DbCommand cmd) {
cmd.ShouldNotBeNull("cmd");
return (IsActiveTransaction)
? Db.ExecuteNonQuery(cmd, ActiveTransaction)
: Db.ExecuteNonQuery(cmd);
}
/// <summary>
/// Execute specified DbCommand
/// </summary>
/// <param name="cmd">Instance of DbCommand to execute</param>
/// <param name="parameters">Parameters for DbCommand to execute</param>
/// <returns>affected row count</returns>
public int ExecuteNonQuery(DbCommand cmd, params IAdoParameter[] parameters) {
cmd.ShouldNotBeNull("cmd");
if(IsDebugEnabled)
log.Debug("Command를 실행합니다... CommandText=[{0}], parameters=[{1}]",
cmd.CommandText, parameters.CollectionToString());
if(parameters.IsNotEmptySequence())
AdoTool.SetParameterValues(Db, cmd, parameters);
return ExecuteNonQueryInternal(cmd);
}
/// <summary>
/// 지정된 Command 를 Paging 정보에 근거해서 수행하고, 결과를 DataTable로 반환한다.
/// </summary>
/// <param name="cmd"></param>
/// <param name="pageIndex">Page index (0부터 시작). null이면 0으로 간주</param>
/// <param name="pageSize">Page Size. 한 페이지에 표현할 요소 수 (보통 10개). null이면 <see cref="AdoTool.DefaultPageSize"/>으로 간주</param>
/// <param name="parameters"></param>
/// <returns></returns>
public virtual PagingDataTable ExecutePagingDataTable(DbCommand cmd,
int? pageIndex,
int? pageSize,
params IAdoParameter[] parameters) {
cmd.ShouldNotBeNull("cmd");
if(IsDebugEnabled)
log.Debug(
"조회를 수행하여 PagingDataTable을 반환합니다... commandText=[{0}], pageIndex=[{1}], pageSize=[{2}], parameters=[{3}]",
cmd.CommandText, pageIndex, pageSize, parameters.CollectionToString());
var maxResults = pageSize.GetValueOrDefault(AdoTool.DefaultPageSize);
var firstResult = pageIndex.GetValueOrDefault(0) * maxResults;
// NOTE: Command에 의한 전체 레코드 수를 구합니다. 다만, 비동기 작업 시에 Command가 닫힐 우려가 있어 CommandText를 이용하여 새로운 Command를 만들게 합니다.
//
var countTask = this.CountAsync(cmd.CommandText, parameters);
var loadTable = ExecuteDataTable(cmd, firstResult, maxResults, parameters);
return new PagingDataTable(loadTable,
pageIndex ?? 0,
pageSize ?? AdoTool.DefaultPageSize,
countTask.Result);
}
/// <summary>
/// 지정된 Command의 ExecuteScalar 메소드를 실행합니다.
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
protected virtual object ExecuteScalarInternal(DbCommand cmd) {
cmd.ShouldNotBeNull("cmd");
return (IsActiveTransaction)
? Db.ExecuteScalar(cmd, ActiveTransaction)
: Db.ExecuteScalar(cmd);
}
/// <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="parameters">DbCommand의 Parameter 정보</param>
/// <returns>instance of <see cref="DataTable"/></returns>
public DataTable ExecuteDataTable(DbCommand cmd, params IAdoParameter[] parameters) {
cmd.ShouldNotBeNull("cmd");
return ExecuteDataTable(cmd, 0, 0, parameters);
}
/// <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>
/// <paramref name="cmd"/>를 실행하여, 결과를 DataSet으로 반환합니다.
/// </summary>
/// <param name="cmd">실행할 <see cref="DbCommand"/> instance.</param>
/// <param name="firstResult">첫번째 레코드의 인덱스 (0부터 시작)</param>
/// <param name="maxResults">최대 레코드 수 (0 이면 무시하고, 마지막 레코드까지 가져온다</param>
/// <param name="parameters">collectio of parameters of Command</param>
/// <returns>결과 셋이 담긴 DataSet</returns>
public virtual DataSet ExecuteDataSet(DbCommand cmd, int firstResult, int maxResults,
params IAdoParameter[] parameters) {
cmd.ShouldNotBeNull("cmd");
if(IsDebugEnabled)
log.Debug("Execute DataSet... commandText=[{0}], firstResult=[{1}], maxResults=[{2}], parameters=[{3}]",
cmd.CommandText, firstResult, maxResults, parameters.CollectionToString());
// DAAB에 보면 DataSet, DataTable 생성시, Locale을 이렇게 지정하고 있네...
//
var dataset = new DataSet { Locale = CultureInfo.InvariantCulture };
var dataTables = ExecuteDataTableAsList(cmd, firstResult, maxResults, parameters);
dataset.Tables.AddRange(dataTables.ToArray());
return dataset;
}
/// <summary>
/// Prepares the CW ref cursor.
/// </summary>
/// <param name="command">The command.</param>
/// <remarks>
/// This is a private method that will build the Oracle package name if your stored procedure
/// has proper prefix and postfix.
/// This functionality is include for
/// the portability of the architecture between SQL and Oracle datbase.
/// This method also adds the reference cursor to the command writer if not already added. This
/// is required for Oracle .NET managed data provider.
/// </remarks>
private void PrepareCWRefCursor(DbCommand command) {
command.ShouldNotBeNull("command");
if(CommandType.StoredProcedure == command.CommandType) {
// Check for ref. cursor in the command writer, if it does not exist, add a known reference cursor out
// of "cur_OUT"
if(QueryProcedureNeedsCursorParameter(command)) {
AddParameter(command as OracleCommand, RefCursorName, OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0,
0, String.Empty, DataRowVersion.Default, Convert.DBNull);
}
}
}
