public static void SetPK(this SqlTable table, SqlColumn column, AscDesc order = AscDesc.Asc, string name = null)
{
var pk = table.Properties.OfType <PrimaryKey>().FirstOrDefault();
if (pk == null)
{
pk = new PrimaryKey(table, name);
table.Properties.Add(pk);
}
pk.SqlColumns.Add(new ColumnAndOrder(column, order));
}
public Select Order(AscDesc ascDesc = AscDesc.Asc, params string[] columnNames)
{
if (columnNames.Any(c => !string.IsNullOrEmpty(c)))
{
_order = new Order
{
AscDesc = ascDesc,
Columns = columnNames.Select(c => c.Contains('.') ? c : $"{_tabloidName}.{c}")
}
}
;
return(this);
}
/// <summary>
/// 以实体类作为查询条件获取符合条件的数据集，NULL值不作为查询条件
/// </summary>
public List <T_Task> Search(T_Task entity, int pageNum, int pageSize, String criteria, T_Task.Columns OrderBy, AscDesc AscDesc, out int totalRecords)
{
String sql = "";
//如果没有任何查询条件，则不开启模糊查询列以减少SQL消耗
if (String.IsNullOrWhiteSpace(criteria))
{
sql = this.PreparedTable(entity, false, false);
}
else
{
sql = this.PreparedTable(entity, true, false);
}
String tbl = "'(" + sql + ") as t'";
List <SqlParameter> pars = new List <SqlParameter>();
String crit = getSearchCriterias(criteria);
String order = OrderBy.ToString() + " " + AscDesc.ToString();
//有待改进，没危险的int数据用了参数插入，有风险的字符串反而直接组装SQL，虽然做了简单的去单引号处理
sql = String.Format("exec sp_paging {0},@pageNum,@pageSize,'{1}','{2}'", tbl, crit, order);
pars.Add(new SqlParameter("@pageNum", pageNum));
pars.Add(new SqlParameter("@pageSize", pageSize));
DataTable dt = DBHelper.ExecuteDataTable(sql, pars);
totalRecords = 0;
if (dt.Rows.Count > 0)
{
totalRecords = (int)dt.Rows[0]["TotalRecord"];
}
return(DtToList(dt));
}
public EntityComparer(string _propertyname = "Id", AscDesc _sorttype = AscDesc.Asc)
{
this.PropertyName = _propertyname;
this.SortType = _sorttype;
}
public Select DropStep(int finalCount, ESqlFunction function = ESqlFunction.LAST, AscDesc ascDesc = AscDesc.Asc, params string[] orderColumnNames)
{
if (!orderColumnNames.Any())
{
throw new ArgumentException("orderedColumnNames needs at least one column");
}
return(DropStep(finalCount, new Order {
AscDesc = ascDesc, Columns = orderColumnNames.Select(c => c.Contains('.') ? c : $"{_tabloidName}.{c}")
}, function));
}
/// <summary>
///
/// </summary>
/// <param name="parametro"></param>
/// <param name="ascDesc"></param>
public ParametroOrder(String parametro, AscDesc ascDesc)
{
this.parametro = parametro;
this.ascDesc = ascDesc;
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> InvertedInputVars, Message message)
{
// init
DBConnection db = COREobject.i.Entitron;
// get view
string orderBy = vars.ContainsKey("OrderBy") ? (string)vars["OrderBy"] : null;
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
AscDesc isDescending = vars.ContainsKey("Descending") && (bool)vars["Descending"] ? AscDesc.Desc : AscDesc.Asc;
Tabloid view = db.Tabloid((string)vars["ViewName"], searchInShared);
var select = view.Select();
int conditionIndex = 1;
/// each condition
while (true)
{
string postfix = (conditionIndex == 1 ? "" : conditionIndex.ToString());
string columnNameVars = $"ColumnName{postfix}";
string valueVars = $"Value{postfix}";
// select all - no condition
if (!vars.ContainsKey(columnNameVars) || !vars.ContainsKey(valueVars))
{
break;
}
string columnName = (string)vars[columnNameVars];
object value = vars[valueVars];
if (value is Newtonsoft.Json.Linq.JValue)
{
select.Where(c => c.Column(columnName).Equal(value.ToString()));
}
// condition is list
else if (!(value is string) && value is IEnumerable)
{
// condition list is empty -> return empty list
if (((IEnumerable <object>)value).Count() == 0)
{
outputVars["Result"] = new List <DBItem>();
return;
}
select.Where(c => c.Column(columnName).In((IEnumerable <object>)value));
}
// condition is list of strings
else if ((value is string) && ((string)value).Contains(","))
{
string[] list = vars["Value"].ToString().Split(',');
select.Where(c => c.Column(columnName).In(list));
}
// condition is object
else
{
select.Where(c => c.Column(columnName).Equal(value));
}
conditionIndex++;
}
// MaxRows
if (vars.ContainsKey("MaxRows") && orderBy != null)
{
select.DropStep((int)vars["MaxRows"], ESqlFunction.LAST, isDescending, orderBy);
}
// order
select.Order(isDescending, orderBy);
// group
if (vars.ContainsKey("GroupBy") && !string.IsNullOrWhiteSpace((string)vars["GroupBy"]))
{
ESqlFunction function = ESqlFunction.SUM;
if (vars.ContainsKey("GroupByFunction"))
{
switch ((string)vars["GroupByFunction"])
{
case "none":
function = ESqlFunction.none;
break;
case "MAX":
function = ESqlFunction.MAX;
break;
case "MIN":
function = ESqlFunction.MIN;
break;
case "AVG":
function = ESqlFunction.AVG;
break;
case "COUNT":
function = ESqlFunction.COUNT;
break;
case "SUM":
function = ESqlFunction.SUM;
break;
case "FIRST":
function = ESqlFunction.FIRST;
break;
case "LAST":
function = ESqlFunction.LAST;
break;
}
}
select.Group(function, columns: (string)vars["GroupBy"]);
}
outputVars["Result"] = select.ToList();
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> invertedVars, Message message)
{
// init
COREobject core = COREobject.i;
DBConnection db = core.Entitron;
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
string orderBy = vars.ContainsKey("OrderBy") ? (string)vars["OrderBy"] : null;
AscDesc isDescending = vars.ContainsKey("Descending") && (bool)vars["Descending"] ? AscDesc.Desc : AscDesc.Asc;
int? maxRows = vars.ContainsKey("MaxRows") ? (int?)vars["MaxRows"] : null;
string groupBy = vars.ContainsKey("GroupBy") && !string.IsNullOrWhiteSpace((string)vars["GroupBy"]) ? (string)vars["GroupBy"] : null;
//
Select select = db.Select((string)vars["ViewName"], searchInShared);
int CondCount = vars.Keys.Where(k => k.StartsWith("CondColumn[") && k.EndsWith("]")).Count();
// setConditions
for (int i = 0; i < CondCount; i++)
{
string condOperator = vars.ContainsKey($"CondOperator[{i}]") ? (string)vars[$"CondOperator[{i}]"] : "Equal";
string condColumn = (string)vars[$"CondColumn[{i}]"];
object condValue = vars[$"CondValue[{i}]"];
var value = condValue;
switch (condOperator)
{
case "Less":
select.Where(c => c.Column(condColumn).Less(value));
break;
case "LessOrEqual":
select.Where(c => c.Column(condColumn).LessOrEqual(value));
break;
case "Greater":
select.Where(c => c.Column(condColumn).Greater(value));
break;
case "GreaterOrEqual":
select.Where(c => c.Column(condColumn).GreaterOrEqual(value));
break;
case "Equal":
select.Where(c => c.Column(condColumn).Equal(value));
break;
case "IsIn":
select.Where(c => c.Column(condColumn).In((IEnumerable <object>)value));
break;
default: // ==
select.Where(c => c.Column(condColumn).Equal(value));
break;
}
}
// MaxRows
if (maxRows != null && orderBy != null)
{
select.DropStep((int)vars["MaxRows"], ESqlFunction.LAST, isDescending, orderBy);
}
// order
select.Order(isDescending, orderBy);
// group
if (groupBy != null)
{
ESqlFunction function = ESqlFunction.SUM;
if (vars.ContainsKey("GroupByFunction"))
{
switch ((string)vars["GroupByFunction"])
{
case "none":
function = ESqlFunction.none;
break;
case "MAX":
function = ESqlFunction.MAX;
break;
case "MIN":
function = ESqlFunction.MIN;
break;
case "AVG":
function = ESqlFunction.AVG;
break;
case "COUNT":
function = ESqlFunction.COUNT;
break;
case "SUM":
function = ESqlFunction.SUM;
break;
case "FIRST":
function = ESqlFunction.FIRST;
break;
case "LAST":
function = ESqlFunction.LAST;
break;
}
}
select.Group(function, columns: (string)vars["GroupBy"]);
}
// return
outputVars["Data"] = select.ToList();
}
public ColumnAndOrderRegistration(string columnName, AscDesc order)
: base(order)
{
ColumnName = columnName;
}
public ColumnAndOrder(SqlColumn sqlColumn, AscDesc order)
: this(order)
{
SqlColumn = sqlColumn;
}
protected ColumnAndOrder(AscDesc order)
{
Order = order;
}
public override void InnerRun(Dictionary <string, object> vars, Dictionary <string, object> outputVars, Dictionary <string, object> invertedVars, Message message)
{
// init
DBConnection db = COREobject.i.Entitron;
string tableName = vars.ContainsKey("TableName") ? (string)vars["TableName"] : (string)vars["__TableName__"];
bool searchInShared = vars.ContainsKey("SearchInShared") ? (bool)vars["SearchInShared"] : false;
string orderBy = vars.ContainsKey("OrderBy") ? (string)vars["OrderBy"] : null;
AscDesc isDescending = vars.ContainsKey("Descending") && (bool)vars["Descending"] ? AscDesc.Desc : AscDesc.Asc;
//
Tabloid tabloid = db.Tabloid(tableName, searchInShared);
Select select = tabloid.Select();
int CondCount = vars.Keys.Where(k => k.StartsWith("CondColumn[") && k.EndsWith("]")).Count();
// setConditions
for (int i = 0; i < CondCount; i++)
{
string condOperator = vars.ContainsKey($"CondOperator[{i}]") ? (string)vars[$"CondOperator[{i}]"] : "Equal";
string condColumn = (string)vars[$"CondColumn[{i}]"];
object condValue = vars[$"CondValue[{i}]"];
DBColumn column = tabloid.Columns.Single(c => c.Name == condColumn);
object value = condOperator != "IsIn"
? DataType.ConvertTo(column.Type, condValue)
: condValue;
switch (condOperator)
{
case "Less":
select.Where(c => c.Column(condColumn).Less(value));
break;
case "LessOrEqual":
select.Where(c => c.Column(condColumn).LessOrEqual(value));
break;
case "Greater":
select.Where(c => c.Column(condColumn).Greater(value));
break;
case "GreaterOrEqual":
select.Where(c => c.Column(condColumn).GreaterOrEqual(value));
break;
case "Equal":
select.Where(c => c.Column(condColumn).Equal(value));
break;
case "IsIn":
// string, multiple values
if ((condValue is string) && ((string)condValue).Contains(","))
{
select.Where(c => c.Column(condColumn).In((condValue as string).Split(',')));
}
// Enumerable
else
{
select.Where(c => c.Column(condColumn).In((IEnumerable <object>)condValue));
}
break;
default: // ==
select.Where(c => c.Column(condColumn).Equal(value));
break;
}
}
// top
if (vars.ContainsKey("Top"))
{
select = select.Limit((int)vars["Top"]);
}
// order
if (!string.IsNullOrWhiteSpace(orderBy))
{
select = select.Order(isDescending, orderBy);
if (vars.ContainsKey("MaxRows"))
{
select.DropStep((int)vars["MaxRows"], ESqlFunction.LAST, isDescending, orderBy);
}
}
// Group
if (vars.ContainsKey("GroupBy") && !string.IsNullOrWhiteSpace((string)vars["GroupBy"]))
{
ESqlFunction function = ESqlFunction.SUM;
if (vars.ContainsKey("GroupByFunction"))
{
switch ((string)vars["GroupByFunction"])
{
case "none":
function = ESqlFunction.none;
break;
case "MAX":
function = ESqlFunction.MAX;
break;
case "MIN":
function = ESqlFunction.MIN;
break;
case "AVG":
function = ESqlFunction.AVG;
break;
case "COUNT":
function = ESqlFunction.COUNT;
break;
case "SUM":
function = ESqlFunction.SUM;
break;
case "FIRST":
function = ESqlFunction.FIRST;
break;
case "LAST":
function = ESqlFunction.LAST;
break;
}
}
select.Group(function, columns: (string)vars["GroupBy"]);
}
// return
outputVars["Data"] = select.ToList();
}
