private OracleCommand CreateCommand(string xml, Func <string, OracleParameter> paramUpdater, string actionName)
{
OracleCommand cmd = _conn.CreateCommand();
cmd.CommandText = xml;
XmlToSql.BuildCommand(cmd, paramUpdater, (elem) =>
{
if (_proxyTagValue == null)
{
return(false);
}
elem.Value = _proxyTagValue;
return(true);
});
if (_conn.State == ConnectionState.Closed)
{
_conn.Open();
}
cmd.BindByName = true;
cmd.InitialLONGFetchSize = 1024; // Retrieve first 1K chars from a long column
_conn.ActionName = actionName;
QueryLogging.TraceOracleCommand(_traceContext, cmd, actionName);
return(cmd);
}
/// <summary>
/// This overload executed Array DML
/// </summary>
/// <param name="xml"></param>
/// <param name="binder"></param>
/// <returns></returns>
public int ExecuteDml(string xml, SqlBinderDmlArray binder)
{
if (binder == null)
{
throw new ArgumentNullException("binder");
}
//Contract.Ensures(this.Connection != null, "The connection should be created before executing a query");
OracleCommand cmd = null;
try
{
cmd = this.CreateCommand(xml, binder.GetParameter, binder.ActionName);
//PrepareConnection(binder.ActionName);
cmd.ArrayBindCount = binder.ArrayBindCount;
var rowsAffected = cmd.ExecuteNonQuery();
binder.OnQueryExecuted(rowsAffected, cmd.Parameters);
return(rowsAffected);
}
catch (OracleException ex)
{
if (_traceContext != null)
{
this._traceContext.Warn("Exception", "", ex);
}
if (ex.Number == 1866)
{
// The datetime class in invalid. This is an Oracle internal error, wich in my opinion is an oracle bug.
// It is raised when the DML query is returning a date column and no rows are affected.
// This code hides this oracle bug
binder.OnQueryExecuted(0, cmd.Parameters);
return(0);
}
throw new OracleDataStoreException(ex, cmd);
}
finally
{
if (cmd != null)
{
foreach (var parameter in cmd.Parameters.OfType <IDisposable>())
{
parameter.Dispose();
}
cmd.Dispose();
}
QueryLogging.TraceQueryEnd(_traceContext);
}
}
/// <summary>
/// We do not use oldValues so there is no concurrency check. We merge all the DeleteParameters
/// specified in the markup. The base class apparently does not do that.
/// </summary>
/// <param name="keys"></param>
/// <param name="oldValues"></param>
/// <returns></returns>
protected override int ExecuteDelete(IDictionary keys, IDictionary oldValues)
{
int nReturn = -1;
DbCommand cmd = this.Connection.CreateCommand();
cmd.Transaction = _transaction;
CreateParametersFromDictionary(keys, cmd);
if (!MergeMarkupParameters(cmd, this.DeleteParameters))
{
return(nReturn);
}
//cmd.CommandText = this.DeleteCommand;
//SetCommandType(cmd, this.DeleteCommandType);
SqlDataSourceCommandEventArgs cmdEventArgs = new SqlDataSourceCommandEventArgs(cmd);
OnDeleting(cmdEventArgs);
try
{
if (!cmdEventArgs.Cancel)
{
SetConnection(cmd, OpenReason.Deleting);
QueryLogging.TraceOracleCommand(_context.Trace, cmd);
// Sharad 20 Sep 2012: This does not seem to be necessary any longer
//cmd.CommandText = cmd.CommandText.Replace("\r\n", " ");
nReturn = cmd.ExecuteNonQuery();
QueryLogging.TraceQueryEnd(this._context.Trace);
SqlDataSourceStatusEventArgs statusEventArgs = new SqlDataSourceStatusEventArgs(cmd, nReturn, null);
OnDeleted(statusEventArgs);
ClearEnumerable();
}
}
catch (DbException ex)
{
SqlDataSourceStatusEventArgs statusEventArgs = new SqlDataSourceStatusEventArgs(cmd, 0, ex);
OnDeleted(statusEventArgs);
if (!statusEventArgs.ExceptionHandled)
{
throw;
}
}
finally
{
cmd.Dispose();
}
return(nReturn);
}
protected override int ExecuteInsert(IDictionary values)
{
//return base.ExecuteInsert(values);
int nReturn = -1;
DbCommand cmd = this.Connection.CreateCommand();
cmd.Transaction = _transaction;
CreateParametersFromDictionary(values, cmd);
if (!MergeMarkupParameters(cmd, InsertParameters))
{
return(nReturn);
}
//cmd.CommandText = this.InsertCommand;
//SetCommandType(cmd, InsertCommandType);
SqlDataSourceCommandEventArgs cmdEventArgs = new SqlDataSourceCommandEventArgs(cmd);
OnInserting(cmdEventArgs);
try
{
if (!cmdEventArgs.Cancel)
{
SetConnection(cmd, OpenReason.Inserting);
QueryLogging.TraceOracleCommand(_context.Trace, cmd);
cmd.CommandText = cmd.CommandText.Replace("\r\n", " ").Trim();
nReturn = cmd.ExecuteNonQuery();
QueryLogging.TraceQueryEnd(this._context.Trace);
SqlDataSourceStatusEventArgs statusEventArgs = new SqlDataSourceStatusEventArgs(cmd, nReturn, null);
OnInserted(statusEventArgs);
ClearEnumerable();
}
}
catch (DbException ex)
{
SqlDataSourceStatusEventArgs statusEventArgs = new SqlDataSourceStatusEventArgs(cmd, 0, ex);
OnInserted(statusEventArgs);
if (!statusEventArgs.ExceptionHandled)
{
throw;
}
}
finally
{
cmd.Dispose();
}
return(nReturn);
}
/// <summary>
/// Executes the passed query using the passed parameters
/// </summary>
/// <param name="xml">The query to execute</param>
/// <param name="binder">Parameter information for the query. Null if there are no parameters.</param>
/// <remarks>
/// </remarks>
public int ExecuteDml(string xml, SqlBinder binder)
{
OracleCommand cmd = null;
try
{
// Sharad 3 Jun 2011: Handling the case when binder is null
if (binder == null)
{
cmd = this.CreateCommand(xml, null, null);
//PrepareConnection(null);
}
else
{
cmd = this.CreateCommand(xml, binder.GetParameter, binder.ActionName);
//PrepareConnection(binder.ActionName);
}
var rowsAffected = cmd.ExecuteNonQuery();
if (binder != null)
{
binder.OnQueryExecuted(rowsAffected, cmd.Parameters);
}
return(rowsAffected);
}
catch (OracleException ex)
{
if (_traceContext != null)
{
this._traceContext.Warn("Exception", "", ex);
}
throw new OracleDataStoreException(ex, cmd);
}
finally
{
if (cmd != null)
{
foreach (var parameter in cmd.Parameters.OfType <IDisposable>())
{
parameter.Dispose();
}
cmd.Dispose();
}
QueryLogging.TraceQueryEnd(_traceContext);
}
}
/// <summary>
/// Executes the query and returns the first row as a strongly typed object.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="xmlQuery"></param>
/// <param name="binder"></param>
/// <returns>The first row as a strongly typed object, or null if no row was found</returns>
/// <remarks>
/// <para>
/// See <see cref="OracleDatastore"/> for a code example.
/// </para>
/// </remarks>
public T ExecuteSingle <T>(string xmlQuery, SqlBinder <T> binder)
{
Contract.Assert(binder != null);
OracleCommand cmd = null;
OracleDataReader reader = null;
try
{
cmd = CreateCommand(xmlQuery, binder.GetParameter, binder.ActionName);
//PrepareConnection(binder.ActionName);
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
var result = binder.MapRows(reader).FirstOrDefault();
return(result);
}
catch (OracleException ex)
{
if (_traceContext != null)
{
this._traceContext.Warn("Exception", "", ex);
}
throw new OracleDataStoreException(ex, cmd);
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (cmd != null)
{
foreach (OracleParameter parameter in cmd.Parameters)
{
parameter.Dispose();
}
cmd.Dispose();
}
QueryLogging.TraceQueryEnd(_traceContext);
}
}
public void SetDatabaseContext(DbCommand cmd, HttpContext context, Control ctl)
{
if (!_parametersChanged)
{
// 1) Do nothing if the parameters have not changed because we have already set the context.
// 2) If Enable property is false then too do nothing
return;
}
StringBuilder sb = new StringBuilder();
sb.Append("BEGIN ");
bool bCode = PrepareApplicationContext(cmd, sb);
if (this.ContextParameters != null && this._contextParameters.Count > 0 && this.EnableSysContext)
{
if (string.IsNullOrEmpty(this.ContextPackageName))
{
throw new HttpException("If ContextParameters are supplied, CustomContextPackage must be specified as well");
}
//OracleParameter param;
if (_uniqueId == Guid.Empty)
{
_uniqueId = Guid.NewGuid();
sb.Append("DBMS_SESSION.SET_IDENTIFIER(client_id => :client_id); ");
bCode = true;
}
DbParameter param = cmd.CreateParameter();
param.ParameterName = "client_id";
param.Value = _uniqueId.ToString();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
IOrderedDictionary values = this.ContextParameters.GetValues(context, ctl);
foreach (Parameter parameter in this.ContextParameters)
{
// ConvertEmptyStringToNull true implies that null value will be set. false implies that null value will
// not be set.
string strValue = string.Format("{0}", values[parameter.Name]);
if (!string.IsNullOrEmpty(strValue) || parameter.ConvertEmptyStringToNull)
{
sb.Append(this.ContextPackageName);
sb.Append(".");
sb.AppendFormat(this.ProcedureFormatString, parameter.Name);
sb.Append("; ");
param = cmd.CreateParameter();
param.ParameterName = parameter.Name;
param.Value = string.IsNullOrEmpty(strValue) ? DBNull.Value : (object)strValue;
param.DbType = parameter.GetDatabaseType();
cmd.Parameters.Add(param);
bCode = true;
}
}
_parametersChanged = false;
}
if (bCode)
{
sb.Append("END;");
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
QueryLogging.TraceOracleCommand(context.Trace, cmd);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Executes the query using the information in the passed <paramref name="binder"/>. When no rows are found, an empty list is returned.
/// </summary>
/// <typeparam name="T">The type of each row in the list of results</typeparam>
/// <param name="xmlQuery"></param>
/// <param name="binder">Information which describes the query to execute</param>
/// <param name="maxRows">Maximum number of rows to retrieve. 0 means all.</param>
/// <returns>A list of rows returned by the query</returns>
/// <exception cref="ArgumentNullException"><paramref name="binder"/> is null</exception>
/// <exception cref="OperationCanceledException">Number of rows retturned by query exceeded <see cref="DefaultMaxRows"/></exception>
/// <exception cref="OracleException">Query execution error</exception>
/// <remarks>
/// <para>
/// The connection is opened if it is not already open.
/// </para>
/// <para>
/// Sharad 17 Nov 2011: Added new parameter <paramref name="maxRows"/>
/// </para>
/// </remarks>
public IList <T> ExecuteReader <T>(string xmlQuery, SqlBinder <T> binder, int maxRows)
{
if (binder == null)
{
throw new ArgumentNullException("binder");
}
OracleCommand cmd = null;
OracleDataReader reader = null;
try
{
cmd = CreateCommand(xmlQuery, binder.GetParameter, binder.ActionName);
//PrepareConnection(binder.ActionName);
reader = cmd.ExecuteReader(maxRows == 1 ? CommandBehavior.SingleRow : CommandBehavior.Default);
if (reader.RowSize > 0 && maxRows > 0)
{
// Safety check. FetchSize optimization is applied only if RowSize is known and maxRows is specified
// Treat default Fetch Size specified in configuration as the maximum allowed.
// See whether we can reduce the fetch size to save memory
reader.FetchSize = Math.Min(reader.RowSize * maxRows, reader.FetchSize);
}
// Lazy loading will not work because the map context will not be available for mapping
//var rowcount = 0;
//foreach (var result in from object row in reader select binder.Mapper.Engine.Map<IOracleDataRow, T>(dict))
var results = binder.MapRows(reader).Take(maxRows > 0 ? maxRows : this.DefaultMaxRows).ToList();
if (maxRows == 0 && reader.Read())
{
// User did not specify maxRows and we did not read all of them. Generate error.
var msg = string.Format("Query aborted because more than {0} rows were retrieved",
this.DefaultMaxRows);
if (_traceContext != null)
{
_traceContext.Warn(msg);
}
throw new OperationCanceledException(msg);
//}
}
if (_traceContext != null)
{
_traceContext.Write(string.Format("ExecuteReader returned {0} rows", results.Count));
}
var b = reader.NextResult();
return(results);
}
catch (OracleException ex)
{
if (_traceContext != null)
{
this._traceContext.Warn("Exception", "", ex);
}
if (ex.Number == 28150)
{
var userId = _builder.UserID;
var proxyUser = _builder.ProxyUserId;
var msg = string.Format("User '{0}' was not authenticated to connect as proxy user", userId) + "\n\n Excute following script to get rid of this error: \n\n" +
string.Format("ALTER USER {0} GRANT CONNECT THROUGH {1};", userId, proxyUser);
throw new AuthenticationException(msg);
}
throw new OracleDataStoreException(ex, cmd);
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (cmd != null)
{
foreach (OracleParameter parameter in cmd.Parameters)
{
parameter.Dispose();
}
cmd.Dispose();
}
QueryLogging.TraceQueryEnd(_traceContext);
}
}
protected override int ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues)
{
if (this.ConflictDetection == ConflictOptions.CompareAllValues)
{
// Do not update if old values are same as new values
// ReSharper disable PossibleNullReferenceException
bool valuesChanged = values.Keys.Cast <object>().Any(key => !values[key].Equals(oldValues[key]));
// ReSharper restore PossibleNullReferenceException
if (!valuesChanged)
{
Trace.TraceWarning("OracleDataSource {0}: Updating cancelled because all old and new values are same", _owner.ID);
return(-1);
}
}
//return base.ExecuteUpdate(keys, values, oldValues);
int nReturn = -1;
DbCommand cmd = this.Connection.CreateCommand();
CreateParametersFromDictionary(keys, cmd);
CreateParametersFromDictionary(values, cmd);
if (!MergeMarkupParameters(cmd, UpdateParameters))
{
return(nReturn);
}
// Command text will be set by OnUpdating
//cmd.CommandText = this.UpdateCommand;
//SetCommandType(cmd, UpdateCommandType);
SqlDataSourceCommandEventArgs cmdEventArgs = new SqlDataSourceCommandEventArgs(cmd);
OnUpdating(cmdEventArgs);
try
{
if (!cmdEventArgs.Cancel)
{
SetConnection(cmd, OpenReason.Updating);
QueryLogging.TraceOracleCommand(_context.Trace, cmd);
cmd.CommandText = cmd.CommandText.Replace("\r\n", " ");
cmd.Transaction = _transaction;
nReturn = cmd.ExecuteNonQuery();
QueryLogging.TraceQueryEnd(this._context.Trace);
ExtractStatusMessages(cmd);
SqlDataSourceStatusEventArgs statusEventArgs = new SqlDataSourceStatusEventArgs(cmd, nReturn, null);
OnUpdated(statusEventArgs);
// After updating, the saved results should not be used
ClearEnumerable();
}
}
catch (DbException ex)
{
SqlDataSourceStatusEventArgs statusEventArgs = new SqlDataSourceStatusEventArgs(cmd, 0, ex);
OnUpdated(statusEventArgs);
if (!statusEventArgs.ExceptionHandled)
{
throw; // new OracleExceptionEx(cmd.CommandText, ex);
}
}
finally
{
cmd.Dispose();
}
return(nReturn);
}
private IEnumerable DoExecuteSelect(DataSourceSelectArguments arguments)
{
DbCommand cmd = this.Connection.CreateCommand(); // new OracleCommand();
//int nReturn = -1;
if (!MergeMarkupParameters(cmd, SelectParameters))
{
Trace.TraceWarning("Datasource {0} is cancelling the query because MergeMarkupParameters returned false",
_owner.ID);
return(null);
}
SqlDataSourceSelectingEventArgs cmdEventArgs = new SqlDataSourceSelectingEventArgs(cmd, arguments);
OnSelecting(cmdEventArgs);
if (cmdEventArgs.Cancel)
{
//Trace.TraceWarning("Datasource {0} is cancelling the query as requested by Selecting event handler",
// m_owner.ID);
return(null);
}
if (arguments.MaximumRows > 0)
{
if (_owner.DataSourceMode == SqlDataSourceMode.DataSet)
{
throw new NotSupportedException("Paging not supported when DataSourceMode=DataSet");
}
cmd.CommandText = ConstructPagingClause(arguments, cmd.CommandText);
}
else if (arguments.RetrieveTotalRowCount)
{
throw new NotSupportedException("Row count needed but no paging ? Let Sharad know if this happens.");
}
else if (!string.IsNullOrEmpty(arguments.SortExpression))
{
cmd.CommandText = ConstructSortingClause(arguments, cmd.CommandText);
}
if (this.CancelSelectOnNullParameter)
{
foreach (DbParameter param in
cmd.Parameters.Cast <DbParameter>().Where(param => param.Value == DBNull.Value || param.Value == null))
{
// Do not execute the query
Trace.TraceWarning("Datasource {0} is cancelling the query because value of {1} is null",
_owner.ID, param.ParameterName);
return(null);
}
}
QueryLogging.TraceOracleCommand(_context.Trace, cmd);
SetConnection(cmd, OpenReason.Selecting);
#if DEBUG
if (cmd.CommandText.IndexOf("--") >= 0)
{
throw new InvalidOperationException("Comments are not allowed within queries");
}
#endif
cmd.CommandText = cmd.CommandText.Replace("\r\n", " ");
SqlDataSourceStatusEventArgs statusEventArgs;
try
{
switch (_owner.DataSourceMode)
{
case SqlDataSourceMode.DataReader:
if (_owner.EnableCaching)
{
throw new NotSupportedException("When EnableCaching is true, DataSourceMode must be DataSet");
}
_enumerable = cmd.ExecuteReader();
break;
case SqlDataSourceMode.DataSet:
DataTable dt = GetDataTable(cmd);
_enumerable = dt.DefaultView;
break;
default:
throw new NotImplementedException();
}
statusEventArgs = new SqlDataSourceStatusEventArgs(cmd, -1, null);
}
catch (DbException ex)
{
statusEventArgs = new SqlDataSourceStatusEventArgs(cmd, 0, ex);
}
QueryLogging.TraceQueryEnd(this._context.Trace);
OnSelected(statusEventArgs);
if (statusEventArgs.Exception == null)
{
if (arguments.RetrieveTotalRowCount)
{
// We should never get here for DataSourceMode=DataSet so _reader is guaranteed to be non null
// Get the total count from the first row of the reader
DbDataReader reader = (DbDataReader)_enumerable;
if (reader.HasRows)
{
foreach (DbDataRecord record in reader)
{
_record = record;
//m_owner.TotalRowCount =
arguments.TotalRowCount = Convert.ToInt32(record[COL_TOTAL_ROWCOUNT]);
break;
}
// Since we have gobbled up one row, use ReaderIterator which is intelligent about this
return(ReaderIterator);
}
arguments.TotalRowCount = 0;
return(_enumerable);
}
return(_enumerable);
}
if (!statusEventArgs.ExceptionHandled)
{
//OracleExceptionEx excep = new OracleExceptionEx(cmd.CommandText, statusEventArgs.Exception);
throw statusEventArgs.Exception;
}
return(null);
}
