private void CreateParameters(string param1Value, string param2Value)
{
container1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
acDataset.RootFilterContainer_ID = container1.ID;
acDataset.SaveToDatabase();
AggregateFilter filter1 = new AggregateFilter(CatalogueRepository, "Filter1", container1);
filter1.WhereSQL = "@bob = 'bob'";
filter1.SaveToDatabase();
var paramCreator = new ParameterCreator(filter1.GetFilterFactory(), null, null);
paramCreator.CreateAll(filter1, null);
container2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
acCohort.RootFilterContainer_ID = container2.ID;
acCohort.SaveToDatabase();
AggregateFilter filter2 = new AggregateFilter(CatalogueRepository, "Filter2", container2);
filter2.WhereSQL = "@bob = 'fish'";
filter2.SaveToDatabase();
paramCreator.CreateAll(filter2, null);
parama1 = filter1.GetAllParameters()[0];
parama1.Value = param1Value;
parama1.SaveToDatabase();
parama2 = filter2.GetAllParameters()[0];
parama2.Value = param2Value;
parama2.SaveToDatabase();
}
/// <summary>
/// Add parameter
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public SqlExec AddParameter(ISqlParameter parameter)
{
Verify.IsNotNull(nameof(parameter), parameter);
Parameters.Add(parameter);
return(this);
}
public ParameterEditorScintillaSection(ParameterRefactorer refactorer, int lineStart, int lineEnd, ISqlParameter parameter, bool editable, string originalText)
{
_refactorer = refactorer;
LineStart = lineStart;
LineEnd = lineEnd;
Parameter = parameter;
Editable = editable;
_querySyntaxHelper = parameter.GetQuerySyntaxHelper();
var prototype = ConstantParameter.Parse(originalText, _querySyntaxHelper);
if (prototype.Value != parameter.Value)
{
throw new ArgumentException("Parameter " + parameter + " was inconsistent with the SQL passed to us based on QueryBuilder.DeconstructStringIntoParameter, they had different Values");
}
if (prototype.ParameterSQL != parameter.ParameterSQL)
{
throw new ArgumentException("Parameter " + parameter + " was inconsistent with the SQL passed to us based on QueryBuilder.DeconstructStringIntoParameter, they had different ParameterSQL");
}
if (prototype.Comment != parameter.Comment)
{
throw new ArgumentException("Parameter " + parameter + " was inconsistent with the SQL passed to us based on QueryBuilder.DeconstructStringIntoParameter, they had different Comment");
}
}
void olvParameters_CellToolTipShowing(object sender, ToolTipShowingEventArgs e)
{
ISqlParameter sqlParameter = (ISqlParameter)e.Model;
e.IsBalloon = true;
e.AutoPopDelay = 32767;
e.StandardIcon = ToolTipControl.StandardIcons.Info;
//if it is a problem parameter
if (parameterEditorScintillaControl1.ProblemObjects.ContainsKey(sqlParameter))
{
var ex = parameterEditorScintillaControl1.ProblemObjects[sqlParameter];
e.StandardIcon = ToolTipControl.StandardIcons.Warning;
e.Title = "Problem Detected With Parameter";
e.Text = ex.Message;
return;
}
//if it is locked
if (Options.IsOverridden(sqlParameter))
{
e.Title = "Parameter is overridden by a higher level";
e.Text = "You have defined a higher level parameter with the same name/datatype which will override this parameters value during query generation";
return;
}
//if it is locked
if (Options.IsHigherLevel(sqlParameter))
{
e.Title = "Parameter is declared at a higher level";
e.Text = "This parameter is declared at " + Options.ParameterManager.GetLevelForParameter(sqlParameter) + " level which is higher than the level you are editing (" + Options.CurrentLevel + "). You cannot change higher level parameters from here, look at the 'Owner' column to see which object the global belongs to";
return;
}
}
/// <summary>
/// Returns the <see cref="ISqlParameter"/> which would be overwritten (ignored) because of higher level parameters during <see cref="GetFinalResolvedParametersList"/>
/// </summary>
/// <param name="existing"></param>
/// <returns>The overriding parameter or null if there are none</returns>
public ISqlParameter GetOverrideIfAnyFor(ISqlParameter existing)
{
var currentLevel = GetLevelForParameter(existing);
var overrides = GetOverridenParameters();
foreach (ParameterLevel level in Enum.GetValues(typeof(ParameterLevel)))
{
if (level > currentLevel)
{
var compatibleOverride = ParametersFoundSoFarInQueryGeneration[level].FirstOrDefault(o => AreDeclaredTheSame(existing, o));
//there are no override compatible parameters at this candidate level or the override is itself overridden at a higher level
if (compatibleOverride == null || overrides.Contains(compatibleOverride))
{
continue;
}
return(compatibleOverride);
}
}
//no overrides
return(null);
}
public bool HandleRename(ISqlParameter parameter, string oldName, string newName)
{
if (string.IsNullOrWhiteSpace(newName))
{
return(false);
}
if (string.IsNullOrWhiteSpace(oldName))
{
return(false);
}
//they are the same name!
if (oldName.Equals(newName))
{
return(false);
}
if (!parameter.ParameterName.Equals(newName))
{
throw new ArgumentException("Expected parameter " + parameter + " to have name '" + newName + "' but it's value was " + parameter.ParameterName + ", this means someone was lying about the rename event");
}
var owner = parameter.GetOwnerIfAny();
var filter = owner as IFilter;
if (filter == null || filter is SpontaneousObject)
{
return(false);
}
//There is no WHERE SQL anyway
if (string.IsNullOrWhiteSpace(filter.WhereSQL))
{
return(false);
}
string before = filter.WhereSQL;
string after = ParameterCreator.RenameParameterInSQL(before, oldName, newName);
//no change was actually made
if (before.Equals(after))
{
return(false);
}
filter.WhereSQL = after;
filter.SaveToDatabase();
if (!RefactoredFilters.Contains(filter))
{
RefactoredFilters.Add(filter);
}
return(true);
}
protected override void BuildParameter(ISqlParameter parm)
{
base.BuildParameter(parm);
if (parm.DataType == DataType.Hierarchical || parm.Value == null)
{
StringBuilder.Append(" :: ");
BuildDataType(new SqlDataType(parm.DataType, parm.SystemType, parm.DbSize, parm.DbSize, parm.DbSize));
}
}
/// <summary>
/// Adds a new global parameter which will overridde other parameters declared at lower <see cref="ParameterLevel"/>
///
/// <para>The <see cref="State"/> must be <see cref="ParameterManagerLifecycleState.AllowingGlobals"/> for this to be allowed</para>
/// </summary>
/// <param name="parameter"></param>
public void AddGlobalParameter(ISqlParameter parameter)
{
if (State != ParameterManagerLifecycleState.AllowingGlobals)
{
throw new InvalidOperationException("Cannot add global parameters at this stage, State must be AllowingGlobals. Basically you can only add globals to a QueryBuilder before it has ever generated any .SQL, to prevent duplication or out dated results vs the results of a Resolved SQL resultant query");
}
ParametersFoundSoFarInQueryGeneration[ParameterLevel.Global].Add(parameter);
}
/// <summary>
/// Returns the <see cref="ParameterLevel"/> that the <see cref="ISqlParameter"/> was found at or null if has not been added to this <see cref="ParameterManager"/>
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public ParameterLevel?GetLevelForParameter(ISqlParameter parameter)
{
if (ParametersFoundSoFarInQueryGeneration.Count(k => k.Value.Contains(parameter)) == 0)
{
return(null);
}
return
(ParametersFoundSoFarInQueryGeneration.Single(k => k.Value.Contains(parameter)).Key);
}
/// <summary>
/// Makes the <see cref="ParameterManager"/> forget about the given <see cref="ISqlParameter"/>
///
/// <para>This operation ignores <see cref="State"/> so you should not use the <see cref="ParameterManager"/> for code generation after calling this method</para>
/// </summary>
/// <param name="deleteable"></param>
public void RemoveParameter(ISqlParameter deleteable)
{
foreach (List <ISqlParameter> parameters in ParametersFoundSoFarInQueryGeneration.Values)
{
if (parameters.Contains(deleteable))
{
parameters.Remove(deleteable);
}
}
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="parameter"></param>
public Query(string query, ISqlParameter parameter)
{
Connection = Sql.Connection;
Command = new SqlCommand(query, Connection);
var parameters = parameter?.GetParameters();
if (parameters != null)
{
Command.Parameters.AddRange(parameters);
}
}
private static bool AreIdentical(ISqlParameter first, ISqlParameter other)
{
bool sameSql = AreDeclaredTheSame(first, other);
string value1 = first.Value ?? "";
string value2 = other.Value ?? "";
bool sameValue = value1.Trim().Equals(value2.Trim(), StringComparison.CurrentCultureIgnoreCase);
return(sameSql && sameValue);
}
private static string DeclareParameterText(ISqlParameter parameter)
{
return(parameter.Size == 0
? "declare @{0} {1}; ".Params(
GetParameterName(parameter),
parameter.SqlDbType.ToString().ToLower())
: "declare @{0} {1}({2}); ".Params(
GetParameterName(parameter),
parameter.SqlDbType.ToString().ToLower(),
parameter.Size));
}
private static bool AreDeclaredTheSame(ISqlParameter first, ISqlParameter other)
{
if (first == null || other == null)
{
throw new NullReferenceException("You cannot pass null parameters into this method");
}
string sql1 = first.ParameterSQL ?? "";
string sql2 = other.ParameterSQL ?? "";
return(sql1.Trim().Equals(sql2.Trim(), StringComparison.CurrentCultureIgnoreCase));
}
/// <summary>
/// Returns the <see cref="ParameterLevel"/> that the <see cref="ISqlParameter"/> was found at or null if has not been added to this <see cref="ParameterManager"/>
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
public ParameterLevel?GetLevelForParameter(ISqlParameter parameter)
{
if (ParametersFoundSoFarInQueryGeneration.Count(k => k.Value.Contains(parameter)) == 0)
{
return(null);
}
return
(ParametersFoundSoFarInQueryGeneration
//take the bottom most level it was found at
.OrderBy(kvp => kvp.Key)
.First(k => k.Value.Contains(parameter)).Key);
}
public void Add(ISqlParameter parameter)
{
string name = parameter.ParameterName;
var snip = new SubstringAutocompleteItem(name);
snip.Tag = name;
snip.Text = parameter.ParameterName;
snip.ImageIndex = GetIndexFor(parameter, RDMPConcept.ParametersNode.ToString());//parameter icon
snip.ToolTipText = snip.ToString();
AddUnlessDuplicate(snip);
}
public SimpleParameterUI(IActivateItems activator, ISqlParameter parameter)
{
_activator = activator;
_parameter = parameter;
InitializeComponent();
lblParameterName.Text = parameter.ParameterName.TrimStart('@');
pbParameter.Image = _activator.CoreIconProvider.GetImage(RDMPConcept.ParametersNode);
tbValue.Text = parameter.Value;
//move the text box to the right of the parameter name but make sure it is minimum visible
tbValue.Left = Math.Min(Width - tbValue.Width, lblParameterName.Right);
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <param name="parameter"></param>
public StoredProcedure(string name, ISqlParameter parameter)
{
Connection = Sql.Connection;
Command = new SqlCommand(name, Connection)
{
CommandType = CommandType.StoredProcedure
};
var parameters = parameter?.GetParameters();
if (parameters != null)
{
Command.Parameters.AddRange(parameters);
}
}
public string DescribeProblem(ISqlParameter parameter)
{
if (string.IsNullOrWhiteSpace(parameter.Value) || parameter.Value == AnyTableSqlParameter.DefaultValue)
{
return("No value defined");
}
if (AnyTableSqlParameter.HasProhibitedName(parameter))
{
return("Parameter name is a reserved name for the RDMP software");
}
return(null);
}
private bool CanOverride(ISqlParameter sqlParameter)
{
if (sqlParameter != null)
{
//if it is not already overridden
if (!Options.IsOverridden(sqlParameter) &&
//and it exists at a lower level
Options.ParameterManager.GetLevelForParameter(sqlParameter) < Options.CurrentLevel)
{
return(true);
}
}
return(false);
}
/// <summary>
/// Checks to ensure char based parameters contains a value, are not longer than the expected length and contain either single quotes or an @ symbol before performing bracket parity checks
/// </summary>
/// <param name="parameter"></param>
public void CheckSyntax(ISqlParameter parameter)
{
if (string.IsNullOrWhiteSpace(parameter.Value))
{
throw new SyntaxErrorException("Parameter " + parameter.ParameterName + " does not have a value");
}
bool isCharBased = parameter.ParameterSQL.ToLower().Contains("char");
if (isCharBased && parameter.Value.Trim().StartsWith("'"))
{
Match matchValue = Regex.Match(parameter.Value, "'[^']*'");
Match matchLength = Regex.Match(parameter.ParameterSQL, "\\([0-9]*\\)");
if (matchValue.Success && matchLength.Success)
{
int userSpecifiedLength = Int32.Parse(matchLength.Value.Substring(1, matchLength.Value.Length - 2));
int actualLength = matchValue.Value.Trim().Length - 2;
if (actualLength > userSpecifiedLength)
{
throw new SyntaxErrorException("You created a parameter of length " + userSpecifiedLength + " (" + parameter.ParameterName + ") but then put a value in it that is " + actualLength + " characters long, the parameter with the problem was:" + parameter.ParameterName);
}
}
}
if (isCharBased && !(parameter.Value.Contains("'") || parameter.Value.Contains("@")))
{
throw new SyntaxErrorException("Parameter " + parameter.ParameterName + " looks like it is character based but it's value does not contain any single quotes (or at least a reference to another variable)");
}
try
{
ParityCheckCharacterPairs(new[] { '(', '[', '\'' }, new[] { ')', ']', '\'' }, parameter.ParameterSQL);
ParityCheckCharacterPairs(new[] { '(', '[', '\'' }, new[] { ')', ']', '\'' }, parameter.Value);
}
catch (SyntaxErrorException exception)
{
throw new SyntaxErrorException("Failed to validate the bracket parity of parameter " + parameter, exception);
}
if (!parameter.GetQuerySyntaxHelper().IsValidParameterName(parameter.ParameterSQL))
{
throw new SyntaxErrorException("parameterSQL is not valid \"" + parameter.ParameterSQL + "\"");
}
}
private void AddParameter(ICollection <IDbDataParameter> parms, string name, ISqlParameter parm)
{
var p = Command.CreateParameter();
var dataType = parm.DataType;
if (dataType == DataType.Undefined)
{
dataType = MappingSchema.GetDataType(
parm.SystemType == typeof(object) && parm.Value != null
? parm.Value.GetType()
: parm.SystemType).DataType;
}
DataProvider.SetParameter(p, name, dataType, parm.Value);
parms.Add(p);
}
/// <summary>
/// Returns true if a <see cref="Comment"/> has been provided and an example/initial <see cref="Value"/> specified. This is a requirement of
/// publishing a filter as a master filter
/// </summary>
/// <param name="sqlParameter"></param>
/// <param name="reasonParameterRejected"></param>
/// <returns></returns>
public static bool IsProperlyDocumented(ISqlParameter sqlParameter, out string reasonParameterRejected)
{
reasonParameterRejected = null;
if (string.IsNullOrWhiteSpace(sqlParameter.ParameterSQL))
{
reasonParameterRejected = "There is no ParameterSQL";
}
else
if (string.IsNullOrWhiteSpace(sqlParameter.Value))
{
reasonParameterRejected = "There is no value/default value listed";
}
return(reasonParameterRejected == null);
}
/// <summary>
/// Generates Sql to comment, declare and set the initial value for the supplied <see cref="ISqlParameter"/>.
/// </summary>
/// <param name="sqlParameter"></param>
/// <returns></returns>
public static string GetParameterDeclarationSQL(ISqlParameter sqlParameter)
{
string toReturn = "";
if (!string.IsNullOrWhiteSpace(sqlParameter.Comment))
{
toReturn += "/*" + sqlParameter.Comment + "*/" + Environment.NewLine;
}
toReturn += sqlParameter.ParameterSQL + Environment.NewLine;
//it's a table valued parameter! advanced
if (!string.IsNullOrEmpty(sqlParameter.Value) && Regex.IsMatch(sqlParameter.Value, @"\binsert\s+into\b", RegexOptions.IgnoreCase))
{
toReturn += sqlParameter.Value + ";" + Environment.NewLine;
}
else
{
toReturn += "SET " + sqlParameter.ParameterName + "=" + sqlParameter.Value + ";" + Environment.NewLine;//its a regular value
}
return(toReturn);
}
private static string GetParameterName(ISqlParameter parameter)
{
return(parameter.ParameterName.StartsWith("@")
? parameter.ParameterName.Substring(1)
: parameter.ParameterName);
}
public void Parameters_Add(ISqlParameter parameter)
{
instance.Parameters.Add(
new SqlParameter(parameter.ParameterName, parameter.Value));
}
/// <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();
}
}
}
}
public bool IsHigherLevel(ISqlParameter parameter)
{
return(ParameterManager.GetLevelForParameter(parameter) > CurrentLevel);
}
private bool SynchronizeParameters(TableValuedFunctionImporter importer, ICheckNotifier notifier)
{
var discoveredParameters = _toSyncTo.GetCurrentDatabase().ExpectTableValuedFunction(_tableToSync.GetRuntimeName(), _tableToSync.Schema).DiscoverParameters();
var currentParameters = _tableToSync.GetAllParameters();
//For each parameter in underlying database
foreach (DiscoveredParameter parameter in discoveredParameters)
{
ISqlParameter existingCatalogueReference = currentParameters.SingleOrDefault(p => p.ParameterName.Equals(parameter.ParameterName));
if (existingCatalogueReference == null)// that is not known about by the TableInfo
{
bool create = notifier.OnCheckPerformed(
new CheckEventArgs(
"TableInfo " + _tableToSync +
" is a Table Valued Function but it does not have a record of the parameter " +
parameter.ParameterName + " which appears in the underlying database", CheckResult.Fail,
null, "Create the Parameter"));
if (!create)
{
return(false); //no longer synched
}
importer.CreateParameter(_tableToSync, parameter);
}
else
{
//it is known about by the Catalogue but has it mysteriously changed datatype since it was imported / last synced?
var dbDefinition = importer.GetParamaterDeclarationSQL(parameter);
//if there is a disagreement on type etc
if (existingCatalogueReference.ParameterSQL != dbDefinition)
{
bool modify =
notifier.OnCheckPerformed(
new CheckEventArgs(
"Parameter " + existingCatalogueReference + " is declared as '" + dbDefinition +
"' but in the Catalogue it appears as '" +
existingCatalogueReference.ParameterSQL + "'", CheckResult.Fail, null,
"Change the definition in the Catalogue to '" + dbDefinition + "'"));
if (!modify)
{
return(false);
}
existingCatalogueReference.ParameterSQL = dbDefinition;
existingCatalogueReference.SaveToDatabase();
}
}
}
//Find redundant parameters - parameters that the catalogue knows about but no longer appear in the table valued function signature in the database
foreach (ISqlParameter currentParameter in currentParameters)
{
if (!discoveredParameters.Any(p => p.ParameterName.Equals(currentParameter.ParameterName)))
{
bool delete =
notifier.OnCheckPerformed(
new CheckEventArgs(
"TableInfo " + _tableToSync +
" is a Table Valued Function, in the Catalogue it has a parameter called " +
currentParameter.ParameterName +
" but this parameter no longer appears in the underlying database", CheckResult.Fail,
null, "Delete Parameter " + currentParameter.ParameterName));
if (!delete)
{
return(false);
}
((IDeleteable)currentParameter).DeleteInDatabase();
}
}
return(true);
}
public bool ShouldBeReadOnly(ISqlParameter p)
{
return(ReadOnly || IsOverridden(p) || IsDifferentLevel(p) || p is SpontaneousObject);
}
