/// <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 CreateNamedQuery(NamedQuery entity) { entityDao.CreateNamedQuery(entity); }
public virtual void CreateNamedQuery(NamedQuery entity) { Create(entity); }
public virtual void DeleteNamedQuery(NamedQuery entity) { entityDao.DeleteNamedQuery(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)); } }
// 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); }