public string GetBaseViewListObjectByAttribute(string baseviewname, string synapseattributename, string attributevalue, string filter)
{
//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 + " LIMIT 1; ";
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.ConvertDataTabletoJSONObject(dt);
return(json);
}
public string GetObject(string synapsenamespace, string synapseentityname, string id, string returnsystemattributes)
{
if (string.IsNullOrWhiteSpace(returnsystemattributes))
{
returnsystemattributes = "0";
}
string fieldList = SynapseEntityHelperServices.GetEntityAttributes(synapsenamespace, synapseentityname, returnsystemattributes);
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: " + keyAttribute, "Client Error");
}
string sql = "SELECT " + fieldList + " FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE " + keyAttribute + " = @p_keyAttributeValue" + " LIMIT 1;";
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.ConvertDataTabletoJSONObject(dt);
return(json);
}
public string GetObjectWithInsert(string synapsenamespace, string synapseentityname, string synapseattributename, string attributevalue, string keyvalue, string returnsystemattributes)
{
if (string.IsNullOrWhiteSpace(returnsystemattributes))
{
returnsystemattributes = "0";
}
if (string.IsNullOrWhiteSpace(keyvalue))
{
keyvalue = System.Guid.NewGuid().ToString();
}
string sqlCount = "SELECT COUNT(*) AS entityRecords FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE " + synapseattributename + " = @p_keyAttributeValue;";
var paramListCount = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("p_keyAttributeValue", attributevalue)
};
int iCount = 0;
DataSet dsCount = new DataSet();
try
{
dsCount = DataServices.DataSetFromSQL(sqlCount, paramListCount);
DataTable dt1 = dsCount.Tables[0];
iCount = System.Convert.ToInt32(dt1.Rows[0]["entityRecords"].ToString());
}
catch (Exception ex)
{
iCount = 0;
}
if (iCount == 0)
{
//insert to return row
string keyAttribute = SynapseEntityHelperServices.GetEntityKeyAttribute(synapsenamespace, synapseentityname);
StringBuilder sb = new StringBuilder();
StringBuilder sb_materialised = new StringBuilder();
var paramListInsert = new List <KeyValuePair <string, object> >();
var paramListInsert_materialised = new List <KeyValuePair <string, object> >();
if (keyAttribute == synapseattributename) //Only insert attributevalue
{
sb.Append("INSERT INTO entitystore." + synapsenamespace + "_" + synapseentityname);
sb.Append(" (" + keyAttribute + ")");
sb.Append(" VALUES (@keyvalue)");
paramListInsert = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("keyvalue", attributevalue)
};
sb_materialised.Append("INSERT INTO entitystorematerialised." + synapsenamespace + "_" + synapseentityname);
sb_materialised.Append(" (" + keyAttribute + ")");
sb_materialised.Append(" VALUES (@keyvalue)");
paramListInsert_materialised = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("keyvalue", attributevalue)
};
}
else
{
sb.Append("INSERT INTO entitystore." + synapsenamespace + "_" + synapseentityname);
sb.Append(" (" + keyAttribute + "," + synapseattributename + ")");
sb.Append(" VALUES (@keyvalue, @synapseattributevalue)");
paramListInsert = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("keyvalue", keyvalue),
new KeyValuePair <string, object>("synapseattributevalue", attributevalue)
};
sb_materialised.Append("INSERT INTO entitystorematerialised." + synapsenamespace + "_" + synapseentityname);
sb_materialised.Append(" (" + keyAttribute + "," + synapseattributename + ")");
sb_materialised.Append(" VALUES (@keyvalue, @synapseattributevalue)");
paramListInsert_materialised = new List <KeyValuePair <string, object> >()
{
new KeyValuePair <string, object>("keyvalue", keyvalue),
new KeyValuePair <string, object>("synapseattributevalue", attributevalue)
};
}
DataServices.executeSQLStatement(sb.ToString(), paramListInsert);
DataServices.executeSQLStatement(sb_materialised.ToString(), paramListInsert_materialised);
}
string fieldList = SynapseEntityHelperServices.GetEntityAttributes(synapsenamespace, synapseentityname, returnsystemattributes);
if (string.IsNullOrEmpty(fieldList))
{
fieldList = " * ";
}
string sql = "SELECT " + fieldList + " FROM entitystorematerialised." + synapsenamespace + "_" + synapseentityname + " WHERE " + synapseattributename + " = @p_keyAttributeValue LIMIT 1;";
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.ConvertDataTabletoJSONObject(dt);
return(json);
}
