/// <summary>
/// Creates and initializes new instance of the QueryBuilder object for the given identifier if it doesn't exist.
/// </summary>
/// <param name="name">Instance identifier of object in the current session.</param>
/// <returns></returns>
public ActionResult Create(string name)
{
// Get an instance of the QueryBuilder object
var qb = _aqbs.Get(name);
if (qb != null)
{
return(new EmptyResult());
}
try
{
// Create an instance of the QueryBuilder object
qb = _aqbs.Create(name);
// The necessary initialization procedures to setup SQL syntax and the source of metadata will be performed automatically
// according to directives in the special configuration section.
// This behavior is enabled by the AddJsonFile or AddXmlFile methods call in the Startup method in Startup.cs file.
// See qb.ConfiguredBy to get information about actual default settings
// Set default query
qb.SQL = GetDefaultSql();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
return(new EmptyResult());
}
private QueryBuilder CreateQueryBuilder()
{
// Create an instance of the QueryBuilder object
var queryBuilder = _aqbs.Create(InstanceId);
queryBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
// Denies metadata loading requests from the metadata provider
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from XML document.
var path = _config["NorthwindXmlMetaData"];
var xml = Path.Combine(_env.WebRootPath, path);
queryBuilder.DataSourceFieldRemoving += QueryBuilderOnDataSourceFieldRemoving;
queryBuilder.DataSourceRemoving += QueryBuilderOnDataSourceRemoving;
queryBuilder.SQLQuery.SQLUpdated += SqlQueryOnSqlUpdated;
queryBuilder.MetadataContainer.ImportFromXML(xml);
//Set default query
queryBuilder.SQL = GetDefaultSql();
return(queryBuilder);
}
private QueryBuilder CreateQueryBuilder()
{
// Create an instance of the QueryBuilder object
var queryBuilder = _aqbs.Create(instanceId);
queryBuilder.SyntaxProvider = new DB2SyntaxProvider();
// Turn displaying of alternate names on in the text of result SQL query
queryBuilder.SQLFormattingOptions.UseAltNames = true;
queryBuilder.SQLQuery.SQLUpdated += OnSQLUpdated;
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from XML document.
var path = _config["Db2XmlMetaData"];
var xml = Path.Combine(_env.WebRootPath, path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
//Set default query
queryBuilder.SQL = GetDefaultSql();
return(queryBuilder);
}
/// <summary>
/// Creates and initializes new instance of the QueryBuilder object if it doesn't exist.
/// </summary>
private void CreateQueryBuilder()
{
// Get an instance of the QueryBuilder object
var qb = _aqbs.Get(instanceId);
if (qb != null)
{
return;
}
// Create an instance of the QueryBuilder object
var queryBuilder = _aqbs.Create(instanceId);
queryBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
// Denies metadata loading requests from the metadata provider
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from XML document.
var path = _config["NorthwindXmlMetaData"];
var xml = Path.Combine(_env.WebRootPath, path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
//Set default query
queryBuilder.SQL = GetDefaultSql();
}
private QueryBuilder CreateQueryBuilder()
{
// Create an instance of the QueryBuilder object
var queryBuilder = _aqbs.Create(instanceId);
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;
// Denies metadata loading requests from the metadata provider
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
queryBuilder.BehaviorOptions.DeleteUnusedObjects = true;
queryBuilder.BehaviorOptions.AddLinkedObjects = true;
// Load MetaData from XML document.
var path = _config["NorthwindXmlMetaData"];
var xml = Path.Combine(_env.WebRootPath, path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
queryBuilder.SQL = GetDefaultSql();
return(queryBuilder);
}
/// <summary>
/// Creates and initializes new instance of the QueryBuilder object for the given identifier if it doesn't exist.
/// </summary>
/// <param name="name">Instance identifier of object in the current session.</param>
/// <returns></returns>
public ActionResult Create(string name)
{
// Get an instance of the QueryBuilder object
var qb = _aqbs.Get(name);
if (qb != null)
{
return(new EmptyResult());
}
try
{
// Create an instance of the QueryBuilder object
qb = _aqbs.Create(name);
// The necessary initialization procedures to setup SQL syntax and the source of metadata will be performed automatically
// according to directives in the special configuration section of 'Web.config' file.
// This behavior is enabled by the QueryBuilderStore.WebConfig() method call in the Application_Start method in Global.asax.cs file.
// See qb.ConfiguredBy to get information about actual default settings
// Set default query
qb.SQL = GetDefaultSql();
}
catch (Exception e)
{
return(new StatusCodeResult(500));
}
return(new EmptyResult());
}
/// <summary>
/// Creates and initializes a new instance of the QueryBuilder object.
/// </summary>
/// <returns>Returns instance of the QueryBuilder object.</returns>
private QueryBuilder CreateQueryBuilder()
{
// Define connection string here
var connectionString = "";
if (string.IsNullOrEmpty(connectionString))
{
throw new ApplicationException("Connection string is not set");
}
// Create an instance of the QueryBuilder object
var queryBuilder = _aqbs.Create(instanceId);
queryBuilder.SyntaxProvider = new AutoSyntaxProvider();
// Turn this property on to suppress parsing error messages when user types non-SELECT statements in the text editor.
queryBuilder.BehaviorOptions.AllowSleepMode = false;
// Bind Active Query Builder to a live database connection.
queryBuilder.MetadataProvider = new ODBCMetadataProvider
{
// Assign an instance of DBConnection object to the Connection property.
Connection = DataBaseHelper.CreateOdbcConnection(connectionString)
};
// Assign the initial SQL query text the user sees on the _first_ page load
queryBuilder.SQL = GetDefaultSql();
return(queryBuilder);
}
/// <summary>
/// Creates and initializes new instance of the QueryBuilder object for the given identifier if it doesn't exist.
/// </summary>
/// <param name="name">Instance identifier of object in the current session.</param>
/// <returns></returns>
public ActionResult CreateQueryBuilder(string name)
{
// Get an instance of the QueryBuilder object
var qb = _aqbs.Get(name);
if (qb != null)
{
return(new EmptyResult());
}
// Create an instance of the QueryBuilder object
var queryBuilder = _aqbs.Create(name);
queryBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
// Denies metadata loading requests from live database connection
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from XML document.
var path = _config["NorthwindXmlMetaData"];
var xml = Path.Combine(_env.WebRootPath, path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
//Set default query
queryBuilder.SQL = GetDefaultSql();
return(new EmptyResult());
}
private QueryBuilder CreateQueryBuilder()
{
// Create an instance of the QueryBuilder object
var queryBuilder = _aqbs.Create(instanceId);
queryBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
// Denies metadata loading requests from the metadata provider
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from XML document.
var path = _config["NorthwindXmlMetaData"];
var xml = Path.Combine(_env.WebRootPath, path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
//Comment these 2 lines for using browser localStorage
ImportUserQueriesFromFile(queryBuilder.UserQueries);
queryBuilder.UserQueries.Changed += ExportUserQueriesToFile;
//Set default query
queryBuilder.SQL = GetDefaultSql();
return(queryBuilder);
}
private QueryBuilder CreateQueryBuilder()
{
// Create an instance of the QueryBuilder object
var qb = _aqbs.Create(instanceId);
SetNorthwindXml(qb);
return(qb);
}
// Use IQueryBuilderService to get access to the server-side instances of Active Query Builder objects.
// See the registration of this service in the Startup.cs.
public ChangeConnectionController(IQueryBuilderService aqbs, IHostingEnvironment env, IConfiguration config)
{
_aqbs = aqbs;
_env = env;
_config = config;
if (_aqbs.Get(instanceId) == null)
{
_aqbs.Create(instanceId);
}
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = _aqbs.Get(instanceId);
if (qb == null)
{
qb = _aqbs.Create(instanceId);
qb.SyntaxProvider = new GenericSyntaxProvider();
}
return(View(qb));
}
/// <summary>
/// Creates and initializes a new instance of the QueryBuilder object.
/// </summary>
/// <returns>Returns instance of the QueryBuilder object.</returns>
private QueryBuilder CreateQueryBuilder()
{
// Define connection string here
//var connectionString = @"Driver={SQL Server};Server=(local);Uid=User;Pwd=Passw0rd;Database=Northwind;";
var connectionString = "";
if (string.IsNullOrEmpty(connectionString))
{
throw new ApplicationException("Connection string is not set");
}
// Create an instance of the QueryBuilder object
var queryBuilder = _aqbs.Create(instanceId);
queryBuilder.SyntaxProvider = new AutoSyntaxProvider();
// Turn this property on to suppress parsing error messages when user types non-SELECT statements in the text editor.
queryBuilder.BehaviorOptions.AllowSleepMode = false;
#region ODBC
// Bind Active Query Builder to a live database connection.
//queryBuilder.MetadataProvider = new ODBCMetadataProvider
//{
// // Assign an instance of DBConnection object to the Connection property.
// Connection = DataBaseHelper.CreateOdbcConnection(connectionString)
//};
#endregion
#region OleDb
// Bind Active Query Builder to a live database connection.
queryBuilder.MetadataProvider = new OLEDBMetadataProvider
{
// Assign an instance of DBConnection object to the Connection property.
Connection = DataBaseHelper.CreateMSAccessConnection("NorthwindDataBase")
};
#endregion
//Comment these 2 lines for using browser localStorage
ImportUserQueriesFromFile(queryBuilder.UserQueries);
queryBuilder.UserQueries.Changed += ExportUserQueriesToFile;
// Assign the initial SQL query text the user sees on the _first_ page load
//queryBuilder.SQL = GetDefaultSql();
return(queryBuilder);
}
private void CreateQueryBuilder(string AInstanceID)
{
// Create an instance of the QueryBuilder object.
var queryBuilder = _aqbs.Create(AInstanceID);
// Create an instance of the proper syntax provider for your database server.
queryBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
// Denies metadata loading requests from live database connection.
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from the pre-generated XML document.
var path = _config["NorthwindXmlMetaData"];
var xml = Path.Combine(_env.WebRootPath, path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
//Set default query.
queryBuilder.SQL = GetDefaultSql();
}
/// <summary>
/// Creates and initializes a new instance of the QueryBuilder object.
/// </summary>
/// <returns>Returns instance of the QueryBuilder object.</returns>
private QueryBuilder CreateQueryBuilder()
{
// Create an instance of the QueryBuilder object
var queryBuilder = _aqbs.Create(instanceId);
queryBuilder.SyntaxProvider = new SQLiteSyntaxProvider();
// Turn this property on to suppress parsing error messages when user types non-SELECT statements in the text editor.
queryBuilder.BehaviorOptions.AllowSleepMode = false;
// Bind Active Query Builder to a live database connection.
queryBuilder.MetadataProvider = new SQLiteMetadataProvider
{
// Assign an instance of DBConnection object to the Connection property.
Connection = DataBaseHelper.CreateSqLiteConnection(Path.Combine(_env.WebRootPath, _config["SqLiteDataBase"]))
};
// Assign the initial SQL query text the user sees on the _first_ page load
queryBuilder.SQL = GetDefaultSql();
return(queryBuilder);
}
/// <summary>
/// Creates and initializes new instance of the QueryBuilder object for the given identifier if it doesn't exist.
/// </summary>
/// <param name="name">Instance identifier of object in the current session.</param>
/// <returns></returns>
public ActionResult CreateQueryBuilder(string name)
{
// Get an instance of the QueryBuilder object
var qb = _aqbs.Get(name);
if (qb != null)
{
return(StatusCode(200));
}
try
{
// Create an instance of the QueryBuilder object
_aqbs.Create(name);
return(StatusCode(200));
}
catch (QueryBuilderException e)
{
return(StatusCode(400, e.Message));
}
}
private QueryBuilder CreateQueryBuilder()
{
// Get instance of QueryBuilder
var queryBuilder = _aqbs.Create(instanceId);
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();
return(queryBuilder);
}
private QueryBuilder CreateQueryBuilder()
{
// Create an instance of the QueryBuilder object
var queryBuilder = _aqbs.Create(instanceId);
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;
// Denies metadata loading requests from the metadata provider
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from XML document.
var path = _config["NorthwindXmlMetaData"];
var xml = Path.Combine(_env.WebRootPath, path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
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("CustomerCompanyName");
f.Expression = "(select c.CompanyName from Customers c where c.CustomerId = orders.CustomerId)";
// Virtual object (table) with virtual fields
// ===========================================================================
o = queryBuilder.MetadataContainer.AddTable("MyOrders");
o.Expression = "Orders";
// first test field - simple expression
f = o.AddField("OrderId_plus_1");
f.Expression = "MyOrders.OrderId + 1";
// second test field - correlated sub-query
f = o.AddField("CustomerCompanyName");
f.Expression = "(select c.CompanyName from Customers c where c.CustomerId = MyOrders.CustomerId)";
// Virtual object (sub-query) with virtual fields
// ===========================================================================
o = queryBuilder.MetadataContainer.AddTable("MyBetterOrders");
o.Expression = "(select OrderId, CustomerId, OrderDate from Orders)";
// first test field - simple expression
f = o.AddField("OrderId_plus_1");
f.Expression = "MyBetterOrders.OrderId + 1";
// second test field - correlated sub-query
f = o.AddField("CustomerCompanyName");
f.Expression = "(select c.CompanyName from Customers c where c.CustomerId = MyBetterOrders.CustomerId)";
queryBuilder.SQLQuery.SQLUpdated += OnSQLUpdated;
queryBuilder.SQL = "SELECT mbo.OrderId_plus_1, mbo.CustomerCompanyName FROM MyBetterOrders mbo";
return(queryBuilder);
}