// <Snippet1>
public void SearchOracleParams()
{
// ...
// create OracleParameterCollection parameters
// ...
if (!parameters.Contains("DName"))
{
Console.WriteLine("ERROR: no such parameter in the collection");
}
else
{
Console.WriteLine("Name: " + parameters["DName"].ToString() +
"Index: " + parameters.IndexOf("DName").ToString());
}
}
/// <summary>
/// Returns a copy of the given DbParameter that was added to the given collection.
/// </summary>
/// <param name="dbParameters">A DbParameter collection to add the parameter clone to</param>
/// <param name="dbParam">A DbParameter to clone</param>
/// <returns>The DbParameter clone</returns>
public override DbParameter CopyParameterToCollection(DbParameterCollection dbParameters
, DbParameter dbParam)
{
OracleParameterCollection oracleParameters = (OracleParameterCollection)dbParameters;
OracleParameter oracleParam = (OracleParameter)dbParam;
if (oracleParameters.Contains(oracleParam.ParameterName))
{
throw new ExceptionEvent(enumExceptionEventCodes.DbParameterExistsInCollection
, string.Format("Parameter {0} already belongs to this collection; use Set to change value."
, oracleParam.ParameterName));
}
oracleParameters.Add(CloneParameter(oracleParam));
return(oracleParameters[oracleParam.ParameterName]);
}
/// <summary>
/// Returns a back-end compliant script that can be executed in an interactive editor
/// such as Management Studio or SQLDeveloper for the given DbCommand.
/// Since the DbCommands are parameterized, the command text will only contain bind variables
/// This function will provide variable declarations and initalizations so that the results
/// can be tested.
/// </summary>
/// <param name="dbCmd">DAAB DbCommand object</param>
/// Returns a back-end compliant script that can be executed in an interactive editor
public override String GetCommandDebugScript(DbCommand dbCmd)
{
try
{
OracleCommand cmd = (OracleCommand)((OracleCommand)dbCmd).Clone();
StringBuilder sb = new StringBuilder();
StringBuilder sbParamList = new StringBuilder();
OracleParameterCollection cmdParams = cmd.Parameters;
// since a command contains bind variables, we cannot execute those in a command window (like Toad)
// we must replace the bindVariable indicator : with some other prefix to distinguish it from a column
// for example: :Param ==> bv_Param
// As a result we must make sure that the param name length is not violated
// we do that by keeping the leftmost portion of the param name
Int32 newCount = cmdParams.Count;
SortedDictionary <string, string> bindVariables = new SortedDictionary <string
, string>(StringComparer.CurrentCultureIgnoreCase);
foreach (OracleParameter param in cmdParams)
{
string bindName = "bv_" + param.ParameterName;
if ((cmdParams.Contains(bindName) || bindName.Length > Constants.ParamNameMaxLength) ||
bindVariables.ContainsKey(bindName))
{
while ((cmdParams.Contains(bindName) || bindName.Length > Constants.ParamNameMaxLength) ||
bindVariables.ContainsKey(bindName))
{
string bindPrefix = "bv_" + (newCount++).ToString();
bindName = bindPrefix + bindName.Substring(bindPrefix.Length, Constants.ParamNameMaxLength
- (bindPrefix.Length));
}
}
if (!bindVariables.ContainsKey(bindName))
{
bindVariables.Add(bindName, param.ParameterName);
}
}
foreach (string bindName in bindVariables.Keys)
{
OracleParameter param = cmdParams[bindVariables[bindName]];
sb.AppendFormat("{0}{1} {2} := {3};{4}"
, sb.Length == 0 ? "declare " : ""
, bindName
, GetParamTypeDecl(param)
, GetParamValue(param)
, Environment.NewLine);
if (cmd.CommandType == CommandType.StoredProcedure)
{
sbParamList.AppendFormat("{0}{1}"
, bindName
, sbParamList.Length > 0 ? ", " : "");
}
cmd.CommandText = cmd.CommandText.ToLower().Replace(
BuildBindVariableName(param.ParameterName.ToLower()), bindName.ToLower());
}
cmd.CommandText = cmd.CommandText.Replace(";", string.Format(";{0}", Environment.NewLine));
cmd.CommandText = cmd.CommandText.ToLower().Replace("begin ", string.Format("begin {0}"
, Environment.NewLine));
if (cmd.CommandType == CommandType.StoredProcedure)
{
sb.AppendFormat("begin{2}{0} ({1});{2}end;{2}"
, cmd.CommandText
, sbParamList.ToString()
, Environment.NewLine);
}
else
{
sb.AppendFormat("begin{1}{0} {1}end;{1}", cmd.CommandText, Environment.NewLine);
}
return(sb.ToString());
}
catch (Exception e)
{
return(string.Format("-- Error while trying to convert command text to debug string; return commandText {0}"
+ "--Error: {1}{0}{2}{0}"
, Environment.NewLine
, e.Message
, dbCmd.CommandText));
}
}
