/// <summary> /// 查找所有的好友策略信息 /// </summary> /// <returns>封装了所有好友策略信息的数据表</returns> public DataTable GetAllFriendshipPolicy() { //定义数据库操作通用类的对象 DbOperate dbo = new DbOperate(); dbo.TableName = FriendshipPolicyOperator.TABLENAME; //定义数据操作对象,初始其要执行的SQL语句 SqlCommand com = new SqlCommand(); com.CommandText = "select * from FriendshipPolicy"; //执行SQL查询,返回封装了所有好友策略信息的数据表 return dbo.Select(com).Tables[FriendshipPolicyOperator.TABLENAME]; }
/// <summary> /// 查找所有消息类型信息 /// </summary> /// <returns>封装了所有消息类型信息的数据表</returns> public DataTable GetAllStar() { //定义数据库操作通用类对象 DbOperate dbo = new DbOperate(); dbo.TableName = MessageTypeOperator.TABLENAME; //定义数据操作对象,并初始化其要执行的SQL语句 SqlCommand com = new SqlCommand(); com.CommandText = "select * from MessageType"; //执行SQL查询,返回封装了所有消息类型信息的数据表 return dbo.Select(com).Tables[MessageTypeOperator.TABLENAME]; }
/// <summary> /// 查询所有血型信息 /// </summary> /// <returns>封装了所有血型信息的数据表</returns> public DataTable GetAllBloodType() { //定义数据库操作通用类对象 DbOperate dbo = new DbOperate(); dbo.TableName = BloodTypeOperator.TABLENAME; //构建数据操作对象,并初始化其SQL语句 SqlCommand com = new SqlCommand(); com.CommandText = "select * from BloodType"; //返回查询结果集中的对应数据表 return dbo.Select(com).Tables[BloodTypeOperator.TABLENAME]; }
/// <summary> /// 查找所有星座信息 /// </summary> /// <returns>封装了所有星座信息的数据表</returns> public DataTable GetAllStar() { //定义数据库操作通用类的对象 DbOperate dbo = new DbOperate(); dbo.TableName = StarOperator.TABLENAME; //构建数据操作对象,并初始化其SQL语句 SqlCommand com = new SqlCommand(); com.CommandText = "select * from Star"; //返回其查询结果集中对应的数据表 return dbo.Select(com).Tables[StarOperator.TABLENAME]; }
public Group GetGroupByGroupId(int groupId) { this.dbo = new DbOperate(); SqlCommand com = new SqlCommand(); string sql = "select * from Group where GroupId={0}"; sql = string.Format(sql, groupId); com.CommandText = sql; DataSet ds = this.dbo.Select(com); Group g = new Group(); if (ds.Tables[GroupOperator.TABLENAME].Rows.Count==1) { DataRow row = ds.Tables[GroupOperator.TABLENAME].Rows[0]; g.GroupID = Convert.ToInt32(row["GroupID"]); g.GroupName = row["GroupName"].ToString(); g.GroupHostID = Convert.ToInt32(row["HostId"]); g.GroupDescription = row["GroupDescription"].ToString(); g.GroupBuildTime = Convert.ToDateTime(row["GroupBuildTime"]); } return g; }
public GroupList GetGroupListByUserId(int uid) { this.dbo = new DbOperate(); SqlCommand com = new SqlCommand(); string sql = string.Format("select * from group where groupId in"+ "(select * from friends where hostId={0} and friendId={0})",uid); com.CommandText = sql; DataTable table = this.dbo.Select(com).Tables[0]; GroupList list = new GroupList(); foreach(DataRow row in table.Rows) { Group g = new Group(); g.GroupID = Convert.ToInt32(row["GroupID"]); g.GroupName = row["GroupName"].ToString(); g.GroupHostID = Convert.ToInt32(row["HostId"]); g.GroupDescription = row["GroupDescription"].ToString(); g.GroupBuildTime = Convert.ToDateTime(row["GroupBuildTime"]); list.Add(g); } return list; }
/// <summary> /// ����������ɹ���QQ���� /// </summary> /// <returns>����ɹ���QQ����</returns> public int getUserId() { //�������ݿ����ͨ������� DbOperate objOperate = new DbOperate(); objOperate.TableName = "Users"; //�������ݲ���������ʼ�佫ִ�е�SQL��� SqlCommand com = new SqlCommand(); com.CommandText = "select max(Id) from Users"; //ִ��SQL��䣬����ѯ�������ݼ����� DataSet objSet=objOperate.Select(com); Console.WriteLine("����ֵ�ǣ�"+objSet.Tables["Users"].Rows[0][0]); //���ز�ѯ���ĵ�һ�����еĵ�һ�еĵ�һ�е����� return (int)objSet.Tables["Users"].Rows[0][0]; }
/// <summary> /// 通过群组号获取群组人员ID /// </summary> /// <param name="groupId">群组ID</param> /// <returns>群组Friend列表</returns> public FriendList LoadFriendListByGroupId(int groupId) { this.list = new FriendList(); DbOperate dbo = new DbOperate(); dbo.TableName = FriendsOperator.TABLENAME; SqlCommand com = new SqlCommand(); com.CommandText = string.Format("select * from Friends where groupId={0}",groupId); DataTable table = dbo.Select(com).Tables[0]; foreach(DataRow row in table.Rows) { FriendItem item = new FriendItem(); item.Id = Convert.ToInt32(row["id"]); item.HostId = Convert.ToInt32(row["HostId"]); item.FriendId = Convert.ToInt32(row["FriendId"]); item.FriendShipId = Convert.ToInt32(row["FriendShipPolicy"]); if (row["GroupId"] != null) { item.GroupId = Convert.ToInt32(row["GroupId"]); } else { item.GroupId = 0; } this.list.Add(item); } return this.list.Length >= 1 ? this.list : null; }
/// <summary> /// 根据用户编号,查找该用户的所有好友信息列表 /// </summary> /// <param name="hostId">用户编号</param>\ /// <param name="friendShipID">好友信息策略</param> /// <returns>该用户对应的好友信息列表</returns> public FriendList LoadFrendList(int hostId,int friendShipID) { //定义数据库操作通用类的一个对象 DbOperate dbo = new DbOperate(); dbo.TableName = FriendsOperator.TABLENAME; //定义数据操作对象,并初始化其将执行的SQL语句 SqlCommand com = new SqlCommand(); com.CommandText = "select * from Friends where HostId=@HostId and FriendShipPolicy=@FriendShipPolicy"; //对数据操作对象的SQL语句中的参数赋值 SqlParameter hid = new SqlParameter("@HostId",SqlDbType.Int); hid.Value = hostId; SqlParameter friendShipPolicy = new SqlParameter("@FriendShipPolicy", SqlDbType.Int); friendShipPolicy.Value = friendShipID; com.Parameters.Add(hid); com.Parameters.Add(friendShipPolicy); //执行SQL语句,获取其数据集中的数据表 DataTable table = dbo.Select(com).Tables[0]; //迭代,将数据表中的好友信息添加到好友列表(集合)中 foreach(DataRow row in table.Rows) { FriendItem item = new FriendItem(); item.Id = Convert.ToInt32(row["id"]); item.HostId = Convert.ToInt32(row["HostId"]); item.FriendId = Convert.ToInt32(row["FriendId"]); item.FriendShipId = Convert.ToInt32(row["FriendShipPolicy"]); if (row["GroupId"] != null && row["GroupId"].ToString().Length >0) { item.GroupId = Convert.ToInt32(row["GroupId"].ToString()); } else { item.GroupId = 0; } this.list.Add(item); } return this.list.Length >= 1 ? this.list : null; }
/// <summary> /// ���ݲ����е��û�ID���¸��û�ʵ�������ݿ��е���Ϣ /// </summary> /// <param name="item">�û�ʵ�����</param> /// <returns>������Ӱ�������</returns> public int Update(UserItem item) { DbOperate dbo = new DbOperate(); dbo.TableName = UsersOperator.TABLENAME; //����һ�����ݲ���������ִ�и������ SqlCommand com = new SqlCommand(); com.CommandText = "update Users set Loginpwd=@LoginPwd,nickName=@nickName,faceId=@faceId,sex=@sex,age=@age,name=@name,starId=@starId,bloodTypeId=@bloodTypeId where Id=@id "; //��SQL��������еIJ���ȷ�����ͣ������θ�ֵ com.Parameters.Add(new SqlParameter("@LoginPwd", SqlDbType.VarChar, 50)).Value = item.LoginPwd; com.Parameters.Add(new SqlParameter("@nickName", SqlDbType.VarChar, 50)).Value = item.NickName; com.Parameters.Add(new SqlParameter("@faceId", SqlDbType.Int)).Value = item.FaceId; com.Parameters.Add(new SqlParameter("@sex", SqlDbType.VarChar, 50)).Value = item.Sex; com.Parameters.Add(new SqlParameter("@age", SqlDbType.Int)).Value = item.Age; com.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 50)).Value = item.Name; com.Parameters.Add(new SqlParameter("@starId", SqlDbType.Int)).Value = item.StarId; com.Parameters.Add(new SqlParameter("@bloodTypeId", SqlDbType.Int)).Value = item.BloodTypeId; com.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = item.Id; //ִ�����ݸ��²��������ر�Ӱ��ļ�¼���� return dbo.UpDate(com); }
/// <summary> /// ���ݱ�Ų����û���Ϣ /// </summary> /// <param name="userid">�û����</param> /// <returns>�û�ʵ�����</returns> public UserItem LoadUser(int userid) { //�������ݿ����ͨ���� DbOperate dbo = new DbOperate(); dbo.TableName = UsersOperator.TABLENAME; //�������ݲ������� SqlCommand com = new SqlCommand(); //�����ѯ��䣬��ȷ���������������ֵ com.CommandText = "select * from users where id=@uid "; SqlParameter uid = new SqlParameter("@uid", SqlDbType.Int); uid.Value = userid; com.Parameters.Add(uid); DataTable table = dbo.Select(com).Tables[0]; //���ݱ�Ž��в��� if (table.Rows.Count == 1) { this.view = table.DefaultView; this.item = new UserItem(); //����ҵ���Ӧ��¼�������װ���û�ʵ������� DataRow row = table.Rows[0]; this.item.Id = Convert.ToInt32(row["Id"]); this.item.LoginPwd = row["LoginPwd"].ToString(); this.item.NickName = row["NickName"].ToString(); this.item.FaceId = Convert.ToInt32(row["FaceId"].ToString().Length<=0 ? 0 : row["FaceId"]); this.item.Sex = row["Sex"].ToString(); this.item.Age = Convert.ToInt32(row["Age"]); this.item.Name = row["Name"].ToString(); this.item.StarId =Convert.ToInt32( row["StarId"]); this.item.BloodTypeId = Convert.ToInt32(row["BloodTypeId"]); //���ظ��û�ʵ����� return this.item; } else { //���û�ҵ���Ӧ�û������ؿն��� return null; } }
/// <summary> /// 获取一定事件范围内,特定好友的聊天记录 /// </summary> /// <param name="hostid"></param> /// <param name="friendId"></param> /// <param name="time"></param> /// <returns></returns> public MessageList GetWeMsg(int hostid, int friendId, DateTime time) { DbOperate dbo = new DbOperate(); dbo.TableName = MessageOperator.TABLENAME; MessageList returnList = new MessageList(); SqlCommand com = new SqlCommand(); com.CommandText = "select * from Messages where toUserId=@hostID and fromUSerid=@friendId and MessageState=0 and MessageTime between @startTime and @endTime"; SqlParameter hostPar = new SqlParameter("@hostID",SqlDbType.Int); hostPar.Value = hostid; SqlParameter friendPar = new SqlParameter("@friendId",SqlDbType.Int); friendPar.Value = friendId; SqlParameter start = new SqlParameter("@startTime",SqlDbType.DateTime); start.Value = time.AddSeconds(-2); SqlParameter end = new SqlParameter("@endTime",SqlDbType.DateTime); end.Value = time.AddSeconds(1); com.Parameters.Add(hostPar); com.Parameters.Add(friendPar); com.Parameters.Add(start); com.Parameters.Add(end); DataSet ds = dbo.Select(com); foreach (DataRow row in ds.Tables[0].Rows) { MessageItem item = new MessageItem(); item.Id = Convert.ToInt32(row["Id"]); item.FromUserId = Convert.ToInt32(row["FromUserId"]); item.ToUserId = hostid; item.Message = row["Message"].ToString(); item.MessageTypeId = Convert.ToInt32(row["MessageTypeId"]); item.MessageState = (MessageState)Convert.ToInt32(row["MessageState"]); item.MessageTime = Convert.ToDateTime(row["MessageTime"]); returnList.Add(item); } return returnList; }
/// <summary> /// 返回某用户的好友列表对象 /// </summary> /// <param name="fromUserId">用户的MyQQ号</param> /// <returns></returns> public MessageList GetOwnerMsgByFromUser(int fromUserId) { DbOperate dbo = new DbOperate(); dbo.TableName = MessageOperator.TABLENAME; SqlCommand com = new SqlCommand(); com.CommandText = "select * from Messages where fromUserId=@fromUserId"; SqlParameter fUserId = new SqlParameter("@fromUserId",SqlDbType.Int); fUserId.Value = fromUserId; dbo.TableName = MessageOperator.TABLENAME; com.Parameters.Add(fUserId); DataSet ds = dbo.Select(com); MessageList list = new MessageList(); foreach (DataRow row in ds.Tables[0].Rows) { MessageItem item = new MessageItem(); item.Id = Convert.ToInt32(row["Id"]); item.FromUserId = Convert.ToInt32(row["FromUserId"]); item.ToUserId =Convert.ToInt32(row["TouserId"]); item.Message = row["Message"].ToString(); item.MessageTypeId = Convert.ToInt32(row["MessageTypeId"]); item.MessageState = (MessageState)Convert.ToInt32(row["MessageState"]); item.MessageTime = Convert.ToDateTime(row["MessageTime"]); list.Add(item); } return list; }
public MessageItem GetMessage(int itemid) { DbOperate dbo = new DbOperate(); dbo.TableName = MessageOperator.TABLENAME; SqlCommand com = new SqlCommand(); com.CommandText = "select * from Messages where id=@MessageId"; SqlParameter Idpar = new SqlParameter("@MessageId", SqlDbType.Int); Idpar.Value = itemid; com.Parameters.Add(Idpar); DataSet ds = dbo.Select(com); if(ds.Tables[0].Rows.Count>0) { DataRow row = ds.Tables[0].Rows[0]; MessageItem item = new MessageItem(); item.Id = Convert.ToInt32(row["Id"]); item.FromUserId = Convert.ToInt32(row["FromUserId"]); item.ToUserId = Convert.ToInt32(row["ToUserId"]); item.Message = row["Message"].ToString(); item.MessageTypeId = Convert.ToInt32(row["MessageTypeId"]); item.MessageState = (MessageState)Convert.ToInt32(row["MessageState"]); item.MessageTime = Convert.ToDateTime(row["MessageTime"]); return item; } return null; }
/// <summary> /// ע��һ���û� /// </summary> /// <param name="item">�û�ʵ�����</param> /// <returns>�����ݿ��и��µļ�¼����</returns> public int InsertUser(UserItem item) { //����һ�����ݿ����ͨ�ö��� DbOperate dbo = new DbOperate(); dbo.TableName = UsersOperator.TABLENAME; //����һ�����ݲ����SQL��� SqlCommand com = new SqlCommand(); com.CommandText = "insert into Users values(@LoginPwd,@nickName,@faceId,@sex,@age,@name,@starId,@bloodTypeId)"; //����SQL���������û�ʵ������ֵ������� com.Parameters.Add(new SqlParameter("@LoginPwd", SqlDbType.VarChar,50)).Value = item.LoginPwd; com.Parameters.Add(new SqlParameter("@nickName", SqlDbType.VarChar, 50)).Value = item.NickName; com.Parameters.Add(new SqlParameter("@faceId", SqlDbType.Int)).Value = item.FaceId; com.Parameters.Add(new SqlParameter("@sex", SqlDbType.VarChar,50)).Value = item.Sex; com.Parameters.Add(new SqlParameter("@age", SqlDbType.Int)).Value = item.Age; com.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar,50)).Value=item.Name; com.Parameters.Add(new SqlParameter("@starId", SqlDbType.Int)).Value = item.StarId; com.Parameters.Add(new SqlParameter("@bloodTypeId", SqlDbType.Int)).Value = item.BloodTypeId; //ִ�����ݿ���������������Ӱ��ļ�¼���� //SqlDataReader objReader = dbo.ModifyAndFind(com); //if (objReader.Read()) //{ // return (int)objReader[0]; //} //return -1; int result = dbo.Insert(com); this._lastUserId = this.getUserId(); return result; }
/// <summary> /// ���������û���Ϣ�б� /// </summary> /// <returns>�����û���Ϣ�б�</returns> public UserList LoadFrendList() { //�������ݿ����ͨ�����һ������ DbOperate dbo = new DbOperate(); dbo.TableName = UsersOperator.TABLENAME; //�������ݲ���������ʼ���佫ִ�е�SQL��� SqlCommand com = new SqlCommand(); com.CommandText = "select * from Users"; //ִ��SQL��䣬��ȡ�����ݼ��е����ݱ� DataTable table = dbo.Select(com).Tables[0]; this.view = table.DefaultView; //�����������ݱ��е��û���Ϣ��ӵ��û��б�����ϣ��� foreach (DataRow row in table.Rows) { UserItem item = new UserItem(); item.Id=Convert.ToInt32(row["Id"]); item.LoginPwd = (string)row["LoginPwd"]; item.NickName = (string)row["NickName"]; item.FaceId = Convert.ToInt32(row["FaceId"]); item.Sex =Convert.ToString( row["Sex"]); item.Age = Convert.ToInt32(row["Age"]); item.Name = Convert.ToString(row["Name"]); item.StarId = Convert.ToInt32(row["StarId"]); item.BloodTypeId = Convert.ToInt32(row["BloodTypeId"]); this.list.Add(item); } return this.list.Length >= 1 ? this.list : null; }
/// <summary> /// 所有插入消息的状态都是未读消息 /// </summary> /// <param name="item"></param> /// <returns></returns> public int InsertMessage(MessageItem item) { DbOperate dbo = new DbOperate(); dbo.TableName = MessageOperator.TABLENAME; SqlCommand com = new SqlCommand(); com.CommandText = "insert into Messages values(@fromUserId,@toUserId,@message,@messageTypeId,@messageState,@messageTime)"; com.Parameters.Add(new SqlParameter("@fromUserId", SqlDbType.Int)).Value = item.FromUserId; com.Parameters.Add(new SqlParameter("@toUserId", SqlDbType.Int)).Value = item.ToUserId; com.Parameters.Add(new SqlParameter("@message", SqlDbType.VarChar,50)).Value = item.Message; com.Parameters.Add(new SqlParameter("@messageTypeId", SqlDbType.Int)).Value = item.MessageTypeId; com.Parameters.Add(new SqlParameter("@messageState", SqlDbType.Int)).Value = item.MessageState; com.Parameters.Add(new SqlParameter("@messageTime", SqlDbType.DateTime)).Value = item.MessageTime; return dbo.Insert(com); }
/// <summary> /// �����û�����������е�¼ /// </summary> /// <param name="userid"></param> /// <param name="userpwd"></param> /// <returns></returns> public UserItem Login(int userid, string userpwd) { //�������ݲ���ͨ����Ķ��� DbOperate dbo = new DbOperate(); dbo.TableName = UsersOperator.TABLENAME; SqlCommand com = new SqlCommand(); //����һ����ѯ��䣬������ѯ���û�����Ӧ�����Ƿ���� com.CommandText = "select * from users where id=@uid and LoginPwd=@pwd"; //��������SQL������������������ SqlParameter uid = new SqlParameter("@uid", SqlDbType.Int); SqlParameter pwd = new SqlParameter("@pwd",SqlDbType.VarChar,50); //��������SQL������ֵ uid.Value = userid; pwd.Value = userpwd; com.Parameters.Add(uid); com.Parameters.Add(pwd); //���ݲ�ѯ������в�ͬ���� if (dbo.Select(com).Tables[0].Rows.Count == 1) { //����ѯ�����װ��һ���û��������� DataRow row = dbo.Select(com).Tables[0].Rows[0]; this.item.Id = Convert.ToInt32(row["Id"]); this.item.LoginPwd = row["LoginPwd"].ToString(); this.item.NickName = row["NickName"].ToString(); this.item.FaceId = Convert.ToInt32(row["FaceId"].ToString().Length<=0 ? 0 : row["FaceId"]); this.item.Sex = row["Sex"].ToString(); this.item.Age = Convert.ToInt32(row["Age"]); this.item.Name = row["Name"].ToString(); this.item.StarId = Convert.ToInt32(row["StarId"]); this.item.BloodTypeId = Convert.ToInt32(row["BloodTypeId"]); return this.item; } else { //δ�ҵ���Ӧ�û����ؿն��� return null; } }
/// <summary> /// 更新消息列表中的消息为已读消息 /// </summary> /// <param name="list"></param> /// <returns></returns> public MessageList UpdateMsg2Readed(MessageList list) { DbOperate dbo = new DbOperate(); dbo.TableName = MessageOperator.TABLENAME; MessageList returnList = new MessageList(); foreach (MessageItem item in list) { SqlCommand com = new SqlCommand(); com.CommandText = "update Messages set MessageState=1 where Id=@MessageId"; SqlParameter Idpar = new SqlParameter("@MessageId", SqlDbType.Int); Idpar.Value = item.Id; com.Parameters.Add(Idpar); dbo.UpDate(com); MessageItem upItem = this.GetMessage(item.Id); returnList.Add(upItem); } return returnList; }
public int AddFriend(int hostId,int friendShipPolicy,int friendId) { DbOperate dbo = new DbOperate(); dbo.TableName = FriendsOperator.TABLENAME; //定义数据操作对象,并初始化其将执行的SQL语句 SqlCommand com = new SqlCommand(); com.CommandText = string.Format("insert into Friends values({0},{1},null,{2})",hostId,friendShipPolicy,friendId); return dbo.Insert(com); }
public GroupOperator() { this.dbo = new DbOperate(); this.dbo.TableName = GroupOperator.TABLENAME; }