public bool Delete(Guid feeTypeId, string modifiedBy, ref bool dbError) { try { using (var connection = GetConnection()) { var update = @"UPDATE FeeType SET lastmodifiedby = @modifiedBy , isdeleted = now() , islive = null WHERE feeTypeId = @feeTypeId AND IsDeleted IS NULL "; var id = connection.Query <int>(update, new { feeTypeId = feeTypeId, modifiedBy = modifiedBy }).FirstOrDefault(); if (id > 0) { return(true); } return(false); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(false); } }
public bool Delete(Guid levelId, string modifiedby, ref bool dbError) { try { using (var connection = GetConnection()) { var update = @" UPDATE Level SET lastmodifiedby = @modifiedby , isdeleted = now() , islive = null WHERE LevelId = @levelId AND LevelID Not IN (Select LevelID FROM Class WHERE LevelID = @levelId AND IsDeleted IS NULL); "; var id = connection.Query <int>(update, new { levelId = levelId, modifiedby = modifiedby }).FirstOrDefault(); return(true); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(false); } }
public List <TestMarkDto> GetListTestMarksByTestId(Guid testID, ref bool dbError) { try { var sqlTestMark = @"SELECT enrol.StudentID, ex.TestID, ex.studentTermRegisterID, stu.SchoolID, tm.TermID , tm.Year , tm.TermNumber , sub.SubjectCode, sub.SubjectName, stu.RegNumber, CONCAT(stu.StudentName,' ',stu.StudentSurname) As FullName, ex.Mark, ex.Percentage As MarkPercentage , tes.OutOf, tes.TestTitle , tes.TestDateCreated FROM TestMark ex INNER JOIN Test tes on tes.TestID = ex.TestID INNER JOIN TeacherClassSubject tcs on tcs.TeacherClassSubjectID = tes.TeacherClassSubjectID INNER JOIN Term tm on tcs.TermID = tm.TermID INNER JOIN Class cl on tcs.ClassID = cl.ClassID INNER JOIN Subject sub on sub.SubjectID = tcs.SubjectID INNER JOIN StudentTermRegister enrol on enrol.studentTermRegisterID = ex.studentTermRegisterID INNER JOIN Student stu on stu.StudentID = enrol.StudentID Where tes.TestId = @TestID AND ex.ISDELETED IS NULL AND tes.ISDELETED IS NULL AND tcs.ISDELETED IS NULL AND tm.ISDELETED IS NULL AND cl.ISDELETED IS NULL AND sub.ISDELETED IS NULL AND enrol.ISDELETED IS NULL AND stu.ISDELETED IS NULL "; using (var connection = GetConnection()) { var list = connection.Query <TestMarkDto>(sqlTestMark , new { TestID = testID } ).AsList(); return(list); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public bool Save(ClassTeacher objClass, string modifiedBy, ref bool dbError) { try { using (var connection = GetConnection()) { var update = @"UPDATE ClassTeacher SET TeacherID = @TeacherID , LastModifiedBy = @modifiedBy WHERE ClassID = @ClassID AND TermID = @TermID AND IsDeleted Is NULL "; var id = connection.Execute(update, new { ClassID = objClass.ClassID, TermID = objClass.TermID, TeacherID = objClass.TeacherID, modifiedBy = modifiedBy, }); if (id <= 0) { var insert = @" INSERT INTO ClassTeacher ( ClassID , TermID , TeacherID , LastModifiedBy ) VALUES ( @ClassID, @TermID , @TeacherID , @modifiedBy ) "; var idI = connection.Execute(insert, new { ClassID = objClass.ClassID, TermID = objClass.TermID, TeacherID = objClass.TeacherID, modifiedBy = modifiedBy }); } return(true); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(false); } }
public List <ExamDto> GetListByYearAndStudentID(Guid studentID, int year, ref bool dbError) { try { var sqlTestMark = @"SELECT enrol.StudentID, ex.TeacherClassSubjectID, ex.StudentTermRegisterID, stu.SchoolID, tm.TermID , tm.Year , tm.TermNumber , sub.SubjectCode, sub.SubjectName, stu.RegNumber, CONCAT(stu.StudentName,' ',stu.StudentSurname) As FullName, ex.Mark, ex.Grade, ex.Comment, stu.IsMale , tm.endDate AS EndOfTermDate FROM Exam ex INNER JOIN TeacherClassSubject tcs on tcs.TeacherClassSubjectID = ex.TeacherClassSubjectID INNER JOIN Term tm on tcs.TermID = tm.TermID INNER JOIN Class cl on tcs.ClassID = cl.ClassID INNER JOIN Subject sub on sub.SubjectID = tcs.SubjectID INNER JOIN StudentTermRegister enrol on enrol.StudentTermRegisterID = ex.StudentTermRegisterID INNER JOIN Student stu on stu.StudentID = enrol.StudentID Where tm.Year = @year AND stu.StudentID = @studentID AND stu.IsDeleted IS NULL AND cl.IsDeleted IS NULL AND tcs.IsDeleted IS NULL AND ex.IsDeleted IS NULL AND tm.IsDeleted IS NULL AND sub.IsDeleted IS NULL AND enrol.IsDeleted IS NULL ORDER BY tm.Year DESC, tm.TermNumber ASC " ; using (var connection = GetConnection()) { var list = connection.Query <ExamDto>(sqlTestMark, new { studentID = studentID, year = year } ).AsList(); return(list); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public int BulkInsert(List <StudentTermRegister> studentTermRegisters, string modifiedBy, ref bool dbError) { int savedRows = 0; try { using (var connection = GetConnection()) { foreach (var studentTermRegister in studentTermRegisters) { var ids = Guid.NewGuid(); var sql = @" INSERT INTO StudentTermRegister (StudentTermRegisterID ,StudentID ,ClassID ,TermID ,IsPhoneSent ,IsEmailSent ,IsAllowedSent , LastModifiedBy) VALUES ( @id ,@StudentID ,@ClassID ,@TermID ,@IsPhoneSent ,@IsEmailSent ,@IsAllowedSent ,@modifiedBy ) " ; var id = connection.Execute(sql, new { id = Guid.NewGuid(), StudentID = studentTermRegister.StudentID, ClassID = studentTermRegister.ClassID, IsAllowedSent = studentTermRegister.IsAllowedSent, IsEmailSent = studentTermRegister.IsEmailSent, IsPhoneSent = studentTermRegister.IsPhoneSent, TermID = studentTermRegister.TermID, modifiedBy = modifiedBy }); if (id >= 0) { savedRows++; } } } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(savedRows); }
public List <TestDto> GetListTestBySubjectIdAndYear(Guid subjectID, int year, ref bool dbError) { try { var sqlTestMark = @"SELECT te.TestID , te.TeacherClassSubjectID , te.TestTitle , te.OutOf , te.TestDateCreated , ( SELECT Avg(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL ) As Average , ( SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND TestMark.Percentage > 49 AND ISDELETED IS NULL ) As NumberPassed , ( SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND TestMark.Percentage < 50 AND ISDELETED IS NULL ) As NumberFailed , ( SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL ) As TotalWritten FROM Test te Where te.TeacherClassSubjectID IN ( SELECT TeacherClassSubject.TeacherClassSubjectID FROM Term INNER JOIN TeacherClassSubject on TeacherClassSubject.TermID = Term.TermID Where Year = @year And SubjectID = @sid AND TeacherClassSubject.ISDELETED IS NULL AND Term.ISDELETED IS NULL ) AND te.ISDELETED IS NULL "; using (var connection = GetConnection()) { var list = connection.Query <TestDto>(sqlTestMark, new { // te.* , tcs.* , cla.* , sub.* , tea.* sid = subjectID, year = year } ).AsList(); return(list); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public TeacherClassSubjectFile Insert(TeacherClassSubjectFile teacherClassSubjectFile, string modifiedby, ref bool dbError) { try { using (var connection = GetConnection()) { teacherClassSubjectFile.TeacherClassSubjectFileId = Guid.NewGuid(); var insert = @" INSERT INTO TeacherClassSubjectFile (TeacherClassSubjectFileId ,TeacherClassSubjectID ,TeacherClassSubjectFileTypeID ,Title ,Description ,FileSizeInBytes ,Filename ,FullUrl ,LastModifiedBy ) VALUES (@TeacherClassSubjectFileId , @TeacherClassSubjectID , @TeacherClassSubjectFileTypeID , @Title , @Description , @FileSizeInBytes , @Filename , @FullUrl , @modifiedby ) "; var id = connection.Execute(insert, new { TeacherClassSubjectFileId = teacherClassSubjectFile.TeacherClassSubjectFileId, TeacherClassSubjectID = teacherClassSubjectFile.TeacherClassSubjectId, TeacherClassSubjectFileTypeID = teacherClassSubjectFile.TeacherClassSubjectFileTypeId, Title = teacherClassSubjectFile.Title, Description = teacherClassSubjectFile.Description, FileSizeInBytes = teacherClassSubjectFile.FileSizeInBytes, Filename = teacherClassSubjectFile.Filename, FullUrl = teacherClassSubjectFile.FullUrl, modifiedby = modifiedby }); return(teacherClassSubjectFile); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(null); } }
public Grade Save(Grade grade, string modifiedby, ref bool dbError) { try { using (var connection = GetConnection()) { if (grade.GradeID == null || Guid.Empty == grade.GradeID) { grade.GradeID = Guid.NewGuid(); var update = @" INSERT INTO Grade (GradeID ,SchoolID ,Description , lastmodifiedby) VALUES ( @id ,@SchoolID ,@Description , @modifiedby) "; var id = connection.Execute(update, new { id = Guid.NewGuid(), SchoolID = grade.SchoolID, Description = grade.Description, modifiedby = modifiedby }); } else { var update = @" UPDATE Grade SET Description = @description , lastmodifiedby = @modifiedby WHERE GradeID = @GradeID AND IsDeleted IS NULL "; var id = connection.Execute(update, new { description = grade.Description, GradeID = grade.GradeID, modifiedby = modifiedby }); } return(grade); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(null); } }
public Test Save(Test test, string modifiedby, ref bool dbError) { try { if (test.TestID == null || test.TestID == Guid.Empty) { var sql = @" INSERT INTO Test (TestID ,TeacherClassSubjectID ,TestTitle ,OutOf ,TestDateCreated , Lastmodifiedby ) VALUES (@id ,@TeacherClassSubjectID ,@TestTitle ,@OutOf ,@dateCreated ,@modifiedby )"; var id = GetConnection().Execute(sql, new { id = Guid.NewGuid(), TeacherClassSubjectID = test.TeacherClassSubjectID, TestTitle = test.TestTitle, OutOf = test.OutOf, dateCreated = test.TestDateCreated, modifiedby = modifiedby }); } else { var sql = @"UPDATE Test SET TestTitle = @TestTitle ,OutOf = @OutOf , LastModifiedBy = @modifiedby WHERE TestID = @TestID "; var id = GetConnection().Execute(sql, new { TestTitle = test.TestTitle, OutOf = test.OutOf, TestID = test.TestID, modifiedby = modifiedby }); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(null); } return(test); }
public PagedList <TeacherDto> GetPagedSearch(Guid schoolID, int PageSize, int PageNo, string searchValue, ref bool dbError) { try { var columnName = "TeacherFullname"; if (PageNo <= 0) { PageNo = 1; } var results = new PagedList <TeacherDto>(); var sql = string.Format(@"SELECT * FROM Teacher WHERE SchoolID = @schoolID AND ISDELETED IS NULL AND {0} LIKE @searchValue ORDER BY {1} LIMIT @PageSize OFFSET @Offset ; SELECT Count(*) FROM Teacher WHERE SchoolID = @schoolID AND ISDELETED IS NULL AND {2} LIKE @searchValue " , columnName, columnName, columnName); using (var connection = GetConnection()) { using (var multi = connection.QueryMultiple(sql , new { schoolID = schoolID, searchValue = "%" + searchValue + "%", PageSize = PageSize, PageNo = PageNo, OffSet = (PageNo - 1) * PageSize })) { results.PagedData = multi.Read <TeacherDto>().ToList(); results.TotalCount = multi.Read <int>().FirstOrDefault(); results.Page = PageNo; results.Size = PageSize; return(results); } } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public StudentTermRegister Insert(StudentTermRegister studentTermRegister, string modifiedBy, ref bool dbError) { try { var sql = @" INSERT INTO StudentTermRegister (StudentTermRegisterID ,StudentID ,ClassID ,TermID ,IsPhoneSent ,IsEmailSent ,IsAllowedSent , LastModifiedBy ) VALUES ( @id ,@StudentID ,@ClassID ,@TermID ,@IsPhoneSent ,@IsEmailSent ,@IsAllowedSent ,@modifiedBy ) " ; studentTermRegister.StudentTermRegisterID = Guid.NewGuid(); var id = GetConnection().Execute(sql, new { id = studentTermRegister.StudentTermRegisterID, StudentID = studentTermRegister.StudentID, ClassID = studentTermRegister.ClassID, IsAllowedSent = studentTermRegister.IsAllowedSent, IsEmailSent = studentTermRegister.IsEmailSent, IsPhoneSent = studentTermRegister.IsPhoneSent, TermID = studentTermRegister.TermID, modifiedBy = modifiedBy }); if (id <= 0) { return(null); } return(studentTermRegister); } catch (Exception er) { dbError = true; DbLog.Error(er); return(null); } }
public bool Save(StudentTermReview studentTermReview, string modifiedby, ref bool dbError) { try { using (var connection = GetConnection()) { var update = @" INSERT INTO StudentTermReview (StudentTermReviewID ,studentTermRegisterID ,TeacherID ,IsReviewGood ,Body ,CreatedDate ,Star5 , lastmodifiedby ) VALUES ( @id , @studentTermRegisterID , @TeacherID , @IsReviewGood , @Body , @dateToday, @Star5 ,@modifiedby ) "; var id = connection.Execute(update, new { id = Guid.NewGuid(), StudentTermRegisterID = studentTermReview.StudentTermRegisterID, TeacherID = studentTermReview.TeacherID, IsReviewGood = studentTermReview.IsReviewGood, Body = studentTermReview.Body, dateToday = DateTime.Today, Star5 = studentTermReview.Star5, modifiedby = modifiedby }); return(true); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(false); } }
public Term Insert(Term term, string modifiedby, ref bool dbError) { try { using (var connection = GetConnection()) { term.TermID = Guid.NewGuid(); var update = @" INSERT INTO Term ( TermID ,Year ,TermNumber ,SchoolID ,StartDate ,EndDate , LAstModifiedBy ) VALUES ( @id , @Year , @TermNumber , @SchoolID , @FromDate , @ToDate ,@modifiedby ) "; var id = connection.Execute(update, new { id = term.TermID, Year = term.Year, TermNumber = term.TermNumber, FromDate = term.StartDate, ToDate = term.EndDate, SchoolID = term.SchoolID, modifiedby = modifiedby }); return(term); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(null); } }
public List <Student> GetIsExistListStudentInRegNumberOrNationalID(List <string> ids, Guid schoolID, bool isRegNumber, ref bool dbError) { try { if (ids == null) { return(null); } string[] idsString = ids.ToArray(); string sql = @"SELECT st.* FROM Student st INNER JOIN School sc on sc.SchoolID = st.SchoolID Where st.SchoolID = @schoolID And st.IDnational IN @idsString AND st.ISDELETED IS NULL AND sc.ISDELETED IS NULL"; if (isRegNumber) { sql = @"SELECT st.* FROM Student st INNER JOIN School sc on sc.SchoolID = st.SchoolID Where st.SchoolID = @schoolID AND st.ISDELETED IS NULL AND sc.ISDELETED IS NULL And st.RegNumber IN @idsString"; } using (var connection = GetConnection()) { var list = connection.Query <Student>(sql, new { idsString = idsString, schoolID = schoolID } ).AsList(); return(list); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public List <TestDto> GetListDtoTestByTeacherClassSubjectId(Guid TeacherClassSubjectID, ref bool dbError) { try { var sqlTestMark = @"SELECT te.TestID , te.TeacherClassSubjectID , te.TestTitle , te.OutOf , te.TestDateCreated , ( SELECT Avg(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL ) As Average , ( SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL AND TestMark.Percentage > 49 ) As NumberPassed , ( SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL AND TestMark.Percentage < 50 ) As NumberFailed , ( SELECT Count(Percentage) FROM TestMark Where TestMark.TestID = te.TestID AND ISDELETED IS NULL ) As TotalWritten FROM Test te Where te.teacherClassSubjectID = @tcsID AND te.ISDELETED IS NULL"; using (var connection = GetConnection()) { var list = connection.Query <TestDto>(sqlTestMark, new { // te.* , tcs.* , cla.* , sub.* , tea.* tcsID = TeacherClassSubjectID } ).AsList(); return(list); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public bool Save(AbsentFromSchool absent, string modifiedby, ref bool dbError) { try { using (var connection = GetConnection()) { var update = @" INSERT INTO AbsentFromSchool (AbsentFromSchoolID , StudentTermRegisterID ,DayAbsent ,Reason , DateCreated , LastModifiedBy ) VALUES ( @AbsentFromSchoolID , @StudentTermRegisterID , @DayAbsent ,@Reason ,@DateCreated , @modifiedby ) "; var id = connection.Execute(update, new { AbsentFromSchoolID = Guid.NewGuid(), StudentTermRegisterID = absent.StudentTermRegisterID, DayAbsent = absent.DayAbsent, Reason = absent.Reason, DateCreated = DateTime.Now, modifiedby = modifiedby }); return(true); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(false); } }
public Log Save(Log log, ref bool dbError) { try { var id = Guid.NewGuid(); var sql = @" INSERT INTO Log (LogID ,TeacherID ,EntityType ,ActionType ,ActionDetails ,ActionDate ) VALUES (@id ,@TeacherID ,@EntityType ,@ActionType ,@ActionDetails ,@ActionDate ); Select @id "; var ids = GetConnection().Query <Guid>(sql, new { id = id, TeacherID = log.TeacherID, EntityType = log.EntityType, ActionType = log.ActionType, ActionDetails = log.ActionDetails, ActionDate = log.ActionDate }).FirstOrDefault(); } catch (Exception er) { dbError = true; DbLog.Error(er); return(null); } return(log); }
public List <Teacher> GetListTeacherBySchoolId(Guid schoolId, ref bool dbError) { try { var sql = @"SELECT tec.* FROM Teacher tec inner join School sch on sch.SchoolID = tec.SchoolID where tec.SchoolID = @schoolId AND tec.ISDELETED IS NULL AND sch.ISDELETED IS NULL "; var parameters = new DynamicParameters(); parameters.Add("@schoolID", schoolId); return(GetList <Teacher>(sql, parameters)); } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public static Log SaveActivity(Log log, ref bool dbError) { try { var id = Guid.NewGuid(); var sql = @" INSERT INTO Log (LogID ,TeacherID ,EntityType ,ActionType ,ActionDetails ,ActionDate ) VALUES (@id ,@TeacherID ,@EntityType ,@ActionType ,@ActionDetails ,@ActionDate ); "; var ids = GetStaticConnection().Execute(sql, new { id = id, TeacherID = log.TeacherID, EntityType = log.EntityType, ActionType = log.ActionType, ActionDetails = log.ActionDetails, ActionDate = log.ActionDate.AddMinutes(2) }); } catch (Exception er) { dbError = true; DbLog.Error(er); return(null); } return(log); }
public int CountTeachersBySchoolId(Guid schoolId, ref bool dbError) { try { var sql = @"SELECT Count(*) FROM Teacher tec inner join School sch on sch.SchoolID = tec.SchoolID where tec.SchoolID = @schoolId AND sch.ISDELETED IS NULL AND tec.ISDELETED IS NULL "; using (var connection = GetConnection()) { return(connection.ExecuteScalar <int>(sql, new { schoolID = schoolId })); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(0); }
public bool DeleteByID(Guid studentTermRegisterId, string modifiedBy, ref bool dbError) { try { // remove exam , test based on studentTermRegisterId var sql = "UPDATE StudentTermRegister SET isdeleted = now() , islive = null , LASTMODIFIEDBY = @modifiedBy Where StudentTermRegisterID = @StudentTermRegisterID "; var id = GetConnection().Execute(sql, new { StudentTermRegisterID = studentTermRegisterId, modifiedBy = modifiedBy }); return(true); } catch (Exception er) { dbError = true; DbLog.Error(er); return(false); } }
public List <Subject> GetUniqueSubjectsTestsWrittenByStudent(Guid studentId, ref bool dbError) { try { var sqlTestMark = @"SELECT * FROM Subject Where SubjectID IN( SELECT SubjectId FROM testmark INNER JOIN test on testmark.testid = test.testid INNER JOIN teacherclasssubject on teacherclasssubject.teacherclasssubjectId = test.teacherclasssubjectid INNER JOIN StudentTermRegister on StudentTermRegister.StudentTermRegisterID = testmark.StudentTermRegisterID WHERE StudentTermRegister.StudentId = @studentId AND test.IsDeleted IS NULL AND testmark.IsDeleted IS NULL AND Subject.IsDeleted IS NULL AND teacherclasssubject.IsDeleted IS NULL GROUP BY SubjectId ) "; using (var connection = GetConnection()) { var list = connection.Query <Subject>(sqlTestMark , new { studentId = studentId } ).AsList(); return(list); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public Teacher GetTeacherByEmail(string email, ref bool dbError) { try { var sql = @"SELECT tec.* FROM Teacher tec where tec.TeacherEmail = @email AND tec.ISDELETED IS NULL "; using (var connection = GetConnection()) { var record = connection.Query <Teacher>(sql, new { email = email } ).FirstOrDefault(); return(record); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public Term GetByID(Guid termID, ref bool dbFlag) { var sql = @"SELECT * FROM Term WHERE TermID = @termID AND ISDELETED IS NULL"; using (var connection = GetConnection()) { try { var list = connection.Query <Term>(sql , new { termID = termID } ).FirstOrDefault(); return(list); } catch (Exception er) { dbFlag = true; DbLog.Error(er); return(null); } } }
public Student GetStudentById(Guid studentID, ref bool dbError) { try { var sql = @"SELECT st.* FROM Student st INNER JOIN School sc on sc.SchoolID = st.SchoolID AND st.StudentID = @studentID AND st.ISDELETED IS NULL AND sc.ISDELETED IS NULL "; using (var connection = GetConnection()) { var list = connection.Query <Student>(sql, new { studentID = studentID }).FirstOrDefault(); return(list); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public bool Delete(Guid gradeID, string modifiedBy, ref bool dbError) { try { using (var connection = GetConnection()) { var update = @" UPDATE Grade SET lastmodifiedby = @modifiedBy , isdeleted = now() , islive = null WHERE GradeID = @GradeID AND ISDELETED IS NULL AND GRADEID NOT IN (SELECT GRADEID FROM GRADEMARK WHERE GRADEID = @GradeID AND ISDELETED IS NULL ) "; var id = connection.Query <int>(update, new { GradeID = gradeID, modifiedBy = modifiedBy }).FirstOrDefault(); return(true); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(false); } }
public Test GetTestByTestId(Guid testID, ref bool dbError) { try { var sqlTestMark = @"SELECT te.* FROM Test te INNER JOIN TeacherClassSubject tcs on te.TeacherClassSubjectID = tcs.TeacherClassSubjectID inner join Class cla on cla.ClassID = tcs.ClassID inner join Subject sub on sub.SubjectId = tcs.SubjectId inner join Teacher tea on tea.TeacherID = tcs.TeacherID WHERE te.TestID = @testID AND te.ISDELETED IS NULL AND tcs.ISDELETED IS NULL AND cla.ISDELETED IS NULL AND sub.ISDELETED IS NULL AND tea.ISDELETED IS NULL "; using (var connection = GetConnection()) { var list = connection.Query <Test>(sqlTestMark , new { TestID = testID } ).FirstOrDefault(); return(list); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }
public bool Delete(Guid teacherId, string modifiedby, ref bool dbError) { try { using (var connection = GetConnection()) { var update = @" UPDATE Teacher SET isdeleted = now() , islive = null , LAstModifiedby = @modifiedby WHERE TeacherId = @teacherId AND TeacherID NOT IN ( SELECT TeacherID FROM TeacherClassSubject Where TeacherID = @teacherId AND ISDELETED IS NULL )"; var id = connection.Execute(update, new { teacherId = teacherId, modifiedby = modifiedby }); return(true); } } catch (Exception er) { dbError = true; DbLog.Error(er); return(false); } }
public Teacher GetTeacherById(Guid teacherID, ref bool dbError) { try { var sql = @"SELECT tec.* FROM Teacher tec inner join School sch on sch.SchoolID = tec.SchoolID where tec.TeacherId = @teacherID AND tec.IsDELETED IS NULL AND sch.ISDELETED IS NULL"; using (var connection = GetConnection()) { var record = connection.Query <Teacher>(sql, new { teacherID = teacherID } ).FirstOrDefault(); return(record); } } catch (Exception er) { dbError = true; DbLog.Error(er); } return(null); }