public List<AllocateClassRoom> CheckTimeOverlapping(string day, int roomId) { string query = "SELECT AllocTimeFrom, AllocTimeTo FROM AllocateRoom where RoomAllocDay=@day and RoomID=@roomId"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.Clear(); command.Parameters.Add("day", SqlDbType.VarChar); command.Parameters["day"].Value = day; command.Parameters.Add("roomId", SqlDbType.Int); command.Parameters["roomId"].Value = roomId; connection.Open(); SqlDataReader reader = command.ExecuteReader(); List<AllocateClassRoom> timeList = new List<AllocateClassRoom>(); while (reader.Read()) { AllocateClassRoom roomObj = new AllocateClassRoom(); roomObj.STime = (TimeSpan)(reader["AllocTimeFrom"]); roomObj.ETime = (TimeSpan)(reader["AllocTimeTo"]); timeList.Add(roomObj); } reader.Close(); connection.Close(); return timeList; }
public string SaveAllocation(AllocateClassRoom allocateClassRoomObj) { string startTime = allocateClassRoomObj.StartTime; bool check = TimeOverlapping(startTime, allocateClassRoomObj.Day, allocateClassRoomObj.RoomId); if (!check) { string message = gateway.SaveAllocation(allocateClassRoomObj); return message; } return "Not Possible"; }
public List<AllocateClassRoom> LoadAllCourseByDepartmet() { string query = "SELECT * FROM Course"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List<AllocateClassRoom> courseList = new List<AllocateClassRoom>(); while (reader.Read()) { AllocateClassRoom course = new AllocateClassRoom(); course.CourseId = Convert.ToInt32(reader["CourseID"].ToString()); course.CourseName = reader["CourseName"].ToString(); course.DepartmentId = Convert.ToInt32(reader["DepartmentID"].ToString()); courseList.Add(course); } reader.Close(); connection.Close(); return courseList; }
public List<AllocateClassRoom> LoadAllDepartment() { string query = "SELECT * FROM Department"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List<AllocateClassRoom> departmentList = new List<AllocateClassRoom>(); while (reader.Read()) { AllocateClassRoom departmet = new AllocateClassRoom(); departmet.DepartmentId = Convert.ToInt32(reader["DepartmentID"].ToString()); departmet.DepartmentName = reader["DepartmentName"].ToString(); departmentList.Add(departmet); } reader.Close(); connection.Close(); return departmentList; }
public string SaveAllocation(AllocateClassRoom allocateClassRoomObj) { string query = "INSERT INTO AllocateRoom VALUES (@deptId,@courseId,@roomId,@day,@isDeleted,@allocTimeFrom,@allocTimeTo)"; SqlCommand insertCommand = new SqlCommand(query, connection); insertCommand.Parameters.Clear(); insertCommand.Parameters.Add("deptId", SqlDbType.Int); insertCommand.Parameters["deptId"].Value = allocateClassRoomObj.DepartmentId; insertCommand.Parameters.Add("courseId", SqlDbType.Int); insertCommand.Parameters["courseId"].Value = allocateClassRoomObj.CourseId; insertCommand.Parameters.Add("roomId", SqlDbType.Int); insertCommand.Parameters["roomId"].Value = allocateClassRoomObj.RoomId; insertCommand.Parameters.Add("day", SqlDbType.VarChar); insertCommand.Parameters["day"].Value = allocateClassRoomObj.Day; insertCommand.Parameters.Add("isDeleted", SqlDbType.Bit); insertCommand.Parameters["isDeleted"].Value = 0; insertCommand.Parameters.Add("allocTimeFrom", SqlDbType.DateTime); insertCommand.Parameters["allocTimeFrom"].Value = allocateClassRoomObj.StartTime; insertCommand.Parameters.Add("allocTimeTo", SqlDbType.DateTime); insertCommand.Parameters["allocTimeTo"].Value = allocateClassRoomObj.EndTime; connection.Open(); int rowAffected = insertCommand.ExecuteNonQuery(); connection.Close(); if (rowAffected > 0) return "Saved Successfully"; return "Saved Failed"; }
public List<AllocateClassRoom> LoadAllRoom() { string query = "SELECT * FROM Room"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); List<AllocateClassRoom> roomList = new List<AllocateClassRoom>(); while (reader.Read()) { AllocateClassRoom roomObj = new AllocateClassRoom(); roomObj.RoomId = Convert.ToInt32(reader["RoomID"].ToString()); roomObj.RoomName = reader["RoomName"].ToString(); roomList.Add(roomObj); } reader.Close(); connection.Close(); return roomList; }