public static string editAdministrator(string adminID, string adminName, string adminGender, string adminBirthDate, string adminSocialID, string adminTel, string adminAddress, string adminRight)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("UPDATE DB_ConEduMS.dbo.tb_Administrator SET adminName = @adminName,adminGender = @adminGender,adminBirthDate = @adminBirthDate,adminSocialID = @adminSocialID, adminTel = @adminTel,adminAddress = @adminAddress,adminRight =@adminRight WHERE adminID = @adminID;", sqlcon);
cmd.Parameters.AddWithValue("@adminID", adminID);
cmd.Parameters.AddWithValue("@adminName", adminName);
cmd.Parameters.AddWithValue("@adminGender", adminGender);
cmd.Parameters.AddWithValue("@adminBirthDate", adminBirthDate);
cmd.Parameters.AddWithValue("@adminSocialID", adminSocialID);
cmd.Parameters.AddWithValue("@adminTel", adminTel);
cmd.Parameters.AddWithValue("@adminAddress", adminAddress);
cmd.Parameters.AddWithValue("@adminRight", adminRight);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string addTeacher(string teacherID, string teacherName, string teacherGender, string teacherBirthDate, string teacherSocialID, string teacherTel, string teacherAddress, string teacherRemark, string majorID)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("INSERT INTO DB_ConEduMS.dbo.tb_teacher VALUES (@teacherID,@teacherName,@teacherGender,@teacherBirthDate,@teacherSocialID,@teacherTel,@teacherAddress,@teacherRemark,@majorID);", sqlcon);
cmd.Parameters.AddWithValue("@teacherID", teacherID);
cmd.Parameters.AddWithValue("@teacherName", teacherName);
cmd.Parameters.AddWithValue("@teacherGender", teacherGender);
cmd.Parameters.AddWithValue("@teacherBirthDate", teacherBirthDate);
cmd.Parameters.AddWithValue("@teacherSocialID", teacherSocialID);
cmd.Parameters.AddWithValue("@teacherTel", teacherTel);
cmd.Parameters.AddWithValue("@teacherAddress", teacherAddress);
cmd.Parameters.AddWithValue("@teacherRemark", teacherRemark);
cmd.Parameters.AddWithValue("@majorID", majorID);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string editStudent(string stuID, string classID, string majorID, string stuGender, string stuBirthDate, string stuSocialID, string stuTel, string stuAddress, string stuRemark)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("UPDATE DB_ConEduMS.dbo.tb_student SET classID = @classID,majorID = @majorID, stuGender = @stuGender,stuBirthDate = @stuBirthDate,stuSocialID = @stuSocialID, stuTel = @stuTel,stuAddress = @stuAddress,stuRemark =@stuRemark WHERE stuID = @stuID;", sqlcon);
cmd.Parameters.AddWithValue("@stuID", stuID);
cmd.Parameters.AddWithValue("@classID", classID);
cmd.Parameters.AddWithValue("@majorID", majorID);
cmd.Parameters.AddWithValue("@stuGender", stuGender);
cmd.Parameters.AddWithValue("@stuBirthDate", stuBirthDate);
cmd.Parameters.AddWithValue("@stuSocialID", stuSocialID);
cmd.Parameters.AddWithValue("@stuTel", stuTel);
cmd.Parameters.AddWithValue("@stuAddress", stuAddress);
cmd.Parameters.AddWithValue("@stuRemark", stuRemark);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string editTeacher(string teacherID, string teacherName, string teacherGender, string teacherBirthDate, string teacherSocialID, string teacherTel, string teacherAddress, string teacherRemark, string majorID)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("UPDATE DB_ConEduMS.dbo.tb_teacher SET teacherName = @teacherName,teacherGender = @teacherGender,teacherBirthDate = @teacherBirthDate, teacherSocialID = @teacherSocialID,teacherTel = @teacherTel,teacherAddress = @teacherAddress, teacherRemark = @teacherRemark,majorID = @majorID WHERE teacherID = @teacherID;", sqlcon);
cmd.Parameters.AddWithValue("@teacherID", teacherID);
cmd.Parameters.AddWithValue("@teacherName", teacherName);
cmd.Parameters.AddWithValue("@teacherGender", teacherGender);
cmd.Parameters.AddWithValue("@teacherBirthDate", teacherBirthDate);
cmd.Parameters.AddWithValue("@teacherSocialID", teacherSocialID);
cmd.Parameters.AddWithValue("@teacherTel", teacherTel);
cmd.Parameters.AddWithValue("@teacherAddress", teacherAddress);
cmd.Parameters.AddWithValue("@teacherRemark", teacherRemark);
cmd.Parameters.AddWithValue("@majorID", majorID);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string getAllMajors()
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("SELECT * FROM DB_ConEduMS.dbo.tb_major;", sqlcon);
DataSet ds;
ds = SQLserverOper.executeSqlWithReturn(cmd);
int i = 1;
resultStr += "[";
foreach (DataRow dr in ds.Tables[0].Rows)
{
resultStr += "{\"majorID\":\"" + dr[0].ToString().Trim() + "\"," +
"\"majorName\":\"" + dr[1].ToString().Trim() + "\"," +
"\"majorRemark\":\"" + dr[2].ToString().Trim() + "\"" +
"}";
if (i != ds.Tables[0].Rows.Count)
{
resultStr += ",";
}
i++;
}
resultStr += "]";
return(resultStr);
}
public static string delTrainingSchool(string schoolID)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("DELETE FROM DB_ConEduMS.dbo.tb_trainingSchool WHERE schoolID = @schoolID;", sqlcon);
cmd.Parameters.AddWithValue("@schoolID", schoolID);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string delMajor(string majorID)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("DELETE FROM DB_ConEduMS.dbo.tb_major WHERE majorID = @majorID;", sqlcon);
cmd.Parameters.AddWithValue("@majorID", majorID);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string addMajor(string majorID, string majorName, string majorRemark)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("INSERT INTO DB_ConEduMS.dbo.tb_major VALUES (@majorID,@majorName,@majorRemark);", sqlcon);
cmd.Parameters.AddWithValue("@majorID", majorID);
cmd.Parameters.AddWithValue("@majorName", majorName);
cmd.Parameters.AddWithValue("@majorRemark", majorRemark);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string editCourse(string courseID, string courseName, string courseRemark)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("UPDATE DB_ConEduMS.dbo.tb_course SET courseName = @courseName,courseRemark = @courseRemark WHERE courseID = @courseID;", sqlcon);
cmd.Parameters.AddWithValue("@courseID", courseID);
cmd.Parameters.AddWithValue("@courseName", courseName);
cmd.Parameters.AddWithValue("@courseRemark", courseRemark);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string editMajor(string majorID, string majorName, string majorRemark)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("UPDATE DB_ConEduMS.dbo.tb_major SET majorName = @majorName,majorRemark = @majorRemark WHERE majorID = @majorID;", sqlcon);
cmd.Parameters.AddWithValue("@majorID", majorID);
cmd.Parameters.AddWithValue("@majorName", majorName);
cmd.Parameters.AddWithValue("@majorRemark", majorRemark);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string addTrainingSchool(string schoolID, string schoolName, string schoolAddress, string schoolTel, string schoolRemark)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("INSERT INTO DB_ConEduMS.dbo.tb_trainingSchool VALUES (@schoolID,@schoolName,@schoolAddress,@schoolTel,@schoolRemark);", sqlcon);
cmd.Parameters.AddWithValue("@schoolID", schoolID);
cmd.Parameters.AddWithValue("@schoolName", schoolName);
cmd.Parameters.AddWithValue("@schoolAddress", schoolAddress);
cmd.Parameters.AddWithValue("@schoolTel", schoolTel);
cmd.Parameters.AddWithValue("@schoolRemark", schoolRemark);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string getAllStudents()
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("SELECT * FROM DB_ConEduMS.dbo.tb_student;", sqlcon);
DataSet ds;
ds = SQLserverOper.executeSqlWithReturn(cmd);
int i = 1;
resultStr += "[";
foreach (DataRow dr in ds.Tables[0].Rows)
{
resultStr += "{\"stuID\":\"" + dr[0].ToString().Trim() + "\"," +
"\"classID\":\"" + dr[1].ToString().Trim() + "\"," +
"\"majorID\":\"" + dr[2].ToString().Trim() + "\"," +
"\"stuGender\":\"" + dr[3].ToString().Trim() + "\"," +
"\"stuBirthDate\":\"" + dr[4].ToString().Trim() + "\"," +
"\"stuSocialID\":\"" + dr[5].ToString().Trim() + "\"," +
"\"stuTel\":\"" + dr[6].ToString().Trim() + "\"," +
"\"stuAddress\":\"" + dr[7].ToString().Trim() + "\"," +
"\"stuRemark\":\"" + dr[8].ToString().Trim() + "\"" +
"}";
if (i != ds.Tables[0].Rows.Count)
{
resultStr += ",";
}
i++;
}
resultStr += "]";
return(resultStr);
}
public static string addScore(string stuID, string courseID, string semesterID, string teacherID, string classID, string score)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("INSERT INTO DB_ConEduMS.dbo.tb_score VALUES (@stuID,@courseID,@semesterID,@teacherID,@classID,@score);", sqlcon);
cmd.Parameters.AddWithValue("@stuID", stuID);
cmd.Parameters.AddWithValue("@courseID", courseID);
cmd.Parameters.AddWithValue("@semesterID", semesterID);
cmd.Parameters.AddWithValue("@teacherID", teacherID);
cmd.Parameters.AddWithValue("@classID", classID);
cmd.Parameters.AddWithValue("@score", score);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string getAllAdministrators()
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("SELECT * FROM DB_ConEduMS.dbo.tb_Administrator;", sqlcon);
DataSet ds;
ds = SQLserverOper.executeSqlWithReturn(cmd);
int i = 1;
resultStr += "[";
foreach (DataRow dr in ds.Tables[0].Rows)
{
resultStr += "{\"adminID\":\"" + dr[0].ToString().Trim() + "\"," +
"\"adminName\":\"" + dr[1].ToString().Trim() + "\"," +
"\"adminGender\":\"" + dr[2].ToString().Trim() + "\"," +
"\"adminBirthDate\":\"" + dr[3].ToString().Trim() + "\"," +
"\"adminSocialID\":\"" + dr[4].ToString().Trim() + "\"," +
"\"adminTel\":\"" + dr[5].ToString().Trim() + "\"," +
"\"adminAddress\":\"" + dr[6].ToString().Trim() + "\"," +
"\"adminRight\":\"" + dr[7].ToString().Trim() + "\"" +
"}";
if (i != ds.Tables[0].Rows.Count)
{
resultStr += ",";
}
i++;
}
resultStr += "]";
return(resultStr);
}
public static string editTrainingSchool(string schoolID, string schoolName, string schoolAddress, string schoolTel, string schoolRemark)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("UPDATE DB_ConEduMS.dbo.tb_trainingSchool SET schoolName = @schoolName,schoolAddress = @schoolAddress,schoolTel = @schoolTel,schoolRemark = @schoolRemark WHERE schoolID = @schoolID;", sqlcon);
cmd.Parameters.AddWithValue("@schoolID", schoolID);
cmd.Parameters.AddWithValue("@schoolName", schoolName);
cmd.Parameters.AddWithValue("@schoolAddress", schoolAddress);
cmd.Parameters.AddWithValue("@schoolTel", schoolTel);
cmd.Parameters.AddWithValue("@schoolRemark", schoolRemark);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string editScore(string stuID, string courseID, string semesterID, string teacherID, string classID, string score)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("UPDATE DB_ConEduMS.dbo.tb_score SET courseID = @courseID,semesterID = @semesterID,teacherID = @teacherID, classID = @classID,score = @score WHERE stuID = @stuID;", sqlcon);
cmd.Parameters.AddWithValue("@stuID", stuID);
cmd.Parameters.AddWithValue("@courseID", courseID);
cmd.Parameters.AddWithValue("@semesterID", semesterID);
cmd.Parameters.AddWithValue("@teacherID", teacherID);
cmd.Parameters.AddWithValue("@classID", classID);
cmd.Parameters.AddWithValue("@score", score);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string addClass(string classID, string majorID, string schoolID, string counselorName, string counselorTel, string startDate, string endDate)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("INSERT INTO DB_ConEduMS.dbo.tb_class VALUES (@classID,@majorID,@schoolID,@counselorName,@counselorTel,@startDate,@endDate);", sqlcon);
cmd.Parameters.AddWithValue("@classID", classID);
cmd.Parameters.AddWithValue("@majorID", majorID);
cmd.Parameters.AddWithValue("@schoolID", schoolID);
cmd.Parameters.AddWithValue("@counselorName", counselorName);
cmd.Parameters.AddWithValue("@counselorTel", counselorTel);
cmd.Parameters.AddWithValue("@startDate", startDate);
cmd.Parameters.AddWithValue("@endDate", endDate);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string getAllScores()
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("SELECT * FROM DB_ConEduMS.dbo.tb_score;", sqlcon);
DataSet ds;
ds = SQLserverOper.executeSqlWithReturn(cmd);
int i = 1;
resultStr += "[";
foreach (DataRow dr in ds.Tables[0].Rows)
{
resultStr += "{\"stuID\":\"" + dr[0].ToString().Trim() + "\"," +
"\"courseID\":\"" + dr[1].ToString().Trim() + "\"," +
"\"semesterID\":\"" + dr[2].ToString().Trim() + "\"," +
"\"teacherID\":\"" + dr[3].ToString().Trim() + "\"," +
"\"classID\":\"" + dr[4].ToString().Trim() + "\"," +
"\"score\":\"" + dr[5].ToString().Trim() + "\"" +
"}";
if (i != ds.Tables[0].Rows.Count)
{
resultStr += ",";
}
i++;
}
resultStr += "]";
return(resultStr);
}
public static string editClass(string classID, string majorID, string schoolID, string counselorName, string counselorTel, string startDate, string endDate)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("UPDATE DB_ConEduMS.dbo.tb_class SET majorID = @majorID,schoolID = @schoolID,counselorName = @counselorName,counselorTel = @counselorTel, startDate = @startDate,endDate = @endDate WHERE classID = @classID;", sqlcon);
cmd.Parameters.AddWithValue("@classID", classID);
cmd.Parameters.AddWithValue("@majorID", majorID);
cmd.Parameters.AddWithValue("@schoolID", schoolID);
cmd.Parameters.AddWithValue("@counselorName", counselorName);
cmd.Parameters.AddWithValue("@counselorTel", counselorTel);
cmd.Parameters.AddWithValue("@startDate", startDate);
cmd.Parameters.AddWithValue("@endDate", endDate);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string addStudent(string stuID, string classID, string majorID, string stuGender, string stuBirthDate, string stuSocialID, string stuTel, string stuAddress, string stuRemark)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("INSERT INTO DB_ConEduMS.dbo.tb_student VALUES (@stuID,@classID,@majorID,@stuGender,@stuBirthDate,@stuSocialID,@stuTel,@stuAddress,@stuRemark);", sqlcon);
cmd.Parameters.AddWithValue("@stuID", stuID);
cmd.Parameters.AddWithValue("@classID", classID);
cmd.Parameters.AddWithValue("@majorID", majorID);
cmd.Parameters.AddWithValue("@stuGender", stuGender);
cmd.Parameters.AddWithValue("@stuBirthDate", stuBirthDate);
cmd.Parameters.AddWithValue("@stuSocialID", stuSocialID);
cmd.Parameters.AddWithValue("@stuTel", stuTel);
cmd.Parameters.AddWithValue("@stuAddress", stuAddress);
cmd.Parameters.AddWithValue("@stuRemark", stuRemark);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
public static string addAdministrator(string adminID, string adminName, string adminGender, string adminBirthDate, string adminSocialID, string adminTel, string adminAddress, string adminRight)
{
string resultStr = "";
SqlConnection sqlcon = null;
SQLserverOper.open(ref sqlcon);
SqlCommand cmd = new SqlCommand("INSERT INTO DB_ConEduMS.dbo.tb_Administrator VALUES (@adminID,@adminName,@adminGender,@adminBirthDate,@adminSocialID,@adminTel,@adminAddress,@adminRight);", sqlcon);
cmd.Parameters.AddWithValue("@adminID", adminID);
cmd.Parameters.AddWithValue("@adminName", adminName);
cmd.Parameters.AddWithValue("@adminGender", adminGender);
cmd.Parameters.AddWithValue("@adminBirthDate", adminBirthDate);
cmd.Parameters.AddWithValue("@adminSocialID", adminSocialID);
cmd.Parameters.AddWithValue("@adminTel", adminTel);
cmd.Parameters.AddWithValue("@adminAddress", adminAddress);
cmd.Parameters.AddWithValue("@adminRight", adminRight);
SQLserverOper.executeSql(cmd);
resultStr = "success";
return(resultStr);
}
