//查询对应好友过去的消息 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("好友信息查询结束"); }