public void InsertFirend(UserFirend ufInfo)
{
if (IsExitsFirendID(ufInfo.UserID, ufInfo.FriendID))
{
return;
}
string sql = @"Insert into Tb_UserFriend (UserID,FriendID,GroupID,Subscription,NickName)
values (@UserID,@FriendID,@GroupID,@Subscription,@NickName)";
SqlParameter[] parms = SqlHelperParameterCache.GetCachedParameterSet(sql);
if (parms == null)
{
parms = new SqlParameter[] {
new SqlParameter("@UserID",SqlDbType.BigInt),
new SqlParameter("@FriendID",SqlDbType.BigInt),
new SqlParameter("@GroupID",SqlDbType.Int),
new SqlParameter("@Subscription",SqlDbType.NVarChar,10),
new SqlParameter("@NickName",SqlDbType.NVarChar,20)
};
SqlHelperParameterCache.CacheParameterSet(sql, parms);
}
parms[0].Value = ufInfo.UserID;
parms[1].Value = ufInfo.FriendID;
parms[2].Value = ufInfo.GroupID;
parms[3].Value = ufInfo.Subscription;
parms[4].Value = ufInfo.NickName;
SqlHelper.ExecuteNonQuery(connString, CommandType.Text, sql, parms);
}
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;
}