async void _build_syntax_btn_Click(object sender, RoutedEventArgs e)
{
var syntax_txt = _query_tb.Text;
var bg_default = _analysis_tb.Background;
_analysis_tb.Background = Brushes.Gold;
var analysis_txt = await Task.Factory.StartNew(() => {
var parser = new TSql130Parser(false);
IList <ParseError> errors;
var fragment = parser.Parse(new StringReader(syntax_txt), out errors);
if (errors.Count > 0)
{
return("ERROR:\n" + String.Join("\n", errors.Select(err => err.Message)));
}
fragment.Accept(new MyNaiveMutator());
var renderer = new Sql130ScriptGenerator();
string sql;
renderer.GenerateScript(fragment, out sql);
return(sql);
});
this.Dispatcher.Invoke(() => {
_analysis_tb.Text = analysis_txt;
_analysis_tb.Background = bg_default;
});
}
public static string GenerateTSql(TSqlFragment script)
{
var generator = new Sql130ScriptGenerator(Settings.SavedSettings.Get().GeneratorOptions);
var builder = new StringBuilder();
generator.GenerateScript(script, new StringWriter(builder));
return builder.ToString();
}
/// <summary>
/// Uses the SQL Formatter to pretty print the code; not strictly necessary as the only place you will see it is in Profiler :)
/// However, by reparsing the code we ensure that any errors in conversion are caught.
/// </summary>
/// <exception cref="Exception">Throws a generic exception if there is a parse error</exception>
protected void FormatSQL()
{
if (reparse)
{
// use the features in the ScriptDom namespace to pretty print our T-SQL
TextReader rdr = new StringReader(SqlStmt.ToString());
IList <ParseError> errors = null;
TSql130Parser parser = new TSql130Parser(true);
TSqlFragment tree = parser.Parse(rdr, out errors);
rdr.Close();
if (errors.Count > 0)
{
Exception e = new Exception(string.Format("Parse Error after converstion on line {0}, column {1}: {2}", errors[0].Line, errors[0].Column, errors[0].Message));
throw e;
}
else
{
Sql130ScriptGenerator scrGen = new Sql130ScriptGenerator();
string formattedSQL = null;
scrGen.GenerateScript(tree, out formattedSQL);
cmd.CommandText = formattedSQL;
}
}
else
{
cmd.CommandText = SqlStmt.ToString();
}
}
public static string GenerateTSql(TSqlFragment script)
{
var generator = new Sql130ScriptGenerator(SavedSettings.Get().GeneratorOptions);
var builder = new StringBuilder();
generator.GenerateScript(script, new StringWriter(builder));
return(builder.ToString());
}
private static SqlScriptGenerator GetGenerator(GenerationOptions options)
{
SqlScriptGenerator generator = null;
switch (options.SqlVersion)
{
case SqlVersion.Sql80:
generator = new Sql80ScriptGenerator();
break;
case SqlVersion.Sql100:
generator = new Sql100ScriptGenerator();
break;
case SqlVersion.Sql110:
generator = new Sql110ScriptGenerator();
break;
case SqlVersion.Sql120:
generator = new Sql120ScriptGenerator();
break;
case SqlVersion.Sql130:
generator = new Sql130ScriptGenerator();
break;
case SqlVersion.Sql140:
generator = new Sql140ScriptGenerator();
break;
case SqlVersion.Sql150:
generator = new Sql150ScriptGenerator();
break;
default:
generator = new Sql90ScriptGenerator();
break;
}
generator.Options.Apply(options);
return(generator);
}
/// <summary>
/// SQL Query string parse and format
/// </summary>
/// <param name="sqlScript">Unformated and unparsed SQL Query String</param>
/// <param name="useScriptOptions">Do we use special format options or by Default. If set to useScriptOptions true and useConfFile false
/// gets the settings from Form.</param>
/// <param name="useConfFile">Do we use the configuration for formats from CustomFormatConfiguration.xlsx file?
/// Both useScriptOptions and useConfFile should be true!</param>
/// <param name="confFilePath">The full path to the file "CustomFormatConfiguration.xlsx" holding the custom format logic </param>
/// <returns></returns>
public string SQLScriptFormater(string sqlScript, bool useScriptOptions, bool useConfFile, string confFilePath)
{
try
{
string strFormattedSQL = null;
using (TextReader rdr = new StringReader(sqlScript))
{
TSql130Parser parser = new TSql130Parser(true);
IList <ParseError> errors = null;
TSqlFragment tree = parser.Parse(rdr, out errors);
if (errors.Count > 0)
{
foreach (ParseError err in errors)
{
strFormattedSQL = strFormattedSQL + err.Message + "\rn";
}
return(strFormattedSQL);
}
Sql130ScriptGenerator srcGen = new Sql130ScriptGenerator();
if (useScriptOptions)
{
//if we use the Excel ConfigFile
if (useConfFile)
{
SqlScriptGeneratorOptions optionsConfig = new SqlScriptGeneratorOptions();
//string confFilePath = System.Environment.CurrentDirectory + "\\" + "CustomFormattingSettings.xlsx";
try
{
if (!File.Exists(confFilePath))
{
throw new FileNotFoundException("File not found in App directory", "CustomFormatConfiguration.xlsx");
}
}
catch (FileNotFoundException ex)
{
MessageBox.Show("Error: " + ex.Message + "\n" + ex.FileName);
}
DataTable formatConfiguration = ReadExcelFile.getExcellToDtbl(confFilePath);
foreach (DataRow dr in formatConfiguration.Rows)
{
setScriptOptions(ref optionsConfig, null, dr);
}
addScriptOptionsToScriptGen(optionsConfig, ref srcGen);
}
//If we use the interface
else
{
//Search for our GroupBox in SQLFormatForm
GroupBox formatSettings = new GroupBox();
IEnumerable <Control> listControls = null;
foreach (System.Windows.Forms.Form form in Application.OpenForms)
{
if (form.Name == "SQLFormatForm")
{
listControls = GetAll(form, "gbFormatSettings");
}
}
var control = listControls.First();
formatSettings = control as GroupBox;
//Loop trought all controls in the group box and set format Settings
SqlScriptGeneratorOptions optionsConfig = new SqlScriptGeneratorOptions();
foreach (Control ctrl in formatSettings.Controls)
{
if ((ctrl is TextBox) || (ctrl is ComboBox) || (ctrl is CheckBox))
{
setScriptOptions(ref optionsConfig, ctrl, null);
}
}
addScriptOptionsToScriptGen(optionsConfig, ref srcGen);
}
}
srcGen.GenerateScript(tree, out strFormattedSQL);
return(strFormattedSQL);
}
}
catch (Exception)
{
throw;
}
}
//Add format settings to the Script Generator
private void addScriptOptionsToScriptGen(SqlScriptGeneratorOptions scriptOptions, ref Sql130ScriptGenerator srcGenScript)
{
if (scriptOptions != null)
{
srcGenScript.Options.AlignClauseBodies = scriptOptions.AlignClauseBodies;
srcGenScript.Options.AlignColumnDefinitionFields = scriptOptions.AlignColumnDefinitionFields;
srcGenScript.Options.AlignSetClauseItem = scriptOptions.AlignSetClauseItem;
srcGenScript.Options.AsKeywordOnOwnLine = scriptOptions.AsKeywordOnOwnLine;
srcGenScript.Options.IncludeSemicolons = scriptOptions.IncludeSemicolons;
srcGenScript.Options.IndentationSize = scriptOptions.IndentationSize;
srcGenScript.Options.IndentSetClause = scriptOptions.IndentSetClause;
srcGenScript.Options.IndentViewBody = scriptOptions.IndentViewBody;
srcGenScript.Options.KeywordCasing = scriptOptions.KeywordCasing;
srcGenScript.Options.MultilineInsertSourcesList = scriptOptions.MultilineInsertSourcesList;
srcGenScript.Options.MultilineInsertTargetsList = scriptOptions.MultilineInsertTargetsList;
srcGenScript.Options.MultilineSelectElementsList = scriptOptions.MultilineSelectElementsList;
srcGenScript.Options.MultilineSetClauseItems = scriptOptions.MultilineSetClauseItems;
srcGenScript.Options.MultilineViewColumnsList = scriptOptions.MultilineViewColumnsList;
srcGenScript.Options.MultilineWherePredicatesList = scriptOptions.MultilineWherePredicatesList;
srcGenScript.Options.NewLineBeforeCloseParenthesisInMultilineList = scriptOptions.NewLineBeforeCloseParenthesisInMultilineList;
srcGenScript.Options.NewLineBeforeFromClause = scriptOptions.NewLineBeforeFromClause;
srcGenScript.Options.NewLineBeforeGroupByClause = scriptOptions.NewLineBeforeGroupByClause;
srcGenScript.Options.NewLineBeforeHavingClause = scriptOptions.NewLineBeforeHavingClause;
srcGenScript.Options.NewLineBeforeJoinClause = scriptOptions.NewLineBeforeJoinClause;
srcGenScript.Options.NewLineBeforeOffsetClause = scriptOptions.NewLineBeforeOffsetClause;
srcGenScript.Options.NewLineBeforeOpenParenthesisInMultilineList = scriptOptions.NewLineBeforeOpenParenthesisInMultilineList;
srcGenScript.Options.NewLineBeforeOrderByClause = scriptOptions.NewLineBeforeOrderByClause;
srcGenScript.Options.NewLineBeforeOutputClause = scriptOptions.NewLineBeforeOutputClause;
srcGenScript.Options.NewLineBeforeWhereClause = scriptOptions.NewLineBeforeWhereClause;
srcGenScript.Options.SqlVersion = scriptOptions.SqlVersion;
srcGenScript.Options.SqlVersion = scriptOptions.SqlVersion;
}
}
