private string GetNameFromMetadataStructureItem(MetadataStructureItem structureItem)
{
if (structureItem.MetadataItem == null)
{
return(structureItem.Caption);
}
string insertString;
if (structureItem.MetadataItem is MetadataField)
{
var nameField = GetNameMetadataItem(structureItem.MetadataItem, false);
var mapItem =
(_mapTreeItems.ContainsKey(structureItem.Parent) ? _mapTreeItems[structureItem.Parent] : null) as DataSource;
var nameObject = mapItem?.AliasAST != null ?
mapItem.NameInQuery :
GetNameMetadataItem(structureItem.MetadataItem.Parent, mapItem == null);
insertString = $"{nameObject}.{nameField}";
}
else
{
var mapItem =
(_mapTreeItems.ContainsKey(structureItem) ? _mapTreeItems[structureItem] : null) as DataSource;
insertString = mapItem?.AliasAST != null
? mapItem.NameInQuery
: GetNameMetadataItem(structureItem.MetadataItem, mapItem == null);
}
return(insertString);
}
// Closing the current query window on deleting the corresponding user query.
private void userQueriesView1_UserQueryItemRemoved(object sender, MetadataStructureItem item)
{
var childWindow = MdiChildren.OfType <ChildForm>().FirstOrDefault(x => x.UserMetadataStructureItem == item);
childWindow?.Close();
SaveSettings();
}
// Closing the current query window on deleting the corresponding user query.
private void QueriesView_OnUserQueryItemRemoved(object sender, MetadataStructureItem item)
{
var childWindow =
MdiContainer1.Children.Cast <ChildWindow>().FirstOrDefault(x => x.UserMetadataStructureItem == item);
if (childWindow != null)
{
childWindow.ForceClose();
}
SaveSettings();
}
private void DatabaseSchemaView_OnItemDoubleClick(object sender, MetadataStructureItem clickeditem)
{
if (clickeditem.MetadataItem == null)
{
return;
}
// Adding a table to the currently active query.
var objectMetadata = (MetadataType.ObjectMetadata & clickeditem.MetadataItem.Type) != 0;
var obj = (MetadataType.Objects & clickeditem.MetadataItem.Type) != 0;
if (!obj && !objectMetadata)
{
return;
}
if (MdiContainer1.ActiveChild == null && MdiContainer1.Children.Count == 0)
{
var childWindow = CreateChildWindow();
if (childWindow == null)
{
return;
}
MdiContainer1.Children.Add(childWindow);
MdiContainer1.ActiveChild = childWindow;
}
var window = (ChildWindow)MdiContainer1.ActiveChild;
if (window == null)
{
return;
}
var metadataItem = clickeditem.MetadataItem;
if (metadataItem == null)
{
return;
}
if ((metadataItem.Type & MetadataType.Objects) <= 0 && metadataItem.Type != MetadataType.Field)
{
return;
}
using (var qualifiedName = metadataItem.GetSQLQualifiedName(null, true))
{
window.QueryView.AddObjectToActiveUnionSubQuery(qualifiedName.GetSQL());
}
}
void DBView_ItemDoubleClick(object sender, MetadataStructureItem clickedItem)
{
if (clickedItem.MetadataItem == null)
{
return;
}
// Adding a table to the currently active query.
if ((MetadataType.Objects & clickedItem.MetadataItem.Type) == 0 &&
(MetadataType.ObjectMetadata & clickedItem.MetadataItem.Type) == 0)
{
return;
}
if (ActiveMdiChild == null)
{
ChildForm childWindow = CreateChildForm();
if (childWindow == null)
{
return;
}
childWindow.Show();
childWindow.Activate();
}
ChildForm window = (ChildForm)ActiveMdiChild;
if (window == null)
{
return;
}
MetadataItem metadataItem = clickedItem.MetadataItem;
if (metadataItem == null)
{
return;
}
if ((metadataItem.Type & MetadataType.Objects) <= 0 && metadataItem.Type != MetadataType.Field)
{
return;
}
using (var qualifiedName = metadataItem.GetSQLQualifiedName(null, true))
{
window.QueryView.AddObjectToActiveUnionSubQuery(qualifiedName.GetSQL());
}
}
private void DatabaseSchemaView1_OnItemDoubleClick(object sender, MetadataStructureItem item)
{
BtnAdd_OnClick(this, null);
}
private void ExportUserQueriesToFile(object sender, MetadataStructureItem item)
{
var uq = (UserQueriesContainer)sender;
uq.ExportToXML(Server.MapPath("~/" + filename));
}
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();
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);
}
catch (Exception ex)
{
string message = "Can't load metadata from XML.";
Logger.Error(message, ex);
StatusBar1.Message.Error(message + " Check log.txt for details.");
}
// Initialization of the Metadata Structure object that's
// responsible for representation of metadata in a tree-like form
try
{
// Disable the automatic metadata structure creation
queryBuilder.MetadataStructure.AllowChildAutoItems = false;
// queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
MetadataFilterItem filter;
// Create a top-level folder containing all objects
MetadataStructureItem allObjects = new MetadataStructureItem();
allObjects.Caption = "All objects";
allObjects.ImageIndex = queryBuilder.MetadataStructure.Structure.Options.FolderImageIndex;
filter = allObjects.MetadataFilter.Add();
filter.ObjectTypes = MetadataType.All;
queryBuilder.MetadataStructure.Items.Add(allObjects);
// Create "Favorites" folder
MetadataStructureItem favorites = new MetadataStructureItem();
favorites.Caption = "Favorites";
favorites.ImageIndex = queryBuilder.MetadataStructure.Structure.Options.FolderImageIndex;
queryBuilder.MetadataStructure.Items.Add(favorites);
MetadataItem metadataItem;
MetadataStructureItem item;
// Add some metadata objects to "Favorites" folder
metadataItem = queryBuilder.MetadataContainer.FindItem <MetadataItem>("Orders");
item = new MetadataStructureItem();
item.MetadataItem = metadataItem;
item.ImageIndex = queryBuilder.MetadataStructure.Structure.Options.UserTableImageIndex;
favorites.Items.Add(item);
metadataItem = queryBuilder.MetadataContainer.FindItem <MetadataItem>("Order Details");
item = new MetadataStructureItem();
item.MetadataItem = metadataItem;
item.ImageIndex = queryBuilder.MetadataStructure.Structure.Options.UserTableImageIndex;
favorites.Items.Add(item);
// Create folder with filter
MetadataStructureItem filteredFolder = new MetadataStructureItem(); // creates dynamic node
filteredFolder.Caption = "Filtered by 'Prod%'";
filteredFolder.ImageIndex = queryBuilder.MetadataStructure.Structure.Options.FolderImageIndex;
filter = filteredFolder.MetadataFilter.Add();
filter.ObjectTypes = MetadataType.Table | MetadataType.View;
filter.Object = "Prod%";
queryBuilder.MetadataStructure.Items.Add(filteredFolder);
// Clears and loads the first level of the metadata structure tree
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.");
}
}
private bool SaveNewUserQuery(ChildForm childWindow)
{
if (string.IsNullOrEmpty(childWindow.SqlQuery.SQL))
{
MessageBox.Show("Nothing to save: SQL query is empty", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
MetadataStructureItem node = null;
string title;
do
{
using (QueryNameForm window = new QueryNameForm())
{
window.QueryName = childWindow.SqlQuery.SQLContext.MetadataContainer.GetUniqueItemName(
MetadataType.UserQuery,
Helpers.Localizer.GetString("strNewQuery", LocalizableConstantsUI.strNewQuery));
if (window.ShowDialog() != DialogResult.OK)
{
return(false);
}
title = window.QueryName;
}
if (!UserQueries.IsUserQueryExist(childWindow.SqlQuery.SQLContext.MetadataContainer, title))
{
var atItem = userQueriesView1.FocusedItem ?? userQueriesView1.MetadataStructure;
if (!UserQueries.IsFolder(atItem))
{
atItem = atItem.Parent;
}
try
{
node = UserQueries.AddUserQuery(childWindow.SqlQuery.SQLContext.MetadataContainer, atItem, title,
childWindow.FormattedQueryText, (int)DefaultImageListImageIndices.VirtualObject, childWindow.QueryView.LayoutSQL);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
break;
}
var path = userQueriesView1.GetPathAtUserQuery(title);
var message = string.IsNullOrEmpty(path)
? @"The same-named query already exists in the root folder."
: string.Format("The same-named query already exists in the \"{0}\" folder.", path);
MessageBox.Show(message, @"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
} while(true);
childWindow.SqlSourceType = ChildForm.SourceType.UserQuery;
childWindow.FileSourcePath = title;
childWindow.Text = title;
childWindow.UserMetadataStructureItem = node;
SaveSettings();
return(true);
}
private void databaseSchemaView1_ItemDoubleClick(object sender, MetadataStructureItem item)
{
btnAddFilter_Click(this, EventArgs.Empty);
}
private void ExportUserQueriesToFile(object sender, MetadataStructureItem item)
{
var uq = (UserQueriesContainer)sender;
uq.ExportToXML(_env.WebRootPath + @"/../" + filename);
}
private void ReloadQueryObjects()
{
if (ActiveUnionSubQuery == null)
{
return;
}
_mapTreeItems.Clear();
var metadataStructure = new MetadataStructure()
{
AllowChildAutoItems = false
};
List <DataSource> listDataSource = ActiveUnionSubQuery.GetVisibleDataSources();
foreach (var dataSource in listDataSource)
{
var itemStructure = new MetadataStructureItem {
MetadataItem = dataSource.MetadataObject
};
_mapTreeItems.Add(itemStructure, dataSource);
if (dataSource.AliasAST != null && !(dataSource is DataSourceQuery))
{
itemStructure.Caption = dataSource.NameInQuery + " (" +
dataSource.GetObjectNameInQuery(SQLGenerationOptions) + ")";
}
if (dataSource.MetadataObject == null)
{
if (string.IsNullOrEmpty(itemStructure.Caption))
{
itemStructure.Caption = dataSource.NameInQuery;
}
itemStructure.ImageIndex = 5;
foreach (var field in dataSource.Metadata.Fields)
{
var metadataItemField = new MetadataStructureItem {
ImageIndex = 11
};
_mapTreeItems.Add(metadataItemField, field);
if (SQLGenerationOptions.UseAltNames && !string.IsNullOrEmpty(field.AltName))
{
metadataItemField.Caption = field.AltName;
}
else
{
metadataItemField.Caption = field.Name;
}
itemStructure.Items.Add(metadataItemField);
}
}
metadataStructure.Items.Add(itemStructure);
}
FillQueryObjectsTree(metadataStructure);
}
private void TreeObjects_OnItemDoubleClick(object sender, MetadataStructureItem clickeditem)
{
var name = GetNameMetadataItem(clickeditem.MetadataItem, true);
InsertTextIntoEditor(name);
}
private void TreeQueryObjects_OnItemDoubleClick(object sender, MetadataStructureItem clickeditem)
{
InsertTextIntoEditor(GetNameFromMetadataStructureItem(clickeditem));
}
protected override void OnLoad(EventArgs e)
{
// set syntax provider
queryBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
// Fill metadata container from the XML file. (For demonstration purposes.)
try
{
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
queryBuilder.MetadataContainer.ImportFromXML("Northwind.xml");
queryBuilder.SQL = @"SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, [Order Details].ProductID,
[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Orders.OrderID > 0 AND [Order Details].Discount > 0" ;
// Disable the automatic metadata structure creation
queryBuilder.MetadataStructure.AllowChildAutoItems = false;
MetadataFilterItem filter;
// Create a top-level folder containing all objects
var allObjects = new MetadataStructureItem
{
Caption = "All objects",
ImageIndex = queryBuilder.DatabaseSchemaViewOptions.ImageIndices.FolderImageIndex
};
filter = allObjects.MetadataFilter.Add();
filter.ObjectTypes = MetadataType.All;
queryBuilder.MetadataStructure.Items.Add(allObjects);
// Create "Favorites" folder
var favorites = new MetadataStructureItem
{
Caption = "Favorites",
ImageIndex = queryBuilder.DatabaseSchemaViewOptions.ImageIndices.FolderImageIndex
};
queryBuilder.MetadataStructure.Items.Add(favorites);
// Add some metadata objects to "Favorites" folder
var metadataItem = queryBuilder.MetadataContainer.FindItem <MetadataItem>("Orders");
var item1 = new MetadataStructureItem
{
MetadataItem = metadataItem,
};
favorites.Items.Add(item1);
metadataItem = queryBuilder.MetadataContainer.FindItem <MetadataItem>("Order Details");
var item2 = new MetadataStructureItem
{
MetadataItem = metadataItem,
};
favorites.Items.Add(item2);
// Create folder with filter
MetadataStructureItem filteredFolder = new MetadataStructureItem
{
Caption = "Filtered by 'Prod%'",
ImageIndex = queryBuilder.DatabaseSchemaViewOptions.ImageIndices.FolderImageIndex
}; // creates dynamic node
filter = filteredFolder.MetadataFilter.Add();
filter.ObjectTypes = MetadataType.Table | MetadataType.View;
filter.Object = "Prod%";
queryBuilder.MetadataStructure.Items.Add(filteredFolder);
queryBuilder.InitializeDatabaseSchemaTree();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
base.OnLoad(e);
}
private QueryBuilder CreateQueryBuilder()
{
// Get instance of QueryBuilder
var queryBuilder = QueryBuilderStore.Factory.MsSql("MetadataStructure");
// Turn this property on to suppress parsing error messages when user types non-SELECT statements in the text editor.
queryBuilder.BehaviorOptions.AllowSleepMode = true;
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from XML document. File name stored in WEB.CONFIG file in [/configuration/appSettings/Db2XmlMetaData] key
var path = ConfigurationManager.AppSettings["NorthwindXmlMetaData"];
var xml = Path.Combine(Server.MapPath("~"), path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
// Initialization of the Metadata Structure object that's
// responsible for representation of metadata in a tree-like form
// Disable the automatic metadata structure creation
queryBuilder.MetadataStructure.AllowChildAutoItems = false;
// queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
MetadataFilterItem filter;
// Create a top-level folder containing all objects
MetadataStructureItem allObjects = new MetadataStructureItem();
allObjects.Caption = "All objects";
filter = allObjects.MetadataFilter.Add();
filter.ObjectTypes = MetadataType.All;
queryBuilder.MetadataStructure.Items.Add(allObjects);
// Create "Favorites" folder
MetadataStructureItem favorites = new MetadataStructureItem();
favorites.Caption = "Favorites";
queryBuilder.MetadataStructure.Items.Add(favorites);
MetadataItem metadataItem;
MetadataStructureItem item;
// Add some metadata objects to "Favorites" folder
metadataItem = queryBuilder.MetadataContainer.FindItem <MetadataItem>("Orders");
item = new MetadataStructureItem();
item.MetadataItem = metadataItem;
favorites.Items.Add(item);
metadataItem = queryBuilder.MetadataContainer.FindItem <MetadataItem>("Order Details");
item = new MetadataStructureItem();
item.MetadataItem = metadataItem;
favorites.Items.Add(item);
// Create folder with filter
MetadataStructureItem filteredFolder = new MetadataStructureItem(); // creates dynamic node
filteredFolder.Caption = "Filtered by 'Prod%'";
filter = filteredFolder.MetadataFilter.Add();
filter.ObjectTypes = MetadataType.Table | MetadataType.View;
filter.Object = "Prod%";
queryBuilder.MetadataStructure.Items.Add(filteredFolder);
queryBuilder.SQL = GetDefaultSql();
return(queryBuilder);
}
private void UserQueriesChanged(object sender, MetadataStructureItem item)
{
var container = (UserQueriesContainer)sender;
container.ExportToXML(Server.MapPath("UserQueriesStructure.xml"));
}
private void InitializeQueryBuilder(QueryBuilder queryBuilder)
{
queryBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
// Turn this property on to suppress parsing error messages when user types non-SELECT statements in the text editor.
queryBuilder.BehaviorOptions.AllowSleepMode = true;
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from XML document.
var path = _config["NorthwindXmlMetaData"];
var xml = Path.Combine(_env.WebRootPath, path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
// Initialization of the Metadata Structure object that's
// responsible for representation of metadata in a tree-like form
// Disable the automatic metadata structure creation
queryBuilder.MetadataStructure.AllowChildAutoItems = false;
// queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
MetadataFilterItem filter;
// Create a top-level folder containing all objects
MetadataStructureItem allObjects = new MetadataStructureItem();
allObjects.Caption = "All objects";
filter = allObjects.MetadataFilter.Add();
filter.ObjectTypes = MetadataType.All;
queryBuilder.MetadataStructure.Items.Add(allObjects);
// Create "Favorites" folder
MetadataStructureItem favorites = new MetadataStructureItem();
favorites.Caption = "Favorites";
queryBuilder.MetadataStructure.Items.Add(favorites);
MetadataItem metadataItem;
MetadataStructureItem item;
// Add some metadata objects to "Favorites" folder
metadataItem = queryBuilder.MetadataContainer.FindItem <MetadataItem>("Orders");
item = new MetadataStructureItem();
item.MetadataItem = metadataItem;
favorites.Items.Add(item);
metadataItem = queryBuilder.MetadataContainer.FindItem <MetadataItem>("Order Details");
item = new MetadataStructureItem();
item.MetadataItem = metadataItem;
favorites.Items.Add(item);
// Create folder with filter
MetadataStructureItem filteredFolder = new MetadataStructureItem(); // creates dynamic node
filteredFolder.Caption = "Filtered by 'Prod%'";
filter = filteredFolder.MetadataFilter.Add();
filter.ObjectTypes = MetadataType.Table | MetadataType.View;
filter.Object = "Prod%";
queryBuilder.MetadataStructure.Items.Add(filteredFolder);
queryBuilder.SQL = GetDefaultSql();
}
