public List <GraphicMessageExt> GetTopExt(string filter)
{
List <GraphicMessageExt> msgListExt = new List <GraphicMessageExt>();
using (MySqlConnection conn = new MySqlConnection(connStr))
{
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand($"select g.*, u.headpic, (select count(1) from `like` where itemId=g.id and itemType='graphic' and expiredAt is null ) as likeCount, (select count(1) from `comments` where comment_post_id=g.id and comment_post_type='graphic' and comment_audit_status=1 and expiredAt is null ) as commentCount from graphicmessage as g left join `user` as u on g.openId=u.openId where g.expiredAt is null and g.isTop=1 {filter} order by g.isTop desc, g.id desc ", conn);
var sqlReader = cmd.ExecuteReader();
while (sqlReader.Read())
{
GraphicMessageExt msg = new GraphicMessageExt();
msg.id = (int)sqlReader["id"];
msg.postId = msg.id.ToString();
msg.name = sqlReader["name"] == DBNull.Value ? string.Empty : (string)sqlReader["name"];
msg.text = sqlReader["text"] == DBNull.Value ? string.Empty : (string)sqlReader["text"];
msg.poster = sqlReader["poster"] == DBNull.Value ? string.Empty : (string)sqlReader["poster"];
msg.pic01 = sqlReader["pic01"] == DBNull.Value ? string.Empty : (string)sqlReader["pic01"];
msg.pic02 = sqlReader["pic02"] == DBNull.Value ? string.Empty : (string)sqlReader["pic02"];
msg.pic03 = sqlReader["pic03"] == DBNull.Value ? string.Empty : (string)sqlReader["pic03"];
msg.pic04 = sqlReader["pic04"] == DBNull.Value ? string.Empty : (string)sqlReader["pic04"];
msg.pic05 = sqlReader["pic05"] == DBNull.Value ? string.Empty : (string)sqlReader["pic05"];
msg.pic06 = sqlReader["pic06"] == DBNull.Value ? string.Empty : (string)sqlReader["pic06"];
msg.audio01 = sqlReader["audio01"] == DBNull.Value ? string.Empty : (string)sqlReader["audio01"];
msg.audio02 = sqlReader["audio02"] == DBNull.Value ? string.Empty : (string)sqlReader["audio02"];
msg.audio03 = sqlReader["audio03"] == DBNull.Value ? string.Empty : (string)sqlReader["audio03"];
msg.openId = sqlReader["openId"] == DBNull.Value ? string.Empty : (string)sqlReader["openId"];
msg.isTop = sqlReader["isTop"] == DBNull.Value ? false : (UInt64)sqlReader["isTop"] == 1;
msg.author = sqlReader["author"] == DBNull.Value ? string.Empty : (string)sqlReader["author"];
msg.wechatUrl = sqlReader["wechatUrl"] == DBNull.Value ? string.Empty : (string)sqlReader["wechatUrl"];
msg.authorHeadPic = sqlReader["headpic"] == DBNull.Value ? string.Empty : (string)sqlReader["headpic"];
msg.likeCount = sqlReader["likeCount"] == DBNull.Value ? 0 : (long)sqlReader["likeCount"];
msg.commentCount = sqlReader["commentCount"] == DBNull.Value ? 0 : (long)sqlReader["commentCount"];
msg.createdAt = sqlReader["createdAt"] == DBNull.Value ? DateTime.MinValue : (DateTime)sqlReader["createdAt"];
msgListExt.Add(msg);
}
}
finally
{
conn.Close();
}
}
return(msgListExt);
}
public GraphicMessageExt GetById(string postId)
{
GraphicMessageExt msg = new GraphicMessageExt();
using (MySqlConnection conn = new MySqlConnection(connStr))
{
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand($"select g.*, u.headpic, (select count(1) from `like` where itemId=g.id and itemType='graphic' and expiredAt is null ) as likeCount, (select count(1) from `comments` where comment_post_id=g.id and comment_post_type='graphic' and comment_audit_status=1 and expiredAt is null ) as commentCount from graphicmessage as g left join `user` as u on g.openId=u.openId where g.expiredAt is null and g.id={postId} ", conn);
var sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
msg.id = (int)sqlReader["id"];
msg.postId = msg.id.ToString();
msg.name = sqlReader["name"] == DBNull.Value ? string.Empty : (string)sqlReader["name"];
msg.text = sqlReader["text"] == DBNull.Value ? string.Empty : (string)sqlReader["text"];
msg.poster = sqlReader["poster"] == DBNull.Value ? string.Empty : (string)sqlReader["poster"];
msg.pic01 = sqlReader["pic01"] == DBNull.Value ? string.Empty : (string)sqlReader["pic01"];
msg.pic02 = sqlReader["pic02"] == DBNull.Value ? string.Empty : (string)sqlReader["pic02"];
msg.pic03 = sqlReader["pic03"] == DBNull.Value ? string.Empty : (string)sqlReader["pic03"];
msg.pic04 = sqlReader["pic04"] == DBNull.Value ? string.Empty : (string)sqlReader["pic04"];
msg.pic05 = sqlReader["pic05"] == DBNull.Value ? string.Empty : (string)sqlReader["pic05"];
msg.pic06 = sqlReader["pic06"] == DBNull.Value ? string.Empty : (string)sqlReader["pic06"];
msg.audio01 = sqlReader["audio01"] == DBNull.Value ? string.Empty : (string)sqlReader["audio01"];
msg.audio02 = sqlReader["audio02"] == DBNull.Value ? string.Empty : (string)sqlReader["audio02"];
msg.audio03 = sqlReader["audio03"] == DBNull.Value ? string.Empty : (string)sqlReader["audio03"];
msg.openId = sqlReader["openId"] == DBNull.Value ? string.Empty : (string)sqlReader["openId"];
msg.author = sqlReader["author"] == DBNull.Value ? string.Empty : (string)sqlReader["author"];
msg.authorHeadPic = sqlReader["headpic"] == DBNull.Value ? string.Empty : (string)sqlReader["headpic"];
msg.likeCount = sqlReader["likeCount"] == DBNull.Value ? 0 : (long)sqlReader["likeCount"];
msg.commentCount = sqlReader["commentCount"] == DBNull.Value ? 0 : (long)sqlReader["commentCount"];
msg.createdAt = sqlReader["createdAt"] == DBNull.Value ? DateTime.MinValue : (DateTime)sqlReader["createdAt"];
}
}
finally
{
conn.Close();
}
}
return(msg);
}
