public async Task<object> SelectMaxAcademicYear() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oCurriculum_academic> result = new List<oCurriculum_academic>(); d.iCommand.CommandText = string.Format("select MAX({1})+1 from {0}", FieldName.TABLE_NAME,FieldName.ACA_YEAR); try { object res = await d.iCommand.ExecuteScalarAsync(); if (res != null) { return res; } else { //Reserved for return error string return null; } } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } }
public async Task<object> SelectWithDetail(oCurriculum_academic curriacadata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Questionare_set_detail> result = new List<Questionare_set_detail>(); curri_id = curriacadata.curri_id; aca_year = curriacadata.aca_year; d.iCommand.CommandText = getSelectByCurriculumAcademicCommand(); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { int qid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]); if (result.FirstOrDefault(t => t.questionare_set_id == qid) == null) { result.Add(new Questionare_set_detail { aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), personnel_id = item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]) : 0, questionare_set_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]), date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString() }); } result.First(t => t.questionare_set_id == qid).target.Add(new User_type { user_type_id = Convert.ToInt32(item.ItemArray[data.Columns[Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID].Ordinal]), user_type = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> Update() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Educational_teacher_staff> result = new List<Educational_teacher_staff>(); string updatecmd = string.Format("update {0} set {1} = {2},{3} = {4},{5} = {6},{7} = {8},{9} = {10} where {11} = {12} ", FieldName.TABLE_NAME, FieldName.DEGREE, ParameterName.DEGREE, FieldName.PRE_MAJOR, ParameterName.PRE_MAJOR, FieldName.MAJOR, ParameterName.MAJOR, FieldName.GRAD_YEAR, ParameterName.GRAD_YEAR, FieldName.COLLEGE, ParameterName.COLLEGE, FieldName.EDUCATION_ID, ParameterName.EDUCATION_ID); string selectcmd = GetSelectEducationByPersonnelIdCommand(); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.PERSONNEL_ID, personnel_id)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.DEGREE, degree)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.PRE_MAJOR, pre_major)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.MAJOR, major)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.GRAD_YEAR, grad_year)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.COLLEGE, college)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.EDUCATION_ID, education_id)); d.iCommand.CommandText = string.Format("BEGIN {0} {1} END", updatecmd, selectcmd); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new Educational_teacher_staff { personnel_id = this.personnel_id, college = item.ItemArray[data.Columns[FieldName.COLLEGE].Ordinal].ToString(), degree = Convert.ToChar(item.ItemArray[data.Columns[FieldName.DEGREE].Ordinal]), education_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EDUCATION_ID].Ordinal]), grad_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GRAD_YEAR].Ordinal]), major = item.ItemArray[data.Columns[FieldName.MAJOR].Ordinal].ToString(), pre_major = item.ItemArray[data.Columns[FieldName.PRE_MAJOR].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (System.Data.SqlClient.SqlException ex) { //Handle error from sql execution if (ex.Number == 8152) return "มีรายละเอียดของข้อมูลการศึกษาบางส่วนที่ต้องการบันทึกมีขนาดที่ยาวเกินกำหนด"; return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> SelectWhere() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2} and {3} = {4} and {5} = {6} and {7} = {8}", FieldName.TABLE_NAME, FieldName.INDICATOR_NUM,ParameterName.INDICATOR_NUM,FieldName.SUB_INDICATOR_NUM,ParameterName.SUB_INDICATOR_NUM, FieldName.ACA_YEAR,ParameterName.ACA_YEAR,FieldName.CURRI_ID,ParameterName.CURRI_ID); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.INDICATOR_NUM, indicator_num)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.SUB_INDICATOR_NUM, sub_indicator_num)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id)); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { string h, m; DateTime timeofday = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.TIME].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture); h = timeofday.Hour.ToString(); m = timeofday.Minute.ToString(); curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(); aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]); detail = item.ItemArray[data.Columns[FieldName.DETAIL].Ordinal].ToString(); strength = item.ItemArray[data.Columns[FieldName.STRENGTH].Ordinal].ToString() != "" ? item.ItemArray[data.Columns[FieldName.STRENGTH].Ordinal].ToString() : null; weakness = item.ItemArray[data.Columns[FieldName.WEAKNESS].Ordinal].ToString() != "" ? item.ItemArray[data.Columns[FieldName.WEAKNESS].Ordinal].ToString() : null; improve = item.ItemArray[data.Columns[FieldName.IMPROVE].Ordinal].ToString() != "" ? item.ItemArray[data.Columns[FieldName.IMPROVE].Ordinal].ToString() : null; indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]); sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]); teacher_id = item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal]) : 0; date = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3]; time = (timeofday.Hour > 9 ? "" : "0") + h + '.' + (timeofday.Minute > 9 ? "" : "0") + m; } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return null; }
public async Task<object> SelectWhereByCurriculumAcademic() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oNew_student_count> result = new List<oNew_student_count>(); d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2} and {3} = {4}", FieldName.TABLE_NAME, FieldName.CURRI_ID, ParameterName.CURRI_ID, FieldName.YEAR, ParameterName.YEAR); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.YEAR, year)); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oNew_student_count { num_admis_f = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_ADMIS_F].Ordinal]), num_admis_m = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_ADMIS_M].Ordinal]), num_childstaff_f = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_CHILDSTAFF_F].Ordinal]), num_childstaff_m = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_CHILDSTAFF_M].Ordinal]), num_direct_f = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_DIRECT_F].Ordinal]), num_direct_m = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_DIRECT_M].Ordinal]), num_goodstudy_f = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_GOODSTUDY_F].Ordinal]), num_goodstudy_m = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_GOODSTUDY_M].Ordinal]), num_others_f = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_OTHERS_F].Ordinal]), num_others_m = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NUM_OTHERS_M].Ordinal]), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR].Ordinal]) }); } res.Close(); data.Dispose(); } else { //Reserved for return error string } } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> SelectWithDetailByCurriculum(string curri_id_data) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Research_detail> result = new List<Research_detail>(); curri_id = curri_id_data; d.iCommand.CommandText = getSelectByCurriculumCommand(); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { int rid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]); if (result.FirstOrDefault(r => r.research_id == rid) == null) result.Add(new Research_detail { name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), file_name = item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString(), research_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.RESEARCH_ID].Ordinal]), year_publish = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR_PUBLISH].Ordinal]) }); result.First(r => r.research_id == rid).researcher.Add(new Teacher_with_t_name { teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]), t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> SelectByIndicatorAndCurriculum(oIndicator inddata, string curri_id) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oEvidence> result = new List<oEvidence>(); d.iCommand.CommandText = string.Format("select * from {0} " + "where {1} = {2} and {3} = '{4}' and {5} = {6} order by {7} ", FieldName.TABLE_NAME,FieldName.INDICATOR_NUM,inddata.indicator_num,FieldName.CURRI_ID, curri_id,FieldName.ACA_YEAR,inddata.aca_year,FieldName.EVIDENCE_REAL_CODE); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oEvidence { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), evidence_code = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVIDENCE_CODE].Ordinal]), evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString(), file_name = item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString(), secret = Convert.ToChar(item.ItemArray[data.Columns[FieldName.SECRET].Ordinal]), teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal]), //DANGER NULLABLE ZONE primary_evidence_num = item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal]) : 0, evidence_real_code = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVIDENCE_REAL_CODE].Ordinal]), indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]) }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> SelectWhereByCurriculumAcademic() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oStudent_status_other> result = new List<oStudent_status_other>(); d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2} and {3} = {4}", FieldName.TABLE_NAME, FieldName.CURRI_ID, ParameterName.CURRI_ID, FieldName.YEAR, ParameterName.YEAR); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.YEAR, year)); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oStudent_status_other { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), grad_in_time = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GRAD_IN_TIME].Ordinal]), grad_over_time = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GRAD_OVER_TIME].Ordinal]), move_in = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.MOVE_IN].Ordinal]), quity1 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUITY1].Ordinal]), quity2 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUITY2].Ordinal]), quity3 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUITY3].Ordinal]), quity4 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUITY4].Ordinal]), year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR].Ordinal]) }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> SelectWithBriefDetail() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Committee_with_detail> result = new List<Committee_with_detail>(); d.iCommand.CommandText = string.Format("select {0}.*,{1},{2},{3},{12} from {0},({4}) as {13} where {5} = '{6}' and {7} = {8} and {0}.{9} = {13}.{10} order by {11}", FieldName.TABLE_NAME,Teacher.FieldName.T_PRENAME,Teacher.FieldName.T_NAME,Teacher.FieldName.FILE_NAME_PIC, oTeacher.getSelectTeacherByJoinCommand(),FieldName.CURRI_ID,curri_id,FieldName.ACA_YEAR,aca_year,FieldName.TEACHER_ID,Teacher.FieldName.TEACHER_ID,FieldName.DATE_PROMOTED,Teacher.FieldName.EMAIL, Teacher.FieldName.ALIAS_NAME); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new Committee_with_detail { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()), date_promoted = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_PROMOTED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal]), t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(), email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> Select() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oCu_curriculum> result = new List<oCu_curriculum>(); d.iCommand.CommandText = string.Format("select * from {0}", FieldName.TABLE_NAME); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oCu_curriculum { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), curr_tname = item.ItemArray[data.Columns[FieldName.CURR_TNAME].Ordinal].ToString(), curr_ename = item.ItemArray[data.Columns[FieldName.CURR_ENAME].Ordinal].ToString(), degree_e_bf = item.ItemArray[data.Columns[FieldName.DEGREE_E_BF].Ordinal].ToString(), degree_e_full = item.ItemArray[data.Columns[FieldName.DEGREE_E_FULL].Ordinal].ToString(), degree_t_bf = item.ItemArray[data.Columns[FieldName.DEGREE_T_BF].Ordinal].ToString(), degree_t_full = item.ItemArray[data.Columns[FieldName.DEGREE_T_FULL].Ordinal].ToString(), level = Convert.ToChar(item.ItemArray[data.Columns[FieldName.LEVEL].Ordinal]), period = Convert.ToChar(item.ItemArray[data.Columns[FieldName.PERIOD].Ordinal]), year = item.ItemArray[data.Columns[FieldName.YEAR].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public object Select() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oStudent_count> result = new List<oStudent_count>(); d.iCommand.CommandText = string.Format("select * from {0}", FieldName.TABLE_NAME); try { System.Data.Common.DbDataReader res = d.iCommand.ExecuteReader(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oStudent_count { ny1 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY1].Ordinal]), ny2 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY2].Ordinal]), ny3 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY3].Ordinal]), ny4 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY4].Ordinal]), ny5 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY5].Ordinal]), ny6 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY6].Ordinal]), ny7 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY7].Ordinal]), ny8 = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.NY8].Ordinal]), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.YEAR].Ordinal]) }); } res.Close(); data.Dispose(); } else { //Reserved for return error string } } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> InsertOrUpdate(List<oSelf_evaluation> list) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; string insertcmd = string.Format("insert into {0} values ",FieldName.TABLE_NAME); //Loop each item to gather data to construct sql cmd string updatecmd = ""; foreach(oSelf_evaluation item in list) { insertcmd += string.Format("({0},{1},{2},{3},'{4}','{5}','{6}',{7})", indicator_num, item.sub_indicator_num, item.teacher_id, item.evaluation_score > 0 ? "'" + item.evaluation_score.ToString() + "'" : "null", item.date, item.time, curri_id, aca_year); if (item != list.Last()) insertcmd += ","; updatecmd += string.Format("update {0} set {1} = {2},{3} = {4},{5} = '{6}',{7} = '{8}' where {9} = {10} and {11} = {12} and {13} = '{14}' and {15} = {16} ", FieldName.TABLE_NAME, FieldName.TEACHER_ID, item.teacher_id, FieldName.EVALUATION_SCORE, item.evaluation_score > 0 ? "'" + item.evaluation_score.ToString() + "'" : "null", FieldName.DATE, item.date, FieldName.TIME, item.time, FieldName.INDICATOR_NUM, item.indicator_num, FieldName.SUB_INDICATOR_NUM, item.sub_indicator_num, FieldName.CURRI_ID, item.curri_id, FieldName.ACA_YEAR, item.aca_year); } string selectcmd = string.Format("select * from {0} where {1} = {2} and {3} = '{4}' and {5} = {6}", FieldName.TABLE_NAME, FieldName.INDICATOR_NUM, indicator_num, FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR,aca_year); d.iCommand.CommandText = string.Format("IF NOT EXISTS ({0}) " + "BEGIN " + "{1} " + "END " + "ELSE " + "BEGIN " + "{2} " + "END", selectcmd, insertcmd, updatecmd); try { await d.iCommand.ExecuteNonQueryAsync(); return null; } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } }
public async Task<object> Delete(List<Research_detail> list) { DBConnector d = new DBConnector(); List<string> file_to_delete = new List<string>(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; string deleteprecmd = string.Format("DELETE FROM {0} OUTPUT DELETED.{3} WHERE {1} = '{2}'", FieldName.TABLE_NAME, FieldName.CURRI_ID, list.First().curri_id, FieldName.FILE_NAME); string excludecond = "1=1 "; foreach (Research_detail item in list) { excludecond += string.Format("and {0} != {1} ", FieldName.RESEARCH_ID, item.research_id); } d.iCommand.CommandText = string.Format("{0} and ({1})", deleteprecmd, excludecond); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { file_to_delete.Add( item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString() ); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return file_to_delete; }
public async Task<object> SelectByCurriID() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2}",FieldName.TABLE_NAME,FieldName.CURRI_ID,ParameterName.CURRI_ID); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id)); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(); curr_tname = item.ItemArray[data.Columns[FieldName.CURR_TNAME].Ordinal].ToString(); curr_ename = item.ItemArray[data.Columns[FieldName.CURR_ENAME].Ordinal].ToString(); degree_e_bf = item.ItemArray[data.Columns[FieldName.DEGREE_E_BF].Ordinal].ToString(); degree_e_full = item.ItemArray[data.Columns[FieldName.DEGREE_E_FULL].Ordinal].ToString(); degree_t_bf = item.ItemArray[data.Columns[FieldName.DEGREE_T_BF].Ordinal].ToString(); degree_t_full = item.ItemArray[data.Columns[FieldName.DEGREE_T_FULL].Ordinal].ToString(); level = Convert.ToChar(item.ItemArray[data.Columns[FieldName.LEVEL].Ordinal]); period = Convert.ToChar(item.ItemArray[data.Columns[FieldName.PERIOD].Ordinal]); year = item.ItemArray[data.Columns[FieldName.YEAR].Ordinal].ToString(); } data.Dispose(); } else { res.Close(); return "ไม่พบข้อมูลหลักสูตรที่ท่านเลือก"; } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return null; }
public async Task<object> SelectExcludeUserType(int mode) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oUser_type> result = new List<oUser_type>(); if(mode == 0) d.iCommand.CommandText = string.Format("select * from {0} where {1} != 'ผู้ดูแลระบบ'", FieldName.TABLE_NAME,FieldName.USER_TYPE_NAME); else if(mode == 1) d.iCommand.CommandText = string.Format("select * from {0} where {1} != 'ผู้ดูแลระบบ' and {1} != 'กรรมการหลักสูตร'", FieldName.TABLE_NAME, FieldName.USER_TYPE_NAME); else d.iCommand.CommandText = string.Format("select * from {0} ", FieldName.TABLE_NAME); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oUser_type { user_type_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.USER_TYPE_ID].Ordinal]), user_type = item.ItemArray[data.Columns[FieldName.USER_TYPE_NAME].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> SelectByQuestionIdAsQuestionForm(int qid) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Questionare_question_answer> result = new List<Questionare_question_answer>(); d.iCommand.CommandText = string.Format("select * from {0} where {1} = {2}", FieldName.TABLE_NAME, FieldName.QUESTIONARE_SET_ID, qid); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new Questionare_question_answer { detail = item.ItemArray[data.Columns[FieldName.DETAIL].Ordinal].ToString(), questionare_set_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]), questionare_question_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_QUESTION_ID].Ordinal]) }); } data.Dispose(); } else { //Reserved for return error string res.Close(); return "แบบสอบถามนี้ถูกลบแล้ว"; } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> SelectWhereOrderByWithKeepYearSource(string wherecond, string orderbycol, int? dir,int source_year) { string[] direction = { "ASC", "DESC" }; DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oIndicator> result = new List<oIndicator>(); d.iCommand.CommandText = string.Format("select * from {0} where {1} order by {2} {3}", FieldName.TABLE_NAME, wherecond, orderbycol, ((dir != null) ? direction[dir.Value] : "")); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oIndicator { aca_year = source_year, indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]), indicator_name_t = item.ItemArray[data.Columns[FieldName.INDICATOR_NAME_T].Ordinal].ToString(), indicator_name_e = item.ItemArray[data.Columns[FieldName.INDICATOR_NAME_E].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> SelectWhere(string wherecond) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oPrimary_evidence> result = new List<oPrimary_evidence>(); d.iCommand.CommandText = string.Format("select * from {0} where {1}", FieldName.TABLE_NAME, wherecond); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oPrimary_evidence { primary_evidence_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PRIMARY_EVIDENCE_NUM].Ordinal]), aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]), evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString(), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> SelectByIndicatorWithKeepAcaYearSource(oIndicator inddata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oSub_indicator> result = new List<oSub_indicator>(); d.iCommand.CommandText = string.Format("select * from {0} where {1} = (select max(j.{1}) from {0} as j where j.{1} <= {2}) and {3} = {4}", FieldName.TABLE_NAME, FieldName.ACA_YEAR,inddata.aca_year,FieldName.INDICATOR_NUM,inddata.indicator_num); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oSub_indicator { aca_year = inddata.aca_year, indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]), sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]), sub_indicator_name = item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NAME].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> InsertOrUpdate() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; d.iCommand.CommandText = string.Format("if not exists(select * from {0} where {9} = {2} and {10} = {3} and {11} = {1} and {12} = {8}) " + "insert into {0} values ({1},{2},{3},{4},{5},{17},{18},{19},{6},{7},{8}) " + "else " + "update {0} set {13}={7},{14}={6},{15}={4}, {16}={5},{20} = {17},{21} = {18},{22} = {19} where {9} = {2} and {10} = {3} and {11} = {1} and {12} = {8} ", FieldName.TABLE_NAME, ParameterName.ACA_YEAR, ParameterName.INDICATOR_NUM, ParameterName.SUB_INDICATOR_NUM, ParameterName.TEACHER_ID, ParameterName.DETAIL, ParameterName.DATE, ParameterName.TIME, ParameterName.CURRI_ID, FieldName.INDICATOR_NUM,FieldName.SUB_INDICATOR_NUM,FieldName.ACA_YEAR,FieldName.CURRI_ID, FieldName.TIME, FieldName.DATE,FieldName.TEACHER_ID, FieldName.DETAIL, /*17*/ParameterName.STRENGTH, ParameterName.WEAKNESS, ParameterName.IMPROVE, /*20*/FieldName.STRENGTH, FieldName.WEAKNESS, FieldName.IMPROVE ); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.INDICATOR_NUM, indicator_num)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.SUB_INDICATOR_NUM, sub_indicator_num)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.TEACHER_ID, teacher_id)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.DETAIL, detail)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.STRENGTH, strength)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.WEAKNESS, weakness)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.IMPROVE, improve)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.DATE, date)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.TIME, time)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id)); try { await d.iCommand.ExecuteNonQueryAsync(); return null; } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } }
public object Select() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oAun_book> result = new List<oAun_book>(); d.iCommand.CommandText = string.Format("select * from {0}", FieldName.TABLE_NAME); try { System.Data.Common.DbDataReader res = d.iCommand.ExecuteReader(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oAun_book { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), file_name = item.ItemArray[data.Columns[FieldName.FILE_NAME].Ordinal].ToString(), personnel_id = item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]) : 0, date = Convert.ToDateTime(item.ItemArray[data.Columns[Self_evaluation.FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3] }); } res.Close(); data.Dispose(); } else { //Reserved for return error string } } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> SelectTeacherIdAndTName(string curri_id) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Teacher_with_t_name> result = new List<Teacher_with_t_name>(); d.iCommand.CommandText = string.Format("select * from ({0}) as {6} where exists(select * from {1} where {6}.{2} = {1}.{3} and {4}='{5}')", getSelectTeacherByJoinCommand(), User_curriculum.FieldName.TABLE_NAME,FieldName.TEACHER_ID,User_curriculum.FieldName.USER_ID,User_curriculum.FieldName.CURRI_ID,curri_id, FieldName.ALIAS_NAME); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new Teacher_with_t_name { teacher_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal]), t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[FieldName.T_NAME].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> Select() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oTitle> result = new List<oTitle>(); d.iCommand.CommandText = string.Format("select * from {0}", FieldName.TABLE_NAME); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add(new oTitle { name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), title_code = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.TITLE_CODE].Ordinal]) }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> InsertQuestionAnswer(Questionare_question_form qdata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; string insertintoquestionareresobj = string.Format("insert into {0} values ",Questionare_result_obj.FieldName.TABLE_NAME); foreach(Questionare_question_answer item in qdata.question_list) { insertintoquestionareresobj += string.Format("({0},{1})", item.questionare_question_id, item.answer); if (item != qdata.question_list.Last()) insertintoquestionareresobj += ","; } string insertintoquestionareressub = string.Format("insert into {0} values ({1},'{2}')", Questionare_result_sub.FieldName.TABLE_NAME, qdata.question_list.First().questionare_set_id, qdata.suggestion); string truecasecmd = string.Format("BEGIN {0} {1} END", insertintoquestionareresobj, insertintoquestionareressub); d.iCommand.CommandText = string.Format("if exists (select * from {0} where {1} = {2}) {3} else return ", Questionare_set.FieldName.TABLE_NAME, Questionare_set.FieldName.QUESTIONARE_SET_ID, qdata.question_list.First().questionare_set_id, truecasecmd); try { int rowaffacted = await d.iCommand.ExecuteNonQueryAsync(); if (rowaffacted > 0) return null; else return "แบบสอบถามนี้ถูกลบแล้ว"; } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } }
public async Task<object> insertNewUserType(List<string> usrtypedata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; string insertintousrtypeanddefpriv = ""; foreach (string type in usrtypedata) { //check whether target user_type is already exists! insertintousrtypeanddefpriv += string.Format("if not exists (select * from {0} where {1} = '{2}') ", FieldName.TABLE_NAME, FieldName.USER_TYPE_NAME, type); insertintousrtypeanddefpriv += string.Format("BEGIN insert into {0} values ('{1}') ", FieldName.TABLE_NAME, type); //insert default privilege as value 1 for every privilege to target user type insertintousrtypeanddefpriv += string.Format("insert into {0} " + "select * from " + "(select {1} from {2} where {3} = '{4}') as targetusrtype, " + "(select {5}, {6} from {7} where {6} = 1) as titleprivdefault END ", Default_privilege_by_type.FieldName.TABLE_NAME, FieldName.USER_TYPE_ID, FieldName.TABLE_NAME, FieldName.USER_TYPE_NAME, type, Title_privilege.FieldName.TITLE_CODE, Title_privilege.FieldName.TITLE_PRIVILEGE_CODE, Title_privilege.FieldName.TABLE_NAME); } d.iCommand.CommandText = insertintousrtypeanddefpriv; try { await d.iCommand.ExecuteNonQueryAsync(); return null; } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } }
public async Task<object> InsertOrUpdate() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; string deletefromcommittee = string.Format("delete from {0} where {1} = {2} and {3} = {4} and {5} = {6} ", Committee.FieldName.TABLE_NAME, FieldName.CURRI_ID, ParameterName.CURRI_ID, FieldName.ACA_YEAR, ParameterName.ACA_YEAR, FieldName.TEACHER_ID, ParameterName.TEACHER_ID); d.iCommand.CommandText = deletefromcommittee + string.Format("IF NOT EXISTS (select * from {0} where {1} = {2} and {3} = {4}) " + "BEGIN " + "INSERT INTO {0} VALUES " + "({5}, {2},{4}) " + "END " + "ELSE " + "BEGIN " + "UPDATE {0} SET {6} = {5} where {1} = {2} and {3} = {4} " + "END", FieldName.TABLE_NAME, FieldName.CURRI_ID, ParameterName.CURRI_ID, FieldName.ACA_YEAR, ParameterName.ACA_YEAR, ParameterName.TEACHER_ID, FieldName.TEACHER_ID); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.TEACHER_ID, teacher_id)); try { await d.iCommand.ExecuteNonQueryAsync(); return null; } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } }
public async Task<object> SelectFileDownloadLink() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; d.iCommand.CommandText = string.Format("select {0} from {1} where {2} = {3} and {4} = {5}", FieldName.FILE_NAME,FieldName.TABLE_NAME, FieldName.CURRI_ID,ParameterName.CURRI_ID, FieldName.ACA_YEAR,ParameterName.ACA_YEAR); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.CURRI_ID, curri_id)); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year)); try { object result = await d.iCommand.ExecuteScalarAsync(); if (result != null) { file_name = result.ToString(); return null; } else { //Reserved for return error string return "notfound"; } } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } }
public async Task<object> Insert(List<UsernamePassword> list, List<string> target_curri_id_list) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<string> result = new List<string>(); string temp5tablename = "#temp5"; string temp6tablename = "#temp6"; string temp7tablename = "#temp7"; string createtabletemp5 = string.Format("CREATE TABLE {0}(" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] VARCHAR(60) NULL," + "PRIMARY KEY ([row_num])) " + "ALTER TABLE {0} " + "ALTER COLUMN {1} VARCHAR(60) COLLATE DATABASE_DEFAULT ", temp5tablename, FieldName.EMAIL); string createtabletemp6 = string.Format("CREATE TABLE {0}(" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] INT NULL," + "PRIMARY KEY ([row_num])) ", temp6tablename, User_list.FieldName.USER_ID); string createtabletemp7 = string.Format("CREATE TABLE {0}(" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] {2} NULL," + "PRIMARY KEY ([row_num])) " + "ALTER TABLE {0} " + "ALTER COLUMN {1} {2} COLLATE DATABASE_DEFAULT ", temp7tablename, User_curriculum.FieldName.CURRI_ID,DBFieldDataType.CURRI_ID_TYPE); string insertintotemp7 = string.Format("insert into {0} values ", temp7tablename); int len = insertintotemp7.Length; foreach (string curriitem in target_curri_id_list) { if (insertintotemp7.Length <= len) insertintotemp7 += string.Format("('{0}')", curriitem); else insertintotemp7 += string.Format(",('{0}')", curriitem); } string insertintousercurri = ""; if (insertintotemp7.Length > len) insertintousercurri = string.Format("insert into {0} " + "select {1},{2} from {3},{4} ", User_curriculum.FieldName.TABLE_NAME, User_curriculum.FieldName.USER_ID, User_curriculum.FieldName.CURRI_ID, temp6tablename, temp7tablename); else insertintotemp7 = ""; string insertcmd = ""; foreach (UsernamePassword item in list) { string ts = DateTime.Now.GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[93]; insertcmd += string.Format( "IF NOT EXISTS(select * from {0} where {1} = '{2}' or {3} = '{2}') " + "begin " + "insert into {4} " + "select * from (insert into {0} ({5}, {1}, {6}, {3}, {7}, {15}) output inserted.{8} " + "values ('{9}', '{2}', '{10}', '{2}', '{11}', '{2}')) as outputinsert " + "insert into {12} ({13}) select {8} from {4} " + insertintousercurri + " " + "delete from {4} " + "end " + "else " + "begin " + "insert into {14} values ('{2}') " + "end ",User_list.FieldName.TABLE_NAME, User_list.FieldName.USERNAME,item.username, User_list.FieldName.EMAIL,temp6tablename, User_list.FieldName.USER_TYPE_ID,FieldName.PASSWORD,FieldName.TIMESTAMP, User_list.FieldName.USER_ID, /*****9****/ 1,item.password,ts, /****12****/ FieldName.TABLE_NAME,FieldName.TEACHER_ID,temp5tablename, User_list.FieldName.T_NAME ); } string selectcmd = string.Format("select {1} from {0} ", temp5tablename, FieldName.EMAIL); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END ", createtabletemp5,createtabletemp6,createtabletemp7, insertintotemp7,insertcmd,selectcmd); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { result.Add( item.ItemArray[data.Columns[FieldName.EMAIL].Ordinal].ToString() ); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } if (result.Count != 0) return result; else return null; }
public async Task<object> SelectByCurriculumAcademic() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Gallery_detail> result = new List<Gallery_detail>(); d.iCommand.CommandText = getSelectByCurriculumAcademicCommand(false); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]); if (result.FirstOrDefault(g => g.gallery_id == gallery_id) == null) { string real_t_prename; if (item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() == "อาจารย์") real_t_prename = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()); else real_t_prename = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString(); result.Add(new Gallery_detail { aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), personnel_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]), t_name = real_t_prename + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]), date_created = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_CREATED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), pictures = new List<Picture>() }); } result.First(g => g.gallery_id == gallery_id).pictures.Add(new Picture { gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[Picture.FieldName.GALLERY_ID].Ordinal]), file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString(), caption = item.ItemArray[data.Columns[Picture.FieldName.CAPTION].Ordinal].ToString() }); } data.Dispose(); } else { //Reserved for return error string } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } return result; }
public async Task<object> UpdateGalleryWithSelect(Gallery_detail gdata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Gallery_detail> result = new List<Gallery_detail>(); Gallery_detail dummyfordeletepictures = new Gallery_detail(); string temp1tablename = "#temp1"; string updatepicturecmd = ""; string ifexistscond = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME, FieldName.GALLERY_ID, gdata.gallery_id); string insertintopicturecmd = string.Format("insert into {0} values ", Picture.FieldName.TABLE_NAME); string deletefrompicturecmd = string.Format("delete from {0} output deleted.{1} where {2} = {3} ", Picture.FieldName.TABLE_NAME, Picture.FieldName.FILE_NAME, Picture.FieldName.GALLERY_ID, gdata.gallery_id); string excludecond = "1=1 "; int len = insertintopicturecmd.Length; string createtabletemp1 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] {2} NULL," + "PRIMARY KEY ([row_num])) " + "ALTER TABLE {0} " + "ALTER COLUMN {1} {2} COLLATE DATABASE_DEFAULT " , temp1tablename, Picture.FieldName.FILE_NAME, DBFieldDataType.FILE_NAME_TYPE); string updategallerycmd = string.Format("update {0} set {1} = '{2}', {3} = '{4}' where {5} = {6} ", FieldName.TABLE_NAME, FieldName.PERSONNEL_ID, gdata.personnel_id, FieldName.NAME, gdata.name, FieldName.GALLERY_ID, gdata.gallery_id); foreach (Picture p in gdata.pictures) { if (p.gallery_id != 0) { updatepicturecmd += string.Format("update {0} set {1} = '{2}' where {3} = {4} and {5} = '{6}' ", Picture.FieldName.TABLE_NAME, Picture.FieldName.CAPTION, p.caption, Picture.FieldName.GALLERY_ID, gdata.gallery_id, Picture.FieldName.FILE_NAME, p.file_name); //Gen delete cond excludecond += string.Format("and {0} != '{1}' ", Picture.FieldName.FILE_NAME, p.file_name); } else { if (insertintopicturecmd.Length <= len) insertintopicturecmd += string.Format("({0},'{1}','{2}')", gdata.gallery_id, p.file_name, p.caption); else insertintopicturecmd += string.Format(",({0},'{1}','{2}')", gdata.gallery_id, p.file_name, p.caption); } } if (insertintopicturecmd.Length <= len) insertintopicturecmd = ""; deletefrompicturecmd += string.Format("and ({0}) ", excludecond); string insertintotemp1 = string.Format("INSERT INTO {0} " + "select * from " + "({1}) " + "as outputdelete ", temp1tablename, deletefrompicturecmd); curri_id = gdata.curri_id; aca_year = gdata.aca_year; string selectcmd = getSelectByCurriculumAcademicCommand(true); d.iCommand.CommandText = string.Format("{0} BEGIN {1} {2} {3} {4} {5} {6} END",ifexistscond, createtabletemp1,updategallerycmd,updatepicturecmd, insertintotemp1,insertintopicturecmd,selectcmd); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); foreach (DataRow item in data.Rows) { gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]); if (gallery_id != 0) { //If date is not null (means result is row with picture data) if (result.FirstOrDefault(g => g.gallery_id == gallery_id) == null) { string real_t_prename; if (item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() == "อาจารย์") real_t_prename = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()); else real_t_prename = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString(); result.Add(new Gallery_detail { aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), personnel_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]), t_name = real_t_prename + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]), date_created = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_CREATED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), pictures = new List<Picture>() }); } result.First(g => g.gallery_id == gallery_id).pictures.Add(new Picture { gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[Picture.FieldName.GALLERY_ID].Ordinal]), file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString(), caption = item.ItemArray[data.Columns[Picture.FieldName.CAPTION].Ordinal].ToString() }); } else { dummyfordeletepictures.pictures.Add(new Picture { file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString() }); } } data.Dispose(); } else { //Reserved for return error string res.Close(); return "ไม่พบข้อมูลอัลบั้มรูปภาพที่ต้องการแก้ไขในระบบ"; } res.Close(); } catch (Exception ex) { //Handle error from sql execution return ex.Message; } finally { //Whether it success or not it must close connection in order to end block d.SQLDisconnect(); } result.Add(dummyfordeletepictures); return result; }