/// <summary> /// 获得数据列表 /// </summary> /// <param name="dapperWheres">查询条件列表</param> public List <T_Goods_Attribute> GetList(List <DapperWhere> dapperWheres) { StringBuilder strSql = new StringBuilder(); string where = ""; Dictionary <string, object> parm = new Dictionary <string, object>(); foreach (DapperWhere item in dapperWheres) { if (where.Length > 0) { where += " and "; } where += item.Where; parm[item.ColumnName] = item.Value; } strSql.Append("select * "); strSql.Append(" FROM I200.dbo.T_Goods_Attribute "); if (where.Length > 0) { strSql.Append(" where " + where); } return(HelperForFrontend.Query <T_Goods_Attribute>(strSql.ToString(), parm).ToList()); }
public int GetAllIntegral(List <DapperWhere> dapperWheres) { string where = ""; Dictionary <string, object> parm = new Dictionary <string, object>(); foreach (DapperWhere item in dapperWheres) { if (where.Length > 0) { where += " and "; } where += item.Where; parm[item.ColumnName] = item.Value; } StringBuilder strSql = new StringBuilder(); strSql.Append(" select sum(eIntegral) from I200.dbo.T_ExchangeLog where eState in (1,2) "); if (where.Length > 0) { strSql.Append(" and " + where); } object obj = HelperForFrontend.ExecuteScalar(strSql.ToString(), parm); if (obj != null) { return(Convert.ToInt32(obj)); } else { return(0); } }
/// <summary> /// 得到一个对象实体 /// </summary> public T_ExchangeLog GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id, eProvince, eStreet, ePostcode, eOperator, eIp, eInsertTime, eLogistics, eLogisticsNumber, eSysName, eSysTime, accid, eProjectId, eProjectName, eIntegral, eQuantity, eState, eRecipient, ePhoneNumber "); strSql.Append(" from I200.dbo.T_ExchangeLog "); strSql.Append(" where id=@id"); return(HelperForFrontend.GetModel <T_ExchangeLog>(strSql.ToString(), new { id = id })); }
/// <summary> /// 分页得到列表<para>如果没有对像的可以传入 object 或者 dynamic </para> /// </summary> /// <param name="pageIndex">显示页号</param> /// <param name="pageSize">每页显示数</param> /// <param name="columnName">需要获取的列名<para>为了方便不在处理,列与列用逗号分开,参照SQL写法</para></param> /// <param name="dapperWheres">条件列表</param> /// <param name="filedOrder">排序</param> /// <returns>返回列表</returns> public List <T> GetList <T>(int pageIndex, int pageSize, string columnName, List <DapperWhere> dapperWheres, string filedOrder) { if (columnName.Length < 1) { columnName = "*"; } StringBuilder strSql = new StringBuilder(); string where = ""; Dictionary <string, object> parm = new Dictionary <string, object>(); foreach (DapperWhere item in dapperWheres) { if (where.Length > 0) { where += " and "; } where += item.Where; parm[item.ColumnName] = item.Value; } strSql.Append(" SELECT * FROM ( "); strSql.Append(" SELECT ROW_NUMBER() OVER ( "); if (filedOrder.Length > 0) { strSql.Append(" order by " + filedOrder + " "); } else { strSql.Append(" order by T.id desc"); } strSql.Append(" )AS overRow, " + columnName + " from I200.dbo.T_ExchangeLog T "); if (where.Length > 0) { strSql.Append(" where " + where + " "); } strSql.Append(" ) TT "); strSql.Append(" WHERE TT.overRow between @startIndex and @endIndex; "); if (pageIndex < 1) { pageIndex = 1; } int bgNumber = ((pageIndex - 1) * pageSize) + 1; int edNumber = (pageIndex) * pageSize; parm["startIndex"] = bgNumber; parm["endIndex"] = edNumber; return(HelperForFrontend.Query <T>(strSql.ToString(), parm).ToList()); }
/// <summary> /// 获得数据列表 /// </summary> /// <param name="top">前几行</param> /// <param name="dapperWheres">查询条件列表</param> /// <param name="filedOrder">排序</param> public List <T_ExchangeLog> GetList(int top, List <DapperWhere> dapperWheres, string filedOrder) { StringBuilder strSql = new StringBuilder(); string where = ""; Dictionary <string, object> parm = new Dictionary <string, object>(); foreach (DapperWhere item in dapperWheres) { if (where.Length > 0) { where += " and "; } where += item.Where; parm[item.ColumnName] = item.Value; } strSql.Append("select "); if (top > 0) { strSql.Append(" top " + top.ToString() + " "); } strSql.Append(" * "); strSql.Append(" FROM I200.dbo.T_ExchangeLog "); if (where.Length > 0) { strSql.Append(" where " + where); } if (filedOrder.Length > 0) { strSql.Append(" order by " + filedOrder); } strSql.Append(";"); return(HelperForFrontend.Query <T_ExchangeLog>(strSql.ToString(), parm).ToList()); }
/// <summary> /// 得到总数 /// </summary> /// <param name="dapperWheres"></param> /// <param name="searchStr"></param> /// <returns></returns> public int GetCount(List <DapperWhere> dapperWheres, string searchStr = "") { StringBuilder strSql = new StringBuilder(); strSql.Append(" select count(*) from I200.dbo.T_Account "); if (string.IsNullOrEmpty(searchStr)) { string where = ""; Dictionary <string, object> parm = new Dictionary <string, object>(); foreach (DapperWhere item in dapperWheres) { if (where.Length > 0) { where += " and "; } where += item.Where; parm[item.ColumnName] = item.Value; } if (where.Length > 0) { strSql.Append(" where " + where); } object obj = HelperForFrontend.ExecuteScalar(strSql.ToString(), parm); if (obj != null) { return(Convert.ToInt32(obj)); } else { return(0); } } else { //Regex reg = new Regex("@/^0?1[3|4|5|8|7][0-9]/d{8}$/");//验证手机号正则 //if (reg.IsMatch(searchStr)) //{ strSql.Append(" where state=1 and (phoneNumber like '%" + searchStr + "%' or "); strSql.Append(" CompanyName like '%" + searchStr + "%' or "); strSql.Append(" UserRealName like '%" + searchStr + "%' or "); strSql.Append(" UserEmail like '%" + searchStr + "%' or "); strSql.Append(" BBS_Uid like '%" + searchStr + "%' "); if (Regex.IsMatch(searchStr, "^((\\+|-)\\d)?\\d*$")) { strSql.Append(" or ID=" + searchStr); } strSql.Append(" )"); //} object obj = HelperForFrontend.ExecuteScalar(strSql.ToString()); if (obj != null) { return(Convert.ToInt32(obj)); } else { return(0); } } }