public int UpdatePostViewCount(int postId, int addCount)
{
string cmdText = "update [loachs_posts] set [viewcount] = [viewcount] + @addcount where [postid]=@postid";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@addcount", OleDbType.Integer, 4, addCount),
OleDbHelper.MakeInParam("@postid", OleDbType.Integer, 4, postId),
};
return(OleDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
public bool UpdateSetting(SettingInfo setting)
{
string cmdText = @"update [loachs_sites] set [setting]=@setting";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@setting", OleDbType.VarWChar, 0, Serialize(setting)),
};
return(OleDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams) == 1);
}
/// <summary>
/// 根据日志ID删除评论
/// </summary>
/// <param name="postId">日志ID</param>
/// <returns></returns>
public int DeleteCommentByPost(int postId)
{
string cmdText = "delete from [loachs_comments] where [postId] = @postId";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@postId", OleDbType.Integer, 4, postId)
};
int result = OleDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
return(result);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="commentId"></param>
/// <returns></returns>
public int DeleteComment(int commentId)
{
CommentInfo comment = GetComment(commentId); //删除前
string cmdText = "delete from [loachs_comments] where [commentId] = @commentId";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@commentId", OleDbType.Integer, 4, commentId)
};
int result = OleDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
return(result);
}
public StatisticsInfo GetStatistics()
{
string cmdText = "select top 1 * from [loachs_sites]";
string insertText = "insert into [loachs_sites] ([PostCount],[CommentCount],[VisitCount],[TagCount],[setting]) values ( '0','0','0','0','<?xml version=\"1.0\" encoding=\"utf-8\"?><SettingInfo xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"></SettingInfo>')";
List <StatisticsInfo> list = DataReaderToList(OleDbHelper.ExecuteReader(cmdText));
if (list.Count == 0)
{
OleDbHelper.ExecuteNonQuery(insertText);
}
list = DataReaderToList(OleDbHelper.ExecuteReader(cmdText));
return(list.Count > 0 ? list[0] : null);
}
public bool UpdateStatistics(StatisticsInfo statistics)
{
string cmdText = @"update [loachs_sites] set
PostCount=@PostCount,
CommentCount=@CommentCount,
VisitCount=@VisitCount,
TagCount=@TagCount";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@PostCount", OleDbType.Integer, 4, statistics.PostCount),
OleDbHelper.MakeInParam("@CommentCount", OleDbType.Integer, 4, statistics.CommentCount),
OleDbHelper.MakeInParam("@VisitCount", OleDbType.Integer, 4, statistics.VisitCount),
OleDbHelper.MakeInParam("@TagCount", OleDbType.Integer, 4, statistics.TagCount),
};
return(OleDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams) == 1);
}
/// <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
)";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@CategoryId", OleDbType.Integer, 4, postinfo.CategoryId),
OleDbHelper.MakeInParam("@Title", OleDbType.VarWChar, 255, postinfo.Title),
OleDbHelper.MakeInParam("@Summary", OleDbType.VarWChar, 0, postinfo.Summary),
OleDbHelper.MakeInParam("@Content", OleDbType.VarWChar, 0, postinfo.Content),
OleDbHelper.MakeInParam("@Slug", OleDbType.VarWChar, 255, postinfo.Slug),
OleDbHelper.MakeInParam("@UserId", OleDbType.Integer, 4, postinfo.UserId),
OleDbHelper.MakeInParam("@CommentStatus", OleDbType.Integer, 1, postinfo.CommentStatus),
OleDbHelper.MakeInParam("@CommentCount", OleDbType.Integer, 4, postinfo.CommentCount),
OleDbHelper.MakeInParam("@ViewCount", OleDbType.Integer, 4, postinfo.ViewCount),
OleDbHelper.MakeInParam("@Tag", OleDbType.VarWChar, 255, postinfo.Tag),
OleDbHelper.MakeInParam("@UrlFormat", OleDbType.Integer, 1, postinfo.UrlFormat),
OleDbHelper.MakeInParam("@Template", OleDbType.VarChar, 50, postinfo.Template),
OleDbHelper.MakeInParam("@Recommend", OleDbType.Integer, 1, postinfo.Recommend),
OleDbHelper.MakeInParam("@Status", OleDbType.Integer, 1, postinfo.Status),
OleDbHelper.MakeInParam("@TopStatus", OleDbType.Integer, 1, postinfo.TopStatus),
OleDbHelper.MakeInParam("@HideStatus", OleDbType.Integer, 1, postinfo.HideStatus),
OleDbHelper.MakeInParam("@CreateDate", OleDbType.Date, 8, postinfo.CreateDate),
OleDbHelper.MakeInParam("@UpdateDate", OleDbType.Date, 8, postinfo.UpdateDate)
};
OleDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
int newId = StringHelper.ObjectToInt(OleDbHelper.ExecuteScalar("select top 1 [PostId] from [Loachs_Posts] order by [PostId] desc"));
//if (newId > 0)
//{
// OleDbHelper.ExecuteNonQuery(string.Format("update [loachs_users] set [postcount]=[postcount]+1 where [userid]={0}", postinfo.UserId));
// OleDbHelper.ExecuteNonQuery("update [loachs_sites] set [postcount]=[postcount]+1");
// OleDbHelper.ExecuteNonQuery(string.Format("update [loachs_terms] set [count]=[count]+1 where [termid]={0}", postinfo.CategoryId));
//}
return(newId);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="postinfo">实体</param>
/// <returns>修改的行数</returns>
public int UpdatePost(PostInfo postinfo)
{
CheckSlug(postinfo);
//PostInfo oldPost = GetPost(postinfo.PostId); //修改前
//if (oldPost.CategoryId != postinfo.CategoryId)
//{
// OleDbHelper.ExecuteNonQuery(string.Format("update [loachs_terms] set [count]=[count]-1 where [termid]={0}", oldPost.CategoryId));
// OleDbHelper.ExecuteNonQuery(string.Format("update [loachs_terms] set [count]=[count]+1 where [termid]={0}", postinfo.CategoryId));
//}
string cmdText = "update [loachs_posts] set [CategoryId]=@CategoryId,[Title]=@Title,[Summary]=@Summary,[Content]=@Content,[Slug]=@Slug,[UserId]=@UserId,[CommentStatus]=@CommentStatus,[CommentCount]=@CommentCount,[ViewCount]=@ViewCount,[Tag]=@Tag,[UrlFormat]=@UrlFormat,[Template]=@Template,[Recommend]=@Recommend,[Status]=@Status,[TopStatus]=@TopStatus,[HideStatus]=@HideStatus,[CreateDate]=@CreateDate,[UpdateDate]=@UpdateDate where [PostId]=@PostId";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@CategoryId", OleDbType.Integer, 4, postinfo.CategoryId),
OleDbHelper.MakeInParam("@Title", OleDbType.VarWChar, 255, postinfo.Title),
OleDbHelper.MakeInParam("@Summary", OleDbType.VarWChar, 0, postinfo.Summary),
OleDbHelper.MakeInParam("@Content", OleDbType.VarWChar, 0, postinfo.Content),
OleDbHelper.MakeInParam("@Slug", OleDbType.VarWChar, 255, postinfo.Slug),
OleDbHelper.MakeInParam("@UserId", OleDbType.Integer, 4, postinfo.UserId),
OleDbHelper.MakeInParam("@CommentStatus", OleDbType.Integer, 1, postinfo.CommentStatus),
OleDbHelper.MakeInParam("@CommentCount", OleDbType.Integer, 4, postinfo.CommentCount),
OleDbHelper.MakeInParam("@ViewCount", OleDbType.Integer, 4, postinfo.ViewCount),
OleDbHelper.MakeInParam("@Tag", OleDbType.VarWChar, 255, postinfo.Tag),
OleDbHelper.MakeInParam("@UrlFormat", OleDbType.Integer, 1, postinfo.UrlFormat),
OleDbHelper.MakeInParam("@Template", OleDbType.VarChar, 50, postinfo.Template),
OleDbHelper.MakeInParam("@Recommend", OleDbType.Integer, 1, postinfo.Recommend),
OleDbHelper.MakeInParam("@Status", OleDbType.Integer, 1, postinfo.Status),
OleDbHelper.MakeInParam("@TopStatus", OleDbType.Integer, 1, postinfo.TopStatus),
OleDbHelper.MakeInParam("@HideStatus", OleDbType.Integer, 1, postinfo.HideStatus),
OleDbHelper.MakeInParam("@CreateDate", OleDbType.Date, 8, postinfo.CreateDate),
OleDbHelper.MakeInParam("@UpdateDate", OleDbType.Date, 8, postinfo.UpdateDate),
OleDbHelper.MakeInParam("@PostId", OleDbType.Integer, 4, postinfo.PostId),
};
return(OleDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
/// <summary>
/// 删除
/// </summary>
/// <param name="PostId">主键</param>
/// <returns>删除的行数</returns>
public int DeletePost(int postid)
{
PostInfo oldPost = GetPost(postid); //删除前
string cmdText = "delete from [loachs_posts] where [PostId] = @PostId";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@PostId", OleDbType.Integer, 4, postid)
};
int result = OleDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
//if (oldPost != null)
//{
// OleDbHelper.ExecuteNonQuery(string.Format("update [loachs_users] set [postcount]=[postcount]-1 where [userid]={0}", oldPost.UserId));
// OleDbHelper.ExecuteNonQuery("update [loachs_sites] set [postcount]=[postcount]-1");
// OleDbHelper.ExecuteNonQuery(string.Format("update [loachs_terms] set [count]=[count]-1 where [termid]={0}", oldPost.CategoryId));
//}
return(result);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="_userinfo"></param>
/// <returns></returns>
public int UpdateUser(UserInfo _userinfo)
{
string cmdText = @"update [loachs_users] set
[Type]=@Type,
[UserName]=@UserName,
[Name]=@Name,
[Password]=@Password,
[Email]=@Email,
[SiteUrl]=@SiteUrl,
[AvatarUrl]=@AvatarUrl,
[Description]=@Description,
[Displayorder]=@Displayorder,
[Status]=@Status,
[PostCount]=@PostCount,
[CommentCount]=@CommentCount,
[CreateDate]=@CreateDate
where UserId=@UserId";
OleDbParameter[] prams =
{
OleDbHelper.MakeInParam("@Type", OleDbType.Integer, 4, _userinfo.Type),
OleDbHelper.MakeInParam("@UserName", OleDbType.VarWChar, 50, _userinfo.UserName),
OleDbHelper.MakeInParam("@Name", OleDbType.VarWChar, 50, _userinfo.Name),
OleDbHelper.MakeInParam("@Password", OleDbType.VarWChar, 50, _userinfo.Password),
OleDbHelper.MakeInParam("@Email", OleDbType.VarWChar, 50, _userinfo.Email),
OleDbHelper.MakeInParam("@SiteUrl", OleDbType.VarWChar, 255, _userinfo.SiteUrl),
OleDbHelper.MakeInParam("@AvatarUrl", OleDbType.VarWChar, 255, _userinfo.AvatarUrl),
OleDbHelper.MakeInParam("@Description", OleDbType.VarWChar, 255, _userinfo.Description),
OleDbHelper.MakeInParam("@Displayorder", OleDbType.VarWChar, 255, _userinfo.Displayorder),
OleDbHelper.MakeInParam("@Status", OleDbType.Integer, 4, _userinfo.Status),
OleDbHelper.MakeInParam("@PostCount", OleDbType.Integer, 4, _userinfo.PostCount),
OleDbHelper.MakeInParam("@CommentCount", OleDbType.Integer, 4, _userinfo.CommentCount),
OleDbHelper.MakeInParam("@CreateDate", OleDbType.Date, 8, _userinfo.CreateDate),
OleDbHelper.MakeInParam("@UserId", OleDbType.Integer, 4, _userinfo.UserId),
};
return(OleDbHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
