public static String UpdateOrdinalPosition(string listId, string listattribute_id, int ordinalposition) { string sql = "UPDATE listsettings.listattribute SET ordinalposition = CAST(@ordinalposition AS int) WHERE list_id= @list_id AND listattribute_id = @listattribute_id;"; var paramList = new List <KeyValuePair <string, string> >() { new KeyValuePair <string, string>("list_id", listId), new KeyValuePair <string, string>("listattribute_id", listattribute_id), new KeyValuePair <string, string>("ordinalposition", ordinalposition.ToString()) }; try { DataServices.executeSQLStatement(sql, paramList); } catch (Exception ex) { var httpErr = new SynapseHTTPError(); httpErr.ErrorCode = "HTTP.400"; httpErr.ErrorType = "Client Error"; httpErr.ErrorDescription = ex.ToString(); return(JsonConvert.SerializeObject(httpErr, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore })); } return("Ordinal Position Updated"); }
public static String RemoveAttributeFromList(string listId, string attributename) { string sqlCheck = "SELECT ordinalposition FROM listsettings.listattribute WHERE list_id= @list_id AND attributename = @attributename;"; var paramListCheck = new List <KeyValuePair <string, string> >() { new KeyValuePair <string, string>("list_id", listId), new KeyValuePair <string, string>("attributename", attributename), }; DataSet dsCheck = DataServices.DataSetFromSQL(sqlCheck, paramListCheck); DataTable dtCheck = new DataTable(); dtCheck = dsCheck.Tables[0]; int ordinalposition = 0; try { ordinalposition = System.Convert.ToInt16(dtCheck.Rows[0]["ordinalposition"].ToString()); } catch (Exception ex) { var a = ex; } string sql = ""; sql = @"UPDATE listsettings.listattribute SET isselected = false, ordinalposition = null WHERE list_id = @list_id AND attributename = @attributename; UPDATE listsettings.listattribute SET ordinalposition = ordinalposition - 1 WHERE list_id = @list_id AND ordinalposition > CAST(@ordinalposition AS INT); "; var paramList = new List <KeyValuePair <string, string> >() { new KeyValuePair <string, string>("list_id", listId), new KeyValuePair <string, string>("attributename", attributename), new KeyValuePair <string, string>("ordinalposition", ordinalposition.ToString()), }; try { DataServices.executeSQLStatement(sql, paramList); } catch (Exception ex) { var httpErr = new SynapseHTTPError(); httpErr.ErrorCode = "HTTP.400"; httpErr.ErrorType = "Client Error"; httpErr.ErrorDescription = ex.ToString(); return(JsonConvert.SerializeObject(httpErr, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore })); } return("Attribute removed"); }
public static String AddAttributeToList(string listId, string attributename, int ordinalposition) { string sqlCheck = "SELECT COUNT(*) AS recCount FROM listsettings.listattribute WHERE list_id= @list_id AND attributename = @attributename;"; var paramListCheck = new List <KeyValuePair <string, string> >() { new KeyValuePair <string, string>("list_id", listId), new KeyValuePair <string, string>("attributename", attributename), }; DataSet dsCheck = DataServices.DataSetFromSQL(sqlCheck, paramListCheck); DataTable dtCheck = new DataTable(); dtCheck = dsCheck.Tables[0]; int recs = 0; try { recs = System.Convert.ToInt16(dtCheck.Rows[0]["recCount"].ToString()); } catch { } string sql = ""; if (recs == 0) //Do insert { sql = @"INSERT INTO listsettings.listattribute(listattribute_id, list_id, baseviewattribute_id, attributename, datatype, ordinalposition) SELECT uuid_generate_v4() AS listattribute_id, @list_id, baseviewattribute_id, @attributename, datatype, CAST(@ordinalposition AS INT) AS ordinalposition FROM listsettings.baseviewattribute WHERE attributename = @attributename AND baseview_id IN (SELECT baseview_id FROM listsettings.listmanager WHERE list_id = @list_id);"; } else //Do update { sql = @"UPDATE listsettings.listattribute SET isselected = true, ordinalposition = CAST(@ordinalposition AS INT) WHERE list_id = @list_id AND attributename = @attributename;"; } var paramList = new List <KeyValuePair <string, string> >() { new KeyValuePair <string, string>("list_id", listId), new KeyValuePair <string, string>("attributename", attributename), new KeyValuePair <string, string>("ordinalposition", ordinalposition.ToString()), }; try { DataServices.executeSQLStatement(sql, paramList); } catch (Exception ex) { var httpErr = new SynapseHTTPError(); httpErr.ErrorCode = "HTTP.400"; httpErr.ErrorType = "Client Error"; httpErr.ErrorDescription = ex.ToString(); return(JsonConvert.SerializeObject(httpErr, Formatting.Indented, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore })); } return("Attribute added"); }