/// <summary>
/// Login by Uid and PWD
/// </summary>
/// <param name="objAdmin"></param>
/// <returns></returns>
public Admin AdminLogin(Admin objAdmin)
{
string sql = "select AdminName from Admins where LoginId ={0} and LoginPwd='{1}' ";
sql = string.Format(sql, objAdmin.LoginId, objAdmin.LoginPwd);
try
{
SqlDataReader objReader = SQLHelper.GetDataReader(sql);
if (objReader.Read())
{
objAdmin.AdminName = objReader["AdminName"].ToString();
}
else
{
objAdmin = null;
}
objReader.Close();
return(objAdmin);
}
catch (Exception ex)
{
throw new Exception("Data access exception" + ex.Message);
}
}
public Student GetStudent(string studentId)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select * from [dbo].[Students] where StudentId={0}");
string sql = string.Format(sqlBuilder.ToString(), studentId);
try
{
SqlDataReader objReader = SQLHelper.GetDataReader(sql);
Student objStudent = new Student();
if (objReader.Read())
{
objStudent.StudentId = Convert.ToInt32(studentId);
objStudent.StudentName = objReader["StudentName"].ToString();
objStudent.Gender = objReader["Gender"].ToString();
objStudent.Birthday = Convert.ToDateTime(objReader["Birthday"]);
objStudent.StudentIdNo = objReader["StudentIdNo"].ToString();
objStudent.CardNo = objReader["CardNo"].ToString();
objStudent.PhoneNumber = objReader["PhoneNumber"].ToString();
objStudent.StudentAddress = objReader["StudentAddress"].ToString();
objStudent.ClassId = Convert.ToInt32(objReader["ClassId"]);
}
objReader.Close();
return(objStudent);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public List <StudentClass> GetAllClasses()
{
string sql = "select ClassId ,ClassName from StudentClass";
SqlDataReader objReader = SQLHelper.GetDataReader(sql);
List <StudentClass> list = new List <StudentClass>();
while (objReader.Read())
{
list.Add(new StudentClass()
{
ClassId = Convert.ToInt32(objReader["ClassId"]),
ClassName = objReader["ClassName"].ToString()
}
);
}
return(list);
}
public List <string> GetAbsentList(string ClassId)
{
string sql = "select FirstName, LastName from Students where StudentId not in (select StudentId from ScoreList) ";
if (ClassId != null && ClassId.Length != 0)
{
sql += " and ClassId = " + ClassId;
}
SqlDataReader reader = SQLHelper.GetDataReader(sql);
List <string> list = new List <string>();
while (reader.Read())
{
list.Add(reader["FirstName"].ToString() + " " + reader["LastName"].ToString());
}
reader.Close();
return(list);
}
public List <Student> GetListByClassId(string className)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select * from [dbo].[Students] ");
sqlBuilder.Append(
"inner join [dbo].[StudentClass] on [dbo].[Students].ClassId = [dbo].[StudentClass].ClassId ");
sqlBuilder.Append("where [dbo].[StudentClass].ClassName like '%{0}%'");
string sql = string.Format(sqlBuilder.ToString(), className);
try
{
List <Student> objStudents = new List <Student>();
SqlDataReader objReaders = SQLHelper.GetDataReader(sql);
while (objReaders.Read())
{
objStudents.Add(new Student()
{
StudentId = Convert.ToInt32(objReaders["StudentId"]),
StudentName = objReaders["StudentName"].ToString(),
Gender = objReaders["Gender"].ToString(),
Birthday = Convert.ToDateTime(objReaders["Birthday"]),
StudentIdNo = objReaders["StudentIdNo"].ToString(),
CardNo = objReaders["CardNo"].ToString(),
PhoneNumber = objReaders["PhoneNumber"].ToString(),
StudentAddress = objReaders["StudentAddress"].ToString(),
ClassId = Convert.ToInt32(objReaders["ClassId"])
});
}
objReaders.Close();
return(objStudents);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public Dictionary <string, string> GetScoreInfo(string ClassId)
{
string sql = "";
if (ClassId == null || ClassId.Length == 0)
{
sql += "select stuCount= count(*),avgCSharp = avg(CSharp),avgDB = avg(SQLServerDB) from ScoreList ;";
sql += "select absentCount = count(*) from Students where StudentId not in (select StudentId from ScoreList)";
}
else // get by Classs
{
sql += "select stuCount= count(*),avgCSharp = avg(CSharp),avgDB = avg(SQLServerDB) from ScoreList ";
sql += " inner join Students on Students.StudentId = ScoreList.StudentId where ClassId = {0};";
sql += "select absentCount = count(*) from Students where StudentId not in (select StudentId from ScoreList) and ClassId={1} ";
}
sql = string.Format(sql, ClassId, ClassId);
SqlDataReader reader = SQLHelper.GetDataReader(sql);
Dictionary <string, string> scoreInfo = null;
if (reader.Read())
{
scoreInfo = new Dictionary <string, string>();
scoreInfo.Add("stuCount", reader["stuCount"].ToString());
scoreInfo.Add("avgCsharp", reader["avgCSharp"].ToString());
scoreInfo.Add("avgDB", reader["avgDB"].ToString());
}
if (reader.NextResult())
{
if (reader.Read())
{
scoreInfo.Add("absentCount", reader["absentCount"].ToString());
}
}
reader.Close();
return(scoreInfo);
}
public SysAdmin Login(SysAdmin objSysAdmin)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("select * from [dbo].[Admins] where LoginId={0} and LoginPwd='{1}'");
string sql = string.Format(sqlBuilder.ToString(), objSysAdmin.LoginId, objSysAdmin.LoginPwd);
try
{
SqlDataReader objReaders = SQLHelper.GetDataReader(sql);
if (objReaders.Read())
{
objSysAdmin.AdminName = objReaders["AdminName"].ToString();
objReaders.Close();
}
return(objSysAdmin);
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
