public int UpdateTag(TagInfo tag)
{
CheckSlug(tag);
string cmdText = @"update Loachs_Terms set
Type=@Type,
Name=@Name,
Slug=@Slug,
Description=@Description,
Displayorder=@Displayorder,
Count=@Count,
CreateDate=@CreateDate
where termid=@termid";
MySqlParameter[] prams =
{
MYSQLHelper.MakeInParam("@Type", MySqlDbType.Int32, 1, (int)TermType.Tag),
MYSQLHelper.MakeInParam("@Name", MySqlDbType.VarChar, 255, tag.Name),
MYSQLHelper.MakeInParam("@Slug", MySqlDbType.VarChar, 255, tag.Slug),
MYSQLHelper.MakeInParam("@Description", MySqlDbType.VarChar, 255, tag.Description),
MYSQLHelper.MakeInParam("@Displayorder", MySqlDbType.Int32, 4, tag.Displayorder),
MYSQLHelper.MakeInParam("@Count", MySqlDbType.Int32, 4, tag.Count),
MYSQLHelper.MakeInParam("@CreateDate", MySqlDbType.Date, 8, tag.CreateDate),
MYSQLHelper.MakeInParam("@termid", MySqlDbType.Int32, 1, tag.TagId),
};
return(Convert.ToInt32(MYSQLHelper.ExecuteScalar(CommandType.Text, cmdText, prams)));
}
/// <summary>
/// 添加
/// </summary>
/// <param name="_userinfo"></param>
/// <returns></returns>
public int InsertUser(UserInfo _userinfo)
{
string cmdText = @" insert into Loachs_Users(
Type,UserName,Name,Password,Email,SiteUrl,AvatarUrl,Description,displayorder,Status,PostCount,CommentCount,CreateDate)
values (
@Type,@UserName,@Name,@Password,@Email,@SiteUrl,@AvatarUrl,@Description,@Displayorder,@Status, @PostCount,@CommentCount,@CreateDate )";
MySqlParameter[] prams =
{
MYSQLHelper.MakeInParam("@Type", MySqlDbType.Int32, 4, _userinfo.Type),
MYSQLHelper.MakeInParam("@UserName", MySqlDbType.VarChar, 50, _userinfo.UserName),
MYSQLHelper.MakeInParam("@Name", MySqlDbType.VarChar, 50, _userinfo.Name),
MYSQLHelper.MakeInParam("@Password", MySqlDbType.VarChar, 50, _userinfo.Password),
MYSQLHelper.MakeInParam("@Email", MySqlDbType.VarChar, 50, _userinfo.Email),
MYSQLHelper.MakeInParam("@SiteUrl", MySqlDbType.VarChar, 255, _userinfo.SiteUrl),
MYSQLHelper.MakeInParam("@AvatarUrl", MySqlDbType.VarChar, 255, _userinfo.AvatarUrl),
MYSQLHelper.MakeInParam("@description", MySqlDbType.VarChar, 255, _userinfo.Description),
MYSQLHelper.MakeInParam("@Displayorder", MySqlDbType.Int32, 4, _userinfo.Displayorder),
MYSQLHelper.MakeInParam("@Status", MySqlDbType.Int32, 4, _userinfo.Status),
MYSQLHelper.MakeInParam("@PostCount", MySqlDbType.Int32, 4, _userinfo.PostCount),
MYSQLHelper.MakeInParam("@CommentCount", MySqlDbType.Int32, 4, _userinfo.CommentCount),
MYSQLHelper.MakeInParam("@CreateDate", MySqlDbType.Date, 8, _userinfo.CreateDate),
};
int r = MYSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
if (r > 0)
{
//return Convert.ToInt32(MYSQLHelper.ExecuteScalar("select top 1 UserId from Loachs_Users order by UserId desc"));
return(Convert.ToInt32(MYSQLHelper.ExecuteScalar("select UserId from Loachs_Users order by UserId desc limit 0,1")));
}
return(0);
}
public int UpdateLink(LinkInfo link)
{
string cmdText = @"update Loachs_Links set
type=@type,
name=@name,
href=@href,
position=@position,
target=@target,
description=@description,
displayorder=@displayorder,
status=@status,
createdate=@createdate
where linkid=@linkid";
MySqlParameter[] prams =
{
MYSQLHelper.MakeInParam("@type", MySqlDbType.Int32, 4, link.Type),
MYSQLHelper.MakeInParam("@name", MySqlDbType.VarChar, 100, link.Name),
MYSQLHelper.MakeInParam("@href", MySqlDbType.VarChar, 255, link.Href),
MYSQLHelper.MakeInParam("@position", MySqlDbType.Int32, 4, link.Position),
MYSQLHelper.MakeInParam("@target", MySqlDbType.VarChar, 50, link.Target),
MYSQLHelper.MakeInParam("@description", MySqlDbType.VarChar, 255, link.Description),
MYSQLHelper.MakeInParam("@displayorder", MySqlDbType.Int32, 4, link.Displayorder),
MYSQLHelper.MakeInParam("@status", MySqlDbType.Int32, 4, link.Status),
MYSQLHelper.MakeInParam("@createdate", MySqlDbType.Date, 8, link.CreateDate),
MYSQLHelper.MakeInParam("@linkid", MySqlDbType.Int32, 4, link.LinkId),
};
return(Convert.ToInt32(MYSQLHelper.ExecuteScalar(CommandType.Text, cmdText, prams)));
}
public int InsertTag(TagInfo tag)
{
CheckSlug(tag);
string cmdText = @"insert into Loachs_Terms
(
Type,Name,Slug,Description,Displayorder,Count,CreateDate
)
values
(
@Type,@Name,@Slug,@Description,@Displayorder,@Count,@CreateDate
)";
MySqlParameter[] prams =
{
MYSQLHelper.MakeInParam("@Type", MySqlDbType.Int32, 1, (int)TermType.Tag),
MYSQLHelper.MakeInParam("@Name", MySqlDbType.VarChar, 255, tag.Name),
MYSQLHelper.MakeInParam("@Slug", MySqlDbType.VarChar, 255, tag.Slug),
MYSQLHelper.MakeInParam("@Description", MySqlDbType.VarChar, 255, tag.Description),
MYSQLHelper.MakeInParam("@Displayorder", MySqlDbType.Int32, 4, tag.Displayorder),
MYSQLHelper.MakeInParam("@Count", MySqlDbType.Int32, 4, tag.Count),
MYSQLHelper.MakeInParam("@CreateDate", MySqlDbType.Date, 8, tag.CreateDate)
};
MYSQLHelper.ExecuteScalar(CommandType.Text, cmdText, prams);
//int newId = Convert.ToInt32(MYSQLHelper.ExecuteScalar("select top 1 termid from Loachs_Terms order by termid desc"));
int newId = Convert.ToInt32(MYSQLHelper.ExecuteScalar("select termid from Loachs_Terms order by termid desc limit 0,1"));
return(newId);
}
/// <summary>
/// 检查别名是否重复
/// </summary>
/// <param name="term"></param>
/// <returns></returns>
private bool CheckSlug(PostInfo post)
{
if (string.IsNullOrEmpty(post.Slug))
{
return(true);
}
while (true)
{
string cmdText = string.Empty;
if (post.PostId == 0)
{
cmdText = string.Format("select count(1) from Loachs_Posts where slug='{0}' ", post.Slug);
}
else
{
cmdText = string.Format("select count(1) from Loachs_Posts where slug='{0}' and postid<>{1}", post.Slug, post.PostId);
}
int r = Convert.ToInt32(MYSQLHelper.ExecuteScalar(cmdText));
if (r == 0)
{
return(true);
}
post.Slug += "-2";
}
}
public int InsertLink(LinkInfo link)
{
string cmdText = @"insert into Loachs_Links
(
type,name,href,position,target,description,displayorder,status,createdate
)
values
(
@type,@name,@href,@position,@target,@description,@displayorder,@status,@createdate
)";
MySqlParameter[] prams =
{
MYSQLHelper.MakeInParam("@type", MySqlDbType.Int32, 4, link.Type),
MYSQLHelper.MakeInParam("@name", MySqlDbType.VarChar, 100, link.Name),
MYSQLHelper.MakeInParam("@href", MySqlDbType.VarChar, 255, link.Href),
MYSQLHelper.MakeInParam("@position", MySqlDbType.Int32, 4, link.Position),
MYSQLHelper.MakeInParam("@target", MySqlDbType.VarChar, 50, link.Target),
MYSQLHelper.MakeInParam("@description", MySqlDbType.VarChar, 255, link.Description),
MYSQLHelper.MakeInParam("@displayorder", MySqlDbType.Int32, 4, link.Displayorder),
MYSQLHelper.MakeInParam("@status", MySqlDbType.Int32, 4, link.Status),
MYSQLHelper.MakeInParam("@createdate", MySqlDbType.Date, 8, link.CreateDate),
};
int r = MYSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
if (r > 0)
{
return(Convert.ToInt32(MYSQLHelper.ExecuteScalar("select linkid from Loachs_Links order by linkid desc limit 0,1")));
}
return(0);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="comment"></param>
/// <returns></returns>
public int InsertComment(CommentInfo comment)
{
string cmdText = @"insert into Loachs_Comments(
PostId, ParentId,UserId,Name,Email,SiteUrl,Content,EmailNotify,IpAddress,CreateDate,Approved)
values (
@PostId, @ParentId,@UserId,@Name,@Email,@SiteUrl,@Content,@EmailNotify,@IpAddress,@CreateDate,@Approved)";
MySqlParameter[] prams =
{
MYSQLHelper.MakeInParam("@PostId", MySqlDbType.Int32, 4, comment.PostId),
MYSQLHelper.MakeInParam("@ParentId", MySqlDbType.Int32, 4, comment.ParentId),
MYSQLHelper.MakeInParam("@UserId", MySqlDbType.Int32, 4, comment.UserId),
MYSQLHelper.MakeInParam("@Name", MySqlDbType.VarChar, 255, comment.Name),
MYSQLHelper.MakeInParam("@Email", MySqlDbType.VarChar, 255, comment.Email),
MYSQLHelper.MakeInParam("@SiteUrl", MySqlDbType.VarChar, 255, comment.SiteUrl),
MYSQLHelper.MakeInParam("@Content", MySqlDbType.VarChar, 255, comment.Content),
MYSQLHelper.MakeInParam("@EmailNotify", MySqlDbType.Int32, 4, comment.EmailNotify),
MYSQLHelper.MakeInParam("@IpAddress", MySqlDbType.VarChar, 255, comment.IpAddress),
MYSQLHelper.MakeInParam("@CreateDate", MySqlDbType.Date, 8, comment.CreateDate),
MYSQLHelper.MakeInParam("@Approved", MySqlDbType.Int32, 4, comment.Approved),
};
MYSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
int newId = Convert.ToInt32(MYSQLHelper.ExecuteScalar("select CommentId from Loachs_Comments order by CommentId desc limit 0,1"));
return(newId);
}
/// <summary>
/// 是否存在
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public bool ExistsUserName(string userName)
{
string cmdText = "select count(1) from Loachs_Users where userName = @userName ";
MySqlParameter[] prams =
{
MYSQLHelper.MakeInParam("@userName", MySqlDbType.VarChar, 50, userName),
};
return(Convert.ToInt32(MYSQLHelper.ExecuteScalar(CommandType.Text, cmdText, prams)) > 0);
}
public SettingInfo GetSetting()
{
//string cmdText = "select top 1 [setting] from [Loachs_Sites]";
string cmdText = "select setting from Loachs_Sites limit 0,1";
string str = Convert.ToString(MYSQLHelper.ExecuteScalar(cmdText));
object obj = DeSerialize(typeof(SettingInfo), str);
if (obj == null)
{
return(new SettingInfo());
}
return((SettingInfo)obj);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="postinfo">实体</param>
/// <returns>成功返回新记录的ID,失败返回 0</returns>
public int InsertPost(PostInfo postinfo)
{
CheckSlug(postinfo);
string cmdText = @"insert into Loachs_Posts
(
CategoryId,Title,Summary,Content,Slug,UserId,CommentStatus,CommentCount,ViewCount,Tag,UrlFormat,Template,Recommend,Status,TopStatus,HideStatus,CreateDate,UpdateDate
)
values
(
@CategoryId,@Title,@Summary,@Content,@Slug,@UserId,@CommentStatus,@CommentCount,@ViewCount,@Tag,@UrlFormat,@Template,@Recommend,@Status,@TopStatus,@HideStatus,@CreateDate,@UpdateDate
)";
MySqlParameter[] prams =
{
MYSQLHelper.MakeInParam("@CategoryId", MySqlDbType.Int32, 4, postinfo.CategoryId),
MYSQLHelper.MakeInParam("@Title", MySqlDbType.VarChar, 255, postinfo.Title),
MYSQLHelper.MakeInParam("@Summary", MySqlDbType.VarChar, 0, postinfo.Summary),
MYSQLHelper.MakeInParam("@Content", MySqlDbType.VarChar, 0, postinfo.Content),
MYSQLHelper.MakeInParam("@Slug", MySqlDbType.VarChar, 255, postinfo.Slug),
MYSQLHelper.MakeInParam("@UserId", MySqlDbType.Int32, 4, postinfo.UserId),
MYSQLHelper.MakeInParam("@CommentStatus", MySqlDbType.Int32, 1, postinfo.CommentStatus),
MYSQLHelper.MakeInParam("@CommentCount", MySqlDbType.Int32, 4, postinfo.CommentCount),
MYSQLHelper.MakeInParam("@ViewCount", MySqlDbType.Int32, 4, postinfo.ViewCount),
MYSQLHelper.MakeInParam("@Tag", MySqlDbType.VarChar, 255, postinfo.Tag),
MYSQLHelper.MakeInParam("@UrlFormat", MySqlDbType.Int32, 1, postinfo.UrlFormat),
MYSQLHelper.MakeInParam("@Template", MySqlDbType.VarChar, 50, postinfo.Template),
MYSQLHelper.MakeInParam("@Recommend", MySqlDbType.Int32, 1, postinfo.Recommend),
MYSQLHelper.MakeInParam("@Status", MySqlDbType.Int32, 1, postinfo.Status),
MYSQLHelper.MakeInParam("@TopStatus", MySqlDbType.Int32, 1, postinfo.TopStatus),
MYSQLHelper.MakeInParam("@HideStatus", MySqlDbType.Int32, 1, postinfo.HideStatus),
MYSQLHelper.MakeInParam("@CreateDate", MySqlDbType.Date, 8, postinfo.CreateDate),
MYSQLHelper.MakeInParam("@UpdateDate", MySqlDbType.Date, 8, postinfo.UpdateDate)
};
MYSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
//int newId = StringHelper.ObjectToInt(MYSQLHelper.ExecuteScalar("select top 1 PostId from Loachs_Posts order by PostId desc"));
int newId = StringHelper.ObjectToInt(MYSQLHelper.ExecuteScalar("select PostId from Loachs_Posts order by PostId desc limit 0,1"));
//if (newId > 0)
//{
// MYSQLHelper.ExecuteNonQuery(string.Format("update [Loachs_Users] set [postcount]=[postcount]+1 where [userid]={0}", postinfo.UserId));
// MYSQLHelper.ExecuteNonQuery("update [Loachs_Sites] set [postcount]=[postcount]+1");
// MYSQLHelper.ExecuteNonQuery(string.Format("update [Loachs_Terms] set [count]=[count]+1 where [termid]={0}", postinfo.CategoryId));
//}
return(newId);
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="postId"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="totalRecord"></param>
/// <returns></returns>
public List <CommentInfo> GetCommentList(int pageSize, int pageIndex, out int totalRecord, int order, int userId, int postId, int parentId, int approved, int emailNotify, string keyword)
{
string condition = " 1=1 ";// "[ParentId]=0 and [PostId]=" + postId;
if (userId != -1)
{
condition += " and userid=" + userId;
}
if (postId != -1)
{
condition += " and postId=" + postId;
}
if (parentId != -1)
{
condition += " and parentId=" + parentId;
}
if (approved != -1)
{
condition += " and approved=" + approved;
}
if (emailNotify != -1)
{
condition += " and emailNotify=" + emailNotify;
}
if (!string.IsNullOrEmpty(keyword))
{
condition += string.Format(" and (content like '%{0}%' or author like '%{0}%' or ipaddress like '%{0}%' or email like '%{0}%' or siteurl like '%{0}%' )", keyword);
}
string cmdTotalRecord = "select count(1) from Loachs_Comments where " + condition;
totalRecord = Convert.ToInt32(MYSQLHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord));
// throw new Exception(cmdTotalRecord);
string cmdText = MYSQLHelper.GetPageSql("Loachs_Comments", "CommentId", "*", pageSize, pageIndex, order, condition);
return(DataReaderToCommentList(MYSQLHelper.ExecuteReader(cmdText)));
}
/// <summary>
/// 统计评论
/// </summary>
/// <param name="userId"></param>
/// <param name="postId"></param>
/// <param name="incChild"></param>
/// <returns></returns>
public int GetCommentCount(int userId, int postId, bool incChild)
{
string condition = " 1=1 ";
if (userId != -1)
{
condition += " and userId = " + userId;
}
if (postId != -1)
{
condition += " and postId = " + postId;
}
if (incChild == false)
{
condition += " and parentid=0";
}
string cmdText = "select count(1) from Loachs_Comments where " + condition;
return(Convert.ToInt32(MYSQLHelper.ExecuteScalar(CommandType.Text, cmdText)));
}
/// <summary>
/// 检查别名是否重复
/// </summary>
/// <param name="term"></param>
/// <returns></returns>
private bool CheckSlug(TagInfo term)
{
while (true)
{
string cmdText = string.Empty;
if (term.TagId == 0)
{
cmdText = string.Format("select count(1) from Loachs_Terms where Slug='{0}' and type={1}", term.Slug, (int)TermType.Tag);
}
else
{
cmdText = string.Format("select count(1) from Loachs_Terms where Slug='{0}' and type={1} and termid<>{2}", term.Slug, (int)TermType.Tag, term.TagId);
}
int r = Convert.ToInt32(MYSQLHelper.ExecuteScalar(cmdText));
if (r == 0)
{
return(true);
}
term.Slug += "-2";
}
}
public List <PostInfo> GetPostList(int pageSize, int pageIndex, out int recordCount, int categoryId, int tagId, int userId, int recommend, int status, int topstatus, int hidestatus, string begindate, string enddate, string keyword)
{
string condition = " 1=1 ";
if (categoryId != -1)
{
condition += " and categoryId=" + categoryId;
}
if (tagId != -1)
{
condition += " and tag like '%{" + tagId + "}%'";
}
if (userId != -1)
{
condition += " and userid=" + userId;
}
if (recommend != -1)
{
condition += " and recommend=" + recommend;
}
if (status != -1)
{
condition += " and status=" + status;
}
if (topstatus != -1)
{
condition += " and topstatus=" + topstatus;
}
if (hidestatus != -1)
{
condition += " and hidestatus=" + hidestatus;
}
if (!string.IsNullOrEmpty(begindate))
{
condition += " and createdate>=#" + begindate + "#";
}
if (!string.IsNullOrEmpty(enddate))
{
condition += " and createdate<#" + enddate + "#";
}
if (!string.IsNullOrEmpty(keyword))
{
condition += string.Format(" and (summary like '%{0}%' or title like '%{0}%' )", keyword);
}
string cmdTotalRecord = "select count(1) from Loachs_Posts where " + condition;
// throw new Exception(cmdTotalRecord);
recordCount = StringHelper.ObjectToInt(MYSQLHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord));
string cmdText = MYSQLHelper.GetPageSql("Loachs_Posts", "PostId", "*", pageSize, pageIndex, 1, condition);
return(DataReaderToCommentList(MYSQLHelper.ExecuteReader(cmdText)));
}