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