/// <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;
}
