protected void ddlCourses_SelectedIndexChanged(object sender, EventArgs e)
{
int courseID = int.Parse(ddlCourses.SelectedValue);
Course course = (Course.GetCourse(courseID)).ElementAt(0);
IEnumerable<Session> sessions = Classes.Session.GetSessionsByCourseID(courseID);
//IEnumerable<ClientSession> clientSessions = (ClientSession.GetClientSessionsByClientID(clientID));
DataTable sessionsTable = new DataTable("SessionsTable");
foreach (Session sesh in sessions)
{
using (DatabaseConnection db = new DatabaseConnection("dbo.GetSession"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("SessionID", sesh.SessionID);
// Open Connection
db.conn.Open();
// Execute Command
SqlDataAdapter MyAdapter = new SqlDataAdapter();
MyAdapter.SelectCommand = db.comm;
DataTable table = new DataTable();
// Read Response
MyAdapter.Fill(table);
sessionsTable.Merge(table);
db.Dispose();
}
}
modSessionTable(sessionsTable);
//gvSessions.DataSource = sessionsTable.DefaultView;
//gvSessions.DataBind();
}
public static int AddStatus(Status status)
{
int statusID = -1;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.AddStatus"))
{
// Set Parameters
AddParameters(status, db.comm);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
statusID = Convert.ToInt32(db.comm.ExecuteScalar());
}
return statusID;
}
public static int AddRoom(Room room)
{
int roomID = -1;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.AddRoom"))
{
// Set Parameters
AddParameters(room, db.comm);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
roomID = Convert.ToInt32(db.comm.ExecuteScalar());
}
return roomID;
}
public static IEnumerable<Status> GetStatuses()
{
IEnumerable<Status> statuses = new List<Status>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetStatuses"))
{
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
statuses = ReadStatuses(reader);
}
return statuses;
}
public static int AddCompany(Company company)
{
int companyID = -1;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.AddCompany"))
{
// Set Parameters
AddParameters(company, db.comm);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
companyID = Convert.ToInt32(db.comm.ExecuteScalar());
}
return companyID;
}
public static IEnumerable<Room> GetRoom(int roomID)
{
IEnumerable<Room> rooms = new List<Room>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetRoom"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("RoomID", roomID);
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
rooms = ReadRooms(reader);
}
return rooms;
}
public static IEnumerable<Status> GetStatus(int statusID)
{
IEnumerable<Status> statuses = new List<Status>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetStatus"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("StatusID", statusID);
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
statuses = ReadStatuses(reader);
}
return statuses;
}
public static IEnumerable<Course> GetCourse(int courseID)
{
IEnumerable<Course> courses = new List<Course>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetCourse"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("CourseID", courseID);
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
courses = ReadCourses(reader);
}
return courses;
}
public static int UpdateSession(Session session, Session oldSession)
{
int rowsAffected = 0;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.UpdateSession"))
{
// Set Parameters
AddParameters(session, db.comm);
AddOldParameters(oldSession, db.comm);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
rowsAffected = db.comm.ExecuteNonQuery();
}
return rowsAffected;
}
public static IEnumerable<Session> GetSessionsWithinNext6WeeksByCourseID(int courseID)
{
IEnumerable<Session> sessions = new List<Session>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetSessionsWithinNext6WeeksByCourseID"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("CourseID", courseID);
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
sessions = ReadSessions(reader);
}
return sessions;
}
public static int CalculateNumberOfAvailableSeatsForSession(int sessionID)
{
int numOfAvailableSeats = 0;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.CalculateNumberOfAvailableSeatsForSession"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("SessionID", sessionID);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
numOfAvailableSeats = Convert.ToInt32(db.comm.ExecuteScalar());
}
return numOfAvailableSeats;
}
public static IEnumerable<Company> GetCompanies()
{
IEnumerable<Company> companies = new List<Company>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetCompanies"))
{
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
companies = ReadCompanies(reader);
}
return companies;
}
public static int GenerateInvoice(int companyID)
{
int invoiceID = -1;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GenerateInvoice"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("CompanyID", companyID);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
invoiceID = Convert.ToInt32(db.comm.ExecuteScalar());
}
return invoiceID;
}
public static int RemoveCompany(Company oldCompany)
{
int rowsAffected = 0;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.RemoveCompany"))
{
// Set Parameters
AddOldParameters(oldCompany, db.comm);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
rowsAffected = db.comm.ExecuteNonQuery();
}
return rowsAffected;
}
public static IEnumerable<ClientSession> GetUnpaidClientSessionsByCompanyID(int companyID)
{
IEnumerable<ClientSession> clientSessions = new List<ClientSession>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetUnpaidClientSessions"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("CompanyID", companyID);
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
clientSessions = ReadClientSessions(reader);
}
return clientSessions;
}
public static IEnumerable<ClientSession> GetClientSession(int clientSessionID)
{
IEnumerable<ClientSession> clientSessions = new List<ClientSession>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetClientSession"))
{
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
clientSessions = ReadClientSessions(reader);
}
return clientSessions;
}
public static IEnumerable<InvoiceItem> GetInvoiceItemsByInvoiceID(int invoiceID)
{
IEnumerable<InvoiceItem> invoiceItems = new List<InvoiceItem>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetInvoiceItemsByInvoiceID"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("InvoiceID", invoiceID);
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
invoiceItems = ReadInvoiceItems(reader);
}
return invoiceItems;
}
public static int UpdateRoom(Room room, Room oldRoom)
{
int rowsAffected = 0;
// Setup Connection
DatabaseConnection db = new DatabaseConnection("dbo.UpdateRoom");
{
// Set Parameters
AddParameters(room, db.comm);
AddOldParameters(oldRoom, db.comm);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
rowsAffected = db.comm.ExecuteNonQuery();
}
return rowsAffected;
}
// Update Instructor, return number of rows affected
public static int UpdateInstructor(Instructor instructor, Instructor oldInstructor)
{
int rowsAffected = 0;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.UpdateInstructor"))
{
// Set Parameters
AddParameters(instructor, db.comm);
AddOldParameters(oldInstructor, db.comm);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
rowsAffected = db.comm.ExecuteNonQuery();
}
return rowsAffected;
}
// Get Instructor
public static IEnumerable<Instructor> GetUnqualifiedInstructorsByCourseID(int courseID)
{
IEnumerable<Instructor> instructors = new List<Instructor>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetUnqualifiedInstructorsByCourseID"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("CourseID", courseID);
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
instructors = ReadInstructors(reader);
}
return instructors;
}
// Get all Instructors
public static IEnumerable<Instructor> GetInstructors()
{
IEnumerable<Instructor> instructors = new List<Instructor>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetInstructors"))
{
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
instructors = ReadInstructors(reader);
}
return instructors;
}
public static int UpdateCourse(Course course, Course oldCourse)
{
int rowsAffected = 0;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.UpdateCourse"))
{
// Set Parameters
AddParameters(course, db.comm);
AddOldParameters(oldCourse, db.comm);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
rowsAffected = db.comm.ExecuteNonQuery();
}
return rowsAffected;
}
// Add Instructor, return InstructorID
public static int AddInstructor(Instructor instructor)
{
int instructorID = -1;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.AddInstructor"))
{
// Set Parameters
AddParameters(instructor, db.comm);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
instructorID = Convert.ToInt32(db.comm.ExecuteScalar());
}
return instructorID;
}
public static IEnumerable<Client> GetClient(int clientID)
{
IEnumerable<Client> clients = new List<Client>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetClient"))
{
// Set Parameters
db.comm.Parameters.AddWithValue("ClientID", clientID);
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
clients = ReadClients(reader);
}
return clients;
}
public static IEnumerable<Room> GetRooms()
{
IEnumerable<Room> rooms = new List<Room>();
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.GetRooms"))
{
// Open Connection
db.conn.Open();
// Execute Command
SqlDataReader reader = db.comm.ExecuteReader();
// Read Response
rooms = ReadRooms(reader);
}
return rooms;
}
public static int AddClientSession(ClientSession clientSession)
{
int clientSessionID = -1;
// Setup Connection
using (DatabaseConnection db = new DatabaseConnection("dbo.AddClientSession"))
{
// Set Parameters
AddParameters(clientSession, db.comm);
// Open Connection
db.conn.Open();
// Execute Command and Read Response
clientSessionID = Convert.ToInt32(db.comm.ExecuteScalar());
}
return clientSessionID;
}
