/// <summary>
/// Executes the query.
/// </summary>
/// <typeparam name="TypeDataTable">The current data table type.</typeparam>
/// <param name="dataTable">The data table to return containing the data.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery <TypeDataTable>(ref TypeDataTable dataTable, string queryText,
CommandType commandType, string connectionString, params DbParameter[] values)
where TypeDataTable : System.Data.DataTable, new()
{
dataTable = new TypeDataTable();
// Initial connection objects.
DbCommand dbCommand = null;
OracleClient.OracleConnection orlConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (orlConnection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
orlConnection.Open();
// Create the command and assign any parameters.
dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.OracleDataType, queryText), orlConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
dataReader.Close();
}
// Close the database connection.
orlConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (orlConnection != null)
{
orlConnection.Close();
}
}
}
