public async Task<IHttpActionResult> Put(Default_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(Default_privilege_by_type_with_privilege_choices ddata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; string InsertOrUpdateCommand = ""; foreach (User_type_privilege ditem in ddata.privilege_list) { InsertOrUpdateCommand += string.Format("IF NOT EXISTS(select * from {0} where {1} = '{2}' and {3} = {4}) " + "BEGIN " + "INSERT INTO {0} values ('{2}',{4},'{5}') " + "END " + "ELSE " + "BEGIN " + "UPDATE {0} set {6} = '{5}' where {1} = '{2}' and {3} = '{4}' " + "END ", FieldName.TABLE_NAME, FieldName.USER_TYPE_ID, ditem.user_type_id, FieldName.TITLE_CODE, ddata.title_code, ditem.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> SelectByTitle() { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; Default_privilege_by_type_with_privilege_choices result = new Default_privilege_by_type_with_privilege_choices(); 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 selectMainData = string.Format("select {0}.*,{1} " + "from {0},{2} " + "where {3} = {4} " + "and {0}.{5} = {2}.{6} ", FieldName.TABLE_NAME, User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME, FieldName.TITLE_CODE, title_code, FieldName.USER_TYPE_ID, User_type.FieldName.USER_TYPE_ID); string selectSetDefaultData = string.Format("select {0}, {1},1 as {2},{3} " + "from {4},{5} " + "where {1} = {6} and {0} != 7 " + "and not exists(select * from {7} " + "where {7}.{8} = {5}.{1} " + "and {7}.{9} = {4}.{0}) ", User_type.FieldName.USER_TYPE_ID,Title.FieldName.TITLE_CODE,Title_privilege.FieldName.TITLE_PRIVILEGE_CODE, User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME, Title.FieldName.TABLE_NAME,title_code,FieldName.TABLE_NAME,FieldName.TITLE_CODE, FieldName.USER_TYPE_ID); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} END", selectTitle, selectPrivilegeChoices, selectMainData, selectSetDefaultData); 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(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; }