public List <TestPaperReportInfo> ReadTheFirstRecordList(string companyID) { List <TestPaperReportInfo> tempList = new List <TestPaperReportInfo>(); if (!string.IsNullOrEmpty(companyID)) { StringBuilder sql = new StringBuilder(); sql.Append("select * from ("); sql.Append("select [UserID],[CateId],[PaperName],[Scorse],[TestDate],[IsPass],row_number() over(partition by [UserID] order by [TestDate]) as rownumber from [" + ShopMssqlHelper.TablePrefix + "TestPaper] where [companyID] in (" + companyID + ")"); sql.Append(") as t where t.rownumber <=1"); using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString())) { while (dr.Read()) { TestPaperReportInfo latestTestPaper = new TestPaperReportInfo(); latestTestPaper.UserID = int.Parse(dr["UserId"].ToString()); latestTestPaper.CourseID = int.Parse(dr["CateId"].ToString()); latestTestPaper.CourseName = dr["PaperName"].ToString(); latestTestPaper.Score = decimal.Parse(dr["Scorse"].ToString()); latestTestPaper.TestDate = Convert.ToDateTime(dr["TestDate"]); latestTestPaper.IsPass = int.Parse(dr["IsPass"].ToString()); tempList.Add(latestTestPaper); } } } return(tempList); }
public List <TestPaperInfo> NewReadList(TestPaperInfo Model) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "TestPaper] "); List <TestPaperInfo> TempList = new List <TestPaperInfo>(); MssqlCondition mssqlCondition = new MssqlCondition(); this.PrepareCondition(mssqlCondition, Model); if (mssqlCondition.ToString() != string.Empty) { sql.Append("where " + mssqlCondition.ToString()); } if (Model.OrderByCondition == string.Empty) { sql.Append(" Order by TestPaperId desc"); } else { sql.Append(" Order by " + Model.OrderByCondition); } using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(dr, TempList); } return(TempList); }
public List <PostPassInfo> ReadPostPassList(PostPassInfo Model, int num) { string sqlNumString = "*"; if (num > 0) { sqlNumString = "top " + num + " " + sqlNumString; } StringBuilder sql = new StringBuilder(); sql.Append("select " + sqlNumString + " from [" + ShopMssqlHelper.TablePrefix + "PassPost] "); List <PostPassInfo> TempList = new List <PostPassInfo>(); MssqlCondition mssqlCondition = new MssqlCondition(); this.PrepareCondition(mssqlCondition, Model); if (mssqlCondition.ToString() != string.Empty) { sql.Append("where " + mssqlCondition.ToString()); sql.Append(" Order By CreateDate desc"); using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(dr, TempList); } } return(TempList); }
public List <ReportPostPassInfo> PostPassRepostList(PostPassInfo Model, string CompanyId) { if (Model.SearchStartDate == DateTime.MinValue) { Model.SearchStartDate = Convert.ToDateTime("2013-1-1"); } List <ReportPostPassInfo> TempList = new List <ReportPostPassInfo>(); StringBuilder sql = new StringBuilder(); sql.Append("select [_PassPost].UserId as UserId,[_PassPost].[PostName] as [PassPostName],WorkingPostName,[_User].WorkingPostID as WorkPostId,[_PassPost].PostId as PostId,[_User].RealName as RealName,(select count(distinct PostId) from [" + ShopMssqlHelper.TablePrefix + "PassPost] Where UserId=[_User].ID and IsRZ=1) as PostNum,[_User].StudyPostId as StudyPostId from [" + ShopMssqlHelper.TablePrefix + "PassPost],[" + ShopMssqlHelper.TablePrefix + "User] "); sql.Append("where [_PassPost].UserId=[_User].ID and [_User].CompanyID in (" + CompanyId + ") and ([_PassPost].CreateDate between '" + Model.SearchStartDate.ToString() + "' and '" + Model.CreateDate.ToString() + "') and [IsRZ]=1"); sql.Append(" Order By [_PassPost].id desc"); using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString())) { while (dr.Read()) { ReportPostPassInfo ReportModel = new ReportPostPassInfo(); { ReportModel.UserId = int.Parse(dr["UserId"].ToString()); ReportModel.PassPostId = dr["PostId"].ToString(); ReportModel.PassPostName = dr["PassPostName"].ToString(); ReportModel.PassPostNum = int.Parse(dr["PostNum"].ToString()); ReportModel.RealName = dr["RealName"].ToString(); ReportModel.WorkingPostName = dr["WorkingPostName"].ToString(); ReportModel.WorkingPostId = int.Parse(dr["WorkPostId"].ToString()); ReportModel.StudyPostId = int.Parse(dr["StudyPostId"].ToString()); } TempList.Add(ReportModel); } } return(TempList); }
public CompanyInfo ReadCompany(int id) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Company] where [CompanyId]=" + id); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { return(GetModel(reader)); } }
//读取信息 public TestSettingInfo ReadTestSetting(int companyId) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "TestSetting] where CompanyId=" + companyId); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { return(GetModel(reader)); } }
//读取信息 public PostPassInfo ReadPostPassInfo(int userID, int postID) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "PassPost] where [UserID]=" + userID + " And [PostID]=" + postID); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { return(this.GetModel(reader)); } }
//读取指定信息 public CourseInfo ReadCourse(int Id) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Course] where CourseId=" + Id); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { return(GetModel(reader)); } }
//读取信息 public TestPaperInfo ReadTheOldTestPaperInfo(int UserId) { StringBuilder sql = new StringBuilder(); sql.Append("select top 1 * from [" + ShopMssqlHelper.TablePrefix + "TestPaper] where UserId=" + UserId + " Order by TestDate"); using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString())) { return(GetModel(dr)); } }
public PostPassInfo ReadPostPassInfo() { StringBuilder sql = new StringBuilder(); sql.Append("select top 1 * from [" + ShopMssqlHelper.TablePrefix + "PassPost] where [IsRZ]=1 Order by id desc"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { return(this.GetModel(reader)); } }
/// <summary> /// 读取所有实体的列表 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> //public List<T> ReadList<T>() where T : BaseModel public List <T> ReadList() { //Type type = typeof(T); string sqlText = string.Format("select {0} from [{1}{2}]", this.GetFieldString(type), ShopMssqlHelper.TablePrefix, tableName); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sqlText)) { return(this.PrepareList(reader)); } }
//读取信息 public TestPaperInfo ReadPaper(TestPaperInfo Model) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "TestPaper] where TestPaperId=@TestPaperId"); SqlParameter[] par = (SqlParameter[])this.ValueParas(Model); using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString(), par)) { return(GetModel(dr)); } }
public List <RenZhengCateInfo> ReadRenZhengCateList() { List <RenZhengCateInfo> RenZhengCateList = new List <RenZhengCateInfo>(); StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "RenZhengCate] Where Del=0 Order by [Order]"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(reader, RenZhengCateList); } return(RenZhengCateList); }
public List <CourseInfo> ReadList() { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Course]"); List <CourseInfo> TempList = new List <CourseInfo>(); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(reader, TempList); } return(TempList); }
public List <QuestionInfo> ReadList(int CateId) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Question] Where [CateId]=" + CateId.ToString()); List <QuestionInfo> TempList = new List <QuestionInfo>(); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(reader, TempList); } return(TempList); }
public List <PostInfo> ReadPostCateAllList() { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Post] "); sql.Append(" Order by [CompanyID],[ParentId],[OrderIndex],[PostName],[PostId] desc"); List <PostInfo> List = new List <PostInfo>(); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(reader, List); } return(List); }
public SMSRecordInfo ReadSMSRecord(string Mobile, string Code) { StringBuilder sql = new StringBuilder(); sql.Append("select top 1 * from [" + ShopMssqlHelper.TablePrefix + "SMSRecord] where [Mobile]='" + Mobile + "'"); if (!string.IsNullOrEmpty(Code)) { sql.Append(" and [VerCode]='" + Code + "'"); } sql.Append(" order by [Id] desc"); using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString())) { return(GetModel(dr)); } }
public List <string> ReadProductClassListByProductID(string productID, string inBrandID) { List <string> classList = new List <string>(); StringBuilder sql = new StringBuilder(); sql.Append("SELECT [ClassID] FROM [" + ShopMssqlHelper.TablePrefix + "Product] where [ID] in (" + productID + ") And [IsSale]=1 And [dbo].[" + ShopMssqlHelper.TablePrefix + "CompareBrand]([BrandID],'" + inBrandID + "',',') = 1 Group by [ClassID] Order by [ClassID] Desc"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { while (reader.Read()) { classList.Add(reader["ClassID"].ToString()); } } return(classList); }
public List <CompanyInfo> ReadCompanyListByParentID(string parentID) { List <CompanyInfo> CompanyList = new List <CompanyInfo>(); if (!string.IsNullOrEmpty(parentID) && parentID != ",") { StringBuilder sql = new StringBuilder(); parentID = parentID.Replace(" ", "").Replace(",,", ","); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Company] "); sql.Append("where [dbo].[" + ShopMssqlHelper.TablePrefix + "CompareSTR](ParentID,'" + parentID + "',',')=1 And State=0 Order By Sort,CompanyID Desc"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(reader, CompanyList); } } return(CompanyList); }
/// <summary> /// 读取实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="id"></param> /// <returns></returns> //public T Read<T>(int id) where T : BaseModel public T Read(int id) { //Type type = typeof(T); string sqlText = string.Format("select {0} from [{1}{2}] where [ID]=@id", this.GetFieldString(type), ShopMssqlHelper.TablePrefix, tableName); SqlParameter[] pt = { new SqlParameter("@id", SqlDbType.Int, 4) }; pt[0].Value = id; using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sqlText, pt)) { return(this.PrepareModel(reader, type)); } }
public List <CompanyInfo> ReadCompanyListByCompanyID(string companyID) { List <CompanyInfo> CompanyList = new List <CompanyInfo>(); if (!string.IsNullOrEmpty(companyID) && companyID != ",") { StringBuilder sql = new StringBuilder(); companyID = companyID.Replace(" ", "").Replace(",,", ","); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Company] "); sql.Append("where CompanyID in (" + companyID + ") Order By Sort,CompanyID Desc"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(reader, CompanyList); } } return(CompanyList); }
public List <TestPaperInfo> ReadList(TestPaperInfo Model) { StringBuilder sql = new StringBuilder(); sql.Append("select count(*) as TestNum,sum(point) as scorse"); if (Model.GroupCondition != "") { sql.Append("," + Model.GroupCondition); } sql.Append(" from [" + ShopMssqlHelper.TablePrefix + "TestPaper] "); List <TestPaperInfo> TempList = new List <TestPaperInfo>(); MssqlCondition mssqlCondition = new MssqlCondition(); this.PrepareCondition(mssqlCondition, Model); if (mssqlCondition.ToString() != string.Empty) { sql.Append("where " + mssqlCondition.ToString()); } if (Model.GroupCondition != "") { sql.Append(" Group by " + Model.GroupCondition); } if (Model.GroupCondition == "cateid") { sql.Append(" Order by Cateid "); } using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString())) { //this.PrepareModel(reader, TempList); while (dr.Read()) { TestPaperInfo Model1 = new TestPaperInfo(); { Model1.TestNum = dr.GetInt32(0); Model1.Point = Convert.ToDecimal(dr[1].ToString()); Model1.UserId = dr.GetInt32(2); Model1.CateId = dr.GetInt32(2); } TempList.Add(Model1); } } return(TempList); }
public int ReadQuestionNum(string courseID) { int questionsNum = 0; if (!string.IsNullOrEmpty(courseID)) { StringBuilder sql = new StringBuilder(); sql.Append("select Count(QuestionId) as [QuestionsNum] from [" + ShopMssqlHelper.TablePrefix + "Question] where [CateId] in (" + courseID + ")"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { if (reader.Read()) { return(reader.GetInt32(0)); } } } return(questionsNum); }
public Dictionary <int, float> ReadTheBestList(int userID, string courseID) { Dictionary <int, float> resultData = new Dictionary <int, float>(); if (!string.IsNullOrEmpty(courseID)) { StringBuilder sql = new StringBuilder(); sql.Append("select * from ("); sql.Append("select [CateId],[Scorse],row_number() over(partition by [CateId] order by [Scorse] desc) as rownumber from [" + ShopMssqlHelper.TablePrefix + "TestPaper] where [Del]=0 and [CateId] in (" + courseID + ") and [UserID]=" + userID); sql.Append(") as t where t.rownumber <=1"); using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString())) { while (dr.Read()) { resultData.Add(int.Parse(dr["CateId"].ToString()), float.Parse(dr["Scorse"].ToString())); } } } return(resultData); }
/// <summary> /// 读取所有实体的列表 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> //public List<T> ReadList<T>() where T : BaseModel public List <T> ReadList <S>(S s) { //Type type = typeof(T); string sqlText = string.Format("select {0} from [{1}{2}]", this.GetFieldString(type), ShopMssqlHelper.TablePrefix, tableName); MssqlCondition mssqlCondition = new MssqlCondition(); this.PrepareCondition(mssqlCondition, s); if (!string.IsNullOrEmpty(mssqlCondition.ToString())) { sqlText = string.Format("{0} where {1}", sqlText, mssqlCondition.ToString()); } using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sqlText)) { return(this.PrepareList(reader)); } }
public List <UserInfo> SearchReportUserList(UserSearchInfo userSearch) { List <UserInfo> userList = new List <UserInfo>(); StringBuilder sql = new StringBuilder(); sql.Append("select [ID],[UserName],[UserPassword],[Email],[Sex],[Introduce],[Photo],[MSN],[QQ],[Tel],[Mobile],[RegionID],[Address],[Birthday],[RegisterIP],[RegisterDate],[LastLoginIP],[LastLoginDate],[LoginTimes],[SafeCode],[FindDate],[Status],[OpenID],[GroupID],[RealName],[CompanyID],[WorkingPostID],[StudyPostID],[WorkingPostName],[Department],[IDCard],[PostStartDate],[EntryDate] from [" + ShopMssqlHelper.TablePrefix + "User] "); MssqlCondition mssqlCondition = new MssqlCondition(); this.PrepareCondition(mssqlCondition, userSearch); if (mssqlCondition.ToString() != string.Empty) { sql.Append("where " + mssqlCondition.ToString()); sql.Append(" Order by [CompanyID] Desc, [Department],[WorkingPostID],[WorkingPostName],[StudyPostID],[RegisterDate]"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareUserModel(reader, userList); } } return(userList); }
public List <CourseCateInfo> ReadCourseCateAllList(CourseCateInfo Model) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "CourseCate] "); List <CourseCateInfo> CourseCateList = new List <CourseCateInfo>(); MssqlCondition mssqlCondition = new MssqlCondition(); this.PrepareCondition(mssqlCondition, Model); if (mssqlCondition.ToString() != string.Empty) { sql.Append("where " + mssqlCondition.ToString()); sql.Append(" Order by [OrderIndex] ASC,CateId ASC"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareCourseCateModel(reader, CourseCateList); } } return(CourseCateList); }
public List <CompanyPostPlanInfo> CompanyPostPlanList(CompanyPostPlanInfo Model) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "CompanyPostPlan] "); List <CompanyPostPlanInfo> CompanyPostPlanList = new List <CompanyPostPlanInfo>(); MssqlCondition mssqlCondition = new MssqlCondition(); this.PrepareCondition(mssqlCondition, Model); if (mssqlCondition.ToString() != string.Empty) { sql.Append("where " + mssqlCondition.ToString()); sql.Append(" Order by StartDate"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(reader, CompanyPostPlanList); } } return(CompanyPostPlanList); }
public List <QuestionInfo> ReadList(QuestionInfo Model) { StringBuilder sql = new StringBuilder(); sql.Append("select " + Model.QuestionNum + " from [" + ShopMssqlHelper.TablePrefix + "Question] "); List <QuestionInfo> TempList = new List <QuestionInfo>(); MssqlCondition mssqlCondition = new MssqlCondition(); this.PrepareCondition(mssqlCondition, Model); if (mssqlCondition.ToString() != string.Empty) { sql.Append("where " + mssqlCondition.ToString()); } sql.Append(" Order by newid()"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(reader, TempList); } return(TempList); }
public List <TestSettingInfo> ReadList(TestSettingInfo Model) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "TestSetting] "); List <TestSettingInfo> TempList = new List <TestSettingInfo>(); MssqlCondition mssqlCondition = new MssqlCondition(); this.PrepareCondition(mssqlCondition, Model); if (mssqlCondition.ToString() != string.Empty) { sql.Append("where " + mssqlCondition.ToString()); sql.Append(" Order by [Id] desc"); using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString())) { this.PrepareModel(reader, TempList); } } return(TempList); }