private void UpdateDbCommandBlock(string dynamicSQL
, DbParameterCollection dbParameters)
{
if (_daMgr.DatabaseType == DataAccessMgr.EnumDbType.Oracle &&
_commandBlockReady)
{
throw new ExceptionEvent(enumExceptionEventCodes.InvalidChangeToOracleDbCommandBlock
, "HINT: Do not access the Property DbCommandBlock until after you are done "
+ "adding blocks to the DbCommand.");
}
// are there any parameters to check against that were already added to the compound command
if (dbParameters != null && dbParameters.Count > 0)
{
foreach (DbParameter newParam in dbParameters)
{
// check to see if the param is in the Compound Command List
if (_dbCommand.Parameters.Contains(newParam.ParameterName))
{
// if the collection contains this parameter
// get the param definition and see if it is the same value and can be reused
DbParameter existingParam = _dbCommand.Parameters[newParam.ParameterName];
if (CompareParamEquality(existingParam, newParam))
{
continue; // dont add it as it exists and can be reused.
}
if (_paramAliases.ContainsKey(newParam.ParameterName))
{
List <DbParameter> paramAliases = _paramAliases[newParam.ParameterName];
bool matchFound = false;
foreach (DbParameter paramAlias in paramAliases)
{
if (CompareParamEquality(paramAlias, newParam))
{
bool paramReplaced;
dynamicSQL = ReplaceParam(dynamicSQL
, BuildBindVariableName(newParam.ParameterName)
, BuildBindVariableName(paramAlias.ParameterName)
, string.Format(Constants.ParamDelimiters, Environment.NewLine)
, out paramReplaced);
if (!paramReplaced)
{
throw new ExceptionEvent(enumExceptionEventCodes.DbCommandBlockParameterReplacementFailed
, string.Format("Unable to replace param {0} with alias {1} in new db Command {2}{3}"
, BuildBindVariableName(newParam.ParameterName)
, BuildBindVariableName(paramAlias.ParameterName)
, dynamicSQL
, Environment.NewLine));
}
else
{
matchFound = true;
break;
}
}
}
if (matchFound)
{
continue;
}
}
// if it is a different value, then we need to create a new instance of the parameter
// obtain the backend specific parameter name (either for the original name or new instance)
// now we need to make sure that the instance does not belong to the original set
// because there is a finite size on a param to
int newCount = _dbCommand.Parameters.Count + dbParameters.Count;
string newParamInstance;
do
{
string copySuffix = Constants.ParamAliasSuffix + newCount.ToString();
newParamInstance = newParam.ParameterName + copySuffix;
if (newParamInstance.Length > Constants.ParamNameOracleMaxLength)
{
newParamInstance = newParam.ParameterName.Substring(0, Constants.ParamNameOracleMaxLength - copySuffix.Length)
+ copySuffix;
}
}while ((_dbCommand.Parameters.Contains(newParamInstance) ||
dbParameters.Contains(newParamInstance)) &&
++newCount > 0);
{
string origParamName = newParam.ParameterName;
bool paramReplaced;
dynamicSQL = ReplaceParam(dynamicSQL
, BuildBindVariableName(newParam.ParameterName)
, BuildBindVariableName(newParamInstance)
, string.Format(Constants.ParamDelimiters, Environment.NewLine)
, out paramReplaced);
if (paramReplaced)
{
newParam.ParameterName = newParamInstance;
}
else
{
throw new ExceptionEvent(enumExceptionEventCodes.DbCommandBlockParameterReplacementFailed
, string.Format("Unable to replace param {0} with {1} in new db Command {2}{3}"
, BuildBindVariableName(newParam.ParameterName)
, BuildBindVariableName(newParamInstance)
, dynamicSQL
, Environment.NewLine));
}
if (!_paramAliases.ContainsKey(origParamName))
{
_paramAliases.Add(origParamName, new List <DbParameter>());
}
List <DbParameter> newParamAliases = _paramAliases[origParamName];
newParamAliases.Add(newParam);
_paramAliases[origParamName] = newParamAliases;
}
}
// if the collection contains this parameter
//otherwise add the new parameter to the collection
_daMgr.CopyParameterToCollection(_dbCommand.Parameters, newParam);
}
}
UpdateCommandText(dynamicSQL + (((_daMgr.DatabaseType == DataAccessMgr.EnumDbType.Oracle ||
_daMgr.DatabaseType == DataAccessMgr.EnumDbType.Db2) &&
!Functions.IsLastCharInText(dynamicSQL, ';')) ? "; " : ""));
++_commandCount; // bump up the command count
}