public MsgInfo GetNextMsg(long chatId, long msgId)
{
using (
var reader = DbUtil.ExecuteSql(LyncDb.GetDb().SqlCnn,
"select MessageId, PlainMsg, DateTime, UserId, ChatId from Message where ChatId=@ChatId and MessageId > @MsgId limit 1",
new[]
{
DbUtil.BuildParameter("@MsgId", DbType.Int64, msgId),
DbUtil.BuildParameter("@ChatId", DbType.Int64, chatId)
}))
{
if (!reader.Read())
return null;
var result = new MsgInfo
{
MsgId = reader.GetInt64(0),
Date = reader.GetDateTime(2).ToString("yyyy-MM-dd hh:mm:ss"),
Message = reader.GetString(1),
UserId = reader.GetInt64(3),
UserName = UserDb.GetDb().GetUserInfoById(reader.GetInt64(3)).Name,
ChatId = reader.GetInt64(4)
};
return result;
}
}
public int LoadMsg(long chatId, int pageIndex, int pageSize, ref List<MsgInfo> listMsgInfo)
{
using (
var reader = DbUtil.ExecuteSql(LyncDb.GetDb().SqlCnn,
"select a.MessageId, a.PlainMsg, a.DateTime, a.UserId, a.ChatId from message a where a.ChatId=@ChatId and a.Messageid not in (select b.messageid from message b where b.ChatId=@ChatId limit @OverCount) limit @PageSize",
new[]
{
DbUtil.BuildParameter("@OverCount", DbType.Int32, pageSize * pageIndex),
DbUtil.BuildParameter("@PageSize", DbType.Int32, pageSize),
DbUtil.BuildParameter("@ChatId", DbType.Int64, chatId)
}))
{
if (!reader.Read())
return 0;
do
{
var result = new MsgInfo
{
MsgId = reader.GetInt64(0),
Date = reader.GetDateTime(2).ToString("yyyy-MM-dd HH:mm:ss"),
Message = reader.GetString(1),
UserId = reader.GetInt64(3),
UserName = UserDb.GetDb().GetUserInfoById(reader.GetInt64(3)).Name,
ChatId = reader.GetInt64(4)
};
listMsgInfo.Add(result);
} while (reader.Read());
return listMsgInfo.Count;
}
}
public List<MsgInfo> SearchMsg(string keyword, long chatId, int pageIndex, int pageSize)
{
if(keyword.Length == 0)
throw new Exception("空查找串");
if (!keyword.Contains("%"))
keyword = "%" + keyword + "%";
var result = new List<MsgInfo>();
using (
var reader = DbUtil.ExecuteSql(LyncDb.GetDb().SqlCnn,
"select a.MessageId, a.PlainMsg, a.DateTime, a.UserId, a.ChatId from Message a where (@ChatId=0 or a.ChatId=@ChatId) and (a.PlainMsg like @SearchData) limit @FromIndex,@MsgCount",
new[]
{
DbUtil.BuildParameter("@ChatId", DbType.Int64, chatId),
DbUtil.BuildParameter("@SearchData", DbType.String, keyword),
DbUtil.BuildParameter("@FromIndex", DbType.Int32, pageIndex * pageSize),
DbUtil.BuildParameter("@MsgCount", DbType.Int32, pageSize)
}))
{
if (reader == null || !reader.Read())
return null;
do
{
var data = new MsgInfo
{
MsgId = reader.GetInt64(0),
Date = reader.GetDateTime(2).ToString("yyyy-MM-dd HH:mm:ss"),
Message = reader.GetString(1),
UserId = reader.GetInt64(3),
UserName = UserDb.GetDb().GetUserInfoById(reader.GetInt64(3)).Name,
ChatId = reader.GetInt64(4)
};
result.Add(data);
} while (reader.Read());
}
return result;
}