public IList<UserGroup> GetUserGroupListByUserID(int userID)
{
IList<UserGroup> result = new List<UserGroup>();
string sql = @"SELECT * FROM Tb_UserGroup WHERE UserID=@UserID ORDER BY SortNum";
SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql);
if (parms == null)
{
parms = new SqlParameter[] {
new SqlParameter("@UserID",SqlDbType.BigInt)
};
SqlHelperParameterCache.CacheParameterSet(sql, parms);
}
parms[0].Value = userID;
using (SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, sql, parms))
{
RowHelper row = new RowHelper(dr);
while (row.Read())
{
result.Add(new UserGroup()
{
GroupName = row.GetString("GroupName"),
ID = row.GetInt32("ID"),
SortNum = row.GetInt32("SortNum"),
UserID = row.GetInt32("UserID")
});
}
dr.Close();
}
return result;
}
public User GetUserByUserName(string userName)
{
User user = new User();
string sql = "select * from Tb_User where UserName=@UserName";
SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql);
if (parms == null)
{
parms = new SqlParameter[] {
new SqlParameter("@UserName",SqlDbType.NVarChar,15)
};
SqlHelperParameterCache.CacheParameterSet(sql, parms);
}
parms[0].Value = userName;
using (SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, sql, parms))
{
RowHelper row = new RowHelper(dr);
if (row.Read())
{
user.UserID = row.GetInt32("UserID");
user.LastLoginIP = row.GetString("LastLoginIP");
user.LastLoginTime = row.GetDateTime("LastLoginTime");
user.RegTime = row.GetDateTime("RegTime");
user.Server = row.GetString("Server");
user.Status = row.GetUInt32("Status");
user.UserName = row.GetString("UserName");
user.UserPwd = row.GetString("UserPwd");
}
dr.Close();
}
return user;
}
public IList<UserFirend> GetFirendListByUserID(int userID)
{
IList<UserFirend> result = new List<UserFirend>();
string sql = @"SELECT TOP (100) PERCENT dbo.Tb_UserFriend.*, dbo.Tb_UserInfo.NickName AS UNickName, dbo.Tb_UserGroup.GroupName, dbo.Tb_UserGroup.SortNum
FROM dbo.Tb_UserFriend INNER JOIN
dbo.Tb_UserInfo ON dbo.Tb_UserFriend.FriendID = dbo.Tb_UserInfo.UserID INNER JOIN
dbo.Tb_UserGroup ON dbo.Tb_UserFriend.GroupID = dbo.Tb_UserGroup.ID where dbo.Tb_UserFriend.UserID=@UserID
ORDER BY dbo.Tb_UserGroup.SortNum DESC";
SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql);
if (parms == null)
{
parms = new SqlParameter[] {
new SqlParameter("@UserID",SqlDbType.BigInt)
};
SqlHelperParameterCache.CacheParameterSet(sql, parms);
}
parms[0].Value = userID;
using (SqlDataReader dr = SqlHelper.ExecuteReader(connString, CommandType.Text, sql, parms))
{
RowHelper row = new RowHelper(dr);
while (row.Read())
{
UserFirend ufInfo = new UserFirend();
ufInfo.ID = row.GetInt32("ID");
ufInfo.FriendID = row.GetInt32("FriendID");
ufInfo.GroupID = row.GetInt32("GroupID");
ufInfo.GroupName = row.GetString("GroupName");
ufInfo.NickName = string.IsNullOrEmpty(row.GetString("NickName")) ? row.GetString("UNickName") : row.GetString("NickName");
ufInfo.Subscription = row.GetString("Subscription");
ufInfo.UserID = row.GetInt32("UserID");
result.Add(ufInfo);
}
dr.Close();
}
return result;
}
private UserInfo FillUserInfo(RowHelper row)
{
UserInfo userInfo = null;
if (row.Read())
{
userInfo = new UserInfo()
{
Age = row.GetInt32("Age"),
BirthDay = row.GetDateTime("BirthDay"),
Email = row.GetString("Email"),
NickName = row.GetString("NickName"),
Sex = row.GetInt16("Sex"),
UserID = row.GetInt32("UserID")
};
}
return userInfo;
}