/// <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)";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@PostId", NpgsqlDbType.Integer, 4, comment.PostId),
PGSQLHelper.MakeInParam("@ParentId", NpgsqlDbType.Integer, 4, comment.ParentId),
PGSQLHelper.MakeInParam("@UserId", NpgsqlDbType.Integer, 4, comment.UserId),
PGSQLHelper.MakeInParam("@Name", NpgsqlDbType.Varchar, 255, comment.Name),
PGSQLHelper.MakeInParam("@Email", NpgsqlDbType.Varchar, 255, comment.Email),
PGSQLHelper.MakeInParam("@SiteUrl", NpgsqlDbType.Varchar, 255, comment.SiteUrl),
PGSQLHelper.MakeInParam("@Content", NpgsqlDbType.Varchar, 255, comment.Content),
PGSQLHelper.MakeInParam("@EmailNotify", NpgsqlDbType.Integer, 4, comment.EmailNotify),
PGSQLHelper.MakeInParam("@IpAddress", NpgsqlDbType.Varchar, 255, comment.IpAddress),
PGSQLHelper.MakeInParam("@CreateDate", NpgsqlDbType.Date, 8, comment.CreateDate),
PGSQLHelper.MakeInParam("@Approved", NpgsqlDbType.Integer, 4, comment.Approved),
};
PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
int newId = Convert.ToInt32(PGSQLHelper.ExecuteScalar("select CommentId from Loachs_Comments order by CommentId desc limit 1 offset 0"));
return(newId);
}
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
)";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@Type", NpgsqlDbType.Integer, 1, (int)TermType.Tag),
PGSQLHelper.MakeInParam("@Name", NpgsqlDbType.Varchar, 255, tag.Name),
PGSQLHelper.MakeInParam("@Slug", NpgsqlDbType.Varchar, 255, tag.Slug),
PGSQLHelper.MakeInParam("@Description", NpgsqlDbType.Varchar, 255, tag.Description),
PGSQLHelper.MakeInParam("@Displayorder", NpgsqlDbType.Integer, 4, tag.Displayorder),
PGSQLHelper.MakeInParam("@Count", NpgsqlDbType.Integer, 4, tag.Count),
PGSQLHelper.MakeInParam("@CreateDate", NpgsqlDbType.Date, 8, tag.CreateDate)
};
PGSQLHelper.ExecuteScalar(CommandType.Text, cmdText, prams);
//int newId = Convert.ToInt32(PGSQLHelper.ExecuteScalar("select top 1 termid from Loachs_Terms order by termid desc"));
int newId = Convert.ToInt32(PGSQLHelper.ExecuteScalar("select termid from Loachs_Terms order by termid desc limit 1 offset 0"));
return(newId);
}
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
)";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@type", NpgsqlDbType.Integer, 4, link.Type),
PGSQLHelper.MakeInParam("@name", NpgsqlDbType.Varchar, 100, link.Name),
PGSQLHelper.MakeInParam("@href", NpgsqlDbType.Varchar, 255, link.Href),
PGSQLHelper.MakeInParam("@position", NpgsqlDbType.Integer, 4, link.Position),
PGSQLHelper.MakeInParam("@target", NpgsqlDbType.Varchar, 50, link.Target),
PGSQLHelper.MakeInParam("@description", NpgsqlDbType.Varchar, 255, link.Description),
PGSQLHelper.MakeInParam("@displayorder", NpgsqlDbType.Integer, 4, link.Displayorder),
PGSQLHelper.MakeInParam("@status", NpgsqlDbType.Integer, 4, link.Status),
PGSQLHelper.MakeInParam("@createdate", NpgsqlDbType.Date, 8, link.CreateDate),
};
int r = PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
if (r > 0)
{
return(Convert.ToInt32(PGSQLHelper.ExecuteScalar("select linkid from Loachs_Links order by linkid desc limit 1 offset 0")));
}
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";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@type", NpgsqlDbType.Integer, 4, link.Type),
PGSQLHelper.MakeInParam("@name", NpgsqlDbType.Varchar, 100, link.Name),
PGSQLHelper.MakeInParam("@href", NpgsqlDbType.Varchar, 255, link.Href),
PGSQLHelper.MakeInParam("@position", NpgsqlDbType.Integer, 4, link.Position),
PGSQLHelper.MakeInParam("@target", NpgsqlDbType.Varchar, 50, link.Target),
PGSQLHelper.MakeInParam("@description", NpgsqlDbType.Varchar, 255, link.Description),
PGSQLHelper.MakeInParam("@displayorder", NpgsqlDbType.Integer, 4, link.Displayorder),
PGSQLHelper.MakeInParam("@status", NpgsqlDbType.Integer, 4, link.Status),
PGSQLHelper.MakeInParam("@createdate", NpgsqlDbType.Date, 8, link.CreateDate),
PGSQLHelper.MakeInParam("@linkid", NpgsqlDbType.Integer, 4, link.LinkId),
};
return(Convert.ToInt32(PGSQLHelper.ExecuteScalar(CommandType.Text, cmdText, prams)));
}
/// <summary>
/// 更新
/// </summary>
/// <param name="comment"></param>
/// <returns></returns>
public int UpdateComment(CommentInfo comment)
{
string cmdText = @"update Loachs_Comments set
PostId=@PostId,
ParentId=@ParentId,
UserId=@UserId,
Name=@Name,
Email=@Email,
SiteUrl=@SiteUrl,
Content=@Content,
EmailNotify=@EmailNotify,
IpAddress=@IpAddress,
CreateDate=@CreateDate,
Approved=@Approved
where CommentId=@CommentId ";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@PostId", NpgsqlDbType.Integer, 4, comment.PostId),
PGSQLHelper.MakeInParam("@ParentId", NpgsqlDbType.Integer, 4, comment.ParentId),
PGSQLHelper.MakeInParam("@UserId", NpgsqlDbType.Integer, 4, comment.UserId),
PGSQLHelper.MakeInParam("@Name", NpgsqlDbType.Varchar, 255, comment.Name),
PGSQLHelper.MakeInParam("@Email", NpgsqlDbType.Varchar, 255, comment.Email),
PGSQLHelper.MakeInParam("@SiteUrl", NpgsqlDbType.Varchar, 255, comment.SiteUrl),
PGSQLHelper.MakeInParam("@Content", NpgsqlDbType.Varchar, 255, comment.Content),
PGSQLHelper.MakeInParam("@EmailNotify", NpgsqlDbType.Integer, 4, comment.EmailNotify),
PGSQLHelper.MakeInParam("@IpAddress", NpgsqlDbType.Varchar, 255, comment.IpAddress),
PGSQLHelper.MakeInParam("@CreateDate", NpgsqlDbType.Date, 8, comment.CreateDate),
PGSQLHelper.MakeInParam("@Approved", NpgsqlDbType.Integer, 4, comment.Approved),
PGSQLHelper.MakeInParam("@CommentId", NpgsqlDbType.Integer, 4, comment.CommentId),
};
return(PGSQLHelper.ExecuteNonQuery(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 )";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@Type", NpgsqlDbType.Integer, 4, _userinfo.Type),
PGSQLHelper.MakeInParam("@UserName", NpgsqlDbType.Varchar, 50, _userinfo.UserName),
PGSQLHelper.MakeInParam("@Name", NpgsqlDbType.Varchar, 50, _userinfo.Name),
PGSQLHelper.MakeInParam("@Password", NpgsqlDbType.Varchar, 50, _userinfo.Password),
PGSQLHelper.MakeInParam("@Email", NpgsqlDbType.Varchar, 50, _userinfo.Email),
PGSQLHelper.MakeInParam("@SiteUrl", NpgsqlDbType.Varchar, 255, _userinfo.SiteUrl),
PGSQLHelper.MakeInParam("@AvatarUrl", NpgsqlDbType.Varchar, 255, _userinfo.AvatarUrl),
PGSQLHelper.MakeInParam("@description", NpgsqlDbType.Varchar, 255, _userinfo.Description),
PGSQLHelper.MakeInParam("@Displayorder", NpgsqlDbType.Integer, 4, _userinfo.Displayorder),
PGSQLHelper.MakeInParam("@Status", NpgsqlDbType.Integer, 4, _userinfo.Status),
PGSQLHelper.MakeInParam("@PostCount", NpgsqlDbType.Integer, 4, _userinfo.PostCount),
PGSQLHelper.MakeInParam("@CommentCount", NpgsqlDbType.Integer, 4, _userinfo.CommentCount),
PGSQLHelper.MakeInParam("@CreateDate", NpgsqlDbType.Date, 8, _userinfo.CreateDate),
};
int r = PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
if (r > 0)
{
//return Convert.ToInt32(PGSQLHelper.ExecuteScalar("select top 1 UserId from Loachs_Users order by UserId desc"));
return(Convert.ToInt32(PGSQLHelper.ExecuteScalar("select UserId from Loachs_Users order by UserId desc limit 1 offset 0")));
}
return(0);
}
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";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@Type", NpgsqlDbType.Integer, 1, (int)TermType.Tag),
PGSQLHelper.MakeInParam("@Name", NpgsqlDbType.Varchar, 255, tag.Name),
PGSQLHelper.MakeInParam("@Slug", NpgsqlDbType.Varchar, 255, tag.Slug),
PGSQLHelper.MakeInParam("@Description", NpgsqlDbType.Varchar, 255, tag.Description),
PGSQLHelper.MakeInParam("@Displayorder", NpgsqlDbType.Integer, 4, tag.Displayorder),
PGSQLHelper.MakeInParam("@Count", NpgsqlDbType.Integer, 4, tag.Count),
PGSQLHelper.MakeInParam("@CreateDate", NpgsqlDbType.Date, 8, tag.CreateDate),
PGSQLHelper.MakeInParam("@termid", NpgsqlDbType.Integer, 1, tag.TagId),
};
return(Convert.ToInt32(PGSQLHelper.ExecuteScalar(CommandType.Text, cmdText, prams)));
}
public int DeleteTag(int tagId)
{
string cmdText = "delete from Loachs_Terms where termid = @termid";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@termid", NpgsqlDbType.Integer, 4, tagId)
};
return(PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
public int DeleteLink(int linkId)
{
string cmdText = "delete from Loachs_Links where linkid = @linkid";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@linkid", NpgsqlDbType.Integer, 4, linkId)
};
return(PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
/// <summary>
/// 是否存在
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public bool ExistsUserName(string userName)
{
string cmdText = "select count(1) from Loachs_Users where userName = @userName ";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@userName", NpgsqlDbType.Varchar, 50, userName),
};
return(Convert.ToInt32(PGSQLHelper.ExecuteScalar(CommandType.Text, cmdText, prams)) > 0);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public int DeleteUser(int userid)
{
string cmdText = "delete from Loachs_Users where userid = @userid";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@userid", NpgsqlDbType.Integer, 4, userid)
};
return(PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
public bool UpdateSetting(SettingInfo setting)
{
string cmdText = @"update Loachs_Sites set setting=@setting";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@setting", NpgsqlDbType.Varchar, 0, Serialize(setting)),
};
return(PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams) == 1);
}
public int UpdatePostViewCount(int postId, int addCount)
{
string cmdText = "update Loachs_Posts set viewcount = viewcount + @addcount where postid=@postid";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@addcount", NpgsqlDbType.Integer, 4, addCount),
PGSQLHelper.MakeInParam("@postid", NpgsqlDbType.Integer, 4, postId),
};
return(PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
/// <summary>
/// 获取实体
/// </summary>
/// <param name="commentId"></param>
/// <returns></returns>
public CommentInfo GetComment(int commentId)
{
string cmdText = "select * from Loachs_Comments where commentId = @commentId";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@commentId", NpgsqlDbType.Integer, 4, commentId)
};
List <CommentInfo> list = DataReaderToCommentList(PGSQLHelper.ExecuteReader(cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
/// <summary>
/// 根据日志ID删除评论
/// </summary>
/// <param name="postId">日志ID</param>
/// <returns></returns>
public int DeleteCommentByPost(int postId)
{
string cmdText = "delete from Loachs_Comments where postId = @postId";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@postId", NpgsqlDbType.Integer, 4, postId)
};
int result = PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
return(result);
}
public TagInfo GetTag(int tagId)
{
string cmdText = "select * from Loachs_Terms where termid = @termid";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@termid", NpgsqlDbType.Integer, 4, tagId)
};
List <TagInfo> list = DataReaderToList(PGSQLHelper.ExecuteReader(CommandType.Text, cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
/// <summary>
/// 获取实体
/// </summary>
/// <param name="slug"></param>
/// <returns></returns>
public PostInfo GetPost(string slug)
{
//string cmdText = "select top 1 * from [Loachs_Posts] where [slug] = @slug";
string cmdText = "select * from Loachs_Posts where slug = @slug limit 1 offset 0";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@slug", NpgsqlDbType.Varchar, 200, slug)
};
List <PostInfo> list = DataReaderToCommentList(PGSQLHelper.ExecuteReader(cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
/// <summary>
/// 获取实体
/// </summary>
/// <param name="PostId">主键</param>
/// <returns></returns>
public PostInfo GetPost(int postid)
{
//string cmdText = "select top 1 * from [Loachs_Posts] where [PostId] = @PostId";
string cmdText = "select * from Loachs_Posts where PostId = @PostId limit 1 offset 0";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@PostId", NpgsqlDbType.Integer, 4, postid)
};
List <PostInfo> list = DataReaderToCommentList(PGSQLHelper.ExecuteReader(cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
/// <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";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@commentId", NpgsqlDbType.Integer, 4, commentId)
};
int result = PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
return(result);
}
/// <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
)";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@CategoryId", NpgsqlDbType.Integer, 4, postinfo.CategoryId),
PGSQLHelper.MakeInParam("@Title", NpgsqlDbType.Varchar, 255, postinfo.Title),
PGSQLHelper.MakeInParam("@Summary", NpgsqlDbType.Varchar, 0, postinfo.Summary),
PGSQLHelper.MakeInParam("@Content", NpgsqlDbType.Varchar, 0, postinfo.Content),
PGSQLHelper.MakeInParam("@Slug", NpgsqlDbType.Varchar, 255, postinfo.Slug),
PGSQLHelper.MakeInParam("@UserId", NpgsqlDbType.Integer, 4, postinfo.UserId),
PGSQLHelper.MakeInParam("@CommentStatus", NpgsqlDbType.Integer, 1, postinfo.CommentStatus),
PGSQLHelper.MakeInParam("@CommentCount", NpgsqlDbType.Integer, 4, postinfo.CommentCount),
PGSQLHelper.MakeInParam("@ViewCount", NpgsqlDbType.Integer, 4, postinfo.ViewCount),
PGSQLHelper.MakeInParam("@Tag", NpgsqlDbType.Varchar, 255, postinfo.Tag),
PGSQLHelper.MakeInParam("@UrlFormat", NpgsqlDbType.Integer, 1, postinfo.UrlFormat),
PGSQLHelper.MakeInParam("@Template", NpgsqlDbType.Varchar, 50, postinfo.Template),
PGSQLHelper.MakeInParam("@Recommend", NpgsqlDbType.Integer, 1, postinfo.Recommend),
PGSQLHelper.MakeInParam("@Status", NpgsqlDbType.Integer, 1, postinfo.Status),
PGSQLHelper.MakeInParam("@TopStatus", NpgsqlDbType.Integer, 1, postinfo.TopStatus),
PGSQLHelper.MakeInParam("@HideStatus", NpgsqlDbType.Integer, 1, postinfo.HideStatus),
PGSQLHelper.MakeInParam("@CreateDate", NpgsqlDbType.Date, 8, postinfo.CreateDate),
PGSQLHelper.MakeInParam("@UpdateDate", NpgsqlDbType.Date, 8, postinfo.UpdateDate)
};
PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
//int newId = StringHelper.ObjectToInt(PGSQLHelper.ExecuteScalar("select top 1 PostId from Loachs_Posts order by PostId desc"));
int newId = StringHelper.ObjectToInt(PGSQLHelper.ExecuteScalar("select PostId from Loachs_Posts order by PostId desc limit 1 offset 0"));
//if (newId > 0)
//{
// PGSQLHelper.ExecuteNonQuery(string.Format("update [Loachs_Users] set [postcount]=[postcount]+1 where [userid]={0}", postinfo.UserId));
// PGSQLHelper.ExecuteNonQuery("update [Loachs_Sites] set [postcount]=[postcount]+1");
// PGSQLHelper.ExecuteNonQuery(string.Format("update [Loachs_Terms] set [count]=[count]+1 where [termid]={0}", postinfo.CategoryId));
//}
return(newId);
}
public bool UpdateStatistics(StatisticsInfo statistics)
{
string cmdText = @"update Loachs_Sites set
PostCount=@PostCount,
CommentCount=@CommentCount,
VisitCount=@VisitCount,
TagCount=@TagCount";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@PostCount", NpgsqlDbType.Integer, 4, statistics.PostCount),
PGSQLHelper.MakeInParam("@CommentCount", NpgsqlDbType.Integer, 4, statistics.CommentCount),
PGSQLHelper.MakeInParam("@VisitCount", NpgsqlDbType.Integer, 4, statistics.VisitCount),
PGSQLHelper.MakeInParam("@TagCount", NpgsqlDbType.Integer, 4, statistics.TagCount),
};
return(PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams) == 1);
}
/// <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)
//{
// PGSQLHelper.ExecuteNonQuery(string.Format("update [Loachs_Terms] set [count]=[count]-1 where [termid]={0}", oldPost.CategoryId));
// PGSQLHelper.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";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@CategoryId", NpgsqlDbType.Integer, 4, postinfo.CategoryId),
PGSQLHelper.MakeInParam("@Title", NpgsqlDbType.Varchar, 255, postinfo.Title),
PGSQLHelper.MakeInParam("@Summary", NpgsqlDbType.Varchar, 0, postinfo.Summary),
PGSQLHelper.MakeInParam("@Content", NpgsqlDbType.Varchar, 0, postinfo.Content),
PGSQLHelper.MakeInParam("@Slug", NpgsqlDbType.Varchar, 255, postinfo.Slug),
PGSQLHelper.MakeInParam("@UserId", NpgsqlDbType.Integer, 4, postinfo.UserId),
PGSQLHelper.MakeInParam("@CommentStatus", NpgsqlDbType.Integer, 1, postinfo.CommentStatus),
PGSQLHelper.MakeInParam("@CommentCount", NpgsqlDbType.Integer, 4, postinfo.CommentCount),
PGSQLHelper.MakeInParam("@ViewCount", NpgsqlDbType.Integer, 4, postinfo.ViewCount),
PGSQLHelper.MakeInParam("@Tag", NpgsqlDbType.Varchar, 255, postinfo.Tag),
PGSQLHelper.MakeInParam("@UrlFormat", NpgsqlDbType.Integer, 1, postinfo.UrlFormat),
PGSQLHelper.MakeInParam("@Template", NpgsqlDbType.Varchar, 50, postinfo.Template),
PGSQLHelper.MakeInParam("@Recommend", NpgsqlDbType.Integer, 1, postinfo.Recommend),
PGSQLHelper.MakeInParam("@Status", NpgsqlDbType.Integer, 1, postinfo.Status),
PGSQLHelper.MakeInParam("@TopStatus", NpgsqlDbType.Integer, 1, postinfo.TopStatus),
PGSQLHelper.MakeInParam("@HideStatus", NpgsqlDbType.Integer, 1, postinfo.HideStatus),
PGSQLHelper.MakeInParam("@CreateDate", NpgsqlDbType.Date, 8, postinfo.CreateDate),
PGSQLHelper.MakeInParam("@UpdateDate", NpgsqlDbType.Date, 8, postinfo.UpdateDate),
PGSQLHelper.MakeInParam("@PostId", NpgsqlDbType.Integer, 4, postinfo.PostId),
};
return(PGSQLHelper.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";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@PostId", NpgsqlDbType.Integer, 4, postid)
};
int result = PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
//if (oldPost != null)
//{
// PGSQLHelper.ExecuteNonQuery(string.Format("update [Loachs_Users] set [postcount]=[postcount]-1 where [userid]={0}", oldPost.UserId));
// PGSQLHelper.ExecuteNonQuery("update [Loachs_Sites] set [postcount]=[postcount]-1");
// PGSQLHelper.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";
NpgsqlParameter[] prams =
{
PGSQLHelper.MakeInParam("@Type", NpgsqlDbType.Integer, 4, _userinfo.Type),
PGSQLHelper.MakeInParam("@UserName", NpgsqlDbType.Varchar, 50, _userinfo.UserName),
PGSQLHelper.MakeInParam("@Name", NpgsqlDbType.Varchar, 50, _userinfo.Name),
PGSQLHelper.MakeInParam("@Password", NpgsqlDbType.Varchar, 50, _userinfo.Password),
PGSQLHelper.MakeInParam("@Email", NpgsqlDbType.Varchar, 50, _userinfo.Email),
PGSQLHelper.MakeInParam("@SiteUrl", NpgsqlDbType.Varchar, 255, _userinfo.SiteUrl),
PGSQLHelper.MakeInParam("@AvatarUrl", NpgsqlDbType.Varchar, 255, _userinfo.AvatarUrl),
PGSQLHelper.MakeInParam("@Description", NpgsqlDbType.Varchar, 255, _userinfo.Description),
PGSQLHelper.MakeInParam("@Displayorder", NpgsqlDbType.Integer, 4, _userinfo.Displayorder),
PGSQLHelper.MakeInParam("@Status", NpgsqlDbType.Integer, 4, _userinfo.Status),
PGSQLHelper.MakeInParam("@PostCount", NpgsqlDbType.Integer, 4, _userinfo.PostCount),
PGSQLHelper.MakeInParam("@CommentCount", NpgsqlDbType.Integer, 4, _userinfo.CommentCount),
PGSQLHelper.MakeInParam("@CreateDate", NpgsqlDbType.Date, 8, _userinfo.CreateDate),
PGSQLHelper.MakeInParam("@UserId", NpgsqlDbType.Integer, 4, _userinfo.UserId),
};
return(PGSQLHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams));
}
