public Datapoint GetNextDatapoint()
{
var datapoint = new Datapoint();
using (var reader = Query("SELECT TOP 1 [id],[name],[country],[count],[ratio] FROM [datapoint] WHERE [ratio] > 0 AND [use] = (SELECT TOP 1 [use] FROM [datapoint] WHERE [ratio] > 0 ORDER BY [use] ASC) ORDER BY NEWID()"))
{
if (!reader.Read())
return null;
datapoint.ID = reader.GetInt32(0);
datapoint.Name = reader.GetString(1);
datapoint.Country = reader.GetString(2);
datapoint.Count = reader.GetInt32(3);
datapoint.Ratio = reader.GetInt32(4);
}
using (var reader = Query("SELECT D.area, Q.text, Q.type FROM dataset D JOIN question Q ON Q.dataset = D.name WHERE name = @name;", new SqlParameter("@name", datapoint.Name)))
{
var result = new List<Question>();
while (reader.Read())
{
var area = reader.GetString(0);
var text = reader.GetString(1);
var type = reader.GetInt32(2);
var question = new Question();
question.Area = area;
question.Text = text;
question.Type = type;
result.Add(question);
}
datapoint.Questions = result;
}
using (var reader = Query("SELECT id, uri, caption, credit FROM endplate WHERE datapoint = @id;", new SqlParameter("@id", datapoint.ID)))
{
var result = new List<Endplate>();
while (reader.Read())
{
var endplate = new Endplate();
endplate.ID = reader.GetInt32(0);
endplate.Uri = reader.GetString(1);
endplate.Caption = reader.GetString(2);
endplate.Credit = reader.GetString(3);
result.Add(endplate);
}
datapoint.Endplates = result;
}
return datapoint;
}
public IEnumerable<Datapoint> GetRelatedDatapoints(int id)
{
using (var reader = StoredProcedure("GetRelatedDatapoints", new SqlParameter("@id", id)))
{
var result = new List<Datapoint>();
while (reader.Read())
{
var datapoint = new Datapoint();
datapoint.ID = reader.GetInt32(0);
datapoint.Name = reader.GetString(1);
datapoint.Country = reader.GetString(2);
datapoint.Count = reader.GetInt32(3);
datapoint.Ratio = reader.GetInt32(4);
result.Add(datapoint);
}
return result;
}
}
