protected void Page_Load(object sender, EventArgs e)
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("QueryResults");
var qt = QueryTransformerStore.Get("QueryResults");
if (qb == null)
{
qb = CreateQueryBuilder();
}
if (qt == null)
{
qt = CreateQueryTransformer(qb.SQLQuery);
}
QueryBuilderControl1.QueryBuilder = qb;
ObjectTreeView1.QueryBuilder = qb;
Canvas1.QueryBuilder = qb;
Grid1.QueryBuilder = qb;
SubQueryNavigationBar1.QueryBuilder = qb;
SqlEditor1.QueryBuilder = qb;
StatusBar1.QueryBuilder = qb;
CriteriaBuilder1.QueryTransformer = qt;
}
/// <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 = QueryBuilderStore.Get(InstanceId);
if (qb != null)
{
return;
}
// Create an instance of the QueryBuilder object
var queryBuilder = QueryBuilderStore.Factory.MsSql(InstanceId);
// Denies metadata loading requests from the metadata provider
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from XML document. File name is stored in the "Web.config" file in [/configuration/appSettings/NorthwindXmlMetaData] key
var path = ConfigurationManager.AppSettings["NorthwindXmlMetaData"];
var xml = Path.Combine(Server.MapPath("~"), path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
//Set default query
queryBuilder.SQL = GetDefaultSql();
}
protected void Page_Load(object sender, EventArgs e)
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("QueryModification");
if (qb == null)
{
qb = CreateQueryBuilder();
}
QueryBuilderControl1.QueryBuilder = qb;
ObjectTreeView1.QueryBuilder = qb;
Canvas1.QueryBuilder = qb;
Grid1.QueryBuilder = qb;
SubQueryNavigationBar1.QueryBuilder = qb;
SqlEditor1.QueryBuilder = qb;
StatusBar1.QueryBuilder = qb;
cbCompanyName.Enabled = cbCustomers.Checked;
tbCompanyName.Enabled = cbCompanyName.Checked;
cbOrderDate.Enabled = cbOrders.Checked;
tbOrderDate.Enabled = cbOrderDate.Checked;
//prepare parsed names
_joinFieldName = qb.SQLContext.ParseQualifiedName(CusomerId);
_companyNameFieldName = qb.SQLContext.ParseQualifiedName(CustomersCompanyName);
_orderDateFieldName = qb.SQLContext.ParseQualifiedName(OrderDate);
}
//////////////////////////////////////////////////////////////////////////
/// 3rd way:
///
/// This method demonstrates loading of metadata through .NET data providers
/// unsupported by our QueryBuilder component. If such data provider is able
/// to execute SQL queries, you can use our EventMetadataProvider with handling
/// it's ExecSQL event. In this event the EventMetadataProvider will provide
/// you SQL queries it use for the metadata retrieval. You have to execute
/// a query and return resulting data reader object.
///
/// Note: In this sample we are using SQLiteSyntaxProvider. You have to use specific syntax providers in your application,
/// e.g. MySQLSyntaxProver, OracleSyntaxProvider, etc.
//////////////////////////////////////////////////////////////////////////
public void Way3(object sender, EventArgs e)
{
ActiveTabs = 2;
var queryBuilder1 = QueryBuilderStore.Get("LoadMetadata");
try
{
_conn.Close();
_conn.Open();
// allow QueryBuilder to request metadata
queryBuilder1.MetadataLoadingOptions.OfflineMode = false;
ResetQueryBuilderMetadata(queryBuilder1);
queryBuilder1.SyntaxProvider = new SQLiteSyntaxProvider();
queryBuilder1.MetadataProvider = new EventMetadataProvider();
((EventMetadataProvider)queryBuilder1.MetadataProvider).ExecSQL += way3EventMetadataProvider_ExecSQL;
queryBuilder1.MetadataStructure.Refresh();
}
catch (Exception ex)
{
queryBuilder1.Message.Error(ex.Message);
}
}
public ChangeConnectionController()
{
if (QueryBuilderStore.Get(_instanceId) == null)
{
QueryBuilderStore.Create(_instanceId);
}
}
public ActionResult SelectRecordsCount(Param[] _params)
{
var qb = QueryBuilderStore.Get(instanceId);
var qt = QueryTransformerStore.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(), JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
return(Json(new ErrorOutput {
Error = e.Message
}, JsonRequestBehavior.AllowGet));
}
}
finally
{
qtForSelectRecordsCount.Dispose();
}
}
/// <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 = QueryBuilderStore.Get(name);
if (qb != null)
{
return(new EmptyResult());
}
// Create an instance of the QueryBuilder object
var queryBuilder = QueryBuilderStore.Factory.MsSql(name);
// Denies metadata loading requests from live database connection
queryBuilder.MetadataLoadingOptions.OfflineMode = true;
// Load MetaData from XML document. File name is stored in the "Web.config" file in [/configuration/appSettings/NorthwindXmlMetaData] key
var path = ConfigurationManager.AppSettings["NorthwindXmlMetaData"];
var xml = Path.Combine(Server.MapPath("~"), path);
queryBuilder.MetadataContainer.ImportFromXML(xml);
//Set default query
queryBuilder.SQL = GetDefaultSql();
return(new EmptyResult());
}
public ActionResult GetData()
{
var qb = QueryBuilderStore.Get(Name);
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, JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
var result = new { columns, data = new List <List <object> > {
new List <object> {
e.Message
}
} };
return(Json(result, JsonRequestBehavior.AllowGet));
}
}
/// <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 = QueryBuilderStore.Get(name);
if (qb != null)
{
return(new EmptyResult());
}
try
{
// Create an instance of the QueryBuilder object
qb = QueryBuilderStore.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 HttpStatusCodeResult(500, e.Message));
}
return(new EmptyResult());
}
// GET
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 = QueryBuilderStore.Get();
return(View(qb));
}
private DataSource AddTable(UnionSubQuery unionSubQuery, string name, string alias)
{
var queryBuilder1 = QueryBuilderStore.Get(InstanceId);
using (var parsedName = queryBuilder1.SQLContext.ParseQualifiedName(name))
using (var parsedAlias = queryBuilder1.SQLContext.ParseIdentifier(alias))
{
return queryBuilder1.SQLQuery.AddObject(unionSubQuery, parsedName, parsedAlias);
}
}
//////////////////////////////////////////////////////////////////////////
/// 1st way:
/// This method demonstrates the direct access to the internal metadata
/// objects collection (MetadataContainer).
//////////////////////////////////////////////////////////////////////////
public void Way1(object sender, EventArgs e)
{
ActiveTabs = 0;
var queryBuilder1 = QueryBuilderStore.Get("LoadMetadata");
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();
}
private bool IsMainSubQuery()
{
var qb = QueryBuilderStore.Get(InstanceId);
if (qb == null)
{
return(false);
}
return(qb.ActiveSubQuery == qb.SQLQuery.QueryRoot);
}
public void Toggle()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("ToggleUseAltNames");
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();
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("BootstrapTheming");
if (qb == null)
{
qb = CreateQueryBuilder();
}
return(View(qb));
}
//////////////////////////////////////////////////////////////////////////
/// 2rd way:
/// This method demonstrates on-demand manual filling of metadata structure using
/// corresponding MetadataContainer.ItemMetadataLoading event
//////////////////////////////////////////////////////////////////////////
public void Way2()
{
var queryBuilder1 = QueryBuilderStore.Get(InstanceId);
ResetQueryBuilderMetadata(queryBuilder1);
// allow QueryBuilder to request metadata
queryBuilder1.MetadataLoadingOptions.OfflineMode = false;
queryBuilder1.MetadataProvider = null;
queryBuilder1.MetadataContainer.ItemMetadataLoading += way2ItemMetadataLoading;
queryBuilder1.MetadataStructure.Refresh();
}
private QueryBuilder GetOrCreateQueryBuilder()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get(qbId);
if (qb == null)
{
qb = CreateQueryBuilder(qbId);
}
return(qb);
}
public ActionResult WithPartiaView()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get(_instanceId);
if (qb == null)
{
qb = CreateQueryBuilder();
}
return(View(qb));
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("UserDefinedFields");
if (qb == null)
{
qb = CreateQueryBuilder();
}
return(View(qb));
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("ClientEventHandle");
if (qb == null)
{
qb = CreateQueryBuilder();
}
return(View(qb));
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("MetadataStructure");
if (qb == null)
{
qb = CreateQueryBuilder();
}
return(View(qb));
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("AlternateNames");
if (qb == null)
{
qb = CreateQueryBuilder();
}
return(View(qb));
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("LoadMetadata");
if (qb == null)
{
qb = QueryBuilderStore.Create("LoadMetadata");
qb.SyntaxProvider = new GenericSyntaxProvider();
}
return(View(qb));
}
private DataSource FindTableInQueryByName(UnionSubQuery unionSubQuery, string name)
{
var queryBuilder1 = QueryBuilderStore.Get(InstanceId);
List<DataSourceObject> foundDatasources;
using (var qualifiedName = queryBuilder1.SQLContext.ParseQualifiedName(name))
{
foundDatasources = new List<DataSourceObject>();
unionSubQuery.FromClause.FindTablesByDbName(qualifiedName, foundDatasources);
}
// if found more than one tables with given name in the query, use the first one
return foundDatasources.Count > 0 ? foundDatasources[0] : null;
}
//////////////////////////////////////////////////////////////////////////
/// 2rd way:
/// This method demonstrates on-demand manual filling of metadata structure using
/// corresponding MetadataContainer.ItemMetadataLoading event
//////////////////////////////////////////////////////////////////////////
public void Way2(object sender, EventArgs e)
{
ActiveTabs = 1;
var queryBuilder1 = QueryBuilderStore.Get("LoadMetadata");
ResetQueryBuilderMetadata(queryBuilder1);
// allow QueryBuilder to request metadata
queryBuilder1.MetadataLoadingOptions.OfflineMode = false;
queryBuilder1.MetadataProvider = null;
queryBuilder1.MetadataContainer.ItemMetadataLoading += way2ItemMetadataLoading;
queryBuilder1.MetadataStructure.Refresh();
}
public void LoadQuery(string query)
{
var qb = QueryBuilderStore.Get(instanceId);
if (query == "artist")
{
qb.SQL = "Select artists.ArtistId, artists.Name From artists";
}
else
{
qb.SQL = "Select tracks.TrackId, tracks.Name From tracks";
}
QueryBuilderStore.Put(qb);
}
public void Change(string name)
{
var queryBuilder = QueryBuilderStore.Get(_instanceId);
queryBuilder.SQLQuery.Clear();
queryBuilder.MetadataContainer.Clear();
if (name == "NorthwindXmlMetaData")
{
SetNorthwindXml(queryBuilder);
}
else
{
SetDb2Xml(queryBuilder);
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get("NoDesignArea");
if (qb == null)
{
qb = CreateQueryBuilder();
}
QueryBuilderControl1.QueryBuilder = qb;
ObjectTreeView1.QueryBuilder = qb;
Grid1.QueryBuilder = qb;
SubQueryNavigationBar1.QueryBuilder = qb;
StatusBar1.QueryBuilder = qb;
SqlEditor1.QueryBuilder = qb;
}
public QueryTransformer Get(string id)
{
var qt = new QueryTransformer {
Tag = id
};
qt.QueryProvider = QueryBuilderStore.Get(id).SQLQuery;
var state = GetState(id);
if (state != null)
{
qt.XML = state;
}
return(qt);
}
protected void UpdateDataGrid(object sender, EventArgs e)
{
var qb = QueryBuilderStore.Get("QueryResults");
var qt = QueryTransformerStore.Get("QueryResults");
if (string.IsNullOrEmpty(qt.SQL))
{
GridView1.DataSource = null;
GridView1.DataBind();
aspPage.Text = "Page: 0";
recordsCount.Text = "Records count: 0";
return;
}
UpdateRecordCount(qt);
if (_page < 0)
{
_page = 0;
}
if (10 * _page > _recordsCount)
{
_page -= 1;
}
qt.Skip((10 * _page).ToString());
qt.Take("10");
using (var conn = DataBaseHelper.CreateSqLiteConnection("SqLiteDataBase"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = qt.SQL;
var reader = cmd.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();
}
}
aspPage.Text = "Page: " + _page;
}
