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;
}
