public void OnSQLUpdated(object sender, EventArgs e)
{
QueryBuilder queryBuilder = SessionStore.Current.QueryBuilder;
PlainTextSQLBuilder plainTextSQLBuilder = new PlainTextSQLBuilder {
QueryBuilder = queryBuilder, KeywordFormat = KeywordFormat.UpperCase, UseAltNames = false
};
PlainTextSQLBuilder plainTextSQLBuilderWithAltNames = new PlainTextSQLBuilder {
QueryBuilder = queryBuilder, KeywordFormat = KeywordFormat.UpperCase, UseAltNames = true
};
SessionStore.Current.Exchange.Data = new ExchangeClass
{
SQL = plainTextSQLBuilder.SQL,
AlternateSQL = plainTextSQLBuilderWithAltNames.SQL
};
}
public void QueryBuilderControl1_Init(object sender, EventArgs e)
{
// Get instance of QueryBuilder
QueryBuilder queryBuilder = QueryBuilderControl1.QueryBuilder;
// Turn this property on to suppress parsing error messages when user types non-SELECT statements in the text editor.
queryBuilder.BehaviorOptions.AllowSleepMode = true;
queryBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
plainTextSQLBuilder = new PlainTextSQLBuilder {
QueryBuilder = queryBuilder, KeywordFormat = KeywordFormat.UpperCase
};
plainTextSQLBuilder2 = new PlainTextSQLBuilder {
QueryBuilder = queryBuilder, KeywordFormat = KeywordFormat.UpperCase, UseAltNames = false, ExpandVirtualObjects = true
};
queryBuilder.OfflineMode = true;
// Load MetaData from XML document. File name stored in WEB.CONFIG file in [/configuration/appSettings/XmlMetaData] key
try
{
var path = ConfigurationManager.AppSettings["XmlMetaData"];
var xml = Path.Combine(Server.MapPath(""), path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
queryBuilder.MetadataStructure.Refresh();
StatusBar1.Message.Information("Metadata loaded");
}
catch (Exception ex)
{
string message =
"Error loading metadata from the database." +
"Check the 'configuration\\connectionStrings' key in the [web.config] file.";
Logger.Error(message, ex);
StatusBar1.Message.Error(message + " Check log.txt for details.");
}
MetadataObject o;
MetadataField f;
// Virtual fields for real object
// ===========================================================================
o = queryBuilder.MetadataContainer.FindItem <MetadataObject>("Orders");
// first test field - simple expression
f = o.AddField("_OrderId_plus_1");
f.Expression = "orders.OrderId + 1";
// second test field - correlated sub-query
f = o.AddField("_CustomerName");
f.Expression = "(select c.CompanyName from Customers c where c.CustomerId = orders.CustomerId)";
// Virtual object (table) with virtual fields
// ===========================================================================
o = queryBuilder.MetadataContainer.AddTable("Orders_tbl");
o.Expression = "Orders";
// first test field - simple expression
f = o.AddField("_tbl_OrderId_plus_1");
f.Expression = "Orders_tbl.OrderId + 1";
// second test field - correlated sub-query
f = o.AddField("_tbl_CustomerName");
f.Expression = "(select c.CompanyName from Customers c where c.CustomerId = Orders_tbl.CustomerId)";
// Virtual object (sub-query) with virtual fields
// ===========================================================================
o = queryBuilder.MetadataContainer.AddTable("Orders_qry");
o.Expression = "(select OrderId, CustomerId, OrderDate from Orders) as dummy_alias";
// first test field - simple expression
f = o.AddField("_qry_OrderId_plus_1");
f.Expression = "Orders_qry.OrderId + 1";
// second test field - correlated sub-query
f = o.AddField("_qry_CustomerName");
f.Expression = "(select c.CompanyName from Customers c where c.CustomerId = Orders_qry.CustomerId)";
// kick queryBuilder to initialize its metadata tree
queryBuilder.InitializeDatabaseSchemaTree();
queryBuilder.SQL = "SELECT dummy_alias._qry_OrderId_plus_1, dummy_alias._qry_CustomerName FROM Orders_qry dummy_alias";
}
