public MyController()
{
var connection = new MySQLDBConnection("sql10.freemysqlhosting.net", "sql10253817", "sql10253817", "IvTyCTEjMH");
connection.Initialize();
this.service = new MySQLCommandService(connection);
}
public IEnumerable <Book> GetPreviousBooksForCourse(int courseId)
{
List <Book> books = new List <Book>();
using (var dbConnection = new MySQLDBConnection(false))
{
dbConnection.OpenConnection();
bool hasData = false;
var cmd = GenerateSqlCommandForConnectionWithText(dbConnection, "previousBooksForCourseId");
cmd.Parameters.AddWithValue("courseIdIn", courseId);
try
{
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
hasData = true;
books.Add(GenerateBookFromReaderData(rdr));
}
if (!hasData)
{
}
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
}
return(books);
}
//returns the id of the newly created course
public int SaveCourseToDatabase(Course courseObj, int instructorId)
{
int id = -1;
using (var dbConnection = new MySQLDBConnection(false))
{
dbConnection.OpenConnection();
var cmd = GenerateSqlCommandForConnectionWithText(dbConnection, "addCourseForInstructor");
cmd.Parameters.AddWithValue("abbreviationIn", courseObj.CourseNumber);
cmd.Parameters.AddWithValue("sectionIn", courseObj.Section);
cmd.Parameters.AddWithValue("termIn", courseObj.Term);
cmd.Parameters.AddWithValue("yearIn", courseObj.Year);
cmd.Parameters.AddWithValue("deptIn", courseObj.Department);
cmd.Parameters.AddWithValue("instructorIdIn", instructorId);
try
{
//TODO refactor the try/catch block (try only one operation and catch only the expected failures)
id = (int)cmd.ExecuteScalar();
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
}
return(id);
}
public void SetCourseSubmitted(Course course)
{
using (var dbConnection = new MySQLDBConnection(false))
{
dbConnection.OpenConnection();
char submittedChar = 'N';
if (course.IsSubmitted)
{
submittedChar = 'Y';
}
var cmd = GenerateSqlCommandForConnectionWithText(dbConnection, "setCourseSubmitted");
cmd.Parameters.AddWithValue("courseIdIn", course.CourseId);
cmd.Parameters.AddWithValue("isSubmittedIn", submittedChar);
cmd.Parameters.AddWithValue("bookOrderOptionIn", course.MyBookOrderOption);
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
//TODO implement this
}
}
}
public IEnumerable <Term> GetAvailableTermsForDept(int deptId)
{
List <Term> termList = new List <Term>();
using (var dbConnection = new MySQLDBConnection(false))
{
dbConnection.OpenConnection();
var cmd = GenerateSqlCommandForConnectionWithText(dbConnection, "getAvailableTerms");
try
{
//TODO refactor the try/catch block (try only one operation and catch only the expected failures)
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
termList.Add(GenerateTermFromReaderData(rdr));
}
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
}
return(termList);
}
private MySqlCommand GenerateSqlCommandForConnectionWithText(MySQLDBConnection dbConnection, string commandText)
{
return(new MySqlCommand
{
Connection = dbConnection.Connection,
CommandText = commandText,
CommandType = CommandType.StoredProcedure
});
}
public void EditBook(Book bookObj, int courseId)
{
var books = new List <Book>();
using (var dbConnection = new MySQLDBConnection(false))
{
bool hasData = false;
dbConnection.OpenConnection();
var cmd = GenerateSqlCommandForConnectionWithText(dbConnection, "updateBookForCourse");
cmd.Parameters.AddWithValue("courseIdIn", courseId);
cmd.Parameters.AddWithValue("isbnIn", bookObj.BookISBN);
cmd.Parameters.AddWithValue("editionIn", bookObj.BookEdition);
cmd.Parameters.AddWithValue("publisherIn", bookObj.BookPublisher);
cmd.Parameters.AddWithValue("titleIn", bookObj.BookTitle);
//TODO fix author
//cmd.Parameters.AddWithValue("authorName", bookObj.BookAuthors?.Aggregate((previous, newest) => previous + ", " + newest) ?? "No author");
cmd.Parameters.AddWithValue("authorIn", bookObj.BookAuthors);
char required;
if (bookObj.BookRequired)
{
required = 'Y';
}
else
{
required = 'N';
}
cmd.Parameters.AddWithValue("requiredIn", required);//TODO implement this
try
{//TODO refactor the try/catch block (try only one operation and catch only the expected failures)
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
hasData = true;
books.Add(GenerateBookFromReaderData(rdr));
}
if (!hasData)
{
}
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
}
}
//TODO: Figure out the parameters
public void SaveCourseToDatabase(CSVCourse[] csvCourse)
{
using (var dbConnection = new MySQLDBConnection(false))
{
dbConnection.OpenConnection();
var cmd = GenerateSqlCommandForConnectionWithText(dbConnection, "addCourseForCSV");
//Project the csv courses into a collection with the database connection, then build and execute the database calls
csvCourse
.Select(course => new { Course = course, Connection = dbConnection })
.Select(BuildCommandForCsvCourseAdd).ToList()
.ForEach(ExecuteCsvDatabaseCourseAdd);
}
}
public void DeleteCourseById(int courseId)
{
using (var dbConnection = new MySQLDBConnection(false))
{
dbConnection.OpenConnection();
var cmd = GenerateSqlCommandForConnectionWithText(dbConnection, "deleteCourseById");
cmd.Parameters.AddWithValue("courseIdIn", courseId);
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
//TODO implement this
}
}
}
public decimal DeleteBookById(decimal bookISBN, int courseId)
{
using (var dbConnection = new MySQLDBConnection(false))
{
dbConnection.OpenConnection();
var cmd = GenerateSqlCommandForConnectionWithText(dbConnection, "removeBookToCourse");
cmd.Parameters.AddWithValue("ISBN", bookISBN);
cmd.Parameters.AddWithValue("courseID", courseId);
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
//TODO implement this
}
}
return(bookISBN);
}
public string DeleteCourseByName(string courseName, int instructorId)
{
using (var dbConnection = new MySQLDBConnection(false))
{
dbConnection.OpenConnection();
var cmd = GenerateSqlCommandForConnectionWithText(dbConnection, "deleteCourseByName");
cmd.Parameters.AddWithValue("courseName", courseName);
cmd.Parameters.AddWithValue("instructorId", instructorId);
try
{
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
//TODO implement this
}
}
return(courseName);
}
public void SaveTermToDataBase(Term termObj)
{
using (var dbConnection = new MySQLDBConnection(false))
{
dbConnection.OpenConnection();
var cmd = GenerateSqlCommandForConnectionWithText(dbConnection, "addAvailableTermYear");
cmd.Parameters.AddWithValue("termIn", termObj.quarter);
cmd.Parameters.AddWithValue("yearIn", termObj.year);
try
{
//TODO refactor the try/catch block (try only one operation and catch only the expected failures)
var rdr = cmd.ExecuteReader();
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
}
}
public IEnumerable <Instructor> GetListOfInstructorsFromDepartmentId(int departmentId)
{
List <Instructor> instructors = new List <Instructor>();
var sut = new MySQLDBConnection(false);
sut.OpenConnection();
bool hasData = false;
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = sut.Connection;
cmd.CommandText = "faculty";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
try
{
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Instructor temp = GenerateInstructorFromReader(rdr);
hasData = true;
instructors.Add(temp);
}
if (!hasData)
{
}
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
sut.CloseConnection();
return(instructors);
}
public IEnumerable <Course> GetListOfCoursesFromInstructorId(int instructorId)
{
List <Course> courses = new List <Course>();
var sut = new MySQLDBConnection(false);
sut.OpenConnection();
bool hasData = false;
var cmd = new MySqlCommand
{
Connection = sut.Connection,
CommandText = "courseByInstructorID",
CommandType = System.Data.CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("input", instructorId);
try
{
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Course temp = GenerateCourseFromReader(rdr);
courses.Add(temp);
}
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
sut.CloseConnection();
return(courses);
}
public MySQLCommandService(MySQLDBConnection con)
{
this.Interceptor = new[] { new ProcedureAspect() };
this.Executor = new MySQLCommandExecutor(con.Connection);
this.generator = new ProxyGenerator();
}