public object getByUserId(string userId)
{
Student stu = null;
string selectSql = "SELECT * FROM t_student WHERE userID=@userID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@userID", userId) };
DataSet ds = SQLServerDBUtil.query(selectSql, sqlParas);
if (ds.Tables[0].Rows.Count != 0)
{
stu = new Student();
stu.Id = ds.Tables[0].Rows[0][0].ToString();
stu.StuId = ds.Tables[0].Rows[0][1].ToString();
stu.Name = ds.Tables[0].Rows[0][2].ToString();
stu.Gender = ds.Tables[0].Rows[0][3].ToString();
stu.Birth = ds.Tables[0].Rows[0][4].ToString();
stu.Phone = ds.Tables[0].Rows[0][5].ToString();
stu.Address = ds.Tables[0].Rows[0][6].ToString();
stu.ClassID = ds.Tables[0].Rows[0][7].ToString();
stu.UserID = ds.Tables[0].Rows[0][8].ToString();
}
return(stu);
}
public object get(string id)
{
Attendance att = null;
string selectSql = "SELECT * FROM t_attendance WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@ID", id) };
DataSet ds = SQLServerDBUtil.query(selectSql, sqlParas);
if (ds.Tables[0].Rows.Count != 0)
{
att = new Attendance();
att.Id = ds.Tables[0].Rows[0][0].ToString();
att.Status = ds.Tables[0].Rows[0][1].ToString();
att.Remark = ds.Tables[0].Rows[0][2].ToString();
att.Recorder = ds.Tables[0].Rows[0][3].ToString();
att.RecorderID = ds.Tables[0].Rows[0][4].ToString();
att.StudID = ds.Tables[0].Rows[0][5].ToString();
att.CourTableID = ds.Tables[0].Rows[0][6].ToString();
}
return(att);
}
public object get(string id)
{
CourseTable courTable = null;
string selectSql = "SELECT * FROM t_courseTable WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@ID", id) };
DataSet ds = SQLServerDBUtil.query(selectSql, sqlParas);
if (ds.Tables[0].Rows.Count != 0)
{
courTable = new CourseTable();
courTable.Id = ds.Tables[0].Rows[0][0].ToString();
courTable.Semester = ds.Tables[0].Rows[0][1].ToString();
courTable.Week = ds.Tables[0].Rows[0][2].ToString();
courTable.WeekDay = ds.Tables[0].Rows[0][3].ToString();
courTable.Place = ds.Tables[0].Rows[0][4].ToString();
courTable.CourseTime = ds.Tables[0].Rows[0][5].ToString();
courTable.TeachID = ds.Tables[0].Rows[0][6].ToString();
courTable.ClassID = ds.Tables[0].Rows[0][7].ToString();
courTable.CourId = ds.Tables[0].Rows[0][8].ToString();
}
return(courTable);
}
public object getByUserId(string userId)
{
Staff staff = null;
string selectSql = "SELECT * FROM t_staff WHERE userID=@userID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@userID", userId) };
DataSet ds = SQLServerDBUtil.query(selectSql, sqlParas);
if (ds.Tables[0].Rows.Count != 0)
{
staff = new Staff();
staff.Id = ds.Tables[0].Rows[0][0].ToString();
staff.Name = ds.Tables[0].Rows[0][1].ToString();
staff.Gender = ds.Tables[0].Rows[0][2].ToString();
staff.Birth = ds.Tables[0].Rows[0][3].ToString();
staff.Phone = ds.Tables[0].Rows[0][4].ToString();
staff.Type = ds.Tables[0].Rows[0][5].ToString();
staff.UserId = ds.Tables[0].Rows[0][6].ToString();
}
return(staff);
}
public object get(string id)
{
Teacher teacher = null;
string selectSql = "SELECT * FROM t_teacher WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@ID", id) };
DataSet ds = SQLServerDBUtil.query(selectSql, sqlParas);
if (ds.Tables[0].Rows.Count != 0)
{
teacher = new Teacher();
teacher.Id = ds.Tables[0].Rows[0][0].ToString();
teacher.TeacherId = ds.Tables[0].Rows[0][1].ToString();
teacher.Name = ds.Tables[0].Rows[0][2].ToString();
teacher.Gender = ds.Tables[0].Rows[0][3].ToString();
teacher.Birth = ds.Tables[0].Rows[0][4].ToString();
teacher.Title = ds.Tables[0].Rows[0][5].ToString();
teacher.Phone = ds.Tables[0].Rows[0][6].ToString();
teacher.Email = ds.Tables[0].Rows[0][7].ToString();
teacher.UserID = ds.Tables[0].Rows[0][8].ToString();
}
return(teacher);
}
public object get(string id)
{
Class clazz = null;
string selectSql = "SELECT * FROM t_class WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@ID", id) };
DataSet ds = SQLServerDBUtil.query(selectSql, sqlParas);
if (ds.Tables[0].Rows.Count != 0)
{
clazz = new Class();
clazz.Id = ds.Tables[0].Rows[0][0].ToString();
clazz.Depart = ds.Tables[0].Rows[0][1].ToString();
clazz.Grade = ds.Tables[0].Rows[0][2].ToString();
clazz.Name = ds.Tables[0].Rows[0][3].ToString();
clazz.StudCount = ds.Tables[0].Rows[0][4].ToString();
clazz.MonitorID = ds.Tables[0].Rows[0][5].ToString();
clazz.TeacherID = ds.Tables[0].Rows[0][6].ToString();
}
return(clazz);
}
public DataSet getByClassId(string classId)
{
string selectSql = "SELECT * FROM t_student where classID=@classID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@classID", classId) };
return(SQLServerDBUtil.query(selectSql, sqlParas));
}
public DataSet getAboutMonitor()
{
string selectSql = "SELECT * FROM t_attendance WHERE recorder='班长' AND courTableID in (SELECT ID FROM t_courseTable WHERE semester=@semester)";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@semester", GlobalVars.SEMESTER) };
return(SQLServerDBUtil.query(selectSql, sqlParas));
}
public void deleteByCourseTableId(string ctId)
{
string deleteSql = "DELETE FROM t_attendance WHERE courTableID=@courTableID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@courTableID", ctId) };
SQLServerDBUtil.execute(deleteSql, sqlParas);
}
public DataSet getByTeacherId(string teachId)
{
string selectSql = "SELECT * FROM t_class WHERE teacherID=@teacherID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@teacherID", teachId) };
return(SQLServerDBUtil.query(selectSql, sqlParas));
}
public DataSet getByClassIdAboutTeacher(string classId)
{
string selectSql = "SELECT * FROM t_attendance WHERE recorder='教师' AND studID in (SELECT ID FROM t_student WHERE classID=@classID) AND courTableID in (SELECT ID FROM t_courseTable WHERE semester=@semester)";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@classId", classId),
new SqlParameter("@semester", GlobalVars.SEMESTER) };
return(SQLServerDBUtil.query(selectSql, sqlParas));
}
public DataSet getByParas(string semester, string teacherID)
{
string selectSql = "SELECT * FROM t_courseTable WHERE semester=@semester and teachID=@teachID ORDER BY courseTime";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@semester", semester),
new SqlParameter("@teachID", teacherID) };
return(SQLServerDBUtil.query(selectSql, sqlParas));
}
public DataSet getByClassId(string classId)
{
string selectSql = "SELECT * FROM t_course WHERE ID in (SELECT distinct courId FROM t_courseTable WHERE classID=@classID AND semester=@semester)";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@classID", classId),
new SqlParameter("@semester", GlobalVars.SEMESTER) };
return(SQLServerDBUtil.query(selectSql, sqlParas));
}
public DataSet getByRecordId(string recordId)
{
string selectSql = "SELECT * FROM t_attendance WHERE recorderID=@recordID AND courTableID in (SELECT ID FROM t_courseTable WHERE semester=@semester)";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@semester", GlobalVars.SEMESTER),
new SqlParameter("@recordID", recordId) };
return(SQLServerDBUtil.query(selectSql, sqlParas));
}
public DataSet getAboutTeacher(string week)
{
string selectSql = "SELECT * FROM t_attendance WHERE recorder='教师' AND courTableID in (SELECT ID FROM t_courseTable WHERE semester=@semester AND week=@week)";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@semester", GlobalVars.SEMESTER),
new SqlParameter("@week", week) };
return(SQLServerDBUtil.query(selectSql, sqlParas));
}
public void delete(object obj)
{
Course course = (Course)obj;
string deleteSql = "DELETE FROM t_course WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@ID", course.Id) };
SQLServerDBUtil.execute(deleteSql, sqlParas);
}
public void delete(object obj)
{
Class clazz = (Class)obj;
string deleteSql = "DELETE t_class WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@ID", clazz.Id) };
SQLServerDBUtil.execute(deleteSql, sqlParas);
}
public void delete(object obj)
{
User user = (User)obj;
string deleteSql = "DELETE FROM t_user WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@ID", user.Id) };
SQLServerDBUtil.execute(deleteSql, sqlParas);
}
public void delete(object obj)
{
Staff staff = (Staff)obj;
string deleteSql = "DELETE FROM t_staff WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@ID", staff.Id) };
SQLServerDBUtil.execute(deleteSql, sqlParas);
}
public void delete(object obj)
{
Attendance att = (Attendance)obj;
string deleteSql = "DELETE FROM t_attendance WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@ID", att.Id) };
SQLServerDBUtil.execute(deleteSql, sqlParas);
}
public DataSet getByParas(string semester, string week, string weekDay, string classID)
{
string selectSql = "SELECT * FROM t_courseTable WHERE semester=@semester and week=@week and weekDay=@weekDay and classID=@classID ORDER BY courseTime";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@semester", semester),
new SqlParameter("@week", week),
new SqlParameter("@weekDay", weekDay),
new SqlParameter("@classID", classID) };
return(SQLServerDBUtil.query(selectSql, sqlParas));
}
public DataSet getByTCC(string semester, string teacherID, string classID, string courID)
{
string selectSql = "SELECT * FROM t_courseTable WHERE semester=@semester and teachID=@teachID and classID=@classID and courId=@courID ORDER BY week";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@semester", semester),
new SqlParameter("@teachID", teacherID),
new SqlParameter("@classID", classID),
new SqlParameter("@courID", courID) };
return(SQLServerDBUtil.query(selectSql, sqlParas));
}
public void save(object obj)
{
User user = (User)obj;
string insertSql = "INSERT INTO t_user(username,password,type) VALUES(@username,@password,@type)";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@username", user.UserName),
new SqlParameter("@password", user.Password),
new SqlParameter("@type", user.Type) };
SQLServerDBUtil.execute(insertSql, sqlParas);
}
public void save(object obj)
{
Course course = (Course)obj;
string insertSql = "INSERT INTO t_course(name,credit,schoolHour) VALUES(@name,@credit,@schoolHour)";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@name", course.Name),
new SqlParameter("@credit", course.Credit),
new SqlParameter("@schoolHour", course.SchoolHour) };
SQLServerDBUtil.execute(insertSql, sqlParas);
}
public void update(object obj)
{
User user = (User)obj;
string updateSql = "UPDATE t_user SET username=@username,password=@password,type=@type WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@username", user.UserName),
new SqlParameter("@password", user.Password),
new SqlParameter("@type", user.Type),
new SqlParameter("@ID", user.Id) };
SQLServerDBUtil.execute(updateSql, sqlParas);
}
public void update(object obj)
{
Course course = (Course)obj;
string updateSql = "UPDATE t_course SET name=@name,credit=@credit,schoolHour=@schoolHour WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@name", course.Name),
new SqlParameter("@credit", course.Credit),
new SqlParameter("@schoolHour", course.SchoolHour),
new SqlParameter("@ID", course.Id) };
SQLServerDBUtil.execute(updateSql, sqlParas);
}
public void save(object obj)
{
Staff staff = (Staff)obj;
string insertSql = "INSERT INTO t_staff(name,gender,birth,phone,type,userID) VALUES(@name,@gender,@birth,@phone,@type,@userID)";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@name", staff.Name),
new SqlParameter("@gender", staff.Gender),
new SqlParameter("@birth", staff.Birth),
new SqlParameter("@phone", staff.Phone),
new SqlParameter("@type", staff.Type),
new SqlParameter("@userID", staff.UserId) };
SQLServerDBUtil.execute(insertSql, sqlParas);
}
public void save(object obj)
{
Class clazz = (Class)obj;
string insertSql = "INSERT INTO t_class(depart,grade,name,studCount,monitorID,teacherID) VALUES(@depart,@grade,@name,@studCount,@monitorID,@teacherID)";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@depart", clazz.Depart),
new SqlParameter("@grade", clazz.Grade),
new SqlParameter("@name", clazz.Name),
new SqlParameter("@studCount", clazz.StudCount),
string.IsNullOrEmpty(clazz.MonitorID)? new SqlParameter("@monitorID", DBNull.Value) : new SqlParameter("@monitorID", clazz.MonitorID),
new SqlParameter("@teacherID", clazz.TeacherID) };
SQLServerDBUtil.execute(insertSql, sqlParas);
}
public void save(object obj)
{
Attendance att = (Attendance)obj;
string insertSql = "INSERT INTO t_attendance(status,remark,recorder,recorderID,studID,courTableID) VALUES(@status,@remark,@recorder,@recorderID,@studID,@courTableID)";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@status", att.Status),
new SqlParameter("@remark", att.Remark),
new SqlParameter("@recorder", att.Recorder),
new SqlParameter("@recorderID", att.RecorderID),
new SqlParameter("@studID", att.StudID),
new SqlParameter("@courTableID", att.CourTableID) };
SQLServerDBUtil.execute(insertSql, sqlParas);
}
public void update(object obj)
{
Staff staff = (Staff)obj;
string updateSql = "UPDATE t_staff SET name=@name,gender=@gender,birth=@birth,phone=@phone,type=@type,userID=@userID WHERE ID=@ID";
SqlParameter[] sqlParas = new SqlParameter[] { new SqlParameter("@name", staff.Name),
new SqlParameter("@gender", staff.Gender),
new SqlParameter("@birth", staff.Birth),
new SqlParameter("@phone", staff.Phone),
new SqlParameter("@type", staff.Type),
new SqlParameter("@userID", staff.UserId),
new SqlParameter("@ID", staff.Id) };
SQLServerDBUtil.execute(updateSql, sqlParas);
}