public async Task <IHttpActionResult> PutToSaveAllPresident(JObject data) { Curriculums_presidents_detail cpdata = data["old_object"].ToObject <Curriculums_presidents_detail>(); oPresident_curriculum datacontext = new oPresident_curriculum(); datacontext.aca_year = Convert.ToInt32(data["aca_year"]); object result = await datacontext.UpdatePresidentData(cpdata); if (result == null) { return(Ok()); } else { return(InternalServerError(new Exception(result.ToString()))); } }
public async Task <object> UpdatePresidentData(Curriculums_presidents_detail data) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } string deletefrompresident = string.Format("delete from {0} where {1} = {2} ", FieldName.TABLE_NAME, FieldName.ACA_YEAR, ParameterName.ACA_YEAR ); string insertintopresidentwithdeletefromcommittee = ""; foreach (KeyValuePair <string, Curri_with_pres_and_cand> kv in data.all_presidents) { foreach (Personnel_brief_detail p in kv.Value.presidents) { //DELETE FROM COMMITTEE insertintopresidentwithdeletefromcommittee += string.Format("delete from {0} where {1} = '{2}' and {3} = {4} and {5} = {6} ", Committee.FieldName.TABLE_NAME, FieldName.CURRI_ID, kv.Key, FieldName.ACA_YEAR, ParameterName.ACA_YEAR, FieldName.TEACHER_ID, p.user_id); //INSERT INTO PRESIDENT insertintopresidentwithdeletefromcommittee += string.Format("insert into {0} values ({1},'{2}',{3}) ", FieldName.TABLE_NAME, p.user_id, kv.Key, ParameterName.ACA_YEAR); } } d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year)); d.iCommand.CommandText = string.Format("BEGIN {0} {1} END", deletefrompresident, insertintopresidentwithdeletefromcommittee); 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> SelectAllCurriculumsAndAllPresidents() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) { return(WebApiApplication.CONNECTDBERRSTRING); } Curriculums_presidents_detail result = new Curriculums_presidents_detail(); string selcurriculumdata = string.Format("select {0}.{1},{2} " + "from {0},{3} " + "where {0}.{1} = {3}.{4} and {5} = {6} ", Cu_curriculum.FieldName.TABLE_NAME, Cu_curriculum.FieldName.CURRI_ID, Cu_curriculum.FieldName.CURR_TNAME, Curriculum_academic.FieldName.TABLE_NAME, Curriculum_academic.FieldName.CURRI_ID, Curriculum_academic.FieldName.ACA_YEAR, ParameterName.ACA_YEAR); string selallpresident = string.Format("select {0}.*,{1},{2},{3},{4} from {0},{5} " + "where {6} = {7} and {8} = {9} ", FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC, Teacher.FieldName.EMAIL, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, FieldName.TEACHER_ID, FieldName.ACA_YEAR, ParameterName.ACA_YEAR); string selallteacherwithcurri = string.Format("select {0}.*,{1},{2},{3},{4} from {0},{5} " + "where {6} = 1 and {0}.{7} = {5}.{8} " + "and {9} in (select {10} from {11} where {12} = {13}) ", User_curriculum.FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC, Teacher.FieldName.EMAIL, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_curriculum.FieldName.USER_ID, User_list.FieldName.USER_ID, User_curriculum.FieldName.CURRI_ID, Curriculum_academic.FieldName.CURRI_ID, Curriculum_academic.FieldName.TABLE_NAME, Curriculum_academic.FieldName.ACA_YEAR, ParameterName.ACA_YEAR); d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year)); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} END", selcurriculumdata, selallpresident, selallteacherwithcurri); try { System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync(); do { if (res.HasRows) { DataTable data = new DataTable(); data.Load(res); if (data.Columns.Count == 2) { foreach (DataRow item in data.Rows) { curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString(); result.all_curri_id.Add(curri_id); result.all_presidents[curri_id] = new Curri_with_pres_and_cand { curri_tname = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString() }; } } else if (data.Columns.Count == 7) { foreach (DataRow item in data.Rows) { curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString(); result.all_presidents[curri_id].presidents.Add(new Personnel_brief_detail { tname = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(), pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()), email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(), user_id = item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal].ToString() }); } } else { foreach (DataRow item in data.Rows) { curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString(); result.all_presidents[curri_id].candidates.Add(new Personnel_brief_detail { tname = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(), pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()), email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(), user_id = item.ItemArray[data.Columns[User_list.FieldName.USER_ID].Ordinal].ToString() }); } } data.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); }