public string GetObjectHistory(string synapsenamespace, string synapseentityname, string id) { string fieldList = SynapseEntityHelperServices.GetEntityAttributes(synapsenamespace, synapseentityname, "1"); if (string.IsNullOrEmpty(fieldList)) { fieldList = " * "; } string keyAttribute = SynapseEntityHelperServices.GetEntityKeyAttribute(synapsenamespace, synapseentityname); if (string.IsNullOrWhiteSpace(keyAttribute)) { throw new InterneuronBusinessException(errorCode: 400, errorMessage: "Invalid Parameters supplied - unable to retrieve key attribute column", "Client Error"); } string sql = "SELECT " + fieldList + " FROM entitystore." + synapsenamespace + "_" + synapseentityname + " WHERE " + keyAttribute + " = @p_keyAttributeValue" + " ORDER BY _sequenceid;"; var paramList = new List <KeyValuePair <string, object> >() { new KeyValuePair <string, object>("p_keyAttributeValue", id) }; DataSet ds = new DataSet(); ds = DataServices.DataSetFromSQL(sql, paramList); DataTable dt = ds.Tables[0]; var json = DataServices.ConvertDataTabletoJSONString(dt); return(json); }
public string ExecuteSQL([FromBody] string data) { var parameterList = new List <KeyValuePair <string, object> >(); int index = 0; string returnValue = string.Empty; if (!string.IsNullOrEmpty(data)) { var dataDict = JsonConvert.DeserializeObject <SQLData>(data); if (dataDict.parameters != null && dataDict.parameters.Count > 0) { foreach (var item in dataDict.parameters) { parameterList.Insert(index, new KeyValuePair <string, object>(item.name, item.value)); index++; } } try { DataSet ds = DataServices.DataSetFromSQL(dataDict.query, parameterList, databaseName: dbStore); returnValue = DataServices.ConvertDataTabletoJSONString(ds.Tables[0]); } catch (Exception ex) { HttpContext.Response.StatusCode = (int)HttpStatusCode.InternalServerError; returnValue = "{ \"Status\": \"Failed\", \"Message\": \"" + ex.Message + ex.StackTrace + "\", \"Value\": \"\"}"; } } return(returnValue); }
public string GetListByAttribute(string synapsenamespace, string synapseentityname, string synapseattributename, string attributevalue, string returnsystemattributes, string orderby, string limit, string offset, string filter) { if (string.IsNullOrWhiteSpace(returnsystemattributes)) { returnsystemattributes = "0"; } string limitString = ""; if (!string.IsNullOrEmpty(limit)) { limitString = " LIMIT " + limit; } string orderBySting = ""; if (!string.IsNullOrEmpty(orderby)) { orderBySting = " ORDER BY " + orderby; } string offsetString = ""; if (!string.IsNullOrEmpty(offset)) { offsetString = " OFFSET " + offset; } //Stub for later use string filterString = ""; if (!string.IsNullOrEmpty(filter)) { filterString = " AND " + filter; } string fieldList = SynapseEntityHelperServices.GetEntityAttributes(synapsenamespace, synapseentityname, returnsystemattributes); if (string.IsNullOrEmpty(fieldList)) { fieldList = " * "; } string sql = "SELECT " + fieldList + " FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE " + synapseattributename + " = @p_keyAttributeValue" + filterString + orderBySting + limitString + offsetString + ";"; var paramList = new List <KeyValuePair <string, object> >() { new KeyValuePair <string, object>("p_keyAttributeValue", attributevalue) }; DataSet ds = new DataSet(); ds = DataServices.DataSetFromSQL(sql, paramList); DataTable dt = ds.Tables[0]; var json = DataServices.ConvertDataTabletoJSONString(dt); return(json); }
public string GetBaseViewListByAttribute(string baseviewname, string synapseattributename, string attributevalue, string orderby, string limit, string offset, string filter) { string limitString = ""; if (!string.IsNullOrEmpty(limit)) { limitString = " LIMIT " + limit; } string orderBySting = ""; if (!string.IsNullOrEmpty(orderby)) { orderBySting = " ORDER BY " + orderby; } string offsetString = ""; if (!string.IsNullOrEmpty(offset)) { offsetString = " OFFSET " + offset; } //string fieldList = SynapseEntityHelperServices.GetBaseViewAttributes(baseviewname); //if (string.IsNullOrEmpty(fieldList)) //{ // fieldList = " * "; //} //Stub for later use string filterString = ""; if (!string.IsNullOrEmpty(filter)) { filterString = " AND " + filter; } string sql = "SELECT * FROM baseview." + baseviewname + " WHERE " + synapseattributename + " = @p_keyAttributeValue" + filterString + orderBySting + limitString + offsetString + "; "; var paramList = new List <KeyValuePair <string, object> >() { new KeyValuePair <string, object>("p_keyAttributeValue", attributevalue) }; DataSet ds = new DataSet(); ds = DataServices.DataSetFromSQL(sql, paramList); DataTable dt = ds.Tables[0]; var json = DataServices.ConvertDataTabletoJSONString(dt); return(json); }
private string GetReturnObjectByID(string synapsenamespace, string synapseentityname, int sequenceid) { string sql = "SELECT * FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE _sequenceid = @p_sequenceid;"; var paramList = new List <KeyValuePair <string, object> >() { new KeyValuePair <string, object>("p_sequenceid", sequenceid) }; DataSet ds = new DataSet(); ds = DataServices.DataSetFromSQL(sql, paramList); DataTable dt = ds.Tables[0]; var json = DataServices.ConvertDataTabletoJSONString(dt); return(json); }
private Tuple <string, string> PostObjectHandler(string synapsenamespace, string synapseentityname, string data, dynamic connectionObj = null, dynamic transactionObj = null) { //27Feb2020-RK //RMF1 - Remove Feature- check if all relations have a key column value supplied in the post request //RMF1 //DataTable dtRel = SynapseEntityHelperServices.GetEntityRelations(synapsenamespace, synapseentityname); string keyAttribute = SynapseEntityHelperServices.GetEntityKeyAttribute(synapsenamespace, synapseentityname); StringBuilder sb = new StringBuilder(); //dynamic dObj = JObject.Parse(data); var dataDict = JsonConvert.DeserializeObject <Dictionary <string, object> >(data); StringBuilder sbCols = new StringBuilder(); StringBuilder sbParams = new StringBuilder(); var paramList = new List <KeyValuePair <string, object> >(); string keyValue = ""; // int iRelationMatches = 0; int iKeyMatches = 0; var count = dataDict.Count(); //RK: 08012019 set_createdby : userid from access token #region set_createdby var useridClaim = User.FindFirst(_configuration["SynapseCore:Settings:TokenUserIdClaimType"]); var userid = "unknown"; if (useridClaim != null) { userid = useridClaim.Value; } else if (dataDict.ContainsKey("_createdby")) { userid = Convert.ToString(dataDict["_createdby"]); } //check if dataDict has _createdby key if (dataDict.ContainsKey("_createdby")) { //update they key value to userid from toke dataDict["_createdby"] = userid; } else { //add _createdby key and set value to userid from token dataDict.Add("_createdby", userid); } #endregion foreach (KeyValuePair <string, object> item in dataDict) { //Count if Key Attribute matches item.Key var a = item.Key; var b = a; if (keyAttribute == item.Key) { keyValue = item.Value.ToString(); iKeyMatches++; } //Count all Key Columns //RMF1 /* foreach (DataRow row in dtRel.Rows) * { * sb.Append(row[0].ToString() + ","); * if (item.Key == row[0].ToString()) * { * iRelationMatches++; * } * }*/ if (item.Value != null) { sbCols.Append(item.Key + ","); sbParams.Append("@p_" + item.Key + ","); paramList.Insert(0, new KeyValuePair <string, object>("p_" + item.Key, item.Value)); } else //Check it is not a key or relation column that is null { if (keyAttribute == item.Key) { throw new InterneuronBusinessException(errorCode: 400, errorMessage: "No value supplied for Key Attribute: " + keyAttribute, "Client Error"); } //Relations //RMF1 /* foreach (DataRow row in dtRel.Rows) * { * if (item.Key == row[0].ToString()) * { * throw new InterneuronBusinessException(errorCode: 400, errorMessage: "No values supplpied for relation: " + item.Key, "Client Error"); * * } * }*/ } } //RMF1 /*if (iRelationMatches < dtRel.Rows.Count) * { * throw new InterneuronBusinessException(errorCode: 400, errorMessage: "Not all relations have values specified.Please ensure that you have values are specified for all of the following fields: " + StringManipulationServices.TrimEnd(sb.ToString(), ","), "Client Error"); * * }*/ if (iKeyMatches < 1) { throw new InterneuronBusinessException(errorCode: 400, errorMessage: "No value supplied for Key Attribute: " + keyAttribute, "Client Error"); } string strCols = "INSERT INTO entitystore." + synapsenamespace + "_" + synapseentityname + "(" + sbCols.ToString().TrimEnd(',') + ") "; string strParams = " VALUES (" + sbParams.ToString().TrimEnd(',') + ") RETURNING _sequenceid;"; var sql = strCols + strParams; DataSet ds = new DataSet(); ds = DataServices.DataSetFromSQL(sql, paramList, existingCon: connectionObj); DataTable dt = ds.Tables[0]; int id = System.Convert.ToInt32(dt.Rows[0][0].ToString()); //Delete all occurances from entitystorematerialised string sqlDelete = "DELETE FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE " + keyAttribute + " = @keyValue;"; var paramListDelete = new List <KeyValuePair <string, object> >() { new KeyValuePair <string, object>("keyValue", keyValue) }; DataServices.executeSQLStatement(sqlDelete, paramListDelete, existingCon: connectionObj); //Get all the entity's columns string entityCols = ""; string sqlEntityCols = "SELECT entitysettings.getentityattributestring(@synapsenamespace, @synapseentityname, 1)"; var paramListEntityCols = new List <KeyValuePair <string, object> >() { new KeyValuePair <string, object>("synapsenamespace", synapsenamespace), new KeyValuePair <string, object>("synapseentityname", synapseentityname) }; DataSet dsEntityCols = new DataSet(); dsEntityCols = DataServices.DataSetFromSQL(sqlEntityCols, paramListEntityCols, existingCon: connectionObj); DataTable dtEntityCols = dsEntityCols.Tables[0]; entityCols = dtEntityCols.Rows[0][0].ToString(); //Insert the newly inserted record into the materialised entity string sqlMaterialisedInsert = string.Empty; if (connectionObj == null) { sqlMaterialisedInsert = "INSERT INTO entitystorematerialised." + synapsenamespace + "_" + synapseentityname + "(" + entityCols + ") " + " SELECT " + entityCols + " FROM entityview." + synapsenamespace + "_" + synapseentityname + " WHERE " + keyAttribute + " = @keyValue;"; } else { sqlMaterialisedInsert = "INSERT INTO entitystorematerialised." + synapsenamespace + "_" + synapseentityname + "(" + entityCols + ") " + " SELECT " + entityCols + " FROM entityview." + synapsenamespace + "_" + synapseentityname + " WHERE " + keyAttribute + " = @keyValue RETURNING *;"; } var paramListMaterialisedInsert = new List <KeyValuePair <string, object> >() { new KeyValuePair <string, object>("keyValue", keyValue) }; //DataServices.executeSQLStatement(sqlMaterialisedInsert, paramListMaterialisedInsert, existingCon: connectionObj); var insertedDataInMaterial = string.Empty; if (connectionObj == null) { DataServices.executeSQLStatement(sqlMaterialisedInsert, paramListMaterialisedInsert); } else { DataSet dsMaterilaized = DataServices.DataSetFromSQL(sqlMaterialisedInsert, paramListMaterialisedInsert, existingCon: connectionObj); insertedDataInMaterial = DataServices.ConvertDataTabletoJSONString(dsMaterilaized.Tables[0]); } //Get the return object //return GetReturnObjectByID(synapsenamespace, synapseentityname, id); string returnedData = string.Empty; if (connectionObj == null) { returnedData = GetReturnObjectByID(synapsenamespace, synapseentityname, id); } return(new Tuple <string, string>(returnedData, insertedDataInMaterial)); }
public string GetListByPost(string synapsenamespace, string synapseentityname, string returnsystemattributes, string orderby, string limit, string offset, string filter, [FromBody] string data) { dynamic results = JsonConvert.DeserializeObject <dynamic>(data); string filters = results[0].filters.ToString(); StringBuilder filtersToApplySB = new StringBuilder(); filtersToApplySB.Append(" WHERE 1 = 1 "); JArray obj = Newtonsoft.Json.JsonConvert.DeserializeObject <JArray>(filters); foreach (var filterApplied in obj) { filtersToApplySB.Append(" AND (" + (string)filterApplied["filterClause"] + ")"); } string filtersSQL = filtersToApplySB.ToString(); string filterparams = results[1].filterparams.ToString(); var paramListFromPost = new List <KeyValuePair <string, object> >(); JArray objParams = Newtonsoft.Json.JsonConvert.DeserializeObject <JArray>(filterparams); foreach (var paramApplied in objParams) { if (paramApplied["paramValue"].Type == JTokenType.Date) { var temp = (DateTime)paramApplied["paramValue"]; var value = temp.ToString("yyyy-MM-ddTHH:mm:ss"); paramListFromPost.Insert(0, new KeyValuePair <string, object>((string)paramApplied["paramName"], value)); } else { paramListFromPost.Insert(0, new KeyValuePair <string, object>((string)paramApplied["paramName"], (string)paramApplied["paramValue"])); } } string limitString = ""; if (!string.IsNullOrEmpty(limit)) { limitString = " LIMIT " + limit; } string orderBySting = ""; if (!string.IsNullOrEmpty(orderby)) { orderBySting = " ORDER BY " + orderby; } string offsetString = ""; if (!string.IsNullOrEmpty(offset)) { offsetString = " OFFSET " + offset; } if (string.IsNullOrWhiteSpace(returnsystemattributes)) { returnsystemattributes = "0"; } string fieldList = SynapseEntityHelperServices.GetEntityAttributes(synapsenamespace, synapseentityname, returnsystemattributes); if (string.IsNullOrEmpty(fieldList)) { fieldList = " * "; } //Stub for later use string filterString = ""; if (!string.IsNullOrEmpty(filter)) { filterString = " AND " + filter; } string sql = "SELECT * FROM (SELECT " + fieldList + " FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE 1=1 " + filterString + orderBySting + limitString + offsetString + ") entview " + filtersSQL + ";"; DataSet ds = new DataSet(); ds = DataServices.DataSetFromSQL(sql, paramListFromPost); DataTable dt = ds.Tables[0]; var json = DataServices.ConvertDataTabletoJSONString(dt); return(json); }