public IHttpActionResult Put(Guid id, [FromBody] JObject fields) { JObject result; using (var connection = DataUtils.GetConnection()) using (var transaction = connection.BeginTransaction()) using (var facade = FacadeObjectFactory.CreateMembership()) { facade.CurrentKey = id.ToString(); facade.SelectByKey(connection, transaction); facade.Merge(fields); facade.Update(connection, transaction); facade.LoadRelatedData(connection, transaction); facade.ProcessRoundTripEvents(connection, transaction); result = GetFields(facade); transaction.Commit(); } return(Json(result)); }
// GET: api/mb_membership/5 public IHttpActionResult Get(Guid id) { using (var connection = DataUtils.GetConnection()) using (var transaction = connection.BeginTransaction()) using (var facade = FacadeObjectFactory.CreateMembership()) { facade.CurrentKey = id.ToString(); facade.SelectByKey(connection, transaction); var fields = GetFields(facade); return(Json(fields)); } }
// GET: api/mb_membership /// <summary> /// Get a list of memberships, filtered by a where condition /// </summary> /// <param name="where">JSON encoded list of fields to search the database for</param> /// <remarks> /// ?where={ "field1": "value", "field2": { "eq": "value }, "field3": { "gt": 5 }, "field4": "IS NULL" } /// </remarks> /// <returns>List of memberships</returns> public IHttpActionResult Get([FromUri] string where) { JArray results = new JArray(); JObject whereFields = null; Dictionary <string, object> searchFields = new Dictionary <string, object>(); try { whereFields = JObject.Parse(where); } catch (Exception) { } if (whereFields != null) { using (var facade = FacadeObjectFactory.CreateMembership()) { // Quick and dirty field validation var verify = new Regex(@"^[a-z0-9_]+$"); foreach (var pair in whereFields) { if (!verify.IsMatch(pair.Key)) { continue; } if (pair.Value == null) { continue; } var field = facade.GetField(pair.Key); var parts = pair.Key.Split('_'); if (field == null) { continue; } if (parts[0] != "mbr" && parts[0] != "mbt") { continue; } searchFields.Add(pair.Key, pair.Value); } } } // Add top just for sanity reasons var SQL = @"SELECT TOP 100 mbr_key FROM mb_membership JOIN mb_member_type ON mbt_key = mbr_mbt_key AND mbt_delete_flag = 0 WHERE mbr_delete_flag = 0"; using (var connection = DataUtils.GetConnection()) using (var transaction = connection.BeginTransaction()) using (var cmd = new NfDbCommand(SQL, connection, transaction)) { foreach (var field in searchFields.Keys) { cmd.CommandText += Environment.NewLine + $" AND [{field}]"; var search = searchFields[field]; if (search is JObject searchOperation) { if (searchOperation.Count != 1) { continue; } var op = (JProperty)searchOperation.First; switch (op.Name) { case "eq": SearchUtils.JTokenParse(cmd, op.Value, field, "="); break; case "ne": SearchUtils.JTokenParse(cmd, op.Value, field, "!="); break; case "gt": SearchUtils.JTokenParse(cmd, op.Value, field, ">"); break; case "ge": SearchUtils.JTokenParse(cmd, op.Value, field, ">="); break; case "lt": SearchUtils.JTokenParse(cmd, op.Value, field, "<"); break; case "le": SearchUtils.JTokenParse(cmd, op.Value, field, "<="); break; } } else if (search is JToken searchValue) { SearchUtils.JTokenParse(cmd, searchValue, field, "="); } } using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { Guid mbr_key = reader.GetGuid(0); using (var facade = FacadeObjectFactory.CreateMembership()) { facade.CurrentKey = mbr_key.ToString(); facade.SelectByKey(connection, transaction); // Suggest using a facade-to-model mapping solution here instead var fields = GetFields(facade); results.Add(fields); } } } } return(Json(results)); }