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; } }