public HttpResponseMessage FindObject(dynamic json)
{
if (json == null)
{
return(Request.CreateResponse <dynamic>(HttpStatusCode.BadRequest,
new
{
Exception = "InvalidObjectFormatException",
Message = "Json object you have provided has invalid format"
}));
}
string connectionString = ConfigurationManager.ConnectionStrings["defaultConnection"].ConnectionString;
CleverDbContext db = new CleverDbContext(connectionString);
try
{
CleverQuery cq = new CleverQuery(json);
var result = db.Find(cq);
return(new HttpResponseMessage()
{
Content = new StringContent(CleverObjectService.GetJsonFromCleverObjectArray(result), Encoding.UTF8, "application/json")
});
}
catch (Exception exp)
{
return(Request.CreateResponse <dynamic>(HttpStatusCode.BadRequest,
new
{
Exception = exp.GetType().Name.ToString(),
Message = exp.Message
}));
}
}
public IEnumerable <CleverObject> Find(CleverQuery cq)
{
if (cq.IsEmpty)
{
return(null);
}
string queryString = "select ob.Id as 'Id', ob.Name, ob.ParentId, at.Id as 'AttributeId', at.Name as 'AttributeName'," +
" at.StringValue, at.DateTimeValue, at.DoubleValue, at.Type, at.CleverObjectId from [CleverObjects] ob " +
" left join [CleverObjectAttributes] at on ob.Id = at.CleverObjectId " +
" where ob.Id in ( ";
if (cq.ObjectConditions.ToList().Count > 0)
{
int counter = 1;
foreach (var query in cq.ObjectConditions)
{
queryString += " select distinct (obj.Id) from [CleverObjects] obj " +
" left join [CleverObjectAttributes] atr on obj.Id = atr.CleverObjectId where ";
queryString += query.GetSqlCondition("obj");
if (counter < cq.ObjectConditions.Count())
{
queryString += " intersect ";
}
counter++;
}
}
if (cq.AttributesConditions.ToList().Count > 0)
{
if (cq.ObjectConditions.ToList().Count > 0)
{
queryString += " intersect ";
}
int count = 1;
foreach (var query in cq.AttributesConditions)
{
queryString += " select distinct (obj.Id) from [CleverObjects] obj " +
" left join [CleverObjectAttributes] atr on obj.Id = atr.CleverObjectId where ";
queryString += query.GetSqlCondition("atr");
if (count < cq.AttributesConditions.Count())
{
queryString += " intersect ";
}
count++;
}
}
queryString += ") order by ob.Id";
List <int> appropriateObjects = new List <int>();
Stack <CleverObject> stack = new Stack <CleverObject>();
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
SqlDataAdapter dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = command;
DataSet ds = new DataSet();
try
{
connection.Open();
dataAdapter.Fill(ds);
if (ds.Tables[0].Rows.Count != 0)
{
foreach (var dataRow in ds.Tables[0].AsEnumerable())
{
int objectId = dataRow.Field <int>("Id");
string objectName = dataRow.Field <string>("Name");
int? objectPartentId = dataRow.Field <int?>("ParentId");
if (stack.Count == 0)
{
stack.Push(new CleverObject()
{
Id = objectId,
ParentId = objectPartentId,
Name = objectName
});
}
var stackedObject = stack.Peek();
if (stackedObject.Id != objectId)
{
stack.Push(new CleverObject()
{
Id = objectId,
ParentId = objectPartentId,
Name = objectName
});
stackedObject = stack.Peek();
}
if (dataRow.Field <string>("Type") != null)
{
var attribute = new CleverObjectAttribute(dataRow.Field <string>("Type"))
{
Id = dataRow.Field <int>("AttributeId"),
Name = dataRow.Field <string>("AttributeName"),
StringValue = dataRow.Field <string>("StringValue"),
DoubleValue = dataRow.Field <double?>("DoubleValue"),
DateTimeValue = dataRow.Field <DateTime?>("DateTimeValue"),
CleverObjectId = dataRow.Field <int>("CleverObjectId")
};
stackedObject.Attributes.Add(attribute);
}
}
}
}
finally
{
connection.Close();
}
}
return(stack.AsEnumerable());
}