/// <summary>
/// 动态Sql拼接
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public WhereHelper <T> Where <T>(Expression <Func <T, bool> > where) where T : class, new()
{
var whereHelper = new WhereHelper <T>(this);
whereHelper.Where(where);
return(whereHelper);
}
public ExpandoObject ExecuteExpandoObject(string sql, params object[] paras)
{
var scalarBuiler = WhereHelper.CreateScalarWhere(sql, paras);
DataSet ds = ExecuteQuery(scalarBuiler.Where, paras);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return(null);
}
foreach (DataTable table in ds.Tables)
{
foreach (DataRow row in table.Rows)
{
IDictionary <string, object> expando = new ExpandoObject();
foreach (DataColumn column in table.Columns)
{
expando.Add(column.Caption, row[column]);
}
return((ExpandoObject)expando);
}
}
return(null);
}
//获取分页数据
public ActionResult GetPageList()
{
long searchId;
bool isId = long.TryParse(Request["searchId"], out searchId);
string searchName = string.IsNullOrEmpty(Request["searchName"]) ? string.Empty : Request["searchName"];
DateTime from, to;
bool fromIsDate = DateTime.TryParse(Request["from"], out from);
bool toIsDate = DateTime.TryParse(Request["to"], out to);
bool showAll = !string.IsNullOrEmpty(Request["show"]);
int pageIndex = Request["page"] != null?int.Parse(Request["page"]) : 1;
int pageSize = Request["rows"] != null?int.Parse(Request["rows"]) : 5;
int totalCount;
WhereHelper <WFInstance> wh = new WhereHelper <WFInstance>();
//wh.Equal("IsDeleted", (byte)0);
if (!showAll)
{
wh.Equal("SubBy", UserLogin.UserId);
}
if (isId)
{
wh.Equal("InstanceId", searchId);
}
if (searchName != string.Empty)
{
wh.Contains("InstanceTitle", searchName);
}
if (fromIsDate)
{
wh.StrGreater("SubTime", from.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (toIsDate)
{
wh.StrLess("SubTime", to.ToString("yyyy-MM-dd") + " 23:59:59");
}
var InstanceList = WFInstanceBll.GetPageList <long>(wh.GetExpression(), i => i.InstanceId, false, pageIndex, pageSize, out totalCount);
var uList = UserInfoBll.GetList <int>(us => true);
var result = from i in InstanceList
from uu in uList
where i.SubBy == uu.UserId
select new
{
UserId = UserLogin.UserId,
SubId = i.SubBy,
InstanceId = i.InstanceId,
InstanceTitle = i.InstanceTitle,
InstanceState = i.InstanceState,
Details = i.Details,
Remark = i.Remark,
SubTime = i.SubTime,
SubBy = uu.RealName != null && uu.RealName != "" ? uu.RealName : uu.Username,
RejectMsg = i.RejectMsg
};
return(Json(new { total = totalCount, rows = result }, JsonRequestBehavior.AllowGet));
}
public WhereHelper <T1, T2> CreateWhere <T1, T2>(JoinType join2 = JoinType.Inner)
where T1 : class, new()
where T2 : class, new()
{
var where = new WhereHelper <T1, T2>(this);
where.join2 = join2;
return(where);
}
/// <summary>
/// 动态Sql拼接，
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <returns></returns>
public WhereHelper <T> Where <T>(string where) where T : class, new()
{
var whereHelper = new WhereHelper <T>(this);
whereHelper.Where(where);
return(whereHelper);
}
//获取分页数据
public ActionResult GetPageList()
{
//预处理搜索条件
long searchId;
bool isId = long.TryParse(Request["searchId"], out searchId);
string searchName = string.IsNullOrEmpty(Request["searchName"]) ? string.Empty : Request["searchName"];
DateTime from, to;
bool fromIsDate = DateTime.TryParse(Request["from"], out from);
bool toIsDate = DateTime.TryParse(Request["to"], out to);
int pageIndex = Request["page"] != null?int.Parse(Request["page"]) : 1;
int pageSize = Request["rows"] != null?int.Parse(Request["rows"]) : 5;
//进行分页查询
int totalCount;
WhereHelper <ActionInfo> wh = new WhereHelper <ActionInfo>();
wh.Equal("IsDeleted", (byte)0);
if (isId)
{
wh.Equal("ActionId", searchId);
}
if (searchName != string.Empty)
{
wh.Contains("ActionTitle", searchName);
}
if (fromIsDate)
{
wh.StrGreater("AddTime", from.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (toIsDate)
{
wh.StrLess("AddTime", to.ToString("yyyy-MM-dd") + " 23:59:59");
}
var actionList = ActionInfoBll.GetPageList <long>(wh.GetExpression(), a => a.ActionId, false, pageIndex, pageSize, out totalCount);
var uList = UserInfoBll.GetList <int>(us => true);
//构造新匿名对象防止导航属性循环引用
var result = from a in actionList
from uu in uList
where a.SubBy == uu.UserId
select new
{
ActionId = a.ActionId,
ActionTitle = a.ActionTitle,
IsMenu = a.IsMenu,
ControllerName = a.ControllerName,
ActionName = a.ActionName,
Remark = a.Remark,
AddTime = a.AddTime,
ModifiedTime = a.ModifiedTime,
SubBy = uu.RealName != null && uu.RealName != "" ? uu.RealName : uu.Username
};
return(Json(new { total = totalCount, rows = result }, JsonRequestBehavior.AllowGet));
}
public DataSet ExecuteQueryScalar(string sql, params object[] paras)
{
if (paras != null && paras.Length > 0 && paras[0] is IDictionary <string, object> )
{
return(ExecuteQueryDict(sql, (IDictionary <string, object>)paras[0]));
}
var scalarBuiler = WhereHelper.CreateScalarWhere(sql, paras);
return(ExecuteQuery(scalarBuiler.Where, scalarBuiler.SqlParameters.ToArray()));
}
/// <summary>
/// 动态Sql拼接，
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <param name="args"></param>
/// <returns></returns>
public WhereHelper <T> Where <T>(string where, params object[] args) where T : class, new()
{
var whereHelper = new WhereHelper <T>(this);
if (string.IsNullOrEmpty(where))
{
throw new ArgumentNullException("where");
}
whereHelper.where (where, args);
return(whereHelper);
}
public WhereHelper <T1, T2, T3, T4> CreateWhere <T1, T2, T3, T4>(JoinType join2 = JoinType.Inner, JoinType join3 = JoinType.Inner, JoinType join4 = JoinType.Inner)
where T1 : class, new()
where T2 : class, new()
where T3 : class, new()
where T4 : class, new()
{
var where = new WhereHelper <T1, T2, T3, T4>(this);
where.join2 = join2;
where.join3 = join3;
where.join4 = join4;
return(where);
}
//获取分页数据
public ActionResult GetPageList()
{
long searchId;
bool isId = long.TryParse(Request["searchId"], out searchId);
string searchName = string.IsNullOrEmpty(Request["searchName"]) ? string.Empty : Request["searchName"];
DateTime from, to;
bool fromIsDate = DateTime.TryParse(Request["from"], out from);
bool toIsDate = DateTime.TryParse(Request["to"], out to);
int pageIndex = Request["page"] != null?int.Parse(Request["page"]) : 1;
int pageSize = Request["rows"] != null?int.Parse(Request["rows"]) : 5;
int totalCount;
WhereHelper <RoleInfo> wh = new WhereHelper <RoleInfo>();
wh.Equal("IsDeleted", (byte)0);
if (isId)
{
wh.Equal("RoleId", searchId);
}
if (searchName != string.Empty)
{
wh.Contains("RoleName", searchName);
}
if (fromIsDate)
{
wh.StrGreater("AddTime", from.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (toIsDate)
{
wh.StrLess("AddTime", to.ToString("yyyy-MM-dd") + " 23:59:59");
}
var roleList = RoleInfoBll.GetPageList <long>(wh.GetExpression(), u => u.RoleId, false, pageIndex, pageSize, out totalCount);
//var roleList = RoleInfoBll.GetPageList<long>(r => (r.IsDeleted == 0) && (isId ? r.RoleId == searchId : true) && (searchName != string.Empty ? r.RoleName.Contains(searchName) : true) && (formIsDate ? from.CompareTo(r.AddTime) < 0 : true) && (toIsDate ? to.CompareTo(r.AddTime) > 0 : true), r => r.RoleId, false, pageIndex, pageSize, out totalCount);
var uList = UserInfoBll.GetList <int>(us => true);
var result = from r in roleList
from uu in uList
where r.SubBy == uu.UserId
select new RoleViewModel
{
RoleId = r.RoleId,
RoleName = r.RoleName,
Remark = r.Remark,
AddTime = r.AddTime,
ModifiedTime = r.ModifiedTime,
SubBy = uu.RealName != null && uu.RealName != "" ? uu.RealName : uu.Username
};
return(Json(new { total = totalCount, rows = result }));
}
//获取分页数据
public ActionResult GetPageList()
{
long searchId;
bool isId = long.TryParse(Request["searchId"], out searchId);
string searchName = string.IsNullOrEmpty(Request["searchName"]) ? string.Empty : Request["searchName"];
DateTime from, to;
bool fromIsDate = DateTime.TryParse(Request["from"], out from);
bool toIsDate = DateTime.TryParse(Request["to"], out to);
int pageIndex = Request["page"] != null?int.Parse(Request["page"]) : 1;
int pageSize = Request["rows"] != null?int.Parse(Request["rows"]) : 5;
int totalCount;
WhereHelper <WFModel> wh = new WhereHelper <WFModel>();
wh.Equal("IsDeleted", (byte)0);
if (isId)
{
wh.Equal("ModelId", searchId);
}
if (searchName != string.Empty)
{
wh.Contains("ModelTitle", searchName);
}
if (fromIsDate)
{
wh.StrGreater("AddTime", from.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (toIsDate)
{
wh.StrLess("AddTime", to.ToString("yyyy-MM-dd") + " 23:59:59");
}
var modelList = WFModelBll.GetPageList <long>(wh.GetExpression(), m => m.ModelId, false, pageIndex, pageSize, out totalCount);
var uList = UserInfoBll.GetList <int>(us => true);
var result = from m in modelList
from uu in uList
where m.SubBy == uu.UserId
select new WFViewModel
{
ModelId = m.ModelId,
ModelTitle = m.ModelTitle,
ControllerName = m.ControllerName,
ActionName = m.ActionName,
Remark = m.Remark,
AddTime = m.AddTime,
ModifiedTime = m.ModifiedTime,
SubBy = uu.RealName != null && uu.RealName != "" ? uu.RealName : uu.Username
};
return(Json(new { total = totalCount, rows = result }, JsonRequestBehavior.AllowGet));
}
private void HandleWhereClause(SelectStatement selectStatement)
{
if (selectStatement.WhereClause != null)
{
var whereStage = WhereHelper.GetWhereStage(LastStage, selectStatement.WhereClause, _visitorMetadata, _usedProperties);
if (LastStage is FromTableStage fromTableStage)
{
//Push the where condition into the table resolvers
fromTableStage.WhereExpression = whereStage.WhereExpression;
fromTableStage.ContainsFullTextSearch = whereStage.ContainsFullTextSearch;
}
else
{
//If its not possible to push up, add it as a normal stage
_stages.Add(whereStage);
}
}
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="page">PageIndex</param>
/// <param name="rows">PageSize</param>
/// <returns></returns>
public ActionResult GetPageList(int page, int rows)
{
//接收查询数据
int id1 = string.IsNullOrEmpty(Request["sid"]) ? 0 : int.Parse(Request["sid"]);
string name1 = string.IsNullOrEmpty(Request["sname"]) ? "" : Request["sname"];
//调用Service来完成查询，获取数据
int total;
//var result=UserInfoService.GetPageList<int>(
// u => (u.IsDelete == false)
// &&
// (id1==0?true:u.UserId==id1)
// &&
// (name1==""?true:u.UserName.Contains(name1)),
// u => u.UserId,
// rows, page,out total);
WhereHelper <UserInfo> where = new WhereHelper <UserInfo>();
where.Equal("IsDelete", false);
if (id1 != 0)
{
where.Equal("UserId", id1);
}
if (name1 != "")
{
where.Contains("UserName", name1);
}
var result = UserInfoService.GetPageList <int>(where.GetExpression(), u => u.UserId, page, rows, out total).Select(u => new
{
u.UserId,
u.UserName
});
return(Json(new { total, rows = result }, JsonRequestBehavior.AllowGet));
}
public ExPageResult ExecutePageExpandoObjects(string sql, string order, int pageIndex, int pageSize, params object[] paras)
{
var scalarBuiler = WhereHelper.CreateScalarWhere(sql, paras);
return(ExecutePageExpandoObjects(scalarBuiler.Where, scalarBuiler.Paras, order, pageIndex, pageSize));
}
public PageResult <T> QueryPage(string where, string order, int pageIndex, int pageSize, params object[] paras)
{
var scalarBuiler = WhereHelper.CreateScalarWhere(where, paras);
return(QueryPage(scalarBuiler.Where, scalarBuiler.Paras, order, pageIndex, pageSize));
}
public List <T> Query(string where, string order = "", params object[] paras)
{
var scalarBuiler = WhereHelper.CreateScalarWhere(where, paras);
return(Query(scalarBuiler.Where, scalarBuiler.Paras, order));
}
public IEnumerable <ExpandoObject> ExecuteExpandoObjects(string sql, params object[] paras)
{
var scalarBuiler = WhereHelper.CreateScalarWhere(sql, paras);
return(ExecuteExpandoObjects(scalarBuiler.Where, scalarBuiler.SqlParameters));
}
/// <summary>
/// 动态Sql拼接
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public WhereHelper <T> Where <T>() where T : class, new()
{
var where = new WhereHelper <T>(this);
return(where);
}
