Structuring an SQL-result into an envelope format

Question

I got something like those two tables here:

Id	Name
198	Test-Project
227	Second test project

and

Id	ProjectId	Name
12	198	Task 1
23	198	Task 2
34	227	Task 1

Those will get mapped in an sql-statement, like this:

select project.Id, task.Id, task.ProjectId, task.Name, project.Name
from Project project 
left join Task task on project.Id = task.ProjectId

which returns this:

Id	Id	ProjectId	Name	Name
198	23	198	Task 1	Test-Project
198	12	198	Task 2	Test-Project
227	34	227	Task 2	Second test project

Now, I want to get a structure for this, like Entity Framework would return (I show in json to make it easier to understand):

{
  "datasets": [
    {
      "records": [
        {
          "fields": [
            {
              "Name": "Name",
              "Value": "Test-Project"
            },
            {
              "Name": "Id",
              "Value": 198
            }
          ],
          "datasets": [
            {
              "records": [
                {
                  "fields": [
                    {
                      "Name": "ProjectId",
                      "Value": 198
                    },
                    {
                      "Name": "Name",
                      "Value": "Task 1"
                    },
                    {
                      "Name": "Id",
                      "Value": 23
                    }
                  ]
                }
...

I tried with SqlDataReader but I cannot figure out, how I can "group" them... I fail at the logical part..

await using SqlCommand cmd = new SqlCommand(sql, connection).SetParameters(parameters);

connection.Open();

Root root = new ();
DataSet dataSet = new ();
await using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
    while (reader.Read())
    {
        Record record = new ();

        for (int i = 0; i < reader.FieldCount; i++)
        {
            Field field = new ()
            {
                Name = _getColumnName(i),
                Value = reader[i]
            };

            record.DataFields.Add(field);
        }

        dataSet.Records.Add(record);
    }
}
root.DataAreas.Add(dataSet);

and the only thing I can get is a flat list containinng all datasets:

{
  "datasets": [
    {
      "records": [
        {
          "fields": [
            {
              "Name": "Id",
              "Value": 23
            },
            {
              "Name": "Name",
              "Value": "Task 1"
            },
            {
              "Name": "Name",
              "Value": "Test-Project"
            },
            {
              "Name": "Id",
              "Value": 198
            },
            {
              "Name": "ProjectId",
              "Value": 198
            }
          ],
          "datasets": []
        }

any idea on how to solve this?

@AndrewCorrigan exactly. And if there was another join unter tasks, i want to show those datasets too (recursively) — Matthias Burger, Dec 16 '21 at 13:51

score 0 · Answer 1 · answered Dec 16 '21 at 14:42

0

you need to use two SQL command like https://stackoverflow.com/a/19448944/3409634 in the first command read parent records, and in the second command read child and add to the parent object.

answered Dec 16 '21 at 14:42

Majid325

34
5

good idea, but this needs too many requests if you have a recursive entity.. or if you got a few more joins... it works, but I need to handle grouping somehow. – Matthias Burger Dec 16 '21 at 15:02

Structuring an SQL-result into an envelope format

1 Answers1