/// <summary>
/// 获取实体
/// </summary>
/// <param name="commentId"></param>
/// <returns></returns>
public CommentInfo GetComment(int commentId)
{
string cmdText = "select * from Loachs_Comments where commentId = @commentId";
SQLiteParameter[] prams =
{
SqliteHelper.MakeInParam("@commentId", DbType.Int32, 4, commentId)
};
List <CommentInfo> list = DataReaderToCommentList(SqliteHelper.ExecuteReader(cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
public CategoryInfo GetCategory(int categoryId)
{
string cmdText = "select * from Loachs_Terms where termid = @termid";
SQLiteParameter[] prams =
{
SqliteHelper.MakeInParam("@termid", DbType.Int32, 4, categoryId)
};
List <CategoryInfo> list = DataReaderToList(SqliteHelper.ExecuteReader(CommandType.Text, cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
//public List<TagInfo> GetTagList(int pageSize, int pageIndex, out int recordCount)
//{
// string condition = " [type]=" + (int)TermType.Tag;
// string cmdTotalRecord = "select count(1) from [Loachs_Terms] where " + condition;
// recordCount = Convert.ToInt32(SqliteHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord));
// string cmdText = SqliteHelper.GetPageSql("[Loachs_Terms]", "[termid]", "*", pageSize, pageIndex, 1, condition);
// return DataReaderToList(SqliteHelper.ExecuteReader(cmdText));
//}
public List <TagInfo> GetTagList(string ids)
{
if (string.IsNullOrEmpty(ids))
{
return(new List <TagInfo>());
}
string cmdText = "select * from Loachs_Terms where termid in (" + ids + ")";
// throw new Exception(cmdText);
return(DataReaderToList(SqliteHelper.ExecuteReader(cmdText)));
}
/// <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 0,1";
SQLiteParameter[] prams =
{
SqliteHelper.MakeInParam("@slug", DbType.String, 200, slug)
};
List <PostInfo> list = DataReaderToCommentList(SqliteHelper.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 0,1";
SQLiteParameter[] prams =
{
SqliteHelper.MakeInParam("@PostId", DbType.Int32, 4, postid)
};
List <PostInfo> list = DataReaderToCommentList(SqliteHelper.ExecuteReader(cmdText, prams));
return(list.Count > 0 ? list[0] : null);
}
public StatisticsInfo GetStatistics()
{
//string cmdText = "select top 1 * from [Loachs_Sites]";
string cmdText = "select * from Loachs_Sites limit 0,1";
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(SqliteHelper.ExecuteReader(cmdText));
if (list.Count == 0)
{
SqliteHelper.ExecuteNonQuery(insertText);
}
list = DataReaderToList(SqliteHelper.ExecuteReader(cmdText));
return(list.Count > 0 ? list[0] : null);
}
/// <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(SqliteHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord));
// throw new Exception(cmdTotalRecord);
string cmdText = SqliteHelper.GetPageSql("Loachs_Comments", "CommentId", "*", pageSize, pageIndex, order, condition);
return(DataReaderToCommentList(SqliteHelper.ExecuteReader(cmdText)));
}
///// <summary>
///// 根据别名获取文章ID
///// </summary>
///// <param name="slug"></param>
///// <returns></returns>
//public int GetPostId(string slug)
//{
// string cmdText = "select [postid] from [Loachs_Posts] where [slug]=@slug";
// SQLiteParameter[] prams = {
// SqliteHelper.MakeInParam("@slug",DbType.String,200,slug),
// };
// return StringHelper.ObjectToInt(SqliteHelper.ExecuteScalar(CommandType.Text, cmdText, prams));
//}
public List <ArchiveInfo> GetArchive()
{
//string cmdText = "select format(createdate, 'yyyymm') as [date] , count(*) as [count] from [Loachs_Posts] where [status]=1 and [hidestatus]=0 group by format(createdate, 'yyyymm') order by format(createdate, 'yyyymm') desc";
string cmdText = "select strftime(\"%Y%m\",createdate) as date , count(*) as count from Loachs_Posts where status=1 and hidestatus=0 group by strftime(\"%Y%m\",createdate) order by strftime(\"%Y%m\",createdate) desc";
List <ArchiveInfo> list = new List <ArchiveInfo>();
using (SQLiteDataReader read = SqliteHelper.ExecuteReader(cmdText))
{
while (read.Read())
{
ArchiveInfo archive = new ArchiveInfo();
string date = read["date"].ToString().Substring(0, 4) + "-" + read["date"].ToString().Substring(4, 2);
archive.Date = Convert.ToDateTime(date);
// archive.Title = read["date"].ToString();
archive.Count = StringHelper.ObjectToInt(read["count"]);
list.Add(archive);
}
}
return(list);
}
///// <summary>
///// 获取实体
///// </summary>
///// <param name="userName"></param>
///// <param name="password"></param>
///// <returns></returns>
//public UserInfo GetUser(string userName, string password)
//{
// string cmdText = "select * from [Loachs_Users] where [userName] = @userName and [Password]=@password";
// SQLiteParameter[] prams = {
// SqliteHelper.MakeInParam("@userName",DbType.String,50,userName),
// SqliteHelper.MakeInParam("@password",DbType.String,50,password),
// };
// List<UserInfo> list = DataReaderToUserList(SqliteHelper.ExecuteReader(CommandType.Text, cmdText, prams));
// if (list.Count > 0)
// {
// return list[0];
// }
// return null;
//}
/// <summary>
/// 获取全部
/// </summary>
/// <returns></returns>
public List <UserInfo> GetUserList()
{
string cmdText = "select * from Loachs_Users order by displayorder asc,userid asc";
return(DataReaderToUserList(SqliteHelper.ExecuteReader(cmdText)));
}
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(SqliteHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord));
string cmdText = SqliteHelper.GetPageSql("Loachs_Posts", "PostId", "*", pageSize, pageIndex, 1, condition);
return(DataReaderToCommentList(SqliteHelper.ExecuteReader(cmdText)));
}
/// <summary>
/// 获取列表
/// </summary>
/// <returns>IList</returns>
public List <PostInfo> GetPostList()
{
string cmdText = "select * from Loachs_Posts order by postid desc";
return(DataReaderToCommentList(SqliteHelper.ExecuteReader(cmdText)));
}
//public LinkInfo GetLink(int linkid)
//{
// string cmdText = "select * from [Loachs_Links] where [linkid] = @linkid";
// SQLiteParameter[] prams = {
// SqliteHelper.MakeInParam("@linkid",DbType.Int32,4,linkid)
// };
// List<LinkInfo> list = DataReaderToList(SqliteHelper.ExecuteReader(CommandType.Text, cmdText, prams));
// return list.Count > 0 ? list[0] : null;
//}
//public List<LinkInfo> GetLinkList(int type, int position, int status)
//{
// string condition = " 1=1 ";
// if (type != -1)
// {
// condition += " and [type]=" + type;
// }
// if (position != -1)
// {
// condition += " and [position]=" + position;
// }
// if (status != -1)
// {
// condition += " and [status]=" + status;
// }
// string cmdText = "select * from [Loachs_Links] where " + condition + " order by [displayorder] asc";
// return DataReaderToList(SqliteHelper.ExecuteReader(cmdText));
//}
public List <LinkInfo> GetLinkList()
{
string cmdText = "select * from Loachs_Links order by displayorder asc,linkid asc";
return(DataReaderToList(SqliteHelper.ExecuteReader(cmdText)));
}
