public static IPageOfList<CompanyInfo> CompanyList(MemberSearchSetting setting) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " C.ApplyDateTime DESC"; fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.QueryFields = "*"; fp.TableName = "Members"; fp.PrimaryKey = "Id"; fp.WithOptions = " WITH(NOLOCK)"; fp.TableReName = "M"; fp.JoinSQL = "INNER JOIN Companies AS C WITH(NOLOCK) ON M.Id = C.UserId"; StringBuilder sbSQL = new StringBuilder(); sbSQL.Append(" 1 = 1"); if(setting.CompanyStatus != CompanyStatus.None){ sbSQL.AppendFormat(" AND C.CompanyStatus = {0}",(int)setting.CompanyStatus); } if(!string.IsNullOrEmpty(setting.CompanyName)){ //这块应该检查一下是否有危险字符,防止SQL注入 //目前没有加上 xingbaifang 2012-12-05 sbSQL.AppendFormat(" AND C.CompanyName LIKE '%{0}%'",setting.CompanyName); } fp.Condition = sbSQL.ToString(); //throw new Exception(fp.Build2005()); IList<CompanyInfo> list = new List<CompanyInfo>(); CompanyInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = GetCompanyByDataRow(dr); if (model != null) { list.Add(model); } } } sbSQL = new StringBuilder(); sbSQL.Append("SELECT COUNT(*) FROM Members AS M WITH(NOLOCK) INNER JOIN Companies AS C WITH(NOLOCK) ON M.Id = C.UserId"); sbSQL.Append(" WHERE 1 = 1 "); if(fp.Condition.Length>0){ sbSQL.AppendFormat(" AND {0}",fp.Condition); } //throw new Exception(sbSQL.ToString()); int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, sbSQL.ToString())); return new PageOfList<CompanyInfo>(list, setting.PageIndex, setting.PageSize, count); }
/// <summary> /// 公司信息列表 /// </summary> /// <param name="setting"></param> /// <returns></returns> public static IPageOfList<CompanyInfo> CompanyList(MemberSearchSetting setting) { return MemberManage.CompanyList(setting); }
public static IPageOfList<MemberInfo> BaseInfoList(MemberSearchSetting setting) { FastPaging fp = new FastPaging(); fp.OverOrderBy = " CreateDateTime DESC"; fp.PageIndex = setting.PageIndex; fp.PageSize = setting.PageSize; fp.QueryFields = "*"; fp.TableName = "Members"; fp.PrimaryKey = "Id"; fp.WithOptions = " WITH(NOLOCK)"; StringBuilder sbSQL = new StringBuilder(); sbSQL.Append(" 1 = 1 "); //根据用户名查询信息 if(!string.IsNullOrEmpty(setting.UserName)){ sbSQL.AppendFormat(" AND UserName LIKE '%{0}%'",Controleng.Common.Utils.ChkSQL(setting.UserName)); } fp.Condition += sbSQL.ToString(); IList<MemberInfo> list = new List<MemberInfo>(); MemberInfo model = null; DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = GetByDataRow(dr); if (model != null) { list.Add(model); } } } string strSQL = "SELECT COUNT(*) FROM Members AS M WITH(NOLOCK) WHERE "; strSQL += fp.Condition; int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL)); return new PageOfList<MemberInfo>(list, setting.PageIndex, setting.PageSize, count); }
/// <summary> /// 基本信息列表 /// </summary> /// <param name="setting"></param> /// <returns></returns> public static IPageOfList<MemberInfo> BaseInfoList(MemberSearchSetting setting) { return MemberManage.BaseInfoList(setting); }