/// <summary>
/// 获取用户与学校相关的信息
/// </summary>
/// <param name="userId">目标用户编号</param>
/// <returns>用户与学校相关的信息</returns>
public static UserWithSchool GetUserWithSchool(int userId)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Users_GetUserWithSchool", CommandType.StoredProcedure))
{
cmd.AddInputParameters("UserId", userId);
UserWithSchool resultData = null;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
resultData = new UserWithSchool
{
UserId = (int)reader["UserId"],
SchoolId = (int)reader["UserId"],
IsTrial = (bool)reader["IsTrial"],
TrialedSource = (string)reader["TrialedSource"],
IsActivated = (bool)reader["IsActivated"],
ActivatedDate = (DateTime)reader["ActivatedDate"],
IsExpired = (bool)reader["IsExpired"],
ExpiredDate = (DateTime)reader["ExpiredDate"],
StudNo = (string)reader["StudNo"],
ClassInfo = (string)reader["ClassInfo"],
Comment = (string)reader["Comment"],
SyncForBbs = (bool)reader["SyncForBbs"],
Status = (int)reader["Status"]
};
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取指定学校的课程信息列表
/// </summary>
/// <param name="schoolId"></param>
/// <returns></returns>
public static IEnumerable <CourseBase> GetSchoolCourses(int schoolId)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_School_GetCourses", CommandType.StoredProcedure))
{
cmd.AddInputParameters("SchoolId", schoolId);
List <CourseBase> resultData = new List <CourseBase>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new CourseBase
{
CourseId = (int)reader["CourseId"],
CategoryId = (int)reader["CategoryId"],
SchoolId = (int)reader["SchoolId"],
CourseName = (string)reader["CourseName"],
CourseCode = (string)reader["CourseCode"],
CourseIcon = (string)reader["CourseIcon"],
Intro = (string)reader["Intro"],
Comment = (string)reader["Comment"],
PriceName = (string)reader["PriceName"],
SalePriceName = (string)reader["SalePriceName"],
Sort = (int)reader["Sort"],
IsEnabled = (bool)reader["IsEnabled"],
CreateDate = (DateTime)reader["CreateDate"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取指定的学校信息
/// </summary>
/// <param name="schoolId">学校编号</param>
/// <returns>学校信息</returns>
public static SchoolBase GetSchoolBaseInfo(int schoolId)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_School_GetDetail", CommandType.StoredProcedure))
{
cmd.AddInputParameters("SchoolId", schoolId);
SchoolBase resultData = null;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
resultData = new SchoolBase
{
SchoolId = (int)reader["SchoolId"],
FullName = (string)reader["FullName"],
ShortName = (string)reader["ShortName"],
EnglishName = (string)reader["EnglishName"],
LargerLogo = (string)reader["LargerLogo"],
SmallerLogo = (string)reader["SmallerLogo"],
SceneryLogo = (string)reader["SceneryLogo"],
AreaCity = (string)reader["AreaCity"],
AreaAddress = (string)reader["AreaAddress"],
Contacter = (string)reader["Contacter"],
Telphone = (string)reader["Telphone"],
Email = (string)reader["Email"],
WebSite = (string)reader["WebSite"],
Introduce = (string)reader["Introduce"],
CreateDate = (DateTime)reader["CreateDate"]
};
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 英译汉联想查询(单个单词)
/// </summary>
/// <param name="word">单词</param>
/// <param name="firstChar">第一个字母</param>
/// <param name="querySize">最大查询联想结果数量</param>
/// <returns></returns>
public static IEnumerable <DictRecord> DictE2cExtendQueryWithSingleWords(string word, string firstChar, int querySize)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Material_Dict_ExtendQueryBySingleWord", CommandType.StoredProcedure))
{
cmd.AddInputParameters("Word, WordLike, FirstChar, Size", word, string.Format("{0}%", word), string.Format("{0}%", firstChar), querySize);
List <DictRecord> resultData = new List <DictRecord>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new DictRecord
{
Word = (string)reader["Word"],
PhonicsEn = (string)reader["PhonicsEn"],
PhonicsUs = (string)reader["PhonicsUs"],
AudioEn = (string)reader["AudioEn"],
AudioUs = (string)reader["AudioUs"],
Explication = (byte[])reader["Explication"],
Example = (byte[])reader["Example"],
TransBase = (byte[])reader["TransBase"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取指定的帖子跟帖信息分页列表
/// </summary>
/// <param name="topicId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static IEnumerable <PostInfo> GetTopicReplyList(int topicId, int pageIndex, int pageSize)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetTopicPostList", CommandType.StoredProcedure))
{
cmd.AddInputParameters("PostId, PageIndex, PageSize", topicId, pageIndex, pageSize);
List <PostInfo> resultData = new List <PostInfo>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new PostInfo
{
TopicId = (int)reader["TopicId"],
PostId = (int)reader["PostId"],
UserId = (int)reader["UserId"],
ExpChanged = (int)reader["ExpChanged"],
VirtualCoinChanged = (int)reader["VirtualCoinChanged"],
FavouredCount = (int)reader["FavouredCount"],
CreateDate = (DateTime)reader["CreateDate"],
Content = (string)reader["Content"],
IsBestReply = (bool)reader["IsBestReply"],
SetBestDate = (DateTime)reader["SetBestDate"],
ReplyForUserId = (int)reader["ReplyForUserId"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取指定用户的所有课时信息列表
/// </summary>
/// <param name="schoolId"></param>
/// <param name="userId"></param>
/// <returns></returns>
public static IEnumerable <LessonInfo> GetUserLessions(int schoolId, int userId)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_School_GetUserLessons", CommandType.StoredProcedure))
{
cmd.AddInputParameters("SchoolId", schoolId);
List <LessonInfo> resultData = new List <LessonInfo>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new LessonInfo
{
CourseId = (int)reader["CourseId"],
LessonId = (int)reader["LessonId"],
ClassId = (int)reader["ClassId"],
LessonDate = (DateTime)reader["LessonDate"],
LessonStartTime = (DateTime)reader["LessonStartTime"],
LessonEndTime = (DateTime)reader["LessonEndTime"],
CreateDate = (DateTime)reader["CreateDate"],
ClassName = (string)reader["ClassName"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 汉译英词典查询
/// </summary>
/// <param name="word"></param>
/// <returns></returns>
public static DictRecord QueryC2EDict(string word)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Material_Dict_QueryC2E", CommandType.StoredProcedure))
{
cmd.AddInputParameters("Word", word);
DictRecord resultData = null;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
resultData = new DictRecord
{
Word = (string)reader["Word"],
TransBase = (byte[])reader["Translation"],
AudioEn = "",
AudioUs = "",
PhonicsEn = "",
PhonicsUs = "",
Example = new byte[0],
Explication = new byte[0]
};
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取用户验证信息
/// </summary>
/// <param name="userName">用户名</param>
/// <param name="userAccess">用户对应的登录权限</param>
/// <returns>用户验证信息</returns>
public static UserVaildInfo GetUserVaildInfo(string userName, UserAccess userAccess)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Users_GetUserVaildInfo", CommandType.StoredProcedure))
{
cmd.AddInputParameters("UserName, UserAccess", userName, userAccess);
UserVaildInfo resultData = null;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
resultData = new UserVaildInfo
{
UserId = (int)reader["UserId"],
UserName = (string)reader["UserName"],
Password = (string)reader["Password"],
PasswordSalt = (string)reader["PasswordSalt"],
IsApproved = (bool)reader["IsApproved"],
IsLocked = (bool)reader["IsLocked"],
LastLockedoutDate = (DateTime)reader["LastLockedoutDate"],
LoginCount = (int)reader["LoginCount"],
LastLoginDate = (DateTime)reader["LastLoginDate"],
UserSite = (int)reader["UserSite"],
UserRole = (int)reader["UserRole"],
UserAccess = (UserAccess)(int)reader["UserAccess"],
LastLoginDeviceId = (int)reader["LastLoginDeviceId"]
};
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取指定的论坛版块详情
/// </summary>
/// <param name="forumId"></param>
/// <param name="schoolId"></param>
/// <returns></returns>
public static ForumInfo GetForumInfoById(int forumId, int schoolId)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetForumById", CommandType.StoredProcedure))
{
cmd.AddInputParameters("ForumId, SchoolId", forumId, schoolId);
ForumInfo resultData = null;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
resultData = new ForumInfo
{
ForumId = (int)reader["ForumId"],
ForumName = (string)reader["ForumName"],
ForumIcon = (string)reader["ForumIcon"],
AllowPost = (int)reader["AllowPost"],
AllowPostType = (int)reader["AllowPostType"],
LinkType = (int)reader["LinkType"]
};
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取更新的论坛版块信息列表
/// </summary>
/// <param name="userId"></param>
/// <param name="deviceId"></param>
/// <param name="schoolId"></param>
/// <returns></returns>
public static IEnumerable <ForumInfo> GetUpdatedForumsList(int userId, int deviceId, int schoolId)
{
using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_GetForumUpdated", CommandType.StoredProcedure))
{
cmd.AddInputParameters("UserId, DeviceId, SchoolId, Date", userId, deviceId, schoolId, DateTime.Now);
List <ForumInfo> resultData = new List <ForumInfo>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new ForumInfo
{
ForumId = (int)reader["ForumId"],
ForumName = (string)reader["ForumName"],
ForumIcon = (string)reader["ForumIcon"],
AllowPost = (int)reader["AllowPost"],
AllowPostType = (int)reader["AllowPostType"],
LinkType = (int)reader["LinkType"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取指定的帖子回复信息
/// </summary>
/// <param name="postId"></param>
/// <returns></returns>
public static PostInfo GetPostInfoById(int postId)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetPostById", CommandType.StoredProcedure))
{
cmd.AddInputParameters("PostId", postId);
PostInfo resultData = null;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
resultData = new PostInfo
{
TopicId = (int)reader["TopicId"],
PostId = (int)reader["PostId"],
UserId = (int)reader["UserId"],
ExpChanged = (int)reader["ExpChanged"],
VirtualCoinChanged = (int)reader["VirtualCoinChanged"],
FavouredCount = (int)reader["FavouredCount"],
CreateDate = (DateTime)reader["CreateDate"],
Content = (string)reader["Content"],
IsBestReply = (bool)reader["IsBestReply"],
SetBestDate = (DateTime)reader["SetBestDate"],
ReplyForUserId = (int)reader["ReplyForUserId"]
};
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 根据群编号或者快速加入码获取群组信息
/// </summary>
/// <param name="groupId">群组编号</param>
/// <param name="quickJoinCode">快速加入码</param>
/// <returns>群组信息</returns>
public static GroupInfo GetGroupInfo(int groupId, string quickJoinCode)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Social_GetGroupDetail", CommandType.StoredProcedure))
{
cmd.AddInputParameters("GroupId, QuickJoinCode", groupId, quickJoinCode);
GroupInfo resultData = null;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
resultData = new GroupInfo
{
GroupId = (int)reader["GroupId"],
GroupName = (string)reader["GroupName"],
GroupIcon = (string)reader["GroupIcon"],
Comment = (string)reader["Comment"],
MaxMemberCount = (int)reader["MaxMemberCount"],
MemberCount = (int)reader["MemberCount"],
GroupType = (int)reader["GroupType"],
CreatorId = (int)reader["CreatorId"],
InterestCode = (string)reader["InterestCode"],
QuickJoinCode = (string)reader["QuickJoinCode"],
CreateDate = (DateTime)reader["CreateDate"]
};
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 汉译英联想查询
/// </summary>
/// <param name="words">分词得到的关键词组</param>
/// <param name="querySize">最大查询联想结果数量</param>
/// <returns></returns>
public static IEnumerable <DictRecord> DictC2eExtendQuery(IEnumerable <string> words, int querySize)
{
List <string> pNameList = new List <string> {
"Size"
};
List <object> pValueList = new List <object> {
querySize
};
List <string> sqlWhere = new List <string>(0);
int idx = 0;
foreach (var word in words)
{
string p = string.Format("@E{0}", idx), p1 = string.Format("@L{0}", idx);
sqlWhere.Add(string.Format(" Word = {0} ", p));
pNameList.Add(p);
pValueList.Add(word);
sqlWhere.Add(string.Format(" Word LIKE {0} ", p1));
pNameList.Add(p1);
pValueList.Add(string.Format("%{0}%", word));
idx++;
}
string querySql = @"SET ROWCOUNT @Size
SELECT Word ,
Translation
FROM Material_Dict_CE
WHERE {0}
ORDER BY Word";
querySql = string.Format(querySql, string.Join(" OR ", sqlWhere));
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, querySql))
{
cmd.AddInputParameters(string.Join(",", pNameList), pValueList.ToArray());
List <DictRecord> resultData = new List <DictRecord>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new DictRecord
{
Word = (string)reader["Word"],
TransBase = (byte[])reader["Translation"],
AudioEn = "",
AudioUs = "",
PhonicsEn = "",
PhonicsUs = "",
Example = new byte[0],
Explication = new byte[0]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 从数据库读取用于缓存的用户信息
/// </summary>
/// <param name="userId">目标用户编号</param>
/// <returns>用于缓存的用户信息</returns>
public static UserCacheInfo GetUserInfoForCache(int userId)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Users_GetUserInfoForCache", CommandType.StoredProcedure))
{
cmd.AddInputParameters("UserId", userId);
UserCacheInfo resultData = null;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
resultData = new UserCacheInfo
{
UserId = (int)reader["UserId"],
UserName = (string)reader["UserName"],
NickName = (string)reader["NickName"],
RealName = (string)reader["RealName"],
BackIcon = (string)reader["BackIcon"],
HeadIcon = (string)reader["HeadIcon"],
Signature = (string)reader["Signature"],
Gender = (int)reader["Gender"],
Area = (string)reader["Area"],
Birthday = (DateTime)reader["Birthday"],
UserAge = (int)reader["UserAge"],
Interests = (string)reader["Interests"],
UserSite = (int)reader["UserSite"],
UserRole = (int)reader["UserRole"],
UserAccess = (int)reader["UserAccess"],
ExpScore = (int)reader["ExpScore"],
ExpLevel = (int)reader["ExpLevel"],
GradeName = (string)reader["GradeName"],
GradeIcon = (string)reader["GradeIcon"],
VirtualCoinCount = (int)reader["VirtualCoinCount"],
FansCount = (int)reader["FansCount"],
FollowedCount = (int)reader["FollowedCount"],
TopicCount = (int)reader["TopicCount"],
ReplyCount = (int)reader["ReplyCount"],
RemindPrivateMessage = (bool)reader["RemindPrivateMessage"],
RemindGroupMessage = (bool)reader["RemindGroupMessage"],
RemindBeFollowed = (bool)reader["RemindBeFollowed"],
RemindTopicBeReply = (bool)reader["RemindTopicBeReply"],
OSPlatform = (int)reader["Platform"],
DeviceToken = (string)reader["Token"],
StudNo = (string)reader["StudNo"],
ClassInfo = (string)reader["ClassInfo"],
Comment = (string)reader["Comment"],
IsTrial = (bool)reader["IsTrial"]
};
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取所有兴趣类型列表
/// </summary>
/// <returns></returns>
public static IEnumerable <InterestTypeInfo> GetInterestTypes()
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Support_GetInterestTypeList", CommandType.StoredProcedure))
{
List <InterestTypeInfo> resultData = new List <InterestTypeInfo>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new InterestTypeInfo
{
Id = (int)reader["Id"],
Name = (string)reader["Name"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取指定用户回复过的帖子信息分页列表
/// </summary>
/// <param name="userId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static IEnumerable <TopicInfo> GetUserRepliedTopicList(int userId, int pageIndex, int pageSize)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetUserRepliedTopicList", CommandType.StoredProcedure))
{
cmd.AddInputParameters("UserId, PageSize, PageIndex", userId, pageSize, pageIndex);
List <TopicInfo> resultData = new List <TopicInfo>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new TopicInfo
{
TopicId = (int)reader["TopicId"],
UserId = (int)reader["UserId"],
AttachContent = (string)reader["AttachContent"],
Title = (string)reader["Title"],
Intro = (string)reader["Intro"],
Icon = (string)reader["Icon"],
Voice = (string)reader["Voice"],
Remark = (string)reader["Remark"],
Reward = (int)reader["Reward"],
IsQuestion = (bool)reader["IsQuestion"],
IsAllowReply = (bool)reader["IsAllowReply"],
IsStick = (bool)reader["IsStick"],
IsRefined = (bool)reader["IsRefined"],
AttrChangedMark = (string)reader["AttrChangedMark"],
ExpChanged = (int)reader["ExpChanged"],
VirtualCoinChanged = (int)reader["VirtualCoinChanged"],
BestAnswerId = (int)reader["BestAnswerId"],
FavouredCount = (int)reader["FavouredCount"],
RepliedCount = (int)reader["RepliedCount"],
ViewCount = (int)reader["ViewCount"],
FavoritedCount = (int)reader["FavoritedCount"],
CreateDate = (DateTime)reader["CreateDate"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 查询符合条件的群组信息分页列表
/// </summary>
/// <param name="keyword"></param>
/// <param name="interest"></param>
/// <param name="onlyLess"></param>
/// <param name="userId"></param>
/// <param name="site"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static IEnumerable <int> GroupFindList(string keyword, string interest, bool onlyLess, int userId, int site, int pageIndex, int pageSize)
{
Tuple <string, string, List <string>, List <object> > sqlPart = CreateGroupFindSqlPart(keyword, interest, onlyLess, site);
string querySql = @"SET ROWCOUNT @PageSize
SELECT *
FROM ( SELECT G.GroupId ,
ROW_NUMBER() OVER ( ORDER BY G.CreateDate DESC, G..GroupId DESC ) AS RowNum
FROM Chat_Groups G
{0}
WHERE NOT EXISTS( SELECT 1 FROM Chat_GroupMembers WHERE UserId = @UserId AND GroupId = G.GroupId )
{1}
) AS List
WHERE List.RowNum > ( @PageIndex - 1 ) * @PageSize
ORDER BY List.CreateDate DESC ,
List.GroupId DESC";
querySql = string.Format(querySql, sqlPart.Item1, sqlPart.Item2);
sqlPart.Item3.AddRange(new List <string>(2)
{
"PageSize", "PageIndex", "UserId"
});
sqlPart.Item4.AddRange(new List <object>(2)
{
pageSize, pageIndex, userId
});
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, querySql, CommandType.Text))
{
cmd.AddInputParameters(string.Join(",", sqlPart.Item3), sqlPart.Item4.ToArray());
List <int> resultData = new List <int>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add((int)reader["GroupId"]);
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取论坛版块新帖数量列表
/// </summary>
/// <param name="userId"></param>
/// <param name="deviceId"></param>
/// <param name="schoolId"></param>
/// <returns></returns>
public static IEnumerable <ForumTopicCount> GetForumsNewTopicCountList(int userId, int deviceId, int schoolId)
{
using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_GetForumTopicCountUpdated", CommandType.StoredProcedure))
{
cmd.AddInputParameters("UserId, DeviceId, SchoolId, Date", userId, deviceId, schoolId, DateTime.Now);
List <ForumTopicCount> resultData = new List <ForumTopicCount>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new ForumTopicCount
{
ForumId = (int)reader["ForumId"],
TopicCount = Convert.ToInt32(reader["TopicCount"])
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取指定的帖子详情
/// </summary>
/// <param name="topicId"></param>
/// <param name="updateTopic">是否同步更新帖子浏览次数和未读跟帖信息</param>
/// <returns></returns>
public static TopicInfo GetTopicInfoById(int topicId, bool updateTopic)
{
using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_GetTopicById", CommandType.StoredProcedure))
{
cmd.AddInputParameters("TopicId, UpdateTopic", topicId, updateTopic);
TopicInfo resultData = null;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
resultData = new TopicInfo
{
TopicId = (int)reader["TopicId"],
UserId = (int)reader["UserId"],
ForumId = (int)reader["ForumId"],
AttachContent = (string)reader["AttachContent"],
Title = (string)reader["Title"],
Remark = (string)reader["Remark"],
Reward = (int)reader["Reward"],
IsQuestion = (bool)reader["IsQuestion"],
IsAllowReply = (bool)reader["IsAllowReply"],
IsStick = (bool)reader["IsStick"],
IsRefined = (bool)reader["IsRefined"],
AttrChangedMark = (string)reader["AttrChangedMark"],
ExpChanged = (int)reader["ExpChanged"],
VirtualCoinChanged = (int)reader["VirtualCoinChanged"],
BestAnswerId = (int)reader["BestAnswerId"],
FavouredCount = (int)reader["FavouredCount"],
RepliedCount = (int)reader["RepliedCount"],
ViewCount = (int)reader["ViewCount"],
FavoritedCount = (int)reader["FavoritedCount"],
CreateDate = (DateTime)reader["CreateDate"],
Content = (string)reader["Content"]
};
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取用户之间单向“关注”关系数据列表
/// </summary>
/// <param name="userId">指定的用户</param>
/// <param name="action">查询类型：0-用户关注的，1-关注用户的</param>
/// <returns>用户之间单向“关注”关系数据列表</returns>
public static IEnumerable <UserFollowed> GetUsersFollowedList(int userId, byte action)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Social_GetUsersFollowedList", CommandType.StoredProcedure))
{
cmd.AddInputParameters("UserId, Action", userId, action);
List <UserFollowed> resultData = new List <UserFollowed>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new UserFollowed
{
UserId = (int)reader["UserId"],
FollowedUserId = (int)reader["FollowedUserId"],
CreateDate = (DateTime)reader["CreateDate"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取指定用户在指定的天数内的签到记录列表
/// </summary>
/// <param name="userId"></param>
/// <param name="days"></param>
/// <returns></returns>
public static IEnumerable <UserCheckedInLog> GetUserCheckedInLogInDays(int userId, int days)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Users_GetUserCheckedInLogs", CommandType.StoredProcedure))
{
cmd.AddInputParameters("UserId, Days, Date", userId, days, DateTime.Now);
List <UserCheckedInLog> resultData = new List <UserCheckedInLog>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new UserCheckedInLog
{
UserId = (int)reader["UserId"],
AppChannel = (string)reader["AppChannel"],
CreateDate = (DateTime)reader["CreateDate"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取指定群组成员列表
/// </summary>
/// <param name="groupId">群组编号</param>
/// <returns>群组成员列表</returns>
public static IEnumerable <GroupMember> GetGroupMemebers(int groupId)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Social_GetGroupMembers", CommandType.StoredProcedure))
{
cmd.AddInputParameters("GroupId", groupId);
List <GroupMember> resultData = new List <GroupMember>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new GroupMember
{
GroupId = (int)reader["GroupId"],
UserId = (int)reader["UserId"],
IsCreator = (bool)reader["IsCreator"],
CreateDate = (DateTime)reader["CreateDate"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取下一个版本信息
/// </summary>
/// <param name="channelCode">渠道标识码</param>
/// <param name="currentVerName">当前版本号</param>
/// <returns>下一个版本信息</returns>
public static AppVersionInfo GetNextVersion(string channelCode, string currentVerName)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Support_GetNextVersion", CommandType.StoredProcedure))
{
cmd.AddInputParameters("ChannelCode, VersionName", channelCode, currentVerName);
AppVersionInfo nextVer = null;
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
nextVer = new AppVersionInfo
{
VersionName = (string)reader["VersionName"],
VersionComment = (string)reader["VersionComment"],
IsRequired = (bool)reader["IsRequired"],
UpgradeSource = (int)reader["UpgradeSource"],
UpgradePath = (string)reader["VersionName"]
};
}
reader.Close();
}
return(nextVer);
}
}
/// <summary>
/// 获取符合条件的前N条广告信息
/// </summary>
/// <param name="appChannel">应用渠道标识码</param>
/// <param name="schoolId">学校编号</param>
/// <param name="topCount">获取的最大广告数量</param>
/// <returns>符合条件的前N条广告信息</returns>
public static IEnumerable <AdvertDetail> GetTopAdverts(string appChannel, int schoolId, int topCount)
{
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Extend_GetTopAdverts", CommandType.StoredProcedure))
{
cmd.AddInputParameters("SchoolId, Size, ChannelCode, Date", schoolId, topCount, appChannel, DateTime.Now);
List <AdvertDetail> resultData = new List <AdvertDetail>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new AdvertDetail
{
AdvertId = (int)reader["AdvertId"],
AdName = (string)reader["AdName"],
SmallerIcon = (string)reader["SmallerIcon"],
AdUrl = (string)reader["AdUrl"],
AdType = (int)reader["AdType"]
});
}
reader.Close();
}
return(resultData);
}
}
/// <summary>
/// 获取符合查询条件的帖子信息列表
/// </summary>
/// <param name="condition"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static IEnumerable <TopicInfo> GetTopicInfoList(TopicQueryConditions condition, int pageIndex, int pageSize)
{
Tuple <string, string, List <string>, List <object> > tuples = CreateSqlPartionByCondition(condition);
string querySql = @"SET ROWCOUNT @PageSize
SELECT *
FROM ( SELECT TopicId ,
UserId ,
AttachContent ,
Title ,
Intro ,
Icon ,
Voice ,
Remark ,
Reward ,
IsQuestion ,
IsAllowReply ,
IsStick ,
IsRefined ,
AttrChangedMark ,
ExpChanged ,
VirtualCoinChanged ,
BestAnswerId ,
FavouredCount ,
RepliedCount ,
ViewCount ,
FavoritedCount ,
CreateDate ,
ROW_NUMBER() OVER ( {1} ) AS RowNum
FROM Forum_Topics
WHERE {0}
) AS List
WHERE List.RowNum > ( @PageIndex - 1 ) * @PageSize
{1}";
querySql = string.Format(querySql, tuples.Item1, tuples.Item2);
tuples.Item3.AddRange(new List <string>(2)
{
"PageSize", "PageIndex"
});
tuples.Item4.AddRange(new List <object>(2)
{
pageSize, pageIndex
});
using (DbCommander cmd = new DbCommander(DbConn.ReadDb, querySql))
{
cmd.AddInputParameters(string.Join(",", tuples.Item3), tuples.Item4.ToArray());
List <TopicInfo> resultData = new List <TopicInfo>(0);
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
resultData.Add(new TopicInfo
{
TopicId = (int)reader["TopicId"],
UserId = (int)reader["UserId"],
AttachContent = (string)reader["AttachContent"],
Title = (string)reader["Title"],
Intro = (string)reader["Intro"],
Icon = (string)reader["Icon"],
Voice = (string)reader["Voice"],
Remark = (string)reader["Remark"],
Reward = (int)reader["Reward"],
IsQuestion = (bool)reader["IsQuestion"],
IsAllowReply = (bool)reader["IsAllowReply"],
IsStick = (bool)reader["IsStick"],
IsRefined = (bool)reader["IsRefined"],
AttrChangedMark = (string)reader["AttrChangedMark"],
ExpChanged = (int)reader["ExpChanged"],
VirtualCoinChanged = (int)reader["VirtualCoinChanged"],
BestAnswerId = (int)reader["BestAnswerId"],
FavouredCount = (int)reader["FavouredCount"],
RepliedCount = (int)reader["RepliedCount"],
ViewCount = (int)reader["ViewCount"],
FavoritedCount = (int)reader["FavoritedCount"],
CreateDate = (DateTime)reader["CreateDate"]
});
}
reader.Close();
}
return(resultData);
}
}
