/// <summary>
/// Run the application.
/// </summary>
public override BoolMessageItem Execute()
{
var query = new NamedQuery();
query.Name = "Top 10 blog posts";
query.Description = "Popular blog posts";
query.ItemType = "text";
query.Roles = "admin";
query.Sql = "select top 10 from blogposts order by AvgRating desc";
query.AddParameters(new List <NamedQueryParam>()
{
new NamedQueryParam()
{
Name = "Count", DefaultValue = "10", IsRequired = false, Typ = typeof(int).Name
}
});
NamedQuery.Create(query);
// To Run the query and get the results table.
// TO_DO: Not implemented yet.
// NamedQuery.Execute("top 10 blog posts");
return(BoolMessageItem.True);
}
protected void lbtnOpen_Click(object sender, EventArgs e)
{
string queryName = ((LinkButton)sender).CommandArgument;
NamedQuery namedQuery = TheNamedQueryMgr.LoadNamedQuery(this.CurrentUser, queryName);
if (namedQuery == null)
{
this.ShowErrorMessage("MasterData.NamedQuery.QueryNameNotExist", queryName);
}
else
{
string url = "~/Main.aspx?mid=" + namedQuery.UserControlPath
+ "__mp--" + namedQuery.ModuleParameter;
//+ "__act--" + BusinessConstants.PAGE_LIST_ACTION;
this.Session["ACT"] = BusinessConstants.PAGE_LIST_ACTION;
if (namedQuery.ActionParameter != null && namedQuery.ActionParameter.Trim() != string.Empty)
{
this.Session["AP"] = namedQuery.ActionParameter;
//url += "__ap--" + namedQuery.ActionParameter;
}
this.Page.Response.Redirect(url);
}
}
/// <summary>
/// Configure the ActiveRecord for Accounts
/// NOTES: - All service objects can be constructed with a repository, validator, settings object.
/// - All service objects can be constructed with a fake repository ( in memory ) for testing.
/// </summary>
/// <param name="context"></param>
public override void Init(object context)
{
string columnsToIndex = "Id,UserName,UserNameLowered,Email,EmailLowered,Password";
IRepository <NamedQuery> repository = new RepositoryInMemory <NamedQuery>(columnsToIndex);
NamedQuery.Init(() => repository, () => new NamedQueryValidator(), new EntitySettings <NamedQuery>(), false, null);
}
public bool SubqueryIsWithMainQuery(NamedQuery subquery)
{
// FromQuery or decorrelated subqueries are merged with main plan
var r = (fromQueries_.ContainsKey(subquery) ||
decorrelatedSubs_.Contains(subquery));
return(r);
}
public Row(
NamedQuery namedQuery,
IEnumerable <NamedQuery> columns,
IEnumerable <DataModelIssue> beginIssues,
IEnumerable <DataModelIssue> middleIssues,
IEnumerable <DataModelIssue> endIssues)
: base(namedQuery.Name, namedQuery.Query)
{
InitializeColumns(columns, beginIssues, middleIssues, endIssues);
}
private static int GetCustomersViaSpRaw(ISisoDatabase database, int customerNoFrom, int customerNoTo)
{
using (var session = database.BeginSession())
{
var q = new NamedQuery("CustomersViaSP");
q.Add(new DacParameter("p0", customerNoFrom));
q.Add(new DacParameter("p1", customerNoTo));
q.Add(new DacParameter("p2", "The delivery street #544"));
return(session.Advanced.NamedQuery <Customer>(q).ToArray().Length);
}
}
// from clause -
// pair each from item with cross join, their join conditions will be handled
// with where clause processing.
//
LogicNode transformFromClause()
{
LogicNode transformOneFrom(TableRef tab)
{
LogicNode from;
switch (tab)
{
case BaseTableRef bref:
if (bref.Table().source_ == TableDef.TableSource.Table)
{
from = new LogicScanTable(bref);
}
else
{
from = new LogicScanStream(bref);
}
if (bref.tableSample_ != null)
{
from = new LogicSampleScan(from, bref.tableSample_);
}
break;
case ExternalTableRef eref:
from = new LogicScanFile(eref);
break;
case CTEQueryRef cref:
if (this is CteSelectStmt css && css.CteId() == cref.cte_.cteId_)
{
cref.query_.cteInfo_ = cteInfo_;
var ctePlan = cref.query_.CreatePlan();
string alias = null;
NamedQuery key;
alias = cref.alias_;
key = new NamedQuery(cref.query_, alias, NamedQuery.QueryType.FROM);
from = new LogicFromQuery(cref, ctePlan);
subQueries_.Add(key);
if (!fromQueries_.ContainsKey(key))
{
fromQueries_.Add(key, from as LogicFromQuery);
}
}
else
{
from = new LogicCteConsumer(cteInfo_.GetCteInfoEntryByCteId(cref.cte_.cteId_), cref);
}
break;
public virtual IEnumerable <T> NamedQuery <T>(string name, Expression <Func <T, bool> > predicate) where T : class
{
return(Try(() =>
{
var queryBuilder = Db.ProviderFactory.GetQueryBuilder <T>(Db.StructureSchemas);
queryBuilder.Where(predicate);
var query = queryBuilder.Build();
var sqlExpression = SqlExpressionBuilder.Process(query);
var namedQuery = new NamedQuery(name);
namedQuery.Add(sqlExpression.WhereCriteria.Parameters);
return OnNamedQueryAs <T, T>(namedQuery);
}));
}
protected void SaveNamedQuery(string queryName, IDictionary <string, string> searchParameters)
{
NamedQuery namedQuery = new NamedQuery();
namedQuery.User = this.CurrentUser;
namedQuery.QueryName = queryName;
string mid = this.Page.Request.QueryString["mid"];
string[] segment = Regex.Split(mid, "__", RegexOptions.IgnoreCase);
namedQuery.UserControlPath = segment[0];
for (int i = 1; i < segment.Length; i++)
{
if (segment[i].Substring(0, segment[i].IndexOf("--")) == "mp")
{
namedQuery.ModuleParameter = segment[i].Substring(segment[i].IndexOf("--") + 2);
break;
}
}
if (searchParameters != null && searchParameters.Count > 0)
{
foreach (string key in searchParameters.Keys)
{
if (namedQuery.ActionParameter == null)
{
namedQuery.ActionParameter = key + "-" + searchParameters[key];
}
else
{
namedQuery.ActionParameter += "_" + key + "-" + searchParameters[key];
}
}
}
try
{
this.TheNamedQueryMgr.CreateNamedQuery(namedQuery);
this.ShowSuccessMessage("Common.Message.NamedQuery.SaveSuccessful", queryName);
}
catch (Exception ex)
{
this.ShowErrorMessage("Common.Message.NamedQuery.Failed", queryName);
}
}
protected void SaveNamedQuery(string queryName, IDictionary<string, string> searchParameters)
{
NamedQuery namedQuery = new NamedQuery();
namedQuery.User = this.CurrentUser;
namedQuery.QueryName = queryName;
string mid = this.Page.Request.QueryString["mid"];
string[] segment = Regex.Split(mid, "__", RegexOptions.IgnoreCase);
namedQuery.UserControlPath = segment[0];
for (int i = 1; i < segment.Length; i++)
{
if (segment[i].Substring(0, segment[i].IndexOf("--")) == "mp")
{
namedQuery.ModuleParameter = segment[i].Substring(segment[i].IndexOf("--") + 2);
break;
}
}
if (searchParameters != null && searchParameters.Count > 0)
{
foreach(string key in searchParameters.Keys)
{
if (namedQuery.ActionParameter == null)
{
namedQuery.ActionParameter = key + "-" + searchParameters[key];
}
else
{
namedQuery.ActionParameter += "_" + key + "-" + searchParameters[key];
}
}
}
try
{
this.TheNamedQueryMgr.CreateNamedQuery(namedQuery);
this.ShowSuccessMessage("Common.Message.NamedQuery.SaveSuccessful", queryName);
}
catch (Exception ex)
{
this.ShowErrorMessage("Common.Message.NamedQuery.Failed", queryName);
}
}
public virtual void UpdateNamedQuery(NamedQuery entity)
{
entityDao.UpdateNamedQuery(entity);
}
public virtual void DeleteNamedQuery(NamedQuery entity)
{
Delete(entity);
}
public virtual void UpdateNamedQuery(NamedQuery entity)
{
Update(entity);
}
public virtual void DeleteNamedQuery(NamedQuery entity)
{
Delete(entity);
}
public virtual void CreateNamedQuery(NamedQuery entity)
{
entityDao.CreateNamedQuery(entity);
}
public virtual void CreateNamedQuery(NamedQuery entity)
{
Create(entity);
}
public virtual void DeleteNamedQuery(NamedQuery entity)
{
entityDao.DeleteNamedQuery(entity);
}
public virtual void UpdateNamedQuery(NamedQuery entity)
{
entityDao.UpdateNamedQuery(entity);
}
public virtual void CreateNamedQuery(NamedQuery entity)
{
entityDao.CreateNamedQuery(entity);
}
public virtual void DeleteNamedQuery(NamedQuery entity)
{
entityDao.DeleteNamedQuery(entity);
}
public virtual void CreateNamedQuery(NamedQuery entity)
{
Create(entity);
}
private static async System.Threading.Tasks.Task <System.Data.DataTable> fetchDataTable(NamedQuery qry, string ConnectionString)
{
// Create a connection, open it and create a command on the connection
try
{
System.Data.DataTable dt = new System.Data.DataTable(qry.TableName);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
await connection.OpenAsync().ConfigureAwait(false);
System.Diagnostics.Debug.WriteLine("Connection Opened ... " + qry.TableName);
using (SqlCommand command = new SqlCommand(qry.SQL, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
System.Diagnostics.Debug.WriteLine("Query Executed ... " + qry.TableName);
dt.Load(reader);
System.Diagnostics.Debug.WriteLine(string.Format("Record Count '{0}' ... {1}", dt.Rows.Count, qry.TableName));
return(dt);
}
}
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Exception Raised ... " + qry.TableName);
System.Diagnostics.Debug.WriteLine(ex.Message);
return(new System.Data.DataTable(qry.TableName));
}
}
public virtual void UpdateNamedQuery(NamedQuery entity)
{
Update(entity);
}
// from clause -
// pair each from item with cross join, their join conditions will be handled
// with where clause processing.
//
LogicNode transformFromClause()
{
LogicNode transformOneFrom(TableRef tab)
{
LogicNode from;
switch (tab)
{
case BaseTableRef bref:
if (bref.Table().source_ == TableDef.TableSource.Table)
{
from = new LogicScanTable(bref);
}
else
{
from = new LogicScanStream(bref);
}
if (bref.tableSample_ != null)
{
from = new LogicSampleScan(from, bref.tableSample_);
}
break;
case ExternalTableRef eref:
from = new LogicScanFile(eref);
break;
case QueryRef qref:
var plan = qref.query_.CreatePlan();
if (qref is FromQueryRef && queryOpt_.optimize_.remove_from_)
{
from = plan;
}
else
{
string alias = null;
if (qref is CTEQueryRef cq)
{
alias = cq.alias_;
}
else if (qref is FromQueryRef fq)
{
alias = fq.alias_;
}
var key = new NamedQuery(qref.query_, alias);
from = new LogicFromQuery(qref, plan);
subQueries_.Add(key);
// if from CTE, then it could be duplicates
if (!fromQueries_.ContainsKey(key))
{
fromQueries_.Add(key, from as LogicFromQuery);
}
}
break;
case JoinQueryRef jref:
// We will form join group on all tables and put a filter on top
// of the joins as a normalized form for later processing.
//
// from a join b on a1=b1 or a3=b3 join c on a2=c2;
// => from a , b, c where (a1=b1 or a3=b3) and a2=c2;
//
LogicJoin subjoin = new LogicJoin(null, null);
Expr filterexpr = null;
for (int i = 0; i < jref.tables_.Count; i++)
{
LogicNode t = transformOneFrom(jref.tables_[i]);
var children = subjoin.children_;
if (children[0] is null)
{
children[0] = t;
}
else
{
if (children[1] is null)
{
children[1] = t;
}
else
{
subjoin = new LogicJoin(t, subjoin);
}
subjoin.type_ = jref.joinops_[i - 1];
filterexpr = filterexpr.AddAndFilter(jref.constraints_[i - 1]);
}
}
Debug.Assert(filterexpr != null);
from = new LogicFilter(subjoin, filterexpr);
break;
default:
throw new InvalidProgramException();
}
return(from);
}
LogicNode root;
if (from_.Count >= 2)
{
var join = new LogicJoin(null, null);
var children = join.children_;
from_.ForEach(x =>
{
LogicNode from = transformOneFrom(x);
if (children[0] is null)
{
children[0] = from;
}
else
{
children[1] = (children[1] is null) ? from :
new LogicJoin(from, children[1]);
}
});
root = join;
}
else if (from_.Count == 1)
{
root = transformOneFrom(from_[0]);
}
else
{
root = new LogicResult(selection_);
}
// distributed plan is required if any table is distributed, subquery
// referenced tables are not counted here. So we may have the query
// shape with main queyr not distributed but subquery is.
//
bool hasdtable = false;
bool onlyreplicated = true;
from_.ForEach(x => checkifHasdtableAndifOnlyReplicated(x, ref hasdtable, ref onlyreplicated));
if (hasdtable)
{
Debug.Assert(!distributed_);
distributed_ = true;
// distributed table query can also use memo
// remote exchange is considered in memo optimization
queryOpt_.optimize_.memo_use_remoteexchange_ = true;
if (onlyreplicated)
{
root = new LogicGather(root, new List <int> {
0
});
}
else
{
root = new LogicGather(root);
}
}
return(root);
}
// from clause -
// pair each from item with cross join, their join conditions will be handled
// with where clauss processing.
//
LogicNode transformFromClause()
{
LogicNode transformOneFrom(TableRef tab)
{
LogicNode from;
switch (tab)
{
case BaseTableRef bref:
from = new LogicScanTable(bref);
break;
case ExternalTableRef eref:
from = new LogicScanFile(eref);
break;
case QueryRef qref:
var plan = qref.query_.CreatePlan();
if (qref is FromQueryRef && queryOpt_.optimize_.remove_from_)
{
from = plan;
}
else
{
string alias = null;
if (qref is CTEQueryRef cq)
{
alias = cq.alias_;
}
else if (qref is FromQueryRef fq)
{
alias = fq.alias_;
}
var key = new NamedQuery(qref.query_, alias);
from = new LogicFromQuery(qref, plan);
subQueries_.Add(key);
// if from CTE, then it could be duplicates
if (!fromQueries_.ContainsKey(key))
{
fromQueries_.Add(key, from as LogicFromQuery);
}
}
break;
case JoinQueryRef jref:
// We will form join group on all tables and put a filter on top
// of the joins as a normalized form for later processing.
//
// from a join b on a1=b1 or a3=b3 join c on a2=c2;
// => from a , b, c where (a1=b1 or a3=b3) and a2=c2;
//
LogicJoin subjoin = new LogicJoin(null, null);
Expr filterexpr = null;
for (int i = 0; i < jref.tables_.Count; i++)
{
LogicNode t = transformOneFrom(jref.tables_[i]);
var children = subjoin.children_;
if (children[0] is null)
{
children[0] = t;
}
else
{
if (children[1] is null)
{
children[1] = t;
}
else
{
subjoin = new LogicJoin(t, subjoin);
}
subjoin.type_ = jref.joinops_[i - 1];
filterexpr = filterexpr.AddAndFilter(jref.constraints_[i - 1]);
}
}
Debug.Assert(filterexpr != null);
from = new LogicFilter(subjoin, filterexpr);
break;
default:
throw new Exception();
}
return(from);
}
LogicNode root;
if (from_.Count >= 2)
{
var join = new LogicJoin(null, null);
var children = join.children_;
from_.ForEach(x =>
{
LogicNode from = transformOneFrom(x);
if (children[0] is null)
{
children[0] = from;
}
else
{
children[1] = (children[1] is null) ? from :
new LogicJoin(from, children[1]);
}
});
root = join;
}
else if (from_.Count == 1)
{
root = transformOneFrom(from_[0]);
}
else
{
root = new LogicResult(selection_);
}
return(root);
}
