private void BuildEqualsSameLiteral(BooleanExpression search, ScalarExpression firstParam, Literal literal)
{
var newExpression = new BooleanParenthesisExpression();
var expression = new BooleanBinaryExpression();
newExpression.Expression = expression;
expression.BinaryExpressionType = BooleanBinaryExpressionType.Or;
var isnull = new BooleanIsNullExpression();
isnull.Expression = firstParam;
expression.FirstExpression = isnull;
var second = new BooleanComparisonExpression();
second.FirstExpression = firstParam;
second.SecondExpression = literal;
expression.SecondExpression = second;
var sql = ScriptDom.GenerateTSql(newExpression);
_replacementsToMake.Add(new Replacements
{
Original = _script.Substring(search.StartOffset, search.FragmentLength),
OriginalLength = search.FragmentLength,
OriginalOffset = search.StartOffset,
Replacement = sql,
OriginalFragment = _currentFragment
});
}
public string ExtractIntoFunction()
{
var browser = new SolutionBrowserForm("");
browser.ShowDialog();
var destination = browser.DestinationItem;
if (destination == null)
{
return(null);
}
var name = browser.GetObjectName();
var function = new CreateFunctionStatement();
var returnSelect = (function.ReturnType = new SelectFunctionReturnType()) as SelectFunctionReturnType;
returnSelect.SelectStatement = GetSelectStatementForQuery();
function.Name = name.ToSchemaObjectName();
var classFolder = destination.ProjectItems.AddFromTemplate("Procedure", name.UnQuote() + ".sql");
var filePath = classFolder.GetStringProperty("FullPath");
File.WriteAllText(filePath, ScriptDom.GenerateTSql(function));
classFolder.Open().Visible = true;
return(GetCallingCode(function));
}
private string GetCallingCode(CreateFunctionStatement function)
{
var callingSelect = new SelectStatement();
var spec = (callingSelect.QueryExpression = new QuerySpecification()) as QuerySpecification;
spec.FromClause = new FromClause();
spec.FromClause.TableReferences.Add(new SchemaObjectFunctionTableReference()
{
SchemaObject = function.Name
});
spec.SelectElements.Add(new SelectStarExpression());
return(ScriptDom.GenerateTSql(spec));
}
public void ShowDuplicateIndexes()
{
var statements = new StatementEnumerator().GetIndexes();
var indexes = new Dictionary <string, List <CodeStatement <CreateIndexStatement> > >();
foreach (var statement in statements)
{
var key = BuildKey(statement.Statement);
if (indexes.ContainsKey(key))
{
indexes[key].Add(statement);
}
else
{
indexes[key] = new List <CodeStatement <CreateIndexStatement> > {
statement
};
}
}
var dups = indexes.Where(p => p.Value.Count > 1);
foreach (var d in dups)
{
OutputPane.WriteMessage("Duplicate Indexes Found: ");
foreach (var statement in d.Value)
{
OutputPane.WriteMessageWithLink(statement.FileName, statement.Line, "{0}",
ScriptDom.GenerateTSql(statement.Statement));
}
}
if (dups == null || !dups.Any())
{
OutputPane.WriteMessage("No Duplicate Indexes Found.");
}
}
private string buildChunkedDelete(DeleteSpecification delete)
{
var counter = new DeclareVariableStatement();
var counterVariable = new DeclareVariableElement();
counterVariable.DataType = new SqlDataTypeReference()
{
SqlDataTypeOption = SqlDataTypeOption.Int
};
counterVariable.VariableName = new Identifier()
{
Value = "@rowcount"
};
counterVariable.Value = new IntegerLiteral()
{
Value = "10000"
};
counter.Declarations.Add(counterVariable);
delete.TopRowFilter = new TopRowFilter();
delete.TopRowFilter.Expression = new ParenthesisExpression()
{
Expression = new IntegerLiteral()
{
Value = "10000"
}
};
var setCounter = new SetVariableStatement();
setCounter.Variable = new VariableReference()
{
Name = "@rowcount"
};
setCounter.Expression = new GlobalVariableExpression()
{
Name = "@@rowcount"
};
setCounter.AssignmentKind = AssignmentKind.Equals;
var deleteStatement = new DeleteStatement();
deleteStatement.DeleteSpecification = delete;
var beginEnd = new BeginEndBlockStatement();
beginEnd.StatementList = new StatementList();
beginEnd.StatementList.Statements.Add(deleteStatement);
beginEnd.StatementList.Statements.Add(setCounter);
var whilePredicate = new BooleanComparisonExpression();
whilePredicate.ComparisonType = BooleanComparisonType.GreaterThan;
whilePredicate.FirstExpression = new VariableReference()
{
Name = "@rowcount"
};
whilePredicate.SecondExpression = new IntegerLiteral()
{
Value = "0"
};
var whileStatement = new WhileStatement();
whileStatement.Predicate = whilePredicate;
whileStatement.Statement = beginEnd;
var text = ScriptDom.GenerateTSql(counter) + "\r\n" + ScriptDom.GenerateTSql(whileStatement);
return(text);
}
