/// <summary>
/// Creates all the <see cref="ISqlParameter"/> required for the given <paramref name="filterToCreateFor"/> (based on it's WHERE Sql). Will perform rename operations
/// where there is already a conflicting <see cref="ISqlParameter"/> declared in the same scope (See <paramref name="existingParametersInScope"/>)
/// </summary>
/// <param name="filterToCreateFor"></param>
/// <param name="existingParametersInScope"></param>
public void CreateAll(IFilter filterToCreateFor, ISqlParameter[] existingParametersInScope)
{
//get what parameter exists
ISqlParameter[] sqlParameters = filterToCreateFor.GetAllParameters() ?? new ISqlParameter[0];
//all parameters in the Select SQL
HashSet <string> parametersRequiredByWhereSQL = GetRequiredParamaterNamesForQuery(filterToCreateFor.WhereSQL, _globals);
//find which current parameters are redundant and delete them
foreach (ISqlParameter parameter in sqlParameters)
{
if (!parametersRequiredByWhereSQL.Contains(parameter.ParameterName))
{
((IDeleteable)parameter).DeleteInDatabase();
}
}
//find new parameters that we don't have
foreach (string requiredParameterName in parametersRequiredByWhereSQL)
{
if (!sqlParameters.Any(p => p.ParameterName.Equals(requiredParameterName)))
{
ISqlParameter matchingTemplateFilter = null;
ISqlParameter newParameter;
//now we might be in the process of cloning another IFilter in which case we want the filters to match the templates ones
if (_importFromIfAny != null)
{
matchingTemplateFilter = _importFromIfAny.SingleOrDefault(t => t.ParameterName.Equals(requiredParameterName));
}
string proposedNewParameterName = requiredParameterName;
int proposedAliasNumber = 2;
//Figure out of there are any collisions with existing parameters
if (existingParametersInScope != null)
{
if (existingParametersInScope.Any(e => e.ParameterName.Equals(proposedNewParameterName)))//there is a conflict between the parameter you are importing and one that already exists in scope
{
while (existingParametersInScope.Any(e => e.ParameterName.Equals(proposedNewParameterName + proposedAliasNumber)))
{
proposedAliasNumber++;
}
//Naming conflict has been resolved! (by adding the proposed alias number on) so record that this is the new name
proposedNewParameterName = proposedNewParameterName + proposedAliasNumber;
}
}
//The final name is different e.g. bob2 instead of bob so propagate into the WHERE SQL of the filter
if (!proposedNewParameterName.Equals(requiredParameterName))
{
filterToCreateFor.WhereSQL = RenameParameterInSQL(filterToCreateFor.WhereSQL, requiredParameterName, proposedNewParameterName);
filterToCreateFor.SaveToDatabase();
}
//If there is a matching Template Filter
if (matchingTemplateFilter != null)
{
string toCreate = matchingTemplateFilter.ParameterSQL;
//theres a rename requirement e.g. 'DECLARE @bob AS int' to 'DECLARE @bob2 AS int' because the existing scope already has a parameter called @bob
if (proposedNewParameterName != requiredParameterName)
{
toCreate = toCreate.Replace(requiredParameterName, proposedNewParameterName);
}
//construct it as a match to the existing parameter declared at the template level (see below for full match propogation)
newParameter = _factory.CreateNewParameter(filterToCreateFor, toCreate);
}
else
{
var syntaxHelper = filterToCreateFor.GetQuerySyntaxHelper();
//its not got a template match so just create it as varchar(50)
var declaration = syntaxHelper.GetParameterDeclaration(proposedNewParameterName, new DatabaseTypeRequest(typeof(string), 50));
newParameter = _factory.CreateNewParameter(filterToCreateFor, declaration);
if (newParameter != null)
{
newParameter.Value = "'todo'";
newParameter.SaveToDatabase();
}
}
if (newParameter == null)
{
throw new NullReferenceException("Parameter construction method returned null, expected it to return an ISqlParameter");
}
//We have a template so copy across the remaining values
if (matchingTemplateFilter != null)
{
newParameter.Value = matchingTemplateFilter.Value;
newParameter.Comment = matchingTemplateFilter.Comment;
newParameter.SaveToDatabase();
}
}
}
}