public override string[] FindUsersInRole(string roleName, string usernameToMatch)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
SecUtility.CheckParameter(ref usernameToMatch, true, true, false, 255, "usernameToMatch");
#region - T-SQL -
StringBuilder strSQL = new StringBuilder();
strSQL.Append(" SELECT u.Username FROM dbo.SiteRole AS r");
strSQL.Append(" INNER JOIN dbo.Tech2Type AS t ON r.id = t.TechId");
strSQL.Append(" INNER JOIN dbo.v_User2ID AS u ON u.id = t.TypeId");
strSQL.Append(" WHERE r.RoleName = @rolename");
strSQL.Append(" AND u.Username LIKE @UserNameToMatch+'%'");
strSQL.Append(" AND t.[Type] = 'UsersInRoles'");
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@rolename", SqlDbType.VarChar, 50),
new SqlParameter("@UserNameToMatch", SqlDbType.VarChar, 20)
};
parms[0].Value = roleName;
parms[1].Value = usernameToMatch;
#endregion
DataTable oTable = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL.ToString(), parms);
return(DataTable2Array(oTable));
}
/// <summary>
/// 根据自定义条件获得文章列表
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
public static List <ArticleInfo> ListWithoutPageByCondition(string condition)
{
List <ArticleInfo> list = new List <ArticleInfo>();
if (string.IsNullOrEmpty(condition))
{
return(list);
}
ArticleInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, condition);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByDataRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
return(list);
}
public static IPageOfList <OrderAdminInfo> List(SearchSetting settings)
{
FastPaging fp = new FastPaging();
fp.PageIndex = settings.PageIndex;
fp.PageSize = settings.PageSize;
fp.TableName = "OrderAdmins";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = " CreateDateTime DESC";
IList <OrderAdminInfo> list = new List <OrderAdminInfo>();
OrderAdminInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = Get(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Count();
return(new PageOfList <OrderAdminInfo>(list, settings.PageIndex, settings.PageSize, count));
}
/// <summary>
/// 广告位列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList <AdPositionInfo> List(AdSearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " CreateDateTime DESC";
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.QueryFields = "*";
fp.TableName = "AdPosition";
fp.PrimaryKey = "Id";
fp.WithOptions = " WITH(NOLOCK)";
IList <AdPositionInfo> list = new List <AdPositionInfo>();
AdPositionInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));
return(new PageOfList <AdPositionInfo>(list, setting.PageIndex, setting.PageSize, count));
}
/// <summary>
/// 下载用户基本信息
/// 2012-12-17
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public static DataTable DownloadBaseInfo(int[] ids, DateTime startDate, DateTime endDate)
{
DataTable dt = new DataTable();
bool isDownload = false; //设置一个状态，避免下载全部用户信息
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append("SELECT * FROM Members WITH(NOLOCK) WHERE 1 =1 ");
if (ids != null && ids.Length > 0)
{
sbSQL.AppendFormat(" AND Id IN({0}) ", string.Join(",", ids));
isDownload = true;
}
if ((startDate > DateTime.MinValue && startDate < DateTime.MaxValue) &&
(endDate > DateTime.MinValue && endDate < DateTime.MaxValue))
{
sbSQL.AppendFormat(" AND CreateDateTime BETWEEN '{0}' AND '{1}'", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
isDownload = true;
}
if (isDownload)
{
return(SQLPlus.ExecuteDataTable(CommandType.Text, sbSQL.ToString()));
}
return(dt);
}
/// <summary>
/// 获得没有分页的博客列表
/// 默认10条
/// </summary>
/// <param name="userId">0</param>
/// <param name="topCount">默认10条</param>
/// <returns></returns>
public static IList <BlogPostInfo> ListWithoutPage(int userId, int topCount = 10)
{
IList <BlogPostInfo> list = new List <BlogPostInfo>();
BlogPostInfo model = null;
string strSQL = "SELECT TOP(@TopCount) * FROM BlogPosts WITH(NOLOCK) WHERE UserId = @UserId AND IsDeleted = 0 ORDER BY CreateDateTime DESC";
SqlParameter[] parms =
{
new SqlParameter("TopCount", SqlDbType.Int),
new SqlParameter("UserId", SqlDbType.Int),
};
parms[0].Value = topCount;
parms[1].Value = userId;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL, parms);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByDataRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
return(list);
}
public override string[] GetUsersInRole(string roleName)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
#region - T-SQL -
StringBuilder strSQL = new StringBuilder();
strSQL.Append(" SELECT u.Username FROM dbo.SiteRole AS r");
strSQL.Append(" INNER JOIN dbo.Tech2Type AS t ON r.id = t.TechId");
strSQL.Append(" INNER JOIN dbo.v_User2ID AS u ON u.id = t.TypeId");
strSQL.Append(" WHERE r.RoleName = @rolename;");
strSQL.Append(" AND t.[Type] = 'UsersInRoles'");
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@rolename", SqlDbType.VarChar, 50)
};
parms[0].Value = roleName;
#endregion
DataTable oTable = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL.ToString(), parms);
StringCollection sc = new StringCollection();
String[] strReturn;
foreach (DataRow r in oTable.Rows)
{
sc.Add(r[0].ToString().Trim());
}
strReturn = new String[sc.Count];
sc.CopyTo(strReturn, 0);
return(strReturn);
}
public static IList <ColumnInfo> ColumnListByParentId(int parentId)
{
string strSQL = "SELECT * FROM Categories WITH(NOLOCK) WHERE ParentId = @ParentId ORDER BY Sort";
SqlParameter parm = new SqlParameter("ParentId", parentId);
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL, parm);
var list = new List <ColumnInfo>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(new ColumnInfo()
{
Alias = dr.Field <string>("Alias"),
Name = dr.Field <string>("Name"),
IsDeleted = dr.Field <bool>("IsDeleted"),
Id = dr.Field <int>("Id"),
Sort = dr.Field <int>("Sort"),
ParentId = dr.Field <int>("ParentId"),
ParentIds = dr.Field <string>("ParentIds"),
RootId = dr.Field <int>("RootId")
});
}
}
return(list);
}
/// <summary>
/// 获得每月的用户数据
/// </summary>
/// <param name="month"></param>
/// <returns></returns>
public static DataTable ListByMonth(string month)
{
string startMonth = string.Format("{0}-01", month);
string strSQL = string.Format("SELECT * FROM dbo.Members WITH(NOLOCK) WHERE CreateDateTime BETWEEN '{0}' AND DATEADD(MONTH,1,'{0}')", startMonth);
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL);
return(dt);
}
public override string[] GetAllRoles()
{
string strSQL = "SELECT RoleName FROM dbo.SiteRoles";
DataTable oTable = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL);
return(DataTable2Array(oTable));
}
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));
}
private DataTable DownLoadUserList(string strUserNameList)
{
StringBuilder sb = new StringBuilder("SET NOCOUNT ON;");
sb.Append(" SELECT UserName,Email,RealName,Mobile,Phone,[Address],Province,City,Position,Industry,LastLoginDateTime,[Type],(CASE [Type] WHEN 1 THEN Members.Company ELSE Companies.CompanyName END ) AS CompanyName FROM [Members] WITH(NOLOCK) LEFT JOIN Companies WITH(NOLOCK) ON Members.Id = Companies.UserId ");
sb.Append(" WHERE");
sb.AppendFormat(" Members.UserName IN ({0})", strUserNameList);
return(SQLPlus.ExecuteDataTable(CommandType.Text, sb.ToString()));
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public static DataTable Export(string month)
{
string strSQL = "SELECT * FROM EletterSubscribes WITH(NOLOCK)";
if (!string.IsNullOrEmpty(month))
{
strSQL = "SELECT * FROM EletterSubscribes WITH(NOLOCK) WHERE [CreateDateTime] BETWEEN DATEADD(m,DATEDIFF(m,'1900-1-1',@Date),'1900-1-1') AND DATEADD(m,DATEDIFF(m,'1900-1-1',@Date)+1,'1900-1-1')-.00001";
}
SqlParameter parm = new SqlParameter("Date", string.Format("{0}-01", month));
return(SQLPlus.ExecuteDataTable(CommandType.Text, strSQL, parm));
}
/// <summary>
/// 获得相关文章
/// </summary>
/// <param name="topCount"></param>
/// <param name="articleId"></param>
/// <returns></returns>
public static List <ArticleInfo> GetRelatedArticleList(int topCount, int articleId, string tags)
{
List <ArticleInfo> list = new List <ArticleInfo>();
tags = tags.Replace("，", ",");
tags = Regex.Replace(tags, @"(\s+)", ",");
string[] t = tags.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Length == 0)
{
return(list);
}
string value = Enumerable.Range(0, t.Length).Select(i => {
if (!string.IsNullOrEmpty(t[i]))
{
return(string.Format("SELECT '{0}' AS T", t[i]));
}
return(string.Empty);
}).Aggregate((a, b) => { return(a + " UNION " + b); });
if (!string.IsNullOrEmpty(value))
{
StringBuilder sbSQL = new StringBuilder();
sbSQL.AppendFormat(" SELECT TOP({0})* FROM Articles AS A WITH(NOLOCK)", topCount);
sbSQL.Append(" WHERE EXISTS(");
sbSQL.Append(" SELECT ArticleId FROM dbo.ArticleTags AS AT WITH(NOLOCK)");
sbSQL.Append(" WHERE EXISTS(");
sbSQL.AppendFormat(" SELECT T FROM ({0}) AS TP WHERE AT.Tag LIKE TP.T+'%'", value); //LIKE效率低
sbSQL.Append(" )");
sbSQL.Append(" AND A.Id = AT.ArticleId");
sbSQL.Append(" GROUP BY AT.ArticleId");
sbSQL.Append(" )");
sbSQL.AppendFormat(" AND A.Id <> {0}", articleId);
sbSQL.Append(" AND IsDeleted = 0");
sbSQL.Append(" ORDER BY PublishDateTime DESC");
//throw new Exception(sbSQL.ToString());
ArticleInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, sbSQL.ToString());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByDataRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
}
return(list);
}
/// <summary>
/// 获得系统分类
/// </summary>
/// <returns></returns>
public static List <CompanyProductCategoryInfo> GetSystemCategoryList()
{
string strSQL = "SELECT * FROM CompanyProductCategories WITH(NOLOCK) WHERE IsSystem = 1 AND IsDeleted = 0";
List <CompanyProductCategoryInfo> list = new List <CompanyProductCategoryInfo>();
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(GetByDataRow(dr));
}
}
return(list);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static List <Tuple <string, int> > MonthList()
{
List <Tuple <string, int> > list = new List <Tuple <string, int> >();
string strSQL = "SELECT COUNT(*) AS c,CONVERT(VARCHAR(7),CreateDateTime,120) AS m FROM dbo.Members WITH(NOLOCK) GROUP BY CONVERT(VARCHAR(7),CreateDateTime,120) ORDER BY m DESC";
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(Tuple.Create(dr.Field <string>("m"), dr.Field <int>("c")));
}
}
return(list);
}
public static IList <Tuple <int, string> > CompanyList()
{
string strSQL = "SELECT * FROM OrderUsers WITH(NOLOCK)";
IList <Tuple <int, string> > list = new List <Tuple <int, string> >();
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(Tuple.Create(dr.Field <int>("id"), dr.Field <string>("CompanyName")));
}
}
return(list);
}
public static List <RoleInfo> List()
{
string strSQL = "SELECT * FROM Roles WITH(NOLOCK) ORDER BY SiteId ASC";
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL);
List <RoleInfo> list = new List <RoleInfo>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(Get(dr));
}
}
return(list);
}
/// <summary>
/// 管理员列表
/// </summary>
/// <returns></returns>
public static List <string> AdminMemberList()
{
var list = new List <string>();
string strSQL = "SELECT UserName FROM AdminMembers";
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(dr.Field <string>("UserName"));
}
}
return(list);
}
/// <summary>
/// 获得所有分类
/// </summary>
/// <returns></returns>
public static IList <VideoCatInfo> VideoCatList()
{
IList <VideoCatInfo> list = new List <VideoCatInfo>();
string strSQL = string.Format("SELECT * FROM Categories WITH(NOLOCK) WHERE [Type] = '{0}' ORDER BY Sort", CatType.Video.ToString().ToLower());
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(GetVideoCatInfoByDataRow(dr));
}
}
return(list);
}
/// <summary>
/// 获得用户自定义分类
/// </summary>
/// <param name="companyId"></param>
/// <returns></returns>
public static List <CompanyProductCategoryInfo> GetCategoryList(int companyId)
{
string strSQL = "SELECT * FROM CompanyProductCategories WITH(NOLOCK) WHERE CompanyId = @CompanyId AND IsDeleted = 0";
SqlParameter parm = new SqlParameter("CompanyId", companyId);
List <CompanyProductCategoryInfo> list = new List <CompanyProductCategoryInfo>();
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL, parm);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(GetByDataRow(dr));
}
}
return(list);
}
private static int[] GetTechIds(int newsId)
{
string strSQL = "SELECT * FROM CompanyNew2Tech WITH(NOLOCK) WHERE NewsId = @NewsId";
SqlParameter parm = new SqlParameter("NewsId", newsId);
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL, parm);
List <int> techIds = new List <int>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
techIds.Add(dr.Field <int>("TechId"));
}
}
return(techIds.ToArray());
}
/// <summary>
/// 获得公司产品类别
/// </summary>
/// <param name="companyId"></param>
/// <returns></returns>
private static List <int> _GetCompanyCategory(int companyId)
{
List <int> list = new List <int>();
string strSQL = "SELECT * FROM Company2Category WITH(NOLOCK) WHERE CompanyId = @CompanyId";
SqlParameter parm = new SqlParameter("CompanyId", companyId);
var dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL, parm);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(dr.Field <int>("CategoryId"));
}
}
return(list);
}
public static List <ForumGroupInfo> GroupList()
{
string strSQL = "SELECT * FROM ForumGroups WITH(NOLOCK) ORDER BY Sort";
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL);
List <ForumGroupInfo> list = new List <ForumGroupInfo>();
ForumGroupInfo model = null;
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
model = GetGroup(item);
list.Add(model);
}
}
return(list);
}
public static List <RoleInfo> GetRolesByAdminId(int adminId)
{
string strSQL = "SELECT * FROM AdminInRoles WITH(NOLOCK) WHERE AdminId = @AdminId";
SqlParameter parm = new SqlParameter("AdminId", adminId);
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL, parm);
List <RoleInfo> roleList = new List <RoleInfo>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
//引用RoleManage.cs
roleList.Add(RoleManage.Get(dr.Field <int>("roleId")));
}
}
return(roleList);
}
public override string[] GetRolesForUser(string username)
{
string cachekey = "GetRolesForUser_" + username;
//if (HttpContext.Current.Cache[cachekey] == null)
{
SecUtility.CheckParameter(ref username, true, false, true, 255, "username");
if (username.Length < 1)
{
return(new string[0]);
}
#region - T-SQL -
StringBuilder strSQL = new StringBuilder();
strSQL.Append(" SELECT * FROM SiteRoles");
strSQL.Append(" WHERE EXISTS(");
strSQL.Append(" SELECT * FROM MembersInRoles");
strSQL.Append(" WHERE EXISTS(");
strSQL.Append(" SELECT * FROM Members");
strSQL.Append(" WHERE Members.UserId = MembersInRoles.UserId");
strSQL.Append(" AND Members.UserName = @UserName");
strSQL.Append(" ) AND SiteRoles.id = MembersInRoles.RoleId");
strSQL.Append(" )");
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@username", SqlDbType.VarChar, 20)
};
parms[0].Value = username;
#endregion
DataTable oTable = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL.ToString(), parms);
StringCollection sc = new StringCollection();
string[] strReturn;
foreach (DataRow r in oTable.Rows)
{
sc.Add(r["RoleName"].ToString().Trim());
}
strReturn = new string[sc.Count];
sc.CopyTo(strReturn, 0);
return(strReturn);
}
}
/// <summary>
/// 列表
/// </summary>
/// <param name="searchSetting"></param>
/// <returns></returns>
public static IPageOfList <CompanyProductInfo> List(CompanyProductSearchSetting searchSetting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " CP.PublishDateTime DESC";
fp.PageIndex = searchSetting.PageIndex;
fp.PageSize = searchSetting.PageSize;
fp.QueryFields = "*";
fp.TableName = "CompanyProducts";
fp.PrimaryKey = "Id";
fp.TableReName = "CP";
fp.WithOptions = " WITH(NOLOCK)";
fp.Condition = " IsDeleted = 0 ";
if (searchSetting.CompanyId > 0)
{
fp.Condition += string.Format(" AND CompanyId = {0}", searchSetting.CompanyId);
}
if (searchSetting.CategoryId > 0)
{
fp.Condition += string.Format(" AND CategoryId = {0}", searchSetting.CategoryId);
}
if (searchSetting.SystemCategoryId > 0)
{
fp.Condition += string.Format(" AND SystemCategoryId = {0}", searchSetting.SystemCategoryId);
}
IList <CompanyProductInfo> list = new List <CompanyProductInfo>();
CompanyProductInfo 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);
}
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));
return(new PageOfList <CompanyProductInfo>(list, searchSetting.PageIndex, searchSetting.PageSize, count));
}
public static List <ForumInfo> List(int groupId)
{
string strSQL = "SELECT * FROM Forums WITH(NOLOCK) WHERE GroupId = @GroupId ORDER BY Sort";
SqlParameter parm = new SqlParameter("GroupId", groupId);
List <ForumInfo> list = new List <ForumInfo>();
ForumInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL, parm);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = Get(dr);
list.Add(model);
}
}
return(list);
}
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="searchSetting"></param>
/// <returns></returns>
public static IPageOfList <BlogPostInfo> List(BlogSearchSetting searchSetting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " BP.CreateDateTime DESC";
fp.PageIndex = searchSetting.PageIndex;
fp.PageSize = searchSetting.PageSize;
fp.QueryFields = "*";
fp.TableName = "BlogPosts";
fp.PrimaryKey = "Id";
fp.TableReName = "BP";
fp.WithOptions = " WITH(NOLOCK)";
StringBuilder sbCondition = new StringBuilder();
sbCondition.Append(" IsDeleted = 0 ");
if (searchSetting.UserId > 0)
{
sbCondition.AppendFormat(" AND UserId = {0}", searchSetting.UserId);
}
if (searchSetting.SystemCategoryId > 0)
{
sbCondition.AppendFormat(" AND SystemCategoryId = {0}", searchSetting.SystemCategoryId);
}
fp.Condition = sbCondition.ToString();
IList <BlogPostInfo> list = new List <BlogPostInfo>();
BlogPostInfo 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);
}
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));
return(new PageOfList <BlogPostInfo>(list, searchSetting.PageIndex, searchSetting.PageSize, count));
}