private DataSource AddTable(UnionSubQuery unionSubQuery, string name, string alias)
{
var queryBuilder1 = _aqbs.Get(instanceId);
using (var parsedName = queryBuilder1.SQLContext.ParseQualifiedName(name))
using (var parsedAlias = queryBuilder1.SQLContext.ParseIdentifier(alias))
{
return(queryBuilder1.SQLQuery.AddObject(unionSubQuery, parsedName, parsedAlias));
}
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = _aqbs.Get(instanceId);
if (qb == null)
{
qb = CreateQueryBuilder();
}
return(View(qb));
}
//////////////////////////////////////////////////////////////////////////
/// 1st way:
/// This method demonstrates the direct access to the internal metadata
/// objects collection (MetadataContainer).
//////////////////////////////////////////////////////////////////////////
public void Way1()
{
var queryBuilder1 = _aqbs.Get(instanceId);
ResetQueryBuilderMetadata(queryBuilder1);
queryBuilder1.SyntaxProvider = new GenericSyntaxProvider();
// prevent QueryBuilder to request metadata
queryBuilder1.MetadataLoadingOptions.OfflineMode = true;
queryBuilder1.MetadataProvider = null;
MetadataContainer metadataContainer = queryBuilder1.MetadataContainer;
metadataContainer.BeginUpdate();
try
{
metadataContainer.Clear();
MetadataNamespace schemaDbo = metadataContainer.AddSchema("dbo");
// prepare metadata for table "Orders"
MetadataObject orders = schemaDbo.AddTable("Orders");
// fields
orders.AddField("OrderId");
orders.AddField("CustomerId");
// prepare metadata for table "Order Details"
MetadataObject orderDetails = schemaDbo.AddTable("Order Details");
// fields
orderDetails.AddField("OrderId");
orderDetails.AddField("ProductId");
// foreign keys
MetadataForeignKey foreignKey = orderDetails.AddForeignKey("OrderDetailsToOrders");
using (MetadataQualifiedName referencedName = new MetadataQualifiedName())
{
referencedName.Add("Orders");
referencedName.Add("dbo");
foreignKey.ReferencedObjectName = referencedName;
}
foreignKey.Fields.Add("OrderId");
foreignKey.ReferencedFields.Add("OrderId");
}
finally
{
metadataContainer.EndUpdate();
}
queryBuilder1.MetadataStructure.Refresh();
}
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 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());
}
public ActionResult SelectRecordsCount(Param[] _params)
{
var qb = _aqbs.Get(instanceId);
var qt = _qts.Get(instanceId);
var qtForSelectRecordsCount = new QueryTransformer {
QueryProvider = qb.SQLQuery
};
try
{
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
try
{
var data = Execute(qtForSelectRecordsCount, _params);
return(Json(data.First().Values.First()));
}
catch (Exception e)
{
return(Json(new ErrorOutput {
Error = e.Message
}));
}
}
finally
{
qtForSelectRecordsCount.Dispose();
}
}
/// <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();
}
/// <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 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());
}
public ActionResult GetData()
{
var qb = _aqbs.Get(instanceId);
var conn = qb.MetadataProvider.Connection;
var sqlQuery = new SQLQuery(qb.SQLContext)
{
SQL = qb.ActiveUnionSubQuery.SQL
};
QueryTransformer qt = new QueryTransformer
{
QueryProvider = sqlQuery
};
qt.Take("7");
var columns = qt.Columns.Select(c => c.ResultName).ToList();
try
{
var data = DataBaseHelper.GetDataList(conn, qt.SQL);
var result = new { columns, data };
return(Json(result));
}
catch (Exception e)
{
var result = new { columns, data = new List <List <object> > {
new List <object> {
e.Message
}
} };
return(Json(result));
}
}
// GET
public IActionResult Index()
{
if (_aqbs.Get(InstanceID) == null)
{
CreateQueryBuilder(InstanceID);
}
return(View());
}
public ActionResult Index()
{
// We've redefined the QueryBuilderStore.Provider object to be of QueryBuilderSqliteStoreProvider class in the Global.asax.cs file.
// The implementation of Get method in this provider gets _OR_creates_new_ QueryBuilder object.
// The initialization of the QueryBuilder object is also internally made by the QueryBuilderSqliteStoreProvider.
var qb = _aqbs.Get();
return(View(qb));
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = _aqbs.Get(instanceId);
var qt = _qts.Get(instanceId);
if (qb == null)
{
qb = CreateQueryBuilder();
}
if (qt == null)
{
qt = CreateQueryTransformer(qb.SQLQuery);
}
ViewBag.QueryTransformer = qt;
return(View(qb));
}
// GET
public IActionResult Index()
{
var qb = _aqbs.Get("HtmlHelpers");
if (qb == null)
{
qb = CreateQueryBuilder();
}
return(View(qb));
}
private void SqlQueryOnSqlUpdated(object sender, EventArgs e)
{
var qb = _aqbs.Get(InstanceId);
if (qb == null)
{
return;
}
var isRequiredPresent = qb.SQLQuery.QueryRoot.Items.OfType <UnionSubQuery>().Any(IsRequiredItemsPresent);
if (!isRequiredPresent)
{
qb.Message.Warning("Required table and field is not present in the query.");
}
else
{
qb.Message.Clear();
}
}
// 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);
}
}
private QueryBuilder GetOrCreateQueryBuilder()
{
// Get an instance of the QueryBuilder object
var qb = _aqbs.Get(instanceId);
if (qb == null)
{
qb = CreateQueryBuilder();
}
return(qb);
}
public ActionResult Toggle()
{
// Get an instance of the QueryBuilder object
var qb = _aqbs.Get(instanceId);
qb.SQLFormattingOptions.UseAltNames = !qb.SQLFormattingOptions.UseAltNames;
// Reload metadata structure to refill it with real or alternate names.
// Note: reloading the structure does not reload the metadata container.
qb.MetadataStructure.Refresh();
return(new EmptyResult());
}
/// <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)
{
try
{
// Create an instance of the QueryBuilder object
_aqbs.Get(name);
return(StatusCode(200));
}
catch (QueryBuilderException e)
{
return(StatusCode(400, e.Message));
}
}
public QueryTransformer Get(string id)
{
var qt = new QueryTransformer {
Tag = id, QueryProvider = _aqbs.Get(id).SQLQuery
};
var state = GetState(id);
if (state != null)
{
qt.XML = state;
}
return(qt);
}
public void LoadQuery(string query)
{
var qb = _aqbs.Get(instanceId);
if (query == "artist")
{
qb.SQL = "Select artists.ArtistId, artists.Name From artists";
}
else
{
qb.SQL = "Select tracks.TrackId, tracks.Name From tracks";
}
_aqbs.Put(qb);
}
public void Change(string name)
{
var queryBuilder = _aqbs.Get(instanceId);
queryBuilder.SQLQuery.Clear();
queryBuilder.MetadataContainer.Clear();
if (name == "NorthwindXmlMetaData")
{
SetNorthwindXml(queryBuilder);
}
else
{
SetDb2Xml(queryBuilder);
}
}
public void OnSQLUpdated(object sender, EventArgs e)
{
var queryBuilder = _aqbs.Get(instanceId);
var data = new ExchangeClass();
data.Statistics = GetQueryStatistic(queryBuilder.QueryStatistics);
data.SubQueries = DumpSubQueries(queryBuilder);
data.QueryStructure = DumpQueryStructureInfo(queryBuilder.ActiveSubQuery);
data.UnionSubQuery = new UnionSubQueryExchangeClass();
data.UnionSubQuery.SelectedExpressions = DumpSelectedExpressionsInfoFromUnionSubQuery(queryBuilder.ActiveUnionSubQuery);
data.UnionSubQuery.DataSources = DumpDataSourcesInfoFromUnionSubQuery(queryBuilder.ActiveUnionSubQuery);
;
data.UnionSubQuery.Links = DumpLinksInfoFromUnionSubQuery(queryBuilder.ActiveUnionSubQuery);
data.UnionSubQuery.Where = GetWhereInfo(queryBuilder.ActiveUnionSubQuery);
queryBuilder.ExchangeData = data;
}
public QueryBuilder ChangeConnection(string name)
{
var queryBuilder = _aqbs.Get(instanceId);
queryBuilder.MetadataContainer.Clear();
if (name == "NorthwindXmlMetaData")
{
SetNorthwindXml(queryBuilder);
}
else if (name == "SQLite")
{
SetSqLite(queryBuilder);
}
else
{
SetDb2Xml(queryBuilder);
}
return(queryBuilder);
}
public ActionResult SelectRecordsCount([FromBody] Param[] _params)
{
var qb = _aqbs.Get(instanceId);
var qt = _qts.Get(instanceId);
var qtForSelectRecordsCount = new QueryTransformer {
QueryProvider = qb.SQLQuery
};
try
{
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
return(GetData(qtForSelectRecordsCount, _params));
}
finally
{
qtForSelectRecordsCount.Dispose();
}
}
public void OnSQLUpdated(object sender, EventArgs e)
{
var qb = _aqbs.Get(instanceId);
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 GetUserQueriesXml()
{
var qb = _aqbs.Get(instanceId);
qb.UserQueries.ExportToXML(Response.Body);
}