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> 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> 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> 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 object Select() { 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}", 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 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 object SelectWhere(string wherecond) { 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}", FieldName.TABLE_NAME, wherecond); 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 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> 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_status_other> result = new List<oStudent_status_other>(); 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_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]) }); } 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> 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 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 object SelectWhere(string wherecond) { 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}",FieldName.TABLE_NAME,wherecond); 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 oSub_indicator { aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), 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() }); } 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 object Select() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<oCurriculum_academic> result = new List<oCurriculum_academic>(); 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 oCurriculum_academic { curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_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> 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> 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> InsertNewGalleryWithSelect(Gallery_detail gdata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Gallery_detail> result = new List<Gallery_detail>(); string temp1tablename = "#temp1"; string temp2tablename = "#temp2"; string createtabletemp1 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] INT NOT NULL," + "PRIMARY KEY ([row_num])) ", temp1tablename, FieldName.GALLERY_ID); string createtabletemp2 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] {3} NULL," + "[{2}] VARCHAR(MAX) NULL," + "PRIMARY KEY ([row_num])) " + "ALTER TABLE {0} " + "ALTER COLUMN {1} {3} COLLATE DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN {2} VARCHAR(MAX) COLLATE DATABASE_DEFAULT " , temp2tablename, Picture.FieldName.FILE_NAME,Picture.FieldName.CAPTION, DBFieldDataType.FILE_NAME_TYPE); string insertintotemp1 = string.Format("INSERT INTO {0} " + "select * from " + "(insert into {1}({2},{3},{4},{5},{6}) output inserted.{7} values " + "('{8}','{9}','{10}','{11}',{12})) " + "as outputinsert ", temp1tablename, FieldName.TABLE_NAME, FieldName.PERSONNEL_ID, FieldName.NAME, FieldName.DATE_CREATED, FieldName.CURRI_ID, FieldName.ACA_YEAR, FieldName.GALLERY_ID, gdata.personnel_id, gdata.name, gdata.date_created, gdata.curri_id, gdata.aca_year); string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null,null)", temp2tablename); foreach (Picture p in gdata.pictures) { insertintotemp2 += string.Format(",('{0}','{1}')", p.file_name,p.caption); } string insertintopicture = string.Format(" INSERT INTO {0} " + "select {1},{2},{3} from {4},{5} where {2} is not null ", Picture.FieldName.TABLE_NAME, FieldName.GALLERY_ID, Picture.FieldName.FILE_NAME, Picture.FieldName.CAPTION, temp1tablename, temp2tablename); curri_id = gdata.curri_id; aca_year = gdata.aca_year; string selectcmd = getSelectByCurriculumAcademicCommand(false); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp1, createtabletemp2, insertintotemp1, insertintotemp2, insertintopicture, 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 (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> Delete(List<Gallery_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} WHERE {1} = '{2}' and {3} = {4} ", FieldName.TABLE_NAME, FieldName.CURRI_ID, list.First().curri_id, FieldName.ACA_YEAR, list.First().aca_year); string excludecond = "1=1 "; foreach (Gallery_detail item in list) { excludecond += string.Format("and {0} != {1} ", FieldName.GALLERY_ID, item.gallery_id); } string deletefullcmd = string.Format("{0} and ({1})", deleteprecmd, excludecond); string temp1tablename = "#temp1"; string createtabletemp1 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] {2} NOT 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 insertintotemp1 = string.Format("INSERT INTO {0} " + "SELECT {1} from {2} where ({3}) " + "and {4} in " + "(select {5} from {6} where {7} = '{8}' and {9} = {10}) ", temp1tablename, Picture.FieldName.FILE_NAME, Picture.FieldName.TABLE_NAME, excludecond, Picture.FieldName.GALLERY_ID, FieldName.GALLERY_ID, FieldName.TABLE_NAME, FieldName.CURRI_ID, list.First().curri_id, FieldName.ACA_YEAR, list.First().aca_year); string selcmd = string.Format("select {0} from {1} ", Picture.FieldName.FILE_NAME, temp1tablename); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} END", createtabletemp1, insertintotemp1, deletefullcmd,selcmd); 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[Picture.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> 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> UpdateResearchWithSelect(Research_detail rdata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Research_detail> result = new List<Research_detail>(); string ifexistscond = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME, FieldName.RESEARCH_ID, rdata.research_id); string temp1tablename = "#temp1"; string createtabletemp1 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] {2} NOT NULL," + "PRIMARY KEY([row_num])) " + "ALTER TABLE {0} " + "ALTER COLUMN {1} {2} COLLATE DATABASE_DEFAULT ", temp1tablename, FieldName.FILE_NAME,DBFieldDataType.FILE_NAME_TYPE); string insertintotemp1 = string.Format("INSERT INTO {0} " + "select * from " + "(update {1} set {2} = '{3}', {4} = '{5}', {6} = {7} " + "OUTPUT deleted.{4} where {8} = {9}) as outputupdate ", temp1tablename, FieldName.TABLE_NAME, FieldName.NAME, rdata.name, FieldName.FILE_NAME, rdata.file_name, FieldName.YEAR_PUBLISH, rdata.year_publish, FieldName.RESEARCH_ID, rdata.research_id); string deletefromresearchowner = string.Format("DELETE FROM {0} where {1} = {2} ", Research_owner.FieldName.TABLE_NAME, Research_owner.FieldName.RESEARCH_ID, rdata.research_id); string insertintoresearchowner = string.Format("INSERT INTO {0} values ", Research_owner.FieldName.TABLE_NAME); foreach (Teacher_with_t_name t in rdata.researcher) { insertintoresearchowner += string.Format("({0},{1})", rdata.research_id, t.teacher_id); if (t != rdata.researcher.Last()) insertintoresearchowner += ","; } string selectcmd = string.Format("select temp1out.{6},r.*,{15}.{1},{15}.{2} from " + "(select {3}.{4},{3}.{5},{3}.{6}," + "{3}.{7}, {3}.{8},{9} from {3}, {10} where " + "{5} = '{11}' and {3}.{4} = {10}.{12}) as r,(select {6} from {14}) as temp1out,({0}) as {15} where r.{9} = {15}.{13} order by {7} ", oTeacher.getSelectTeacherByJoinCommand(), Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, FieldName.TABLE_NAME, FieldName.RESEARCH_ID, FieldName.CURRI_ID, FieldName.FILE_NAME, FieldName.NAME, FieldName.YEAR_PUBLISH, Research_owner.FieldName.TEACHER_ID, Research_owner.FieldName.TABLE_NAME, rdata.curri_id, Research_owner.FieldName.RESEARCH_ID, Teacher.FieldName.TEACHER_ID,temp1tablename,Teacher.FieldName.ALIAS_NAME); d.iCommand.CommandText = string.Format("{0} BEGIN {1} {2} {3} {4} {5} END",ifexistscond, createtabletemp1, insertintotemp1, deletefromresearchowner, insertintoresearchowner, selectcmd); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); //get to-be delete file_name set in file_name property of main object for future use file_name = data.Rows[0].ItemArray[0].ToString(); 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[3].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(); 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> InsertNewResearchWithSelect(Research_detail rdata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Research_detail> result = new List<Research_detail>(); string temp1tablename = "#temp1"; string temp2tablename = "#temp2"; string createtabletemp1 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] INT NOT NULL," + "PRIMARY KEY ([row_num])) ", temp1tablename, FieldName.RESEARCH_ID); string createtabletemp2 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] INT NULL," + "PRIMARY KEY ([row_num])) " , temp2tablename, Research_owner.FieldName.TEACHER_ID); string insertintotemp1 = string.Format("INSERT INTO {0} " + "select * from " + "(insert into {1} output inserted.{2} values " + "('{3}', '{4}', {5}, '{6}')) as outputinsert ", temp1tablename,FieldName.TABLE_NAME,FieldName.RESEARCH_ID, rdata.curri_id,rdata.name,rdata.year_publish,rdata.file_name); string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null)", temp2tablename); foreach(Teacher_with_t_name item in rdata.researcher) insertintotemp2 += string.Format(",({0})", item.teacher_id); string insertintoresowner = string.Format(" INSERT INTO {0} " + "select {1},{2} from {3},{4} where {2} is not null ", Research_owner.FieldName.TABLE_NAME, FieldName.RESEARCH_ID, Research_owner.FieldName.TEACHER_ID, temp1tablename, temp2tablename); curri_id = rdata.curri_id; string selectcmd = getSelectByCurriculumCommand(); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp1, createtabletemp2, insertintotemp1, insertintotemp2, insertintoresowner, 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) { 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> UpdateUserData(User_information_with_privilege_information userdata) { DBConnector d = new DBConnector(); User_information_with_privilege_information result = new User_information_with_privilege_information(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; string temp80tablename = "#temp80"; string createtabletemp80 = string.Format("create table {0}(" + "[row_num] int identity(1, 1) not null," + "[file_name_pic_del] {1} null," + "primary key([row_num])) " + "alter table {0} " + "alter column[file_name_pic_del] {1} collate database_default ", temp80tablename, DBFieldDataType.FILE_NAME_TYPE); string mainupdatecmd = ""; if(userdata.information.file_name_pic == null) { mainupdatecmd = string.Format("update {0} set {1} = '{2}', {3} = '{4}', {5} = '{6}', {7} = '{8}'," + "{9} = '{10}', {11} = '{12}' where {13} = {14} ", User_list.FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, userdata.information.t_prename, Teacher.FieldName.T_NAME, userdata.information.t_name, Teacher.FieldName.E_PRENAME, userdata.information.e_prename, Teacher.FieldName.E_NAME, userdata.information.e_name, Teacher.FieldName.TEL, userdata.information.tel, Teacher.FieldName.ADDR, userdata.information.addr, User_list.FieldName.USER_ID, userdata.user_id); } else { mainupdatecmd = string.Format("insert into {17} " + "select * from " + "(update {0} set {1} = '{2}', {3} = '{4}', {5} = '{6}', {7} = '{8}'," + "{9} = '{10}', {11} = '{12}', {13} = '{14}' output deleted.{13} where {15} = {16}) as outputupdate ", User_list.FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, userdata.information.t_prename, Teacher.FieldName.T_NAME, userdata.information.t_name, Teacher.FieldName.E_PRENAME, userdata.information.e_prename, Teacher.FieldName.E_NAME, userdata.information.e_name, Teacher.FieldName.TEL, userdata.information.tel, Teacher.FieldName.ADDR, userdata.information.addr, Teacher.FieldName.FILE_NAME_PIC, userdata.information.file_name_pic, User_list.FieldName.USER_ID, userdata.user_id,temp80tablename); } //email must UNIQUE string emailupdatecmd = string.Format("if not exists (select * from {0} where {1} = '{2}' and {3} != {4}) " + "BEGIN " + "update {0} set {1} = '{2}' where {3} = {4} " + "END ", User_list.FieldName.TABLE_NAME, Teacher.FieldName.EMAIL, userdata.information.email, User_list.FieldName.USER_ID, userdata.user_id); string updateteachertable = ""; string deletefromtechin = ""; string insertintotechin = ""; if (userdata.user_type == "อาจารย์") { updateteachertable = string.Format("update {0} set {1} = '{2}' where {3} = {4} ", Teacher.FieldName.TABLE_NAME, Teacher.FieldName.STATUS, userdata.information.status, Teacher.FieldName.TEACHER_ID, userdata.user_id); deletefromtechin = string.Format("delete from {0} where {1} = {2} ", Technical_interested.FieldName.TABLE_NAME,Technical_interested.FieldName.TEACHER_ID, userdata.user_id); if (userdata.information.interest.Count != 0) { insertintotechin = string.Format("insert into {0} values ", Technical_interested.FieldName.TABLE_NAME); int insertintotechinlength = insertintotechin.Length; foreach (string topic in userdata.information.interest) { if (insertintotechin.Length <= insertintotechinlength) insertintotechin += string.Format("({0},'{1}')", userdata.user_id, topic); else insertintotechin += string.Format(",({0},'{1}')", userdata.user_id, topic); } } } string deletefromeducationcmd = ""; if (userdata.user_type != "นักศึกษา") { deletefromeducationcmd = string.Format("delete from {0} where {1} = {2} ", Educational_teacher_staff.FieldName.TABLE_NAME, Educational_teacher_staff.FieldName.PERSONNEL_ID, userdata.user_id); string excludecmd = "1=1 "; foreach (Educational_teacher_staff e in userdata.information.education) excludecmd += string.Format("and {0} != {1} ",Educational_teacher_staff.FieldName.EDUCATION_ID,e.education_id); deletefromeducationcmd += string.Format("and ({0}) ", excludecmd); } string selectuserdatacmd = ""; if (userdata.user_type == "อาจารย์") selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 0, "#temp99"); else if (userdata.user_type == "เจ้าหน้าที่") selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 1, "#temp98"); else if (userdata.user_type == "นักศึกษา") selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 2, "#temp97"); else if (userdata.user_type == "ศิษย์เก่า") selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 3, "#temp96"); else if (userdata.user_type == "บริษัท") selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 4, "#temp95"); else if (userdata.user_type == "ผู้ประเมินจากภายนอก") selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 5, "#temp94"); else if (userdata.user_type == "ผู้ดูแลระบบ") selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 6, "#temp93"); else selectuserdatacmd = getSelectUserDataCommand(userdata.user_id.ToString(), 7, "#temp92"); string selectfiletodelcmd = string.Format("select * from {0} ", temp80tablename); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} {6} {7} {8} END ", createtabletemp80, mainupdatecmd, emailupdatecmd, updateteachertable, deletefromtechin, insertintotechin, deletefromeducationcmd, selectuserdatacmd, selectfiletodelcmd); file_name_pic = null; try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable tabledata = new DataTable(); tabledata.Load(res); foreach (DataRow item in tabledata.Rows) { if (tabledata.Columns.Contains(Teacher.FieldName.T_PRENAME)) { //1 retrieve user_data from pre-defined select table command string usrtype = item.ItemArray[tabledata.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(); if (usrtype == "อาจารย์") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]); else if (usrtype == "เจ้าหน้าที่") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Staff.FieldName.STAFF_ID].Ordinal]); else if (usrtype == "นักศึกษา" || usrtype == "ศิษย์เก่า") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Student.FieldName.USER_ID].Ordinal]); else if (usrtype == "บริษัท") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Company.FieldName.COMPANY_ID].Ordinal]); else if (usrtype == "ผู้ประเมินจากภายนอก") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Assessor.FieldName.ASSESSOR_ID].Ordinal]); else if (usrtype == "ผู้ดูแลระบบ") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Admin.FieldName.ADMIN_ID].Ordinal]); else result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[User_list.FieldName.USER_ID].Ordinal]); result.username = item.ItemArray[tabledata.Columns[Teacher.FieldName.USERNAME].Ordinal].ToString(); result.user_type = usrtype; //********************************************** result.information.addr = item.ItemArray[tabledata.Columns[Teacher.FieldName.ADDR].Ordinal].ToString(); result.information.citizen_id = item.ItemArray[tabledata.Columns[Teacher.FieldName.CITIZEN_ID].Ordinal].ToString(); result.information.email = item.ItemArray[tabledata.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(); result.information.tel = item.ItemArray[tabledata.Columns[Teacher.FieldName.TEL].Ordinal].ToString(); result.information.gender = item.ItemArray[tabledata.Columns[Teacher.FieldName.GENDER].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.GENDER].Ordinal]) : ' '; result.information.file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[tabledata.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()); result.information.timestamp = item.ItemArray[tabledata.Columns[Teacher.FieldName.TIMESTAMP].Ordinal].ToString(); result.information.e_name = item.ItemArray[tabledata.Columns[Teacher.FieldName.E_NAME].Ordinal].ToString(); result.information.e_prename = item.ItemArray[tabledata.Columns[Teacher.FieldName.E_PRENAME].Ordinal].ToString(); result.information.t_name = item.ItemArray[tabledata.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(); result.information.t_prename = item.ItemArray[tabledata.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString(); result.information.SetPassword(item.ItemArray[tabledata.Columns[Teacher.FieldName.PASSWORD].Ordinal].ToString()); if (usrtype == "อาจารย์") { result.information.degree = item.ItemArray[tabledata.Columns[Teacher.FieldName.DEGREE].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.DEGREE].Ordinal]) : ' '; result.information.position = item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal]) : ' '; result.information.personnel_type = item.ItemArray[tabledata.Columns[Teacher.FieldName.PERSONNEL_TYPE].Ordinal].ToString(); result.information.person_id = item.ItemArray[tabledata.Columns[Teacher.FieldName.PERSON_ID].Ordinal].ToString(); result.information.room = item.ItemArray[tabledata.Columns[Teacher.FieldName.ROOM].Ordinal].ToString(); result.information.status = item.ItemArray[tabledata.Columns[Teacher.FieldName.STATUS].Ordinal].ToString(); result.information.alive = item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[tabledata.Columns[Teacher.FieldName.ALIVE].Ordinal]) : -1; } else if (usrtype == "เจ้าหน้าที่") { result.information.room = item.ItemArray[tabledata.Columns[Staff.FieldName.ROOM].Ordinal].ToString(); } else if (usrtype == "บริษัท") { result.information.company_name = item.ItemArray[tabledata.Columns[Company.FieldName.COMPANY_NAME].Ordinal].ToString(); } else if (usrtype == "ผู้ประเมินจากภายนอก") { } else if (usrtype == "นักศึกษา") { } else if (usrtype == "ศิษย์เก่า") { } } else if (tabledata.Columns.Contains(Educational_teacher_staff.FieldName.COLLEGE)) { //2 retrieve education data(all user type except student) result.information.education.Add(new Educational_teacher_staff { college = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.COLLEGE].Ordinal].ToString(), degree = Convert.ToChar(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.DEGREE].Ordinal].ToString()), grad_year = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal]) : 0, major = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.MAJOR].Ordinal].ToString(), personnel_id = result.user_id, education_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.EDUCATION_ID].Ordinal]), pre_major = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.PRE_MAJOR].Ordinal].ToString() }); } else if (tabledata.Columns.Contains("user_curri_id")) { //3 retrieve user_curri_id which user is in result.curri_id_in.Add(item.ItemArray[tabledata.Columns["user_curri_id"].Ordinal].ToString()); } else if (tabledata.Columns.Contains("pres_curri_id")) { //4 retrieve president_in (pres_curri_id, aca_year) when user is? (teacher only) if (result.president_in == null) result.president_in = new Dictionary<string, List<int>>(); string curri_id = item.ItemArray[tabledata.Columns["pres_curri_id"].Ordinal].ToString(); if (!result.president_in.ContainsKey(curri_id)) { result.president_in.Add(curri_id, new List<int>()); } result.president_in[curri_id].Add(Convert.ToInt32(item.ItemArray[tabledata.Columns[President_curriculum.FieldName.ACA_YEAR].Ordinal])); } else if (tabledata.Columns.Contains("comm_curri_id")) { //5 retrieve committee_in (comm_curri_id, aca_year) when user is? (teacher only) if (result.committee_in == null) result.committee_in = new Dictionary<string, List<int>>(); string curri_id = item.ItemArray[tabledata.Columns["comm_curri_id"].Ordinal].ToString(); if (!result.committee_in.ContainsKey(curri_id)) { result.committee_in.Add(curri_id, new List<int>()); } result.committee_in[curri_id].Add(Convert.ToInt32(item.ItemArray[tabledata.Columns[Committee.FieldName.ACA_YEAR].Ordinal])); } else if (tabledata.Columns.Contains(Technical_interested.FieldName.TOPIC_INTERESTED)) { //6 retrieve topic_interested (teacher only) result.information.interest.Add(item.ItemArray[tabledata.Columns[Technical_interested.FieldName.TOPIC_INTERESTED].Ordinal].ToString()); } else if (tabledata.Columns.Contains("evid_curri_id")) { //7 retrieve not_send_primary (teacher only ? evid_curri_id,curr_tname, aca_year, evidence_name) if (result.not_send_primary == null) result.not_send_primary = new List<Evidence_brief_detail>(); result.not_send_primary.Add(new Evidence_brief_detail { curri_id = item.ItemArray[tabledata.Columns["evid_curri_id"].Ordinal].ToString(), curr_tname = item.ItemArray[tabledata.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString(), aca_year = Convert.ToInt32(item.ItemArray[tabledata.Columns[Primary_evidence.FieldName.ACA_YEAR].Ordinal]), evidence_name = item.ItemArray[tabledata.Columns[Primary_evidence.FieldName.EVIDENCE_NAME].Ordinal].ToString() }); } else if (tabledata.Columns.Contains(Extra_privilege.FieldName.TITLE_CODE)) { //8 retrieve privilege (use predefined select from temp table cmd) string curri_id = item.ItemArray[tabledata.Columns[User_curriculum.FieldName.CURRI_ID].Ordinal].ToString(); if (Convert.ToInt32(item.ItemArray[tabledata.Columns["privilege_type"].Ordinal]) == 1) { //Add normal privilege if (!result.privilege.ContainsKey(curri_id)) { result.privilege.Add(curri_id, new Dictionary<int, int>()); } result.privilege[curri_id][Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_CODE].Ordinal])] = Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]); } else { //Add committee privilege if (result.committee_privilege == null) result.committee_privilege = new Dictionary<string, Dictionary<int, int>>(); if (!result.committee_privilege.ContainsKey(curri_id)) { result.committee_privilege.Add(curri_id, new Dictionary<int, int>()); } result.committee_privilege[curri_id][Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_CODE].Ordinal])] = Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]); } } else if (tabledata.Columns.Contains("file_name_pic_del")) { //get file name pic to delete file_name_pic = item.ItemArray[tabledata.Columns["file_name_pic_del"].Ordinal].ToString(); } } tabledata.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) break; } } while (!res.IsClosed); 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> selectUserData(int usrid) { DBConnector d = new DBConnector(); User_information_with_privilege_information result = new User_information_with_privilege_information(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; d.iCommand.CommandText = string.Format("if exists (select * from {0} where {1} = {2}) " + "{3} " + "else if exists (select * from {4} where {5} = {2}) " + "{6} " + "else if exists (select * from {7} where {8} = {2}) " + "{9} " + "else if exists (select * from {10} where {11} = {2}) " + "{12} " + "else if exists (select * from {13} where {14} = {2}) " + "{15} " + "else if exists (select * from {16} where {17} = {2}) " + "{18} " + "else if exists (select * from {19} where {20} = {2}) " + "{21} " + "else if exists (select * from {22} where {23} = {2}) " + "{24} ", Teacher.FieldName.TABLE_NAME, Teacher.FieldName.TEACHER_ID, usrid, getSelectUserDataCommand(usrid.ToString(), 0,"#temp99"), Staff.FieldName.TABLE_NAME, Staff.FieldName.STAFF_ID, getSelectUserDataCommand(usrid.ToString(), 1,"#temp98"), Student.FieldName.TABLE_NAME, Student.FieldName.USER_ID, getSelectUserDataCommand(usrid.ToString(), 2,"#temp97"), Alumni.ExtraFieldName.TABLE_NAME, Alumni.FieldName.USER_ID, getSelectUserDataCommand(usrid.ToString(), 3,"#temp96"), Company.FieldName.TABLE_NAME, Company.FieldName.COMPANY_ID, getSelectUserDataCommand(usrid.ToString(), 4,"#temp95"), Assessor.FieldName.TABLE_NAME, Assessor.FieldName.ASSESSOR_ID, getSelectUserDataCommand(usrid.ToString(), 5,"#temp94"), Admin.FieldName.TABLE_NAME, Admin.FieldName.ADMIN_ID, getSelectUserDataCommand(usrid.ToString(), 6,"#temp93"), User_list.FieldName.TABLE_NAME,User_list.FieldName.USER_ID, getSelectUserDataCommand(usrid.ToString(), 7, "#temp92")); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable tabledata = new DataTable(); tabledata.Load(res); foreach (DataRow item in tabledata.Rows) { if (tabledata.Columns.Contains(Teacher.FieldName.T_PRENAME)) { //1 retrieve user_data from pre-defined select table command string usrtype = item.ItemArray[tabledata.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(); if (usrtype == "อาจารย์") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Teacher.FieldName.TEACHER_ID].Ordinal]); else if (usrtype == "เจ้าหน้าที่") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Staff.FieldName.STAFF_ID].Ordinal]); else if (usrtype == "นักศึกษา" || usrtype == "ศิษย์เก่า") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Student.FieldName.USER_ID].Ordinal]); else if (usrtype == "บริษัท") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Company.FieldName.COMPANY_ID].Ordinal]); else if (usrtype == "ผู้ประเมินจากภายนอก") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Assessor.FieldName.ASSESSOR_ID].Ordinal]); else if (usrtype == "ผู้ดูแลระบบ") result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Admin.FieldName.ADMIN_ID].Ordinal]); else result.user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[User_list.FieldName.USER_ID].Ordinal]); result.username = item.ItemArray[tabledata.Columns[Teacher.FieldName.USERNAME].Ordinal].ToString(); result.user_type = usrtype; //********************************************** result.information.addr = item.ItemArray[tabledata.Columns[Teacher.FieldName.ADDR].Ordinal].ToString(); result.information.citizen_id = item.ItemArray[tabledata.Columns[Teacher.FieldName.CITIZEN_ID].Ordinal].ToString(); result.information.email = item.ItemArray[tabledata.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(); result.information.tel = item.ItemArray[tabledata.Columns[Teacher.FieldName.TEL].Ordinal].ToString(); result.information.gender = item.ItemArray[tabledata.Columns[Teacher.FieldName.GENDER].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.GENDER].Ordinal]) : ' '; result.information.file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[tabledata.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()); result.information.timestamp = item.ItemArray[tabledata.Columns[Teacher.FieldName.TIMESTAMP].Ordinal].ToString(); result.information.e_name = item.ItemArray[tabledata.Columns[Teacher.FieldName.E_NAME].Ordinal].ToString(); result.information.e_prename = item.ItemArray[tabledata.Columns[Teacher.FieldName.E_PRENAME].Ordinal].ToString(); result.information.t_name = item.ItemArray[tabledata.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(); result.information.t_prename = item.ItemArray[tabledata.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString(); result.information.SetPassword(item.ItemArray[tabledata.Columns[Teacher.FieldName.PASSWORD].Ordinal].ToString()); if (usrtype == "อาจารย์") { result.information.degree = item.ItemArray[tabledata.Columns[Teacher.FieldName.DEGREE].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.DEGREE].Ordinal]) : ' '; result.information.position = item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal].ToString() != "" ? Convert.ToChar(item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal]) : ' '; result.information.personnel_type = item.ItemArray[tabledata.Columns[Teacher.FieldName.PERSONNEL_TYPE].Ordinal].ToString(); result.information.person_id = item.ItemArray[tabledata.Columns[Teacher.FieldName.PERSON_ID].Ordinal].ToString(); result.information.room = item.ItemArray[tabledata.Columns[Teacher.FieldName.ROOM].Ordinal].ToString(); result.information.status = item.ItemArray[tabledata.Columns[Teacher.FieldName.STATUS].Ordinal].ToString(); result.information.alive = item.ItemArray[tabledata.Columns[Teacher.FieldName.POSITION].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[tabledata.Columns[Teacher.FieldName.ALIVE].Ordinal]) : -1; } else if (usrtype == "เจ้าหน้าที่") { result.information.room = item.ItemArray[tabledata.Columns[Staff.FieldName.ROOM].Ordinal].ToString(); } else if (usrtype == "บริษัท") { result.information.company_name = item.ItemArray[tabledata.Columns[Company.FieldName.COMPANY_NAME].Ordinal].ToString(); } else if (usrtype == "ผู้ประเมินจากภายนอก") { } else if (usrtype == "นักศึกษา") { } else if (usrtype == "ศิษย์เก่า") { } } else if (tabledata.Columns.Contains(Educational_teacher_staff.FieldName.COLLEGE)) { //2 retrieve education data(all user type except student) result.information.education.Add(new Educational_teacher_staff { college = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.COLLEGE].Ordinal].ToString(), degree = Convert.ToChar(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.DEGREE].Ordinal].ToString()), grad_year = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal]) : 0, major = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.MAJOR].Ordinal].ToString(), personnel_id = result.user_id, education_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.EDUCATION_ID].Ordinal]), pre_major = item.ItemArray[tabledata.Columns[Educational_teacher_staff.FieldName.PRE_MAJOR].Ordinal].ToString() }); } else if (tabledata.Columns.Contains("user_curri_id")) { //3 retrieve user_curri_id which user is in result.curri_id_in.Add(item.ItemArray[tabledata.Columns["user_curri_id"].Ordinal].ToString()); } else if (tabledata.Columns.Contains("pres_curri_id")) { //4 retrieve president_in (pres_curri_id, aca_year) when user is? (teacher only) if (result.president_in == null) result.president_in = new Dictionary<string, List<int>>(); string curri_id = item.ItemArray[tabledata.Columns["pres_curri_id"].Ordinal].ToString(); if (!result.president_in.ContainsKey(curri_id)) { result.president_in.Add(curri_id, new List<int>()); } result.president_in[curri_id].Add(Convert.ToInt32(item.ItemArray[tabledata.Columns[President_curriculum.FieldName.ACA_YEAR].Ordinal])); } else if (tabledata.Columns.Contains("comm_curri_id")) { //5 retrieve committee_in (comm_curri_id, aca_year) when user is? (teacher only) if (result.committee_in == null) result.committee_in = new Dictionary<string, List<int>>(); string curri_id = item.ItemArray[tabledata.Columns["comm_curri_id"].Ordinal].ToString(); if (!result.committee_in.ContainsKey(curri_id)) { result.committee_in.Add(curri_id, new List<int>()); } result.committee_in[curri_id].Add(Convert.ToInt32(item.ItemArray[tabledata.Columns[Committee.FieldName.ACA_YEAR].Ordinal])); } else if (tabledata.Columns.Contains(Technical_interested.FieldName.TOPIC_INTERESTED)) { //6 retrieve topic_interested (teacher only) result.information.interest.Add(item.ItemArray[tabledata.Columns[Technical_interested.FieldName.TOPIC_INTERESTED].Ordinal].ToString()); } else if (tabledata.Columns.Contains("evid_curri_id")) { //7 retrieve not_send_primary (teacher only ? evid_curri_id,curr_tname, aca_year, evidence_name) if (result.not_send_primary == null) result.not_send_primary = new List<Evidence_brief_detail>(); result.not_send_primary.Add(new Evidence_brief_detail { curri_id = item.ItemArray[tabledata.Columns["evid_curri_id"].Ordinal].ToString(), curr_tname = item.ItemArray[tabledata.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString(), aca_year = Convert.ToInt32(item.ItemArray[tabledata.Columns[Primary_evidence.FieldName.ACA_YEAR].Ordinal]), evidence_name = item.ItemArray[tabledata.Columns[Primary_evidence.FieldName.EVIDENCE_NAME].Ordinal].ToString() }); } else if (tabledata.Columns.Contains(Extra_privilege.FieldName.TITLE_CODE)) { //8 retrieve privilege (use predefined select from temp table cmd) string curri_id = item.ItemArray[tabledata.Columns[User_curriculum.FieldName.CURRI_ID].Ordinal].ToString(); if (Convert.ToInt32(item.ItemArray[tabledata.Columns["privilege_type"].Ordinal]) == 1) { //Add normal privilege if (!result.privilege.ContainsKey(curri_id)) { result.privilege.Add(curri_id, new Dictionary<int, int>()); } result.privilege[curri_id][Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_CODE].Ordinal])] = Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]); } else { //Add committee privilege if (result.committee_privilege == null) result.committee_privilege = new Dictionary<string, Dictionary<int, int>>(); if (!result.committee_privilege.ContainsKey(curri_id)) { result.committee_privilege.Add(curri_id, new Dictionary<int, int>()); } result.committee_privilege[curri_id][Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_CODE].Ordinal])] = Convert.ToInt32(item.ItemArray[tabledata.Columns[Extra_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]); } } } tabledata.Dispose(); } else if (!res.IsClosed) { if (!res.NextResult()) break; } } while (!res.IsClosed); 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; }
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> 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> 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 object UpdatePassword(string preferoldpassword,ref string newpassword,int user_id) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; d.iCommand.CommandText = string.Format("select {0} from {1} where {2} = {3}", Teacher.FieldName.PASSWORD,User_list.FieldName.TABLE_NAME,User_list.FieldName.USER_ID,user_id); try { object oldpass = d.iCommand.ExecuteScalar(); if (oldpass != null) { PasswordVerificationResult result = hasher.VerifyHashedPassword(oldpass.ToString(), preferoldpassword); if(result == PasswordVerificationResult.Success || result == PasswordVerificationResult.SuccessRehashNeeded) { newpassword = hasher.HashPassword(newpassword); d.iCommand.CommandText = string.Format("update {0} set {1} = '{2}' where {3} = {4}", User_list.FieldName.TABLE_NAME, Teacher.FieldName.PASSWORD, newpassword, User_list.FieldName.USER_ID, user_id); d.iCommand.ExecuteNonQuery(); return null; } else { return "รหัสผ่านเก่าไม่ถูกต้อง"; } } 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(); } }