/// <summary>
/// get the Sql Variable names that are embedded between the @@ tags. Return a comma-delimited list.
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public string GetSQLVariableNames(string str)
{
bool bFoundEnd = false;
if (str.IndexOf("@@", 0) < 0)
{
return("");
}
if (this.m_bInit)
{
m_strNewString = "";
this.m_strVariableNames = "";
}
string strVariable;
try
{
for (int x = 0; x <= str.Trim().Length - 1; x++)
{
if (x == str.Trim().Length - 1)
{
m_strNewString = m_strNewString + str.Substring(x, 1);
break;
}
if (str.Substring(x, 2) == "@@")
{
strVariable = "";
bFoundEnd = false;
for (int y = x + 2; y <= str.Trim().Length - 1; y++)
{
if (str.Substring(y, 2) == "@@")
{
bFoundEnd = true;
//find the substitution variable in the collection
for (int z = 0; z <= ReferenceSQLMacroSubstitutionVariableCollection.Count - 1; z++)
{
if (ReferenceSQLMacroSubstitutionVariableCollection.Item(z).VariableName.ToString().Trim().ToUpper() == strVariable.Trim().ToUpper())
{
if (str.Substring(y, 2) == "@@")
{
if (ReferenceSQLMacroSubstitutionVariableCollection.Item(z).SQLVariableSubstitutionString.IndexOf("@@", 0) >= 0)
{
m_bInit = false;
this.m_strVariableNames = this.GetSQLVariableNames(ReferenceSQLMacroSubstitutionVariableCollection.Item(z).SQLVariableSubstitutionString);
m_bInit = true;
}
else
{
m_strNewString = m_strNewString + ReferenceSQLMacroSubstitutionVariableCollection.Item(z).SQLVariableSubstitutionString;
}
break;
}
}
}
x = y + 1;
break;
}
else
{
strVariable = strVariable + str.Substring(y, 1);
}
}
if (bFoundEnd == true)
{
m_strVariableNames += strVariable + ",";
}
}
else
{
m_strNewString = m_strNewString + str.Substring(x, 1);
}
}
}
catch
{
return(m_strVariableNames);
}
//remove the comma from the end of the string
//if (m_strVariableNames.Length > 0 && m_strVariableNames.Substring(m_strVariableNames.Length-1,1)==",") m_strVariableNames=m_strVariableNames.Substring(0,m_strVariableNames.Length-1);
return(m_strVariableNames);
}
/// <summary>
/// Translate SQL variable substitution variables to there value string
/// </summary>
/// <param name="str"></param>
public string SQLTranslateVariableSubstitution(string str)
{
if (str.IndexOf("@@", 0) < 0)
{
return(str);
}
if (this.m_bInit)
{
m_strNewString = "";
}
string strVariable;
try
{
for (int x = 0; x <= str.Trim().Length - 1; x++)
{
if (x == str.Trim().Length - 1)
{
m_strNewString = m_strNewString + str.Substring(x, 1);
break;
}
if (str.Substring(x, 2) == "@@")
{
strVariable = "";
for (int y = x + 2; y <= str.Trim().Length - 1; y++)
{
if (str.Substring(y, 2) == "@@")
{
//find the substitution variable in the collection
for (int z = 0; z <= ReferenceSQLMacroSubstitutionVariableCollection.Count - 1; z++)
{
if (ReferenceSQLMacroSubstitutionVariableCollection.Item(z).VariableName.ToString().Trim().ToUpper() == strVariable.Trim().ToUpper())
{
if (str.Substring(y, 2) == "@@")
{
if (ReferenceSQLMacroSubstitutionVariableCollection.Item(z).SQLVariableSubstitutionString.IndexOf("@@", 0) >= 0)
{
m_bInit = false;
m_strNewString = SQLTranslateVariableSubstitution(ReferenceSQLMacroSubstitutionVariableCollection.Item(z).SQLVariableSubstitutionString);
m_bInit = true;
}
else
{
m_strNewString = m_strNewString + ReferenceSQLMacroSubstitutionVariableCollection.Item(z).SQLVariableSubstitutionString;
}
break;
}
}
}
x = y + 1;
break;
}
else
{
strVariable = strVariable + str.Substring(y, 1);
}
}
}
else
{
m_strNewString = m_strNewString + str.Substring(x, 1);
}
}
}
catch
{
return(m_strNewString);
}
return(m_strNewString);
}