/// <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="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="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="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="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="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> /// <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="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="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="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="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="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="topicId"></param> /// <returns></returns> public static int GetTopicReplyCount(int topicId) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetTopicPostCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("TopicId", topicId); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 检测用户名是否已存在 /// </summary> /// <param name="userName">待检测的用户名</param> /// <param name="userAccess">用户对应的登录权限</param> /// <returns>用户名是否已存在</returns> public static bool UserNameIsExists(string userName, UserAccess userAccess) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Users_UserNameCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserName, UserAccess", userName, userAccess); return(Convert.ToInt32(cmd.ExecuteScalar()) > 0); } }
/// <summary> /// 用户对指定的目标数据“点赞” /// </summary> /// <param name="userId">用户编号</param> /// <param name="targetId">目标数据编号</param> /// <param name="t">类型:1-帖子,2-回复</param> public static void CreateUserFavoured(int userId, int targetId, byte t) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_CreateUserFavoured", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, DataId, T, Date", userId, targetId, t, DateTime.Now); cmd.ExecuteNonQuery(); } }
/// <summary> /// 更新跟帖内容 /// </summary> /// <param name="postInfo"></param> public static void UpdatePostContent(PostFullInfo postInfo) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_UpdateReplyContent", CommandType.StoredProcedure)) { cmd.AddInputParameters("PostId, Content", postInfo.PostId, postInfo.Content); cmd.ExecuteNonQuery(); } }
/// <summary> /// 更新帖子浏览次数 /// </summary> /// <param name="topicId"></param> public static void UpdateTopicViewCount(int topicId) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_AddTopicViewCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("TopicId", topicId); cmd.ExecuteNonQuery(); } }
/// <summary> /// 查询指定用户对指定的目标数据“点赞”次数 /// </summary> /// <param name="userId">用户编号</param> /// <param name="targetId">目标数据编号</param> /// <param name="t">类型:1-帖子,2-回复</param> /// <returns></returns> public static int UserFavouredCount(int userId, int targetId, byte t) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetUserFavouredCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, TargetId, T", userId, targetId, t); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 更改学校试用用户账号状态信息 /// </summary> /// <param name="userWithSchool">用户相关信息</param> /// <param name="action">更改状态:0-激活,1-过期</param> public static void SchoolTrialUserStatusChanged(UserWithSchool userWithSchool, int action = 0) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_SchoolTrialUserStatusChanged", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, IsActivated, ActivatedDate, IsExpired, ExpiredDate, Action", userWithSchool.UserId, userWithSchool.IsActivated, userWithSchool.ActivatedDate, userWithSchool.IsExpired, userWithSchool.ExpiredDate, action); cmd.ExecuteNonQuery(); } }
/// <summary> /// 更新设备授权码 /// </summary> /// <param name="device"></param> public static void SetDeviceToken(UserDevice device) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Support_DeviceSetToken", CommandType.StoredProcedure)) { cmd.AddInputParameters("DeviceId, Token", device.DeviceId, device.Token); cmd.ExecuteNonQuery(); } }
/// <summary> /// 更新用户登录成功相关信息 /// </summary> /// <param name="uExtInfo"></param> public static void UpdateUserWhenSignIn(UserExtInfo uExtInfo) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UpdateUserWhenSignIn", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, LastLoginDeviceId, LastLoginAPPChannel, LastLoginAccountChannel, LastLoginDate", uExtInfo.UserId, uExtInfo.LastLoginDeviceId, uExtInfo.LastLoginAPPChannel, uExtInfo.LastLoginAccountChannel, DateTime.Now); cmd.ExecuteNonQuery(); } }
/// <summary> /// 更新帖子内容等 /// </summary> /// <param name="topic"></param> public static void UpdateTopicContent(TopicFullInfo topic) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Bbs_UpdateTopicContent", CommandType.StoredProcedure)) { cmd.AddInputParameters("TopicId, Content, Icon, Voice", topic.TopicId, topic.Content, topic.Icon, topic.Voice); cmd.ExecuteNonQuery(); } }
/// <summary> /// 用户虚拟币账户余额变更 /// </summary> /// <param name="userId">目标用户编号</param> /// <param name="coinChanged">虚拟币账户余额变化数量</param> public static void UserCoinChanged(int userId, int coinChanged) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UserVirtualCoinChanged", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, CoinChanged", userId, coinChanged); cmd.ExecuteNonQuery(); } }
/// <summary> /// 更新用户头像、背景图片信息 /// </summary> /// <param name="userId">用户编号</param> /// <param name="userIcon">用户图标路径</param> /// <param name="iconType">更新类型:0-头像, 1-个人信息背景图片</param> public static void UpdateUserIcon(int userId, string userIcon, byte iconType = 0) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UpdateUserIcon", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, IconUrl, Datetime, T", userId, userIcon, DateTime.Now, iconType); cmd.ExecuteNonQuery(); } }
/// <summary> /// 获取指定用户,指定日期内签到次数 /// </summary> /// <param name="log"></param> /// <returns></returns> public static int UserCheckedInCountWithDay(UserCheckedInLog log) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Users_GetUserCheckedInCountWithDay", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, Date", log.UserId, log.CreateDate); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 将指定的用户设定为非锁定状态 /// </summary> /// <param name="userId"></param> public static void SetUsesUnLocked(int userId) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UnLockUserAccount", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId", userId); cmd.ExecuteNonQuery(); } }
/// <summary> /// 获取指定用户回复过的帖子总数 /// </summary> /// <param name="userId"></param> /// <returns></returns> public static int GetUserRepliedTopicCount(int userId) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_Bbs_GetUserRepliedTopicCount", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId", userId); return(Convert.ToInt32(cmd.ExecuteScalar())); } }
/// <summary> /// 获取指定学校、指定角色类型的用户编号列表 /// </summary> /// <param name="schoolId"></param> /// <param name="userRole"></param> /// <returns></returns> public static IEnumerable <int> GetSchoolUserIds(int schoolId, int userRole) { using (DbCommander cmd = new DbCommander(DbConn.ReadDb, "SP_School_GetCsUsers", CommandType.StoredProcedure)) { cmd.AddInputParameters("SchoolId, UserRole", schoolId, userRole); return(cmd.ExecuteDataTable().AsEnumerable().Select(dr => dr.Field <int>("UserId"))); } }
/// <summary> /// 指定用户,指定日期内进行签到。返回签到状态:0-重复签到,1-签到成功 /// </summary> /// <param name="log"></param> /// <returns>返回签到状态:0-重复签到,1-签到成功</returns> public static int UserCheckInToday(UserCheckedInLog log) { using (DbCommander cmd = new DbCommander(DbConn.WriteDb, "SP_Users_UserCheckedIn", CommandType.StoredProcedure)) { cmd.AddInputParameters("UserId, AppChannel, Date", log.UserId, log.AppChannel, log.CreateDate); return(Convert.ToInt32(cmd.ExecuteScalar())); } }