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 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 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 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 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);
}
private bool UpdateTeacherRemainingCourse(int teacherId, int courseId, int courseCredit)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
Teacher teacher = GetCurrentcurrentRemainingCourseId(teacherId);
int currentRemainingCredit = teacher.RemainingCredit;
int updateRemainingCredit = currentRemainingCredit + courseCredit;
if (updateRemainingCredit > teacher.CreditToBeTaken)
{
updateRemainingCredit = teacher.CreditToBeTaken;
}
db.cmdText = "UPDATE Teachers SET RemainingCredit = @RemainingCredit WHERE Id = @Id";
db.command.Parameters.AddWithValue("@RemainingCredit", updateRemainingCredit);
db.command.Parameters.AddWithValue("@Id", teacherId);
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 AssignCourseTeacher(CourseTeacher courseTeacher)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "INSERT INTO CourseTeacher(DepartmentId, TeacherId, CourseId, Status) VALUES(@DepartmentId, @TeacherId, @CourseId, @Status)";
db.command.Parameters.AddWithValue("@DepartmentId", courseTeacher.DepartmentId);
db.command.Parameters.AddWithValue("@TeacherId", courseTeacher.TeacherId);
db.command.Parameters.AddWithValue("@CourseId", courseTeacher.CourseId);
db.command.Parameters.AddWithValue("@Status", true);
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
if (UpdateTeacherRemainingCourse(courseTeacher.TeacherId, courseTeacher.CourseCredit))
{
flag = true;
}
else
{
flag = false;
}
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
public bool SaveCourse(Course course)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
if (string.IsNullOrEmpty(course.Description))
{
course.Description = string.Empty;
}
db.cmdText = "INSERT INTO Courses(Code, Name, Credit, Description, DepartmentId, SemesterId) VALUES(@Code, @Name, @Credit, @Description, @DepartmentId, @SemesterId)";
db.command.Parameters.AddWithValue("@Code", course.Code);
db.command.Parameters.AddWithValue("@Name", course.Name);
db.command.Parameters.AddWithValue("@Credit", course.Credit);
db.command.Parameters.AddWithValue("@Description", course.Description);
db.command.Parameters.AddWithValue("@DepartmentId", course.DepartmentId);
db.command.Parameters.AddWithValue("@SemesterId", course.SemesterId);
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
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);
}
public int RegisterUser(SystemUser user)
{
int userId = 0;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "INSERT INTO Users(FullName, Email, Password, Status) VALUES(@FullName, @Email, @Password, @Status)";
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.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
userId = GetUserIdByEmail(user.Email);
}
else
{
userId = 0;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(userId);
}
public bool IsOverlapping(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");
db.cmdText = "SELECT RoomNo, Day, FromTime, ToTime FROM AllocateClassrooms WHERE RoomNo = @RoomNo AND Day = @Day AND FromTime = @FromTime AND ToTime = @ToTime AND Status = @Status";
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("@Status", true);
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 List <Setting> GetAllSliderSettingsById(int userId)
{
List <Setting> listOfSetting = new List <Setting>();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM SliderImages WHERE UserId = @UserId";
db.command.Parameters.Add("UserId", SqlDbType.Int);
db.command.Parameters["UserId"].Value = userId;
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Setting setting = new Setting();
setting.Id = int.Parse(reader["Id"].ToString());
setting.UserId = int.Parse(reader["UserId"].ToString());
setting.Image = reader["Images"].ToString();
setting.Title = reader["Title"].ToString();
setting.Description = reader["Description"].ToString();
listOfSetting.Add(setting);
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(listOfSetting);
}
public Student GetStudentById(int id)
{
Student student = new Student();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM Students WHERE Id = @Id";
db.command.Parameters.AddWithValue("@Id", id);
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
student.Id = int.Parse(reader["Id"].ToString());
student.RegNo = reader["RegNo"].ToString();
student.Name = reader["Name"].ToString();
student.Email = reader["Email"].ToString();
student.ContactNo = reader["ContactNo"].ToString();
student.Date = Convert.ToDateTime(reader["Date"].ToString());
student.Address = reader["Address"].ToString();
student.DepartmentId = int.Parse(reader["DepartmentId"].ToString());
break;
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(student);
}
public bool UpdateTeacher(Teacher teacher)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "UPDATE Teachers SET Name = @Name, Address = @Address, Email = @Email, ContactNo = @ContactNo, DesignationId = @DesignationId, DepartmentId = @DepartmentId, CreditToBeTaken = @CreditToBeTaken, RemainingCredit = @RemainingCredit WHERE Id = @Id";
db.command.Parameters.AddWithValue("@Name", teacher.Name);
db.command.Parameters.AddWithValue("@Address", teacher.Address);
db.command.Parameters.AddWithValue("@Email", teacher.Email);
db.command.Parameters.AddWithValue("@ContactNo", teacher.ContactNo);
db.command.Parameters.AddWithValue("@DesignationId", teacher.DesignationId);
db.command.Parameters.AddWithValue("@DepartmentId", teacher.DepartmentId);
db.command.Parameters.AddWithValue("@CreditToBeTaken", teacher.CreditToBeTaken);
db.command.Parameters.AddWithValue("@RemainingCredit", teacher.RemainingCredit);
db.command.Parameters.AddWithValue("@Id", teacher.Id);
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}
private void GetTeacherTakenCreditSetRemainingCredit()
{
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "UPDATE Teachers SET RemainingCredit = CreditToBeTaken";
db.Open();
db.command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
}
public SystemUser GetUserById(int id)
{
SystemUser user = new SystemUser();
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "SELECT * FROM Users WHERE Id = @Id";
db.command.Parameters.Add("Id", SqlDbType.Int);
db.command.Parameters["Id"].Value = id;
db.Open();
SqlDataReader reader = db.command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
user.UserId = int.Parse(reader["Id"].ToString());
user.FullName = reader["FullName"].ToString();
user.Email = reader["Email"].ToString();
//user.Password = reader["Password"].ToString();
user.Status = Convert.ToBoolean(reader["Status"].ToString());
break;
}
}
reader.Close();
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(user);
}
public bool DeleteCourseTeacher(int id, int cid, int tid)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
CourseManager courseManager = new CourseManager();
Course course = courseManager.GetCourseById(cid);
if (UpdateTeacherRemainingCourse(tid, cid, Convert.ToInt32(course.Credit)))
{
db.cmdText = "DELETE FROM CourseTeacher WHERE Id = @Id AND CourseId = @CourseId";
db.command.Parameters.AddWithValue("@Id", id);
db.command.Parameters.AddWithValue("@CourseId", cid);
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 UpdateStudent(Student student)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "UPDATE Students SET RegNo = @RegNo, Name = @Name, Email = @Email, ContactNo = @ContactNo, Date = @Date, Address = @Address, DepartmentId = @DepartmentId WHERE Id = @Id";
db.command.Parameters.AddWithValue("@RegNo", student.RegNo);
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.command.Parameters.AddWithValue("@Id", student.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 SaveTeacher(Teacher teacher)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "INSERT INTO Teachers(Name, Address, Email, ContactNo, DesignationId, DepartmentId, CreditToBeTaken, RemainingCredit) VALUES(@Name, @Address, @Email, @ContactNo, @DesignationId, @DepartmentId, @CreditToBeTaken, @RemainingCredit)";
db.command.Parameters.AddWithValue("@Name", teacher.Name);
db.command.Parameters.AddWithValue("@Address", teacher.Address);
db.command.Parameters.AddWithValue("@Email", teacher.Email);
db.command.Parameters.AddWithValue("@ContactNo", teacher.ContactNo);
db.command.Parameters.AddWithValue("@DesignationId", teacher.DesignationId);
db.command.Parameters.AddWithValue("@DepartmentId", teacher.DepartmentId);
db.command.Parameters.AddWithValue("@CreditToBeTaken", teacher.CreditToBeTaken);
db.command.Parameters.AddWithValue("@RemainingCredit", teacher.CreditToBeTaken);
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 UpdateDepartment(Department dept)
{
bool flag = false;
DBPlayer db = new DBPlayer();
try
{
db.cmdText = "UPDATE Departments SET Code = @Code, Name = @Name WHERE Id = @Id";
db.command.Parameters.Add("Code", SqlDbType.NVarChar);
db.command.Parameters["Code"].Value = dept.Code;
db.command.Parameters.Add("Name", SqlDbType.NVarChar);
db.command.Parameters["Name"].Value = dept.Name;
db.command.Parameters.Add("Id", SqlDbType.Int);
db.command.Parameters["Id"].Value = dept.Id;
db.Open();
int rowsAffected = db.command.ExecuteNonQuery();
if (rowsAffected > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
throw;
}
finally
{
db.Close();
}
return(flag);
}