public async Task<IHttpActionResult> Put(Extra_privilege_individual_with_privilege_choices data) { object result = await datacontext.InsertOrUpdate(data); if (result == null) return Ok(); else return InternalServerError(new Exception(result.ToString())); }
public async Task<object> InsertOrUpdate(Extra_privilege_individual_with_privilege_choices edata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; string InsertOrUpdateCommand = ""; foreach (User_privilege e in edata.privilege_list) { InsertOrUpdateCommand += string.Format("IF NOT EXISTS(select * from {0} where {1} = '{2}' and {3} = '{4}' and {5} = {6}) " + "BEGIN " + "INSERT INTO {0} values ('{2}','{4}',{6},{7}) " + "END " + "ELSE " + "BEGIN " + "UPDATE {0} set {8} = {7} where {1} = '{2}' and {3} = '{4}' and {5} = {6} " + "END ", FieldName.TABLE_NAME, FieldName.USER_ID, e.user_id, FieldName.CURRI_ID, edata.curri_id, FieldName.TITLE_CODE, edata.title_code, e.privilege.title_privilege_code, FieldName.TITLE_PRIVILEGE_CODE); } d.iCommand.CommandText = InsertOrUpdateCommand; try { await d.iCommand.ExecuteNonQueryAsync(); } 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> SelectByCurriculumAndTitle() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; Extra_privilege_individual_with_privilege_choices result = new Extra_privilege_individual_with_privilege_choices(); string temp5tablename = "#temp5"; string selectTitle = string.Format("select * from {0} where {1} = {2} ", Title.FieldName.TABLE_NAME, Title.FieldName.TITLE_CODE, title_code); string selectPrivilegeChoices = string.Format("select * from {0} where {1} = {2} ", Title_privilege.FieldName.TABLE_NAME, Title_privilege.FieldName.TITLE_CODE, title_code); string createtabletemp5 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] INT NOT NULL," + "[{2}] {8} NOT NULL," + "[{3}] INT NOT NULL," + "[{4}] INT NOT NULL," + "[{5}] VARCHAR(16) NULL," + "[{6}] VARCHAR(60) NULL," + "[{7}] {9} NULL," + "PRIMARY KEY([row_num])" + ") " + "alter table {0} " + "alter column [{2}] {8} collate database_default " + "alter table {0} " + "alter column [{5}] VARCHAR(16) collate database_default " + "alter table {0} " + "alter column [{6}] VARCHAR(60) collate database_default " + "alter table {0} " + "alter column [{7}] {9} collate database_default ", temp5tablename, FieldName.USER_ID, FieldName.CURRI_ID, FieldName.TITLE_CODE, FieldName.TITLE_PRIVILEGE_CODE, User_list.FieldName.T_PRENAME, User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC, DBFieldDataType.CURRI_ID_TYPE, DBFieldDataType.FILE_NAME_TYPE ); string insertintotemp5_maindata = string.Format("insert into {12} " + "select {0}.*," + "{1} = {2} " + ", {3}, {4} " + "from {0}, {5} " + "where {6} = {7} and {8} = '{9}' " + "and {0}.{10} = {5}.{11} ", FieldName.TABLE_NAME, User_list.FieldName.T_PRENAME, NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME), User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC, User_list.FieldName.TABLE_NAME, FieldName.TITLE_CODE, title_code, FieldName.CURRI_ID, curri_id, FieldName.USER_ID, User_list.FieldName.USER_ID, temp5tablename); string insertintotemp5_setextrapriv = string.Format("insert into {19} " + "select {0}.{1},{2}.{3},{4},{5}," + "{6} = {7} " + ", {8}, {9} " + "from {0}, {10}, {2} " + "where {4} = {11} and {2}.{3} = '{12}' " + "and {0}.{1} = {10}.{13} " + "and {2}.{14} = {0}.{15} " + "and {2}.{3} = {10}.{16} " + "and not exists(" + "select * from {19} where " + "{0}.{1} = {17} " + "and {2}.{4} = {18}" + ") ", User_list.FieldName.TABLE_NAME,User_list.FieldName.USER_ID, Extra_privilege_by_type.FieldName.TABLE_NAME, Extra_privilege_by_type.FieldName.CURRI_ID, Extra_privilege_by_type.FieldName.TITLE_CODE, Extra_privilege_by_type.FieldName.TITLE_PRIVILEGE_CODE, User_list.FieldName.T_PRENAME, NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME), User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC, User_curriculum.FieldName.TABLE_NAME,title_code,curri_id, User_curriculum.FieldName.USER_ID, Extra_privilege_by_type.FieldName.USER_TYPE_ID, User_list.FieldName.USER_TYPE_ID, User_curriculum.FieldName.CURRI_ID, FieldName.USER_ID,FieldName.TITLE_CODE, temp5tablename ); string insertintotemp5_setdefpriv = string.Format("insert into {18} " + "select {0}.{1},{2},{3},{4}," + "{5} = {6} " + ", {7}, {8} " + "from {0}, {9}, {10} " + "where {3} = {11} and {2} = '{12}' " + "and {0}.{1} = {9}.{13} " + "and {0}.{14} = {10}.{15} " + "and not exists(" + "select * from {18} where " + "{0}.{1} = {16} " + "and {10}.{3} = {17}" + ") ", User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, User_curriculum.FieldName.CURRI_ID, Default_privilege_by_type.FieldName.TITLE_CODE, Default_privilege_by_type.FieldName.TITLE_PRIVILEGE_CODE, User_list.FieldName.T_PRENAME, NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME), User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC, User_curriculum.FieldName.TABLE_NAME,Default_privilege_by_type.FieldName.TABLE_NAME, title_code,curri_id, User_curriculum.FieldName.USER_ID, User_list.FieldName.USER_TYPE_ID, Default_privilege_by_type.FieldName.USER_TYPE_ID, FieldName.USER_ID,FieldName.TITLE_CODE, temp5tablename ); string selectfromtemp5 = string.Format("select * from {0} order by {1} ", temp5tablename, FieldName.USER_ID); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} {6} END", createtabletemp5, insertintotemp5_maindata, insertintotemp5_setextrapriv, insertintotemp5_setdefpriv, selectTitle, selectPrivilegeChoices, selectfromtemp5); try { //Set result's curri_id result.curri_id = curri_id; 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(Title.FieldName.NAME)) { //Set title name from title table result result.title_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title.FieldName.TITLE_CODE].Ordinal]); result.name = item.ItemArray[tabledata.Columns[Title.FieldName.NAME].Ordinal].ToString(); } else if (tabledata.Columns.Contains(Title_privilege.FieldName.PRIVILEGE)) { //Set privilege choice for target title result.choices.Add(new Privilege_choice { title_privilege_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]), privilege = item.ItemArray[tabledata.Columns[Title_privilege.FieldName.PRIVILEGE].Ordinal].ToString() }); } else { //Read main privilege data int title_priv_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.TITLE_PRIVILEGE_CODE].Ordinal]); result.privilege_list.Add(new User_privilege { user_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.USER_ID].Ordinal]), t_name = item.ItemArray[tabledata.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[tabledata.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(), file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[tabledata.Columns[User_list.FieldName.FILE_NAME_PIC].Ordinal].ToString()), privilege = new Privilege_choice { title_privilege_code = title_priv_code, //Find privilege caption from choices array privilege = result.choices.First(t => t.title_privilege_code == title_priv_code).privilege } }); } } 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; }