private ActiveRecord findItem <ActiveRecord>(DataTable <ActiveRecord> table, WhereItem condition) where ActiveRecord : DataRecord { ActiveRecord result; if (condition.Operation != WhereOperation.Equal || condition.Column != "ID") { result = null; var type = typeof(ActiveRecord); var field = type.GetField(condition.Column); if (field == null) { throw new InvalidOperationException("Unknown field: " + condition.Column); } foreach (var record in table.MemoryRecords.Values) { var value = field.GetValue(record); if (condition.Operand.Equals(value)) { result = record; break; } } } else { table.MemoryRecords.TryGetValue((int)condition.Operand, out result); } return(result); }
private string BuildSQLText_DELETE() { if (_list.Count <= 0) { return(null); } var buffer = new StringBuilder(); //1.Parts:DELETE if (!_list.Any(itm => itm is DeleteItem)) { throw new ApplicationException("The script is missing delete-item!"); } buffer.AppendLine(_list.Where(itm => itm is DeleteItem).First().Text.Trim()); //2.Parts:WHERE WhereItem where = null; if (_list.Any(itm => itm is WhereItem)) { _list.Where(itm => itm is WhereItem).ToList().ForEach(itm => { where = where == null ? (WhereItem)itm : where + (WhereItem)itm; }); buffer.AppendLine(where.Text.Trim()); } return(buffer.ToString()); }
public FromClass InnerJoin(Entity to, WhereItem where) { var result = new FromClass(this); result._joins.Add(new Join(to, where)); return(result); }
public DBRefList <T> Retrieve <T>(Clause clause = null) where T : TableEntity { var script = new SQLScript(Accessor, Commons.Objects.Enums.SQLTYPE.SELECT); var from = new FromItem <T>(Accessor); var select = new SelectItem(Accessor, from); script.AddItems(select, from); if (clause != null) { var where = new WhereItem(Accessor, clause, from); script.AddItems(where); } var list = new DBRefList <T>(); var table = Accessor.Retrieve(script.ExportCommand()); if (table != null && table.Rows.Count > 0) { foreach (var row in table.Rows) { list.NewEntity().LoadFrom(row as DataRow, 0); } } return(list); }
public void Clone() { var item = new WhereItem("field1", "a string"); var clone = item.Clone(); Assert.AreEqual(item, clone); clone.Value = "something-else"; Assert.AreNotEqual(item, clone); clone.Value = "a string"; Assert.AreEqual(item, clone); var parm = new QueryParameter("someparm", "testvalue"); var item2 = new WhereString("field2=@someparm or field3='value'", parm); var clause = new WhereClause(JoinType.And, item, item2); var clone2 = clause.Clone(); Assert.AreEqual(clause, clone2); parm.Value = "testvalue2"; Assert.AreNotEqual(clause, clone2); parm.Value = "testvalue"; Assert.AreEqual(clause, clone2); item.Value = "n"; Assert.AreNotEqual(clause, clone2); item.Value = "a string"; Assert.AreEqual(clause, clone2); }
private string BuildSQLText_SELECT() { if (_list.Count <= 0) { return(null); } var buffer = new StringBuilder(); //1.Parts:SELECT if (!_list.Any(itm => itm is SelectItem)) { throw new ApplicationException("The script is missing select-item!"); } SelectItem select = null; _list.Where(itm => itm is SelectItem).ToList().ForEach(itm => { select = select == null ? (SelectItem)itm : select + (SelectItem)itm; }); buffer.AppendLine(select.Text); //2.Parts:FROM if (!_list.Any(itm => itm is FromItem)) { throw new ApplicationException("The script is missing from-item!"); } if (_list.Count(itm => itm is FromItem) > 1) { throw new ApplicationException("The script contains multiple from-items!"); } FromItem from = _list.First(itm => itm is FromItem) as FromItem; buffer.AppendLine(from.Text); //3.Parts:JOIN if (_list.Any(itm => itm is JoinItem)) { _list.Where(itm => itm is JoinItem).ToList().ForEach(join => { buffer.AppendLine(join.Text); }); } //4.Parts:WHERE //if (!_list.Any(itm => itm is WhereItem)) throw new ApplicationException("The script is missing where-item!"); WhereItem where = null; if (_list.Any(itm => itm is WhereItem)) { _list.Where(itm => itm is WhereItem).ToList().ForEach(itm => { where = where == null ? (WhereItem)itm : where + (WhereItem)itm; }); buffer.AppendLine(where.Text); } return(buffer.ToString()); }
public void IsEmpty() { var item = new WhereItem(); Assert.IsTrue(item.IsEmpty); item.Field = "test"; Assert.IsFalse(item.IsEmpty); }
internal void AddWhereFilter(WhereItem where, SQLParamCreater creater) { var filter = Filter(where.TableItem, creater); if (filter != null) { where.And(filter); } }
//BAD // Really unhappy with appending a guid to the end of a parameter name, // because it changes the name of the parameter everytime, which // most likely means each one is cached seperately by sql server. // This negates one of the main reasons for paramterized sql. private string createSqlNeededForAWhereType(WhereItem item) { var parameterName = "@" + item.Name + item.UniqueKey; return new Match<WhereType, string>(item.WhereType) .When(WhereType.None, () => item.Name + " = " + parameterName) .When(WhereType.And, () => " AND (" + item.Name + " = " + parameterName + ")") .When(WhereType.Or, () => " OR (" + item.Name + " = " + parameterName + ")") .Go(); }
public void Clone() { var item = new WhereItem("field1", "a string"); var clone = item.Clone(); Assert.AreEqual(item, clone); clone.Value = "something-else"; Assert.AreNotEqual(item, clone); clone.Value = "a string"; Assert.AreEqual(item, clone); }
public void Create() { var item = new WhereItem("field1", 123); Assert.AreEqual("field1=@field1", item.GetSql()); Assert.AreEqual(1, item.Parameters.Count()); item = new WhereItem("field1", "a string", ComparisonType.LikeEndsWith, false); Assert.AreEqual("field1 LIKE '%a string'", item.ToString()); }
public void Create() { var item1 = new WhereItem("field1", 123); var parm = new QueryParameter("someparm", "testvalue"); var item2 = new WhereString("field2=@someparm", parm); var clause = new WhereClause(JoinType.And, item1, item2); Assert.AreEqual("field1=@field1 AND (field2=@someparm)", clause.GetSql()); Assert.AreEqual(2, clause.Parameters.Count()); }
private string getSqlOperation(WhereItem item, SqlQuery query) { string format; var operand = getSqlOperand(null, item.Operand); switch (item.Operation) { case WhereOperation.Equal: if (operand == null) { format = "`{0}` IS {1}"; } else { format = "`{0}` = {1}"; } break; case WhereOperation.HasSubstring: format = "`{0}` LIKE {1}"; operand = string.Format("%{0}%", operand); break; case WhereOperation.IsSimilar: format = "MATCH(`{0}`) AGAINST({1} IN BOOLEAN MODE)"; break; case WhereOperation.Lesser: format = "`{0}` < {1}"; break; case WhereOperation.LesserOrEqual: format = "`{0}` <= {1}"; break; case WhereOperation.Greater: format = "`{0}` > {1}"; break; case WhereOperation.GreaterOrEqual: format = "`{0}` >= {1}"; break; default: throw new NotSupportedException("Cannot process operation" + item.Operation); } var column = item.Column; var operandHolder = query.CreateParameter(column, operand); return(string.Format(format, column, operandHolder)); }
/// <summary> /// 根据主键查询 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="parentId"></param> /// <returns></returns> public Page <WSellReturnLine> LinePageSearch(long pageIndex, long pageSize, int parentId) { //根据主表查询 WhereItem item = new WhereItem() { condition = "=", datatype = "int", field = "ParentID", value = parentId.ToString() }; string strSql = srlRepository.GetSearchSql(new List <WhereItem>() { item }); return(LinePageSearch(pageIndex, pageSize, strSql)); }
public void Combine() { var item1 = new WhereItem("field1", 123); var parm = new QueryParameter("someparm", "testvalue"); var item2 = new WhereString("field2=@someparm or field3='value'", parm); var item3 = new WhereItem("field4", 123, parameterize: false); var clause = new WhereClause(JoinType.And, item1, item2); Assert.AreEqual("field1=@field1 AND (field2=@someparm or field3='value')", clause.GetSql()); var newClause = clause.Clone(); newClause.Add(item3, JoinType.Or); Assert.AreEqual("(field1=@field1 AND (field2=@someparm or field3='value')) OR field4=123", newClause.GetSql()); Assert.AreEqual(2, newClause.Parameters.Count()); }
internal void FillSaveScript(SQLScriptCollection collection) { switch (this.ColumnsStatus) { case STATUS.ASSIGNED: case STATUS.RAW: var script1 = collection.NewSQLScript(null, SQLTYPE.INSERT); var insert = new InsertItem <TableEntity>(collection.accessor, Entity); Columns.ToList().ForEach(col => { var param = collection.NewParameter(col.Name, col.Value); insert.Append(new Clause(script1.accessor, param.CoveredName, new KeyValuePair <string, object>[] { param.ToKeyValuePair() })); }); script1.AddItem(insert); break; case STATUS.CHANGED: var script2 = collection.NewSQLScript(null, SQLTYPE.UPDATE); var update = new UpdateItem <TableEntity>(collection.accessor, Entity); Columns.Where(c => c.Status == STATUS.CHANGED).ToList().ForEach(col => { var param = collection.NewParameter(col.Name, col.Value); update.Append(new Clause(script2.accessor, string.Format("{0} = {1}", col.Name, param.CoveredName), new KeyValuePair <string, object>[] { param.ToKeyValuePair() })); }); script2.AddItem(update); var where = new WhereItem(script2.accessor, update); Columns.Where(c => c.IsPK).ToList().ForEach(col => { var param = collection.NewParameter(col.Name, col.Value); where.And(where.ColumnEquals(col.Name, param)); }); script2.AddItem(where); break; } foreach (var foreign in this.Foreigns) { foreign.Save(collection); } }
internal void FillDeleteScript(SQLScriptCollection collection) { var script = collection.NewSQLScript(null, SQLTYPE.DELETE); var delete = new DeleteItem <TableEntity>(collection.accessor, Entity); script.AddItem(delete); var where = new WhereItem(script.accessor, delete); Columns.Where(c => c.IsPK).ToList().ForEach(col => { var param = collection.NewParameter(col.Name, col.Value); where.And(where.ColumnEquals(col.Name, param)); }); script.AddItem(where); if (this.HasForeigns) { foreach (var foreign in this.Foreigns) { foreign.Delete(collection); } } }
public String Build(out SqlParameter[] parameters) { StringBuilder where = new StringBuilder(); parameters = new SqlParameter[] { }; List <SqlParameter> paramList = parameters.ToList <SqlParameter>(); if (Count() == 0) { return(where.ToString()); } list[list.Count - 1].Logic = ""; where.Append("where"); for (int i = 0; i < Count(); i++) { WhereItem item = list[i]; String paramName = String.Format("@{0}{1}", item.FieldName, i); where.Append(String.Format(" {0} {1} {2} {3}", item.FieldName, item.Condition, paramName, item.Logic)); paramList.Add(new SqlParameter(paramName, item.Value)); } parameters = paramList.ToArray <SqlParameter>(); return(where.ToString()); }
public static WhereItem Or(WhereItem isEqualTo) { return new WhereItem(WhereType.Or, isEqualTo.Name, isEqualTo.Value); }
private IEnumerable <ActiveRecord> applyItem <ActiveRecord>(DataTable <ActiveRecord> table, IEnumerable <ActiveRecord> rows, WhereItem where) where ActiveRecord : DataRecord { foreach (var row in rows) { var column = table.GetColumnValue(where.Column, row); switch (where.Operation) { case WhereOperation.Equal: if (column.Equals(where.Operand)) { yield return(row); } break; case WhereOperation.IsSimilar: //TODO implement case WhereOperation.HasSubstring: var value = column as string; if (value.Contains(where.Operand.ToString())) { yield return(row); } break; case WhereOperation.GreaterOrEqual: if (column is long) { var longValue = (long)column; if (longValue >= (long)where.Operand) { yield return(row); } break; } throw new NotImplementedException("Operand"); case WhereOperation.Greater: if (column is int) { var longValue = (int)column; if (longValue > (int)where.Operand) { yield return(row); } break; } throw new NotImplementedException("Operand"); default: throw new NotImplementedException("Operation"); } } }
private void FillSelectScript(string name, int level, SQLScriptCollection collection, WhereItem where = null, Stack <JoinItem <TableEntity> > stack = null) { if (collection == null) { throw new ArgumentNullException("collection"); } var script = collection.NewSQLScript(level == 0 ? 0.ToString() : string.Format("{0}-{1}", name.Trim(), level), SQLTYPE.SELECT); var alias = collection.NewTableAlias(); var from = new FromItem <TableEntity>(collection.accessor, Entity, alias); var select = new SelectItem(collection.accessor, from); script.AddItems(select, from); //Build Jions JoinItem <TableEntity>[] join_items = null; if (stack != null) { if (HasForeigns) { join_items = new JoinItem <TableEntity> [stack.Count]; stack.CopyTo(join_items, 0); } TableItem table1 = from; JoinItem <TableEntity> table2 = null; while (stack.Count > 0) { table2 = stack.Pop().Clone() as JoinItem <TableEntity>; table2.ON(table1, table2.Keys.ToArray()); table2.Entity.AddJoinFilter(table2, collection.SQLParamCreater); script.AddItems(table2); table1 = table2; } } //Build Where if (level == 0) { var where1 = new WhereItem(collection.accessor, from); Columns.Where(c => c.IsPK).ToList().ForEach(col => { var param = collection.NewParameter(col.Value); where1.And(where1.ColumnEquals(col.Name, param)); }); where = where == null ? where1 : where + where1; } if (where != null) { script.AddItem(where.Clone() as WhereItem); } var where0 = new WhereItem(collection.accessor, from); Entity.AddWhereFilter(where0, collection.SQLParamCreater); script.AddItem(where0); foreach (var foreign in this.Foreigns) { var nstack = join_items != null ? new Stack <JoinItem <TableEntity> >(join_items) : new Stack <JoinItem <TableEntity> >(); nstack.Push(new JoinItem <TableEntity>(collection.accessor, Entity, alias, foreign.Keys.Select(key => key.ToKeyValuePair()).ToArray())); foreign.EntityInst.Schema.FillSelectScript(foreign.Name, level + 1, collection, where.Clone() as WhereItem, nstack); } }
public Join(Entity to, WhereItem on, string joinType = "inner") { this.joinType = joinType; To = to; On = on; }
public static WhereItem And(WhereItem isEqualTo) { return new WhereItem(WhereType.And, isEqualTo.Name, isEqualTo.Value); }
public CaseClass When(WhereItem where, object then) { return(new CaseClass(where, then, this)); }
public CaseClass(WhereItem where, object then, CaseClass parent) { _where = where; _then = then; _parent = parent; }
public static CaseClass Case(WhereItem when, object then) { return(new CaseClass(when, then, null)); }
public SelectSql Where(SqlQuery query) { if (null != query) { WhereItem item = new WhereItem(query); this.wheres.Add(item); } return this; }