//查询对应好友过去的消息
public static void QueryMessageOld(String Username, String FriendName, String Id, ref ObservableCollection <MessageMix> messageMixGroup)
{
Console.WriteLine(Id + "聊天消息查询开始");
//该好友对应的消息表的表名字
String table = "F" + Id + "message";
//先获得一个数据库连接
SQLiteConnection sQLiteConnection = SqliteConnect.GetSqliteConnect();
sQLiteConnection.Open();
try
{
SQLiteCommand qLiteCommand = sQLiteConnection.CreateCommand();
//获取前面的数据
qLiteCommand.CommandText = "select * from " + table + " where messagedate < @messagedate order by messagedate desc limit 30";
qLiteCommand.Parameters.AddWithValue("@messagedate", messageMixGroup.First().MessageDate);
//获得查询结果集
SQLiteDataReader sqlitreader = qLiteCommand.ExecuteReader();
int messcount = messageMixGroup.Count;
MessageMix message = null;
while (sqlitreader.Read())
{
message = new MessageMix();
//将数据插入到最前面，因为在查询是是按日期从大到小排的，而日期小的应该在前面
messageMixGroup.Insert(0, message);
//获得好友信息
message.FriendId = sqlitreader[0].ToString();
message.Message = sqlitreader[1].ToString();
message.MessageDate = sqlitreader[2].ToString();
message.FriendName = FriendName;
message.UserName = Username;
if (int.Parse(sqlitreader[3].ToString()) == 0)
{
//好友发过来的消息
message.Type = "Left";
}
else
{
//自己发给好友的消息
message.Type = "Right";
}
}
sqlitreader.Close();
}
catch (Exception ex)
{
Console.WriteLine("SaveFriendInfo Error : " + ex);
}
sQLiteConnection.Close();
Console.WriteLine("好友信息查询结束");
}
//查询对应好友的聊天信息
public static void QueryMessage(String Username, String FriendName, String Id, ref ObservableCollection <MessageMix> messageMixGroup)
{
Console.WriteLine(Id + "聊天消息查询开始");
//该好友对应的消息表的表名字
String table = "F" + Id + "message";
//先获得一个数据库连接
SQLiteConnection sQLiteConnection = SqliteConnect.GetSqliteConnect();
sQLiteConnection.Open();
try
{
SQLiteCommand qLiteCommand = sQLiteConnection.CreateCommand();
//这里要先看看数据库有没有变化
//目前可行方法 ①:查询数据库消息总数，如果消息数没有改变，那么就直接跳出，不作变化
// ②:数据库有没有可行的变化与否的方法
//TODO 添加倒数条数指示，用于向前向后翻页。添加总数指示，用于数据库变化方法①
//如果记录条数为0，说明是第一次读数据，所以使用这个语句
if (messageMixGroup.Count == 0)
{
qLiteCommand.CommandText = "select * from " + table + " order by messagedate desc limit 30";
}
else
{
//如果不是第一次读取数据，那么在数据表中寻找日期大于最后一条已装入信息的记录
qLiteCommand.CommandText = "select * from " + table + " where messagedate > @messagedate order by messagedate desc";
qLiteCommand.Parameters.AddWithValue("@messagedate", messageMixGroup.Last().MessageDate);
}
//获得查询结果集
SQLiteDataReader sqlitreader = qLiteCommand.ExecuteReader();
int messcount = messageMixGroup.Count;
MessageMix message = null;
while (sqlitreader.Read())
{
message = new MessageMix();
//将数据插入到最前面，因为在查询是是按日期从大到小排的，而日期小的应该在前面
messageMixGroup.Insert(messcount, message);
//获得好友信息
message.FriendId = sqlitreader[0].ToString();
message.Message = sqlitreader[1].ToString();
message.MessageDate = sqlitreader[2].ToString();
message.FriendName = FriendName;
message.UserName = Username;
if (int.Parse(sqlitreader[3].ToString()) == 0)
{
//好友发过来的消息
message.Type = "Left";
}
else
{
//自己发给好友的消息
message.Type = "Right";
}
}
sqlitreader.Close();
}
catch (Exception ex)
{
Console.WriteLine("SaveFriendInfo Error : " + ex);
}
sQLiteConnection.Close();
Console.WriteLine("好友信息查询结束");
}
