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;
}
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 a new instance of the QueryTransformer object.
/// </summary>
/// <param name="query">SQL Query to transform.</param>
/// <returns>Returns instance of the QueryTransformer object.</returns>
private QueryTransformer CreateQueryTransformer(SQLQuery query)
{
var qt = QueryTransformerStore.Create(instanceId);
qt.QueryProvider = query;
qt.AlwaysExpandColumnsInQuery = true;
return(qt);
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.GetOrCreate(instanceId, InitializeQueryBuilder);
var qt = QueryTransformerStore.GetOrCreate(instanceId, q =>
{
q.QueryProvider = qb.SQLQuery;
q.AlwaysExpandColumnsInQuery = true;
});
ViewBag.QueryTransformer = qt;
return(View(qb));
}
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;
}
protected void Page_Load(object sender, EventArgs e)
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.GetOrCreate(InstanceId, InitializeQueryBuilder);
var qt = QueryTransformerStore.GetOrCreate(InstanceId, q =>
{
q.QueryProvider = qb.SQLQuery;
q.AlwaysExpandColumnsInQuery = true;
});
QueryBuilderControl1.QueryBuilder = qb;
ObjectTreeView1.QueryBuilder = qb;
Canvas1.QueryBuilder = qb;
Grid1.QueryBuilder = qb;
SubQueryNavigationBar1.QueryBuilder = qb;
SqlEditor1.QueryBuilder = qb;
StatusBar1.QueryBuilder = qb;
CriteriaBuilder1.QueryTransformer = qt;
}
public ActionResult Index()
{
// Get an instance of the QueryBuilder object
var qb = QueryBuilderStore.Get(instanceId);
var qt = QueryTransformerStore.Get(instanceId);
if (qb == null)
{
qb = CreateQueryBuilder();
}
if (qt == null)
{
qt = CreateQueryTransformer(qb.SQLQuery);
}
ViewBag.QueryTransformer = qt;
return(View(qb));
}
private void UpdateRecordCount(QueryTransformer qt)
{
var qtForSelectRecordsCount = QueryTransformerStore.Create("QueryResults_for_select_records_count");
qtForSelectRecordsCount.QueryProvider = qt.QueryProvider;
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
try
{
var data = SelectRecordsCount.GetData(qtForSelectRecordsCount, new Param[0]);
_recordsCount = int.Parse(data.First().Values.First().ToString());
recordsCount.Text = "Records count: " + _recordsCount;
}
finally
{
QueryTransformerStore.Remove("QueryResults_for_select_records_count");
}
}
public override object GetDataForModel(Param[] _params)
{
var qb = QueryBuilderStore.Get("QueryResults");
var qt = QueryTransformerStore.Get("QueryResults");
var qtForSelectRecordsCount = QueryTransformerStore.Create("QueryResults_for_select_records_count");
qtForSelectRecordsCount.QueryProvider = qb.SQLQuery;
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
try
{
var data = GetData(qtForSelectRecordsCount, _params);
return(data.First().Values.First());
}
finally
{
QueryTransformerStore.Remove("QueryResults_for_select_records_count");
}
}
public override object GetDataForModel(Param[] _params)
{
var qb = QueryBuilderStore.Get("QueryResults");
var qt = QueryTransformerStore.Get("QueryResults");
var qtForSelectRecordsCount = new QueryTransformer {
QueryProvider = qt.QueryProvider
};
try
{
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
var data = GetData(qtForSelectRecordsCount, _params);
return(data.First().Values.First());
}
finally
{
qtForSelectRecordsCount.Dispose();
}
}
public ActionResult GetData(GridModel m)
{
var qt = QueryTransformerStore.Get(instanceId);
qt.Skip((m.Pagenum * m.Pagesize).ToString());
qt.Take(m.Pagesize == 0 ? "" : m.Pagesize.ToString());
if (!string.IsNullOrEmpty(m.Sortdatafield))
{
qt.Sortings.Clear();
if (!string.IsNullOrEmpty(m.Sortorder))
{
var c = qt.Columns.FindColumnByResultName(m.Sortdatafield);
if (c != null)
{
qt.OrderBy(c, m.Sortorder.ToLower() == "asc");
}
}
}
return(GetData(qt, m.Params));
}
//CUT:PRO{{
public void DisposeStates()
{
QueryBuilderStore.Remove();
QueryTransformerStore.Remove();
}
private void OnPreLoad(object sender, EventArgs eventArgs)
{
QueryBuilderStore.Remove();
QueryTransformerStore.Remove();
}
