public List <RoomAllocationView> GetAllocationInformationByDepartment(int departmentId)
{
string query = "SELECT C.CourseCode, C.CourseName, FORMAT(CAST(A.StartTime AS DATETIME),'h:mm tt') AS StartTime, FORMAT(CAST(A.EndTime AS DATETIME),'h:mm tt') AS EndTime, R.RoomNo, D.[DayName] FROM Courses C LEFT JOIN RoomAllocation A ON ( C.CourseId = A.CourseId AND A.IsCurrent=1 ) LEFT JOIN Rooms R ON A.RoomId = R.RoomId LEFT JOIN [Days] D ON A.DayId = D.DayId WHERE C.DepartmentId=@deptId ORDER BY C.CourseCode ASC";
using (connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.Clear();
command.Parameters.Add("deptId", sqlDbType: SqlDbType.Int);
command.Parameters["deptId"].Value = departmentId;
List <RoomAllocationView> roomAllocationViews = new List <RoomAllocationView>();
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
RoomAllocationView aAllocationView = new RoomAllocationView
{
CourseCode = reader["CourseCode"].ToString(),
CourseName = reader["CourseName"].ToString(),
RoomNo = reader["RoomNo"].ToString(),
DayName = reader["DayName"].ToString(),
StartTime = reader["StartTime"].ToString(),
EndTime = reader["EndTime"].ToString()
};
roomAllocationViews.Add(aAllocationView);
}
return(roomAllocationViews);
}
}
public List <RoomAllocationView> GetAllocationInfoByDeptId(int departmentId)
{
string query = "SELECT * FROM RoomAllocationView WHERE DepartmentId = '" + departmentId + "'";
Command = new SqlCommand(query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
List <RoomAllocationView> roomAllocationViews = new List <RoomAllocationView>();
while (Reader.Read())
{
RoomAllocationView roomAllocationView = new RoomAllocationView();
roomAllocationView.CourseId = Convert.ToInt32(Reader["CourseId"]);
roomAllocationView.CourseCode = Reader["CourseCode"].ToString();
roomAllocationView.CourseName = Reader["CourseName"].ToString();
roomAllocationView.RoomNo = Reader["RoomNo"].ToString();
roomAllocationView.Day = Reader["Day"].ToString();
roomAllocationView.FromTime = Convert.ToString(Reader["FromTime"]);
roomAllocationView.ToTime = Reader["ToTime"].ToString();
roomAllocationViews.Add(roomAllocationView);
}
Connection.Close();
return(roomAllocationViews);
}
