private void SaveInFile()
{
// Save the query text to file
if (QBuilder.ActiveUnionSubQuery == null)
{
return;
}
var saveFileDialog1 = new SaveFileDialog()
{
DefaultExt = "sql",
FileName = "query",
Filter = "SQL query files (*.sql)|*.sql|All files|*.*"
};
if (saveFileDialog1.ShowDialog() != true)
{
return;
}
using (var sw = new StreamWriter(saveFileDialog1.FileName))
{
sw.Write(FormattedSQLBuilder.GetSQL(QBuilder.ActiveUnionSubQuery.QueryRoot, QBuilder.SQLFormattingOptions));
}
}
private void ApplyText()
{
var sqlFormattingOptions = queryBuilder1.SQLFormattingOptions;
switch (_mode)
{
case ModeEditor.Entire:
textBox1.Text = queryBuilder1.FormattedSQL;
break;
case ModeEditor.SubQuery:
if (queryBuilder1.ActiveUnionSubQuery == null)
{
break;
}
var subQuery = queryBuilder1.ActiveUnionSubQuery.ParentSubQuery;
textBox1.Text = FormattedSQLBuilder.GetSQL(subQuery, sqlFormattingOptions);
break;
case ModeEditor.Expression:
if (queryBuilder1.ActiveUnionSubQuery == null)
{
break;
}
var unionSubQuery = queryBuilder1.ActiveUnionSubQuery;
textBox1.Text = FormattedSQLBuilder.GetSQL(unionSubQuery, sqlFormattingOptions);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
public CommonTab(FormattedSQLBuilder builder, SQLBuilderSelectFormat selectFormat)
{
InitializeComponent();
_builder = builder;
_formattingOptions = (SQLFormattingOptions)_builder.Options;
_selectFormat = selectFormat;
}
private void sqlQuery_SQLUpdated(object sender, EventArgs e)
{
// Handle the event raised by SQL Builder object that the text of SQL query is changed
// Hide error banner if any
errorBox1.Show(null, sqlContext1.SyntaxProvider);
// update the text box with formatted query text created with default formatting options
_lastValidSql = sqlTextEditor1.Text = FormattedSQLBuilder.GetSQL(sqlQuery1.QueryRoot, new SQLFormattingOptions());
}
private void _query_SQLUpdated(object sender, EventArgs e)
{
// at this stage you can get simple unformatted query text...
//SqlBox.Text = _query.SQL;
// ... or format the query text with SQL formatter
SQLFormattingOptions formattingOptions = new SQLFormattingOptions {
KeywordFormat = KeywordFormat.UpperCase
};
var sql = FormattedSQLBuilder.GetSQL(_query.QueryRoot, formattingOptions);
// put the result SQL query text to the text box
SqlBox.Text = sql;
}
public FormattedOptionsDemo()
{
InitializeComponent();
_builder = new FormattedSQLBuilder(sqlQuery1.SqlFormattingOptions);
sqlContext1.MetadataContainer.ImportFromXML("Northwind.xml");
sqlQuery1.SQL = "SELECT\r\n customer.first_name,\r\n customer.last_name,\r\n rental.return_date\r\nFROM\r\n customer\r\n " +
"INNER JOIN rental ON rental.customer_id = customer.customer_id\r\n INNER JOIN (SELECT\r\n address.*\r\n " +
" FROM\r\n address\r\n INNER JOIN city ON address.city_id = city.city_id\r\n WHERE\r\n city.country_id = 5)" +
" addr ON customer.address_id = addr.address_id\r\nWHERE\r\n customer.store_id IN (SELECT\r\n store.store_id\r\n FROM\r\n " +
" store\r\n WHERE\r\n (store.manager_staff_id = 10 AND\r\n store.store_id < 11) OR\r\n (((store.manager_staff_id = TRUE) OR\r\n " +
" (store.manager_staff_id BETWEEN 10 AND 20))))\r\nORDER BY\r\n rental.return_date";
sqlQuery1.SqlFormattingOptions.Updated += SqlFormattingOptions_Updated;
_builder.Updated += _builder_Updated;
treeView.ExpandAll();
}
private void ApplyText()
{
var sqlFormattingOptions = Builder.SQLFormattingOptions;
if (TextEditor == null)
{
return;
}
switch (_mode)
{
case ModeEditor.Entire:
_lastValidSql = TextEditor.Text = Builder.FormattedSQL;
break;
case ModeEditor.SubQuery:
if (Builder.ActiveUnionSubQuery == null)
{
break;
}
var subQuery = Builder.ActiveUnionSubQuery.ParentSubQuery;
_lastValidSql = TextEditor.Text = FormattedSQLBuilder.GetSQL(subQuery, sqlFormattingOptions);
break;
case ModeEditor.Expression:
if (Builder.ActiveUnionSubQuery == null)
{
break;
}
var unionSubQuery = Builder.ActiveUnionSubQuery;
_lastValidSql = TextEditor.Text = FormattedSQLBuilder.GetSQL(unionSubQuery, sqlFormattingOptions);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
public void OnSQLUpdated(object sender, EventArgs e)
{
var qb = QueryBuilderStore.Get("AlternateNames");
var opts = new SQLFormattingOptions();
opts.Assign(qb.SQLFormattingOptions);
opts.KeywordFormat = KeywordFormat.UpperCase;
// get SQL query with real object names
opts.UseAltNames = false;
var plainSql = FormattedSQLBuilder.GetSQL(qb.SQLQuery.QueryRoot, opts);
// get SQL query with alternate names
opts.UseAltNames = true;
var sqlWithAltNames = FormattedSQLBuilder.GetSQL(qb.SQLQuery.QueryRoot, opts);
// prepare additional data to be sent to the client
qb.ExchangeData = new
{
SQL = plainSql,
AlternateSQL = sqlWithAltNames
};
}
public void OnSQLUpdated(object sender, EventArgs e)
{
var qb = QueryBuilderStore.Get(InstanceId);
var opts = new SQLFormattingOptions();
opts.Assign(qb.SQLFormattingOptions);
opts.KeywordFormat = KeywordFormat.UpperCase;
// get query with virtual objects and fields
opts.ExpandVirtualObjects = false;
var sqlWithVirtObjects = FormattedSQLBuilder.GetSQL(qb.SQLQuery.QueryRoot, opts);
// get SQL query with real object names
opts.ExpandVirtualObjects = true;
var plainSql = FormattedSQLBuilder.GetSQL(qb.SQLQuery.QueryRoot, opts);
// prepare additional data to be sent to the client
qb.ExchangeData = new
{
SQL = plainSql,
VirtualObjectsSQL = sqlWithVirtObjects
};
}
private void sqlQuery_SQLUpdated(object sender, EventArgs e)
{
// Text of SQL query has been updated.
// To get the query text, ready for execution on SQL server with real object names just use SQL property.
_lastValidSql = sqlTextEditor.Text = FormattedSQLBuilder.GetSQL(_sqlQuery.QueryRoot, new SQLFormattingOptions());
}
public MainQueryTab(FormattedSQLBuilder builder)
{
_builder = builder;
_options = (SQLFormattingOptions)builder.Options;
InitializeComponent();
}
