public static DataRowCollection GetCourseToAvailableGroups(int courseID, int day, int period)
{
string expr = String.Format("SELECT GetCourseToGroup.[GroupID],GetCourseToGroup.[GroupName] FROM GetCourseToGroup WHERE (((GetCourseToGroup.[GroupID]) Not In (SELECT MainGroupID FROM GetLessonsOfGroupMembers WHERE (Day = {1} AND Period = {2}))) AND (GetCourseToGroup.[CourseID])={0});", courseID, day, period);
DataTable data = OledbHelper.GetTable(expr);
return(data.Rows);
}
public static DataTable GetAssignmentStudentsByID(int ID)
{
string Expr = string.Format("SELECT [Users].FName, [Users].LName, [StudentToAssignment].Grade FROM Users INNER JOIN StudentToAssignment ON ([Users].UserID =[ StudentToAssignment].StudentID) WHERE ((([StudentToAssignment].AssignmentID)={0})) ORDER BY [Users].FName, [Users].LName;", ID.ToString());
DataTable dt = OledbHelper.GetTable(Expr);
return(dt);
}
/// <summary>
/// Returns A Collection Of Lesson To Group Relations.
/// Fields: TeacherID, Teacher.FName, Teacher.LName, Lesson.LessonID, Lesson.Day, Lesson.Period, Groups.GroupName, Groups.GroupName, Subjects.SubjectName
/// ORDERED By Lesson.Day, Lesson.Period - ASC,ASC
/// </summary>
/// <param name="GroupID"></param>
/// <returns></returns>
public static DataRowCollection GetLessonsByGroup(int GroupID)
{
string expr = string.Format("SELECT * FROM GetLessonsOfGroupMembers WHERE [MainGroupID] = {0}", GroupID);
DataTable data = OledbHelper.GetTable(expr);
return(data.Rows);
}
public static DataRowCollection GetGroupsOfCourse(int CourseID)
{
string expr = String.Format("SELECT * FROM GetCourseToGroup Where CourseID = {0}", CourseID);
DataTable data = OledbHelper.GetTable(expr);
return(data.Rows);
}
/// <summary>
/// Returns A Collection Of Lesson To Teacher Relations.
/// Fields: TeacherID, Teacher.FName, Teacher.LName, Lesson.LessonID, Lesson.Day, Lesson.Period, Groups.GroupName, Groups.GroupID, Subjects.SubjectName
/// ORDERED By Lesson.Day, Lesson.Period - ASC,ASC
/// </summary>
/// <param name="TeacherID"></param>
/// <returns></returns>
public static DataRowCollection GetLessonsByTeacher(int TeacherID)
{
string expr = string.Format("SELECT * FROM GetLessonsByTeacher WHERE [TeacherID] = {0}", TeacherID);
DataTable data = OledbHelper.GetTable(expr);
return(data.Rows);
}
public static User GetUser(string username, string password)
{
DataTable Data = OledbHelper.GetTable("Select * From Users Where UName='" + username + "' AND PWord='" + password + "'");
DataRow DataR = Data.Rows[0];
return(new User(int.Parse(DataR["UserID"].ToString()), DataR["FName"].ToString(), DataR["LName"].ToString(), int.Parse(DataR["UserType"].ToString())));
}
public static DataRowCollection GetStudentsOfGroup(int GroupID)
{
string expr = string.Format("SELECT * FROM GetStudentsByGroup WHERE GetStudentsByGroup.[GroupID] = {0}", GroupID);
DataTable Data = OledbHelper.GetTable(expr);
return(Data.Rows);
}
public static DataRowCollection GetCourseToAvailableTeachers(int Day, int Period, int CourseID)
{
string expr = String.Format("SELECT [Users].[FName] & ' ' & [Users].[LName] AS Name, Users.[UserID] AS TeacherID FROM Users INNER JOIN GetTeacherToCourse ON Users.UserID = GetTeacherToCourse.TeacherID WHERE (((GetTeacherToCourse.TeacherID) Not In (SELECT GetLessonsByTeacher.[TeacherID] FROM GetLessonsByTeacher WHERE (((GetLessonsByTeacher.[Day])={1})) AND ((GetLessonsByTeacher.[Period])={2}))) AND ((GetTeacherToCourse.CourseID)={0}));", CourseID, Day, Period);
DataTable data = OledbHelper.GetTable(expr);
return(data.Rows);
}
public static DataRowCollection GetCourseToTeacher(int teacherID, int grade)
{
string expr = String.Format("SELECT * FROM GetTeacherToCourse Where TeacherID = {0} AND Grade = {1}", teacherID, grade);
DataTable data = OledbHelper.GetTable(expr);
return(data.Rows);
}
public static DataRowCollection GetCourseToGroup()
{
string expr = "SELECT * FROM GetCourseToGroup";
DataTable data = OledbHelper.GetTable(expr);
return(data.Rows);
}
public static DataRowCollection GetCourseAndTeacherToSubjects(int CourseID, int TeacherID)
{
string expr = String.Format("SELECT * FROM GetCourseAndTeacherToSubjects Where CourseID = {0} AND TeacherID = {1}", CourseID, TeacherID);
DataTable data = OledbHelper.GetTable(expr);
return(data.Rows);
}
//base method
public static DataTable GetFilteredTable(string TableName, string[] Columns, FilterCollection Filters)
{
//Format an Expression:
// Handle Columns Part
#region Format Columns To an SQL Select-Cmpatible Format
//Form
Columns = Columns.Select(
C => //Initializes Variable string C - Current Column
string.Format("{0}.{1}", TableName, C) + (
(C == Columns.Last()) //Setsa boolean Expression that checks if C is the Last Column
? "" //If Expression is true add a blank string (Do nothing)
: "," //If Expression is false add ","
) + " "
).ToArray();
#endregion
#region Create ColumnsString
string ColumnsString = "";
foreach (string Column in Columns)
{
ColumnsString += Column;
}
string expr = string.Format("SELECT {0}", ColumnsString);
#endregion
// Handle TableName Part
expr += string.Format(" FROM {0}", TableName);
// Handle Conditions Part
string[] Conditions = Filters.GetQueries();
if (Conditions.Count() > 0)
{
#region Format Conditions To an SQL Condition Format
//Format Conditions To Easily Chain Them.
//Formats The Last Condition To "(ConditionString)"
//Formats The Other Conditions To "(ConditionString) AND "
Conditions = Conditions.Select(
C => //Initializes Variable string C - Current Condition
string.Format("({0})", C) + (
((C == Conditions.Last())) //Sets a boolean Expression that checks if C is the Last Condition
? "" //If Expression is true add a blank string (Do nothing),
: " AND " //If Expression is false add " AND ".
)
).ToArray();
#endregion
#region Create ConditionsString
string ConditionsString = "";
foreach (string Condition in Conditions)
{
ConditionsString += Condition;
}
expr += string.Format(" WHERE ({0})", ConditionsString);
#endregion
}
// Close expression With a Semicolon
expr += ";";
return(OledbHelper.GetTable(expr));
}
public static DataRow GetTeacherToMinAndMaxGrades(int TeacherID)
{
string expr = String.Format("SELECT Min,Max FROM GetTeacherToMinAndMaxGrades WHERE TeacherID = {0}", TeacherID);
return(OledbHelper.GetTable(expr).Rows[0]);
}
public static int GetCourseGrade(int CourseID)
{
string expr = string.Format("SELECT Courses.[Grade] From Courses Where (Courses.[CourseID] = {0})", CourseID);
return((int)OledbHelper.GetTable(expr).Rows[0][0]);
}
public static string GetCourseName(int CourseID)
{
string expr = string.Format("SELECT Courses.[CourseName] FROM Courses WHERE (Courses.[CourseID] = {0})", CourseID);
return((string)OledbHelper.GetTable(expr).Rows[0][0]);
}
public static bool IsTeacherAvailable(int teacherID, int day, int period)
{
string expr = string.Format("SELECT Count(GetLessonsByTeacher.LessonID) AS LessonCount FROM GetLessonsByTeacher HAVING (GetLessonsByTeacher.[TeacherID]={0}) AND (GetLessonsByTeacher.[Day]={1}) AND (GetLessonsByTeacher.[Period]={2});", teacherID, day, period);
return(((int)OledbHelper.GetTable(expr).Rows[0]["LessonCount"]) == 0);
}
public static DataRowCollection GetTeachers()
{
DataTable Data = OledbHelper.GetTable("SELECT Teacher.UserID, Teacher.FName, Teacher.LName FROM Users AS Teacher WHERE (((Teacher.UserType)=0))");
return(Data.Rows);
}
public static DataRow GetCurrentLesson(int TeacherID)
{
return(OledbHelper.GetTable("Select * From Lessons L Where L.[StartTime] < AND L.[EndTime] < GETTIME() AND L.[TeacherID] == " + TeacherID.ToString()).Rows[0]);
}
public static string GetNameOfUser(int UserID)
{
string expr = string.Format(" SELECT ([FName]&' '&[LName]) as Name FROM Users Where ([UserID] = {0})", UserID);
return(OledbHelper.GetTable(expr).Rows[0]["Name"].ToString());
}
public static DataRow GetCourseOfGroup(int GroupID)
{
string expr = string.Format("SELECT * FROM GetCourseToGroup WHERE (GetCourseToGroup.[GroupID] = {0})", GroupID);
return(OledbHelper.GetTable(expr).Rows[0]);
}
public static string GetGroupName(int GroupID)
{
string expr = string.Format("SELECT Groups.[GroupName] From Groups WHERE (Groups.[GroupID] = {0});", GroupID);
return((string)OledbHelper.GetTable(expr).Rows[0][0]);
}
public static void SQL()
{
bool SQL = true;
string str = "";
System.Console.Clear();
System.Console.ForegroundColor = ConsoleColor.Red;
System.Console.Write("SQL Mode ");
System.Console.ResetColor();
while (SQL)
{
System.Console.WriteLine("---------");
System.Console.WriteLine(str);
System.Console.ForegroundColor = ConsoleColor.Yellow;
System.Console.Write("> ");
System.Console.ResetColor();
str = System.Console.ReadLine();
try
{
if (str == "")
{
SQL = false;
}
else if (str.ToLower().Contains("adduser"))
{
string UName = "";
string PWord = "";
for (int i = 0; i < 3; i++)
{
UName += (Random.Next(10).ToString());
PWord += (Random.Next(10).ToString());
}
System.Console.ForegroundColor = ConsoleColor.Yellow;
System.Console.Write(UserMethods.AddUser(UName, PWord) + "\n");
System.Console.ResetColor();
}
else if (str.ToLower().Contains("select"))
{
System.Data.DataTable Table = OledbHelper.GetTable(str);
foreach (System.Data.DataColumn Col in Table.Columns)
{
System.Console.ForegroundColor = ConsoleColor.Cyan;
System.Console.Write("{0,15}|", Col.ColumnName.ToString());
System.Console.ResetColor();
}
System.Console.Write("\n");
foreach (System.Data.DataRow Row in Table.Rows)
{
foreach (var item in Row.ItemArray)
{
System.Console.Write("{0,15}|", item.ToString());
}
System.Console.Write("\n");
}
}
else
{
OledbHelper.Execute(str);
System.Console.ForegroundColor = ConsoleColor.Green;
System.Console.Write("Success. ");
System.Console.ResetColor();
}
//System.Console.ReadLine();
}
catch (Exception e)
{
//throw e;
if (str == "")
{
SQL = false;
}
else
{
System.Console.ForegroundColor = ConsoleColor.Red;
System.Console.Write("Invalid. ");
System.Console.WriteLine(e.Message);
System.Console.ResetColor();
// System.Console.ReadLine();
}
}
}
}