public async Task<IHttpActionResult> Put(Extra_privilege_by_type_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_by_type_with_privilege_choices edata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; string InsertOrUpdateCommand = ""; foreach(User_type_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_TYPE_ID, e.user_type_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_by_type_with_privilege_choices result = new Extra_privilege_by_type_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}] {6} NOT NULL," + "[{3}] INT NOT NULL," + "[{4}] INT NOT NULL," + "[{5}] VARCHAR(40) NOT NULL, " + "PRIMARY KEY([row_num]) " + ") " + "alter table {0} " + "alter column [{2}] {6} collate database_default " + "alter table {0} " + "alter column [{5}] VARCHAR(40) collate database_default ", temp5tablename,FieldName.USER_TYPE_ID,FieldName.CURRI_ID,FieldName.TITLE_CODE, FieldName.TITLE_PRIVILEGE_CODE,User_type.FieldName.USER_TYPE_NAME, DBFieldDataType.CURRI_ID_TYPE ); string insertintotemp5_maindata = string.Format("insert into {9} " + "select {0}.*,{1} from {0},{2} " + "where {3} = {4} and {5} = '{6}' " + "and {0}.{7} = {2}.{8} ", FieldName.TABLE_NAME,User_type.FieldName.USER_TYPE_NAME,User_type.FieldName.TABLE_NAME, FieldName.TITLE_CODE,title_code,FieldName.CURRI_ID, curri_id, FieldName.USER_TYPE_ID,User_type.FieldName.USER_TYPE_ID,temp5tablename ); string insertintotemp5_setdefaultdata = string.Format("insert into {12} " + "select {0}.{1},'{2}' as {3},{4},{5},{6} " + "from {0},{7} " + "where {4} = {8} " + "and not exists " + "(select * from {12} where " + "{0}.{1} = {9} " + "and {0}.{4} = {10}) " + "and {0}.{1} = {7}.{11} ", Default_privilege_by_type.FieldName.TABLE_NAME,Default_privilege_by_type.FieldName.USER_TYPE_ID, curri_id,FieldName.CURRI_ID, Default_privilege_by_type.FieldName.TITLE_CODE, Default_privilege_by_type.FieldName.TITLE_PRIVILEGE_CODE,User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME,title_code,FieldName.USER_TYPE_ID,FieldName.TITLE_CODE, User_type.FieldName.USER_TYPE_ID,temp5tablename ); string selectfromtemp5 = string.Format("select * from {0} order by {1} ", temp5tablename, FieldName.USER_TYPE_ID); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp5, insertintotemp5_maindata, insertintotemp5_setdefaultdata, 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_type_privilege { user_type_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.USER_TYPE_ID].Ordinal]), user_type = item.ItemArray[tabledata.Columns[User_type.FieldName.USER_TYPE_NAME].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; }