public bool DeleteTeacher(int id)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "DELETE FROM Teachers WHERE Id = @Id";
db.command.Parameters.AddWithValue("@Id", id);
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public CourseTeacher GetCourseCourseTeacherById(int id, int cid, int tid)
{
CourseTeacher courseTeacher = new CourseTeacher();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM CourseTeacherDepartmentView WHERE Id = @Id AND CourseId = @CourseId AND TeacherId = @TeacherId";
db.command.Parameters.AddWithValue("@Id", id);
db.command.Parameters.AddWithValue("@CourseId", cid);
db.command.Parameters.AddWithValue("@TeacherId", tid);
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
courseTeacher.Id = int.Parse(reader["Id"].ToString());
courseTeacher.CourseId = int.Parse(reader["CourseId"].ToString());
courseTeacher.CourseCode = reader["CourseCode"].ToString();
courseTeacher.CourseName = reader["CourseName"].ToString();
courseTeacher.CourseCredit = Convert.ToInt32(reader["CourseCredit"]);
courseTeacher.TeacherId = int.Parse(reader["TeacherId"].ToString());
courseTeacher.TeacherName = reader["TeacherName"].ToString();
courseTeacher.TeacherDesignation = reader["TeacherDesignation"].ToString();
courseTeacher.TeacherCreditToBeTaken = Convert.ToInt32(reader["TeacherCreditToBeTaken"]);
courseTeacher.TeacherRemainingCredit = Convert.ToInt32(reader["TeacherRemainingCredit"]);
courseTeacher.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
courseTeacher.DepartmentName = reader["DepartmentName"].ToString();
courseTeacher.CourseAssignStatus = Convert.ToBoolean(reader["Status"].ToString());
if (courseTeacher.CourseAssignStatus)
{
courseTeacher.Status = "Assigned";
}
else
{
courseTeacher.Status = "Not Assigned";
}
break;
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(courseTeacher);
}
public bool IsTeacherExist(string teacherEmail)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT Email FROM Teachers WHERE Email = @Email";
db.command.Parameters.AddWithValue("@Email", teacherEmail);
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
flag = true;
}
else
{
flag = false;
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public bool DeleteClassroom(string roomNo)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "DELETE FROM RoomNo WHERE RoomNo = @RoomNo";
db.command.Parameters.Add("RoomNo", SqlDbType.NVarChar);
db.command.Parameters["RoomNo"].Value = roomNo;
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public DataTable GetStudentResultReportByStudentId(int studentId)
{
DataTable dt = new DataTable();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "EXEC REPORT_STUDENT_RESULT @Id = @StudentId";
db.command.Parameters.AddWithValue("@StudentId", studentId);
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
dt.Load(reader);
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(dt);
}
public bool UnallocateAllClassroom()
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "UPDATE AllocateClassrooms SET Status = @Status";
db.command.Parameters.AddWithValue("@Status", false);
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public bool SaveClassroom(Classroom classroom)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "INSERT INTO RoomNo(RoomNo) VALUES(@RoomNo)";
db.command.Parameters.Add("RoomNo", SqlDbType.VarChar);
db.command.Parameters["RoomNo"].Value = classroom.RoomNo;
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public List <Classroom> GetAllClassroom()
{
List <Classroom> listOfClassroom = new List <Classroom>();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM RoomNo";
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Classroom classroom = new Classroom();
classroom.RoomNo = reader["RoomNo"].ToString();
listOfClassroom.Add(classroom);
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(listOfClassroom);
}
public bool IsUserExist()
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT Email FROM Users";
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
flag = true;
}
else
{
flag = false;
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public bool UnassignCourses()
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "UPDATE CourseTeacher SET Status = @Status";
db.command.Parameters.AddWithValue("@Status", false);
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
GetTeacherTakenCreditSetRemainingCredit();
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public bool DeleteSliderImageSettings(int id)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "DELETE FROM SliderImages WHERE Id = @Id";
db.command.Parameters.Add("Id", SqlDbType.Int);
db.command.Parameters["Id"].Value = id;
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public List <AllocateClassroom> GetAllAllocateClassroomList()
{
List <AllocateClassroom> listOfAllocateClassroom = new List <AllocateClassroom>();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM AllocateClassroomListView ORDER BY RoomNo";
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
AllocateClassroom allocateClassroom = new AllocateClassroom();
allocateClassroom.Id = int.Parse(reader["Id"].ToString());
allocateClassroom.RoomNo = reader["RoomNo"].ToString();
allocateClassroom.Day = reader["Day"].ToString();
allocateClassroom.FromTime = Convert.ToDateTime(reader["FromTime"].ToString());
allocateClassroom.ToTime = Convert.ToDateTime(reader["ToTime"].ToString());
allocateClassroom.Time = allocateClassroom.FromTime.ToShortTimeString() + " - " + allocateClassroom.ToTime.ToShortTimeString();
allocateClassroom.TimeDuration = reader["Duration"].ToString();
allocateClassroom.AllocationDate = Convert.ToDateTime(reader["AllocationDate"].ToString());
allocateClassroom.CourseId = int.Parse(reader["CourseId"].ToString());
allocateClassroom.CourseCode = reader["CourseCode"].ToString();
allocateClassroom.CourseName = reader["CourseName"].ToString();
allocateClassroom.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
allocateClassroom.DepartmentName = reader["DepartmentName"].ToString();
allocateClassroom.StatusFlag = Convert.ToBoolean(reader["Status"]);
if (Convert.ToBoolean(reader["Status"]))
{
allocateClassroom.Status = "Allocated";
}
else
{
allocateClassroom.Status = "Unallocated";
}
listOfAllocateClassroom.Add(allocateClassroom);
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(listOfAllocateClassroom);
}
public List <CourseTeacher> GetAllCourseCourseTeacher()
{
List <CourseTeacher> listOfCourseTeacher = new List <CourseTeacher>();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM CourseTeacherDepartmentView";
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CourseTeacher courseTeacher = new CourseTeacher();
courseTeacher.Id = int.Parse(reader["Id"].ToString());
courseTeacher.CourseId = int.Parse(reader["CourseId"].ToString());
courseTeacher.CourseCode = reader["CourseCode"].ToString();
courseTeacher.CourseName = reader["CourseName"].ToString();
courseTeacher.CourseCredit = Convert.ToInt32(reader["CourseCredit"]);
courseTeacher.TeacherId = int.Parse(reader["TeacherId"].ToString());
courseTeacher.TeacherName = reader["TeacherName"].ToString();
courseTeacher.TeacherDesignation = reader["TeacherDesignation"].ToString();
courseTeacher.TeacherCreditToBeTaken = Convert.ToInt32(reader["TeacherCreditToBeTaken"]);
courseTeacher.TeacherRemainingCredit = Convert.ToInt32(reader["TeacherRemainingCredit"]);
courseTeacher.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
courseTeacher.DepartmentName = reader["DepartmentName"].ToString();
courseTeacher.CourseAssignStatus = Convert.ToBoolean(reader["Status"].ToString());
if (courseTeacher.CourseAssignStatus)
{
courseTeacher.Status = "Assigned";
}
else
{
courseTeacher.Status = "Not Assigned";
}
listOfCourseTeacher.Add(courseTeacher);
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(listOfCourseTeacher);
}
public bool Login(SystemUser user)
{
bool flag = false;
DBPlayer db = new DBPlayer();
SessionManager session = new SessionManager();
try
{
db.cmdText = "SELECT * FROM Users WHERE Email = @Email AND Password = @Password";
db.command.Parameters.Add("Email", SqlDbType.NVarChar);
db.command.Parameters["Email"].Value = user.Email;
db.command.Parameters.Add("Password", SqlDbType.NVarChar);
db.command.Parameters["Password"].Value = user.Password;
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
SystemUser userInfo = new SystemUser();
userInfo.UserId = int.Parse(reader["Id"].ToString());
userInfo.FullName = reader["FullName"].ToString();
userInfo.Email = reader["Email"].ToString();
userInfo.Status = Convert.ToBoolean(reader["Status"]);
session.ActiveUserId = userInfo.UserId;
session.Add("ActiveUser", userInfo);
break;
}
flag = true;
}
else
{
flag = false;
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public bool UpdateAllocateClassroom(AllocateClassroom allocateClassroom)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
string fromTime = allocateClassroom.FromTime.ToString("hh:mm tt");
string toTime = allocateClassroom.ToTime.ToString("hh:mm tt");
TimeSpan start = TimeSpan.Parse(allocateClassroom.FromTime.ToString("HH:mm"));
TimeSpan end = TimeSpan.Parse(allocateClassroom.ToTime.ToString("HH:mm"));
//TimeSpan duration = allocateClassroom.ToTime - allocateClassroom.FromTime;
TimeSpan duration = new TimeSpan(0, 0, 0, (int)allocateClassroom.ToTime.Subtract(allocateClassroom.FromTime).TotalSeconds);
db.cmdText = "UPDATE AllocateClassrooms SET RoomNo = @RoomNo, Day = @Day, FromTime = @FromTime, ToTime = @ToTime, StartTime = @StartTime, EndTime = @EndTime, Duration = @Duration, AllocationDate = @AllocationDate, Status = @Status WHERE Id = @Id AND DepartmentId = @DepartmentId AND CourseId = @CourseId";
db.command.Parameters.AddWithValue("@RoomNo", allocateClassroom.RoomNo);
db.command.Parameters.AddWithValue("@Day", allocateClassroom.Day);
db.command.Parameters.AddWithValue("@FromTime", fromTime);
db.command.Parameters.AddWithValue("@ToTime", toTime);
db.command.Parameters.AddWithValue("@StartTime", start);
db.command.Parameters.AddWithValue("@EndTime", end);
db.command.Parameters.AddWithValue("@Duration", duration);
db.command.Parameters.AddWithValue("@AllocationDate", DateTime.Now);
db.command.Parameters.AddWithValue("@Status", allocateClassroom.StatusFlag);
db.command.Parameters.AddWithValue("@Id", allocateClassroom.Id);
db.command.Parameters.AddWithValue("@DepartmentId", allocateClassroom.DepartmentId);
db.command.Parameters.AddWithValue("@CourseId", allocateClassroom.CourseId);
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public List <Setting> GetAllBasicSettings()
{
List <Setting> listOfSetting = new List <Setting>();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM Settings";
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Setting setting = new Setting();
setting.UserId = int.Parse(reader["UserId"].ToString());
setting.InstituteFullName = reader["InstituteFullName"].ToString();
setting.InstituteShortName = reader["InstituteShortName"].ToString();
setting.BrandDescription = reader["BrandDescription"].ToString();
setting.BrandLogo = reader["BrandLogo"].ToString();
setting.BrandFavicon = reader["BrandFavicon"].ToString();
setting.Location = reader["Location"].ToString();
setting.Email = reader["Email"].ToString();
setting.MobileNumber = reader["MobileNumber"].ToString();
setting.PhoneNumber = reader["PhoneNumber"].ToString();
setting.Website = reader["Website"].ToString();
setting.GoogleMap = reader["GoogleMap"].ToString();
setting.AboutUs = reader["AboutUs"].ToString();
setting.FacebookLink = reader["FacebookLink"].ToString();
setting.GooglePlusLink = reader["GooglePlusLink"].ToString();
setting.TwitterLink = reader["TwitterLink"].ToString();
setting.YoutTubeLink = reader["YoutTubeLink"].ToString();
setting.LinkedInLink = reader["LinkedInLink"].ToString();
setting.GitHubLink = reader["GitHubLink"].ToString();
listOfSetting.Add(setting);
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(listOfSetting);
}
public List <Course> GetAllCourse()
{
List <Course> listOfCourse = new List <Course>();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM Courses";
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Course course = new Course();
course.Id = int.Parse(reader["Id"].ToString());
course.EId = Security.Encrypt(reader["Id"].ToString());
course.Code = reader["Code"].ToString();
course.Name = reader["Name"].ToString();
course.Credit = float.Parse(reader["Credit"].ToString());
if (string.IsNullOrEmpty(reader["Description"].ToString()))
{
course.Description = "N/A";
}
else
{
course.Description = reader["Description"].ToString();
}
course.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
course.SemesterId = int.Parse(reader["SemesterId"].ToString());
listOfCourse.Add(course);
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(listOfCourse);
}
public bool SaveStudent(Student student)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
if (string.IsNullOrEmpty(student.Address))
{
student.Address = string.Empty;
}
//DateTime now = DateTime.Now;
//string year = now.Year.ToString();
string year = DateTime.Parse(student.Date.ToString()).Year.ToString();
int studentCount = GetStudentNumber(student.DepartmentId, year) + 1;
db.cmdText = "INSERT INTO Students(RegNo, Name, Email, ContactNo, Date, Address, DepartmentId) VALUES(@RegNo, @Name, @Email, @ContactNo, @Date, @Address, @DepartmentId)";
db.command.Parameters.AddWithValue("@RegNo", student.DepartmentCode + "-" + year + "-" + GetThreeDigitNumber(studentCount));
db.command.Parameters.AddWithValue("@Name", student.Name);
db.command.Parameters.AddWithValue("@Email", student.Email);
db.command.Parameters.AddWithValue("@ContactNo", student.ContactNo);
db.command.Parameters.AddWithValue("@Date", student.Date);
db.command.Parameters.AddWithValue("@Address", student.Address);
db.command.Parameters.AddWithValue("@DepartmentId", student.DepartmentId);
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public Student GetAllStudentEnrollCourseById(int id, int sid, int cid)
{
Student studentEnrollCourse = new Student();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM StudentEnrollCourseView WHERE Id = @Id AND StudentId = @StudentId AND CourseId = @CourseId";
db.command.Parameters.AddWithValue("@Id", id);
db.command.Parameters.AddWithValue("@StudentId", sid);
db.command.Parameters.AddWithValue("@CourseId", cid);
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
studentEnrollCourse.Id = int.Parse(reader["Id"].ToString());
studentEnrollCourse.StudentId = int.Parse(reader["StudentId"].ToString());
studentEnrollCourse.RegNo = reader["RegNo"].ToString();
studentEnrollCourse.Name = reader["Name"].ToString();
studentEnrollCourse.Email = reader["Email"].ToString();
studentEnrollCourse.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
studentEnrollCourse.DepartmentName = reader["DepartmentName"].ToString();
studentEnrollCourse.CourseId = int.Parse(reader["CourseId"].ToString());
studentEnrollCourse.CourseCode = reader["CourseCode"].ToString();
studentEnrollCourse.CourseName = reader["CourseName"].ToString();
studentEnrollCourse.Date = Convert.ToDateTime(reader["Date"].ToString());
studentEnrollCourse.Status = Convert.ToBoolean(reader["Status"].ToString());
break;
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(studentEnrollCourse);
}
public List <AllocateClassroom> GetAllAllocateClassroom()
{
List <AllocateClassroom> listOfAllocateClassroom = new List <AllocateClassroom>();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM AllocateClassroomAndCourseView";
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
AllocateClassroom allocateClassroom = new AllocateClassroom();
allocateClassroom.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
allocateClassroom.CourseCode = reader["CourseCode"].ToString();
allocateClassroom.CourseName = reader["CourseName"].ToString();
allocateClassroom.ScheduleInfo = GetScheduleInfoByCourseCode(allocateClassroom.CourseCode);
if (listOfAllocateClassroom == null)
{
listOfAllocateClassroom.Add(allocateClassroom);
}
else
{
if (!IsCourseCodeExistInList(listOfAllocateClassroom, allocateClassroom.CourseCode))
{
listOfAllocateClassroom.Add(allocateClassroom);
}
}
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(listOfAllocateClassroom);
}
public bool IsCourseAssign(int courseId)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM CourseTeacher WHERE CourseId = @CourseId AND Status = @Status";
db.command.Parameters.AddWithValue("@CourseId", courseId);
db.command.Parameters.AddWithValue("@Status", true);
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CourseTeacher courseTeacher = new CourseTeacher();
courseTeacher.CourseAssignStatus = bool.Parse(reader["CourseAssignStatus"].ToString());
if (courseTeacher.CourseAssignStatus)
{
flag = true;
}
else
{
flag = false;
}
}
}
else
{
flag = false;
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public Student GetStudentResultById(int id, int sid, int cid)
{
Student studentResult = new Student();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM StudentDepartmentCourseResultView WHERE Id = @Id AND StudentId = @StudentId AND CourseId = @CourseId";
db.command.Parameters.AddWithValue("@Id", id);
db.command.Parameters.AddWithValue("@StudentId", sid);
db.command.Parameters.AddWithValue("@CourseId", cid);
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
studentResult.Id = int.Parse(reader["Id"].ToString());
studentResult.StudentId = int.Parse(reader["StudentId"].ToString());
studentResult.RegNo = reader["RegNo"].ToString();
studentResult.Name = reader["Name"].ToString();
studentResult.Email = reader["Email"].ToString();
studentResult.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
studentResult.DepartmentName = reader["DepartmentName"].ToString();
studentResult.CourseId = int.Parse(reader["CourseId"].ToString());
studentResult.CourseCode = reader["CourseCode"].ToString();
studentResult.CourseName = reader["CourseName"].ToString();
studentResult.Grade = reader["Grade"].ToString();
break;
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(studentResult);
}
public bool UpdateSliderImageSettings(Setting setting)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "UPDATE SliderImages SET UserId = @UserId, Images = @Images, Title = @Title, Description = @Description WHERE Id = @Id";
db.command.Parameters.Add("UserId", SqlDbType.Int);
db.command.Parameters["UserId"].Value = setting.UserId;
db.command.Parameters.Add("Images", SqlDbType.NVarChar);
db.command.Parameters["Images"].Value = setting.Image;
db.command.Parameters.Add("Title", SqlDbType.NVarChar);
db.command.Parameters["Title"].Value = setting.Title;
db.command.Parameters.Add("Description", SqlDbType.NVarChar);
db.command.Parameters["Description"].Value = setting.Description;
db.command.Parameters.Add("Id", SqlDbType.Int);
db.command.Parameters["Id"].Value = setting.Id;
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public bool UpdateSliderSettings(List <Setting> setting)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
//db.cmdText = "UPDATE SliderImages SET UserId = @UserId, Images = @Images, Title = @Title, Description = @Description WHERE UserId = @UserId";
if (DeleteSliderSettings(setting[0].UserId))
{
db.cmdText = "INSERT INTO SliderImages(UserId, Images, Title, Description) VALUES(@UserId, @Images, @Title, @Description)";
db.Open();
foreach (Setting s in setting)
{
db.command.Parameters.Clear();
db.command.Parameters.Add("Images", SqlDbType.NVarChar);
db.command.Parameters["Images"].Value = s.Image;
db.command.Parameters.Add("Title", SqlDbType.NVarChar);
db.command.Parameters["Title"].Value = s.Title;
db.command.Parameters.Add("Description", SqlDbType.NVarChar);
db.command.Parameters["Description"].Value = s.Description;
db.command.Parameters.Add("UserId", SqlDbType.Int);
db.command.Parameters["UserId"].Value = s.UserId;
int rowsAffected = db.command.ExecuteNonQuery();
}
flag = true;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public bool SaveSliderDefaultSettings(List <Setting> sliderSetting)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "INSERT INTO SliderImages(UserId, Images, Title, Description) VALUES(@UserId, @Images, @Title, @Description)";
db.command.Parameters.Add("UserId", SqlDbType.Int);
db.command.Parameters.Add("Images", SqlDbType.NVarChar);
db.command.Parameters.Add("Title", SqlDbType.NVarChar);
db.command.Parameters.Add("Description", SqlDbType.NVarChar);
int rowsAffected = 0;
db.Open();
foreach (var setting in sliderSetting)
{
db.command.Parameters["UserId"].Value = setting.UserId;
db.command.Parameters["Images"].Value = setting.Image;
db.command.Parameters["Title"].Value = setting.Title;
db.command.Parameters["Description"].Value = setting.Description;
rowsAffected += db.command.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public bool UpdateUser(SystemUser user)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "UPDATE Users SET FullName = @FullName, Email = @Email, Password = @Password, Status = @Status WHERE Id = @Id";
db.command.Parameters.Add("FullName", SqlDbType.NVarChar);
db.command.Parameters["FullName"].Value = user.FullName;
db.command.Parameters.Add("Email", SqlDbType.NVarChar);
db.command.Parameters["Email"].Value = user.Email;
db.command.Parameters.Add("Password", SqlDbType.NVarChar);
db.command.Parameters["Password"].Value = user.Password;
db.command.Parameters.Add("Status", SqlDbType.Bit);
db.command.Parameters["Status"].Value = user.Status;
db.command.Parameters.Add("Id", SqlDbType.Int);
db.command.Parameters["Id"].Value = user.UserId;
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public List <Student> GetAllStudentEnrollCourse()
{
List <Student> listOfStudentEnrollCourse = new List <Student>();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM StudentEnrollCourseView";
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Student student = new Student();
student.Id = int.Parse(reader["Id"].ToString());
student.StudentId = int.Parse(reader["StudentId"].ToString());
student.RegNo = reader["RegNo"].ToString();
student.Name = reader["Name"].ToString();
student.Email = reader["Email"].ToString();
student.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
student.DepartmentName = reader["DepartmentName"].ToString();
student.CourseId = int.Parse(reader["CourseId"].ToString());
student.CourseCode = reader["CourseCode"].ToString();
student.CourseName = reader["CourseName"].ToString();
student.Date = Convert.ToDateTime(reader["Date"].ToString());
student.Status = Convert.ToBoolean(reader["Status"].ToString());
listOfStudentEnrollCourse.Add(student);
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(listOfStudentEnrollCourse);
}
public List <Teacher> GetAllTeacher()
{
List <Teacher> listOfTeacher = new List <Teacher>();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM TeacherDesignationDepartmentView";
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Teacher teacher = new Teacher();
teacher.Id = int.Parse(reader["Id"].ToString());
teacher.EId = Security.Encrypt(teacher.Id.ToString());
teacher.Name = reader["Name"].ToString();
teacher.Address = reader["Address"].ToString();
teacher.Email = reader["Email"].ToString();
teacher.ContactNo = reader["ContactNo"].ToString();
teacher.DesignationName = reader["DesignationName"].ToString();
teacher.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
teacher.DepartmentName = reader["DepartmentName"].ToString();
teacher.CreditToBeTaken = int.Parse(reader["CreditToBeTaken"].ToString());
teacher.RemainingCredit = int.Parse(reader["RemainingCredit"].ToString());
listOfTeacher.Add(teacher);
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(listOfTeacher);
}
public List <Student> GetAllStudentResult()
{
List <Student> studentResultList = new List <Student>();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM StudentDepartmentCourseResultView";
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Student studentResult = new Student();
studentResult.Id = int.Parse(reader["Id"].ToString());
studentResult.StudentId = int.Parse(reader["StudentId"].ToString());
studentResult.RegNo = reader["RegNo"].ToString();
studentResult.Name = reader["Name"].ToString();
studentResult.Email = reader["Email"].ToString();
studentResult.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
studentResult.DepartmentName = reader["DepartmentName"].ToString();
studentResult.CourseId = int.Parse(reader["CourseId"].ToString());
studentResult.CourseCode = reader["CourseCode"].ToString();
studentResult.CourseName = reader["CourseName"].ToString();
studentResult.Grade = reader["Grade"].ToString();
studentResultList.Add(studentResult);
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(studentResultList);
}
public Teacher GetTeacherById(int id)
{
Teacher teacher = new Teacher();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM TeacherDesignationDepartmentView WHERE Id = @Id";
db.command.Parameters.AddWithValue("@Id", id);
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
teacher.Id = int.Parse(reader["Id"].ToString());
teacher.Name = reader["Name"].ToString();
teacher.Address = reader["Address"].ToString();
teacher.Email = reader["Email"].ToString();
teacher.ContactNo = reader["ContactNo"].ToString();
teacher.DesignationId = int.Parse(reader["DesignationId"].ToString());
teacher.DesignationName = reader["DesignationName"].ToString();
teacher.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
teacher.DepartmentName = reader["DepartmentName"].ToString();
teacher.CreditToBeTaken = int.Parse(reader["CreditToBeTaken"].ToString());
teacher.RemainingCredit = int.Parse(reader["RemainingCredit"].ToString());
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(teacher);
}
