/// <summary>
/// 将选择的Excel数据表查询后封装成对象集合
/// </summary>
/// <param name="path">excel路径</param>
/// <returns></returns>
public List <StudentExt> GetStudentByExcel(string path)
{
List <StudentExt> studentList = new List <StudentExt>();
string sql = "select * from [Student$]";
try
{
DataSet ds = OledbHelper.DataSet(sql, path);
DataTable dt = ds.Tables[0];
foreach (DataRow item in dt.Rows)
{
studentList.Add(new StudentExt
{
StudentName = item["姓名"].ToString(),
Gender = item["性别"].ToString(),
Birthday = Convert.ToDateTime(item["出生日期"]),
StudentIdNo = item["身份证号"].ToString(),
PhoneNumber = item["电话号码"].ToString(),
CardNo = item["考勤卡号"].ToString(),
Age = DateTime.Now.Year - Convert.ToDateTime(item["出生日期"].ToString()).Year,
StudentAddress = item["家庭住址"].ToString(),
ClassId = Convert.ToInt32(item["班级编号"]),
});
}
return(studentList);
}
catch (Exception ex)
{
throw ex;
}
}
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);
}
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);
}
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())));
}
/// <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 GetStudentsOfGroup(int GroupID)
{
string expr = string.Format("SELECT * FROM GetStudentsByGroup WHERE GetStudentsByGroup.[GroupID] = {0}", GroupID);
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 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 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);
}
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 DataRowCollection GetCourseToGroup()
{
string expr = "SELECT * FROM GetCourseToGroup";
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 bool AddUser(string Username, string Password)
{
if (!UserExists(Username, Password))
{
try
{
string cmd = "INSERT INTO Users (UName,PWord) Values ('" + Username + "','" + Password + "');";
OledbHelper.Execute(cmd);
return(true);
}
catch
{
return(false);
}
}
return(false);
}
/// <summary>
/// Creates a user with the provided information,
/// returns the created user's ID.
/// </summary>
/// <param name="Username">Created User's Username</param>
/// <param name="Password">Created User's Password</param>
/// <param name="FName">Created User's First Name</param>
/// <param name="LName">Created User's Surname</param>
/// <param name="UserType">Created User's Type:
/// -1: Admin, 0: Teacher, 1: Student</param>
public static int CreateUser(string Username, string Password, string FName, string LName, int UserType)
{
if (!UserExists(Username, Password))
{
try
{
string cmd = string.Format("INSERT INTO Users ([UName],[PWord],[FName],[LName],[UserType]) VALUES ('{0}','{1}','{2}','{3}',{4});", Username, Password, FName, LName, UserType.ToString());
OledbHelper.Execute(cmd);
return(GetUserByCredentials(Username, Password).ID);
}
catch
{
return(-1);
}
}
return(-1);
}
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 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 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 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]);
}
/// <summary>
/// Removes The Specified Lesson From The Database
/// </summary>
/// <param name="LessonID"></param>
public static void RemoveLesson(int LessonID)
{
string expr = String.Format("DELETE * FROM Lessons Where [LessonID]={0}", LessonID);
OledbHelper.Execute(expr);
}
//Update A Group's CourseID
public static void UpdateGroup(int GroupID, int CourseID)
{
string expr = string.Format("UPDATE Groups SET Groups.CourseID = {1} WHERE (((Groups.GroupID)={0}));", GroupID, CourseID);
OledbHelper.Execute(expr);
}
//Update A Group's GroupName
public static void UpdateGroup(int GroupID, string GroupName)
{
string expr = string.Format("UPDATE Groups SET Groups.GroupName = '{1}' WHERE (((Groups.GroupID)={0}));", GroupID, GroupName);
OledbHelper.Execute(expr);
}
public static void UpdateLesson(int LessonID, int TeacherID, int GroupID, int SubjectID, int Day, int Period)
{
string expr = string.Format("UPDATE Lessons SET Lessons.GroupID = {1}, Lessons.TeacherID = {2}, Lessons.SubjectID = {3}, Lessons.[Day] = {4}, Lessons.Period = {5} WHERE (((Lessons.LessonID)={0}));", LessonID, GroupID, TeacherID, SubjectID, Day, Period);
OledbHelper.Execute(expr);
}
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();
}
}
}
}
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 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 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 void AddLesson(int TeacherID, int GroupID, int SubjectID, int Day, int Period)
{
string Expr = string.Format("INSERT INTO Lessons ([TeacherID],[GroupID],[SubjectID],[Day],[Period]) Values ({0},{1},{2},{3},{4})", TeacherID, GroupID, SubjectID, Day, Period);
OledbHelper.Execute(Expr);
}
