How to using Dapper to extract data from column dynamically altered table

Question

I am facing an issue due to client's new requirement. I have a table called Tasks and a table named Codes.

Tasks table:

ID	Taskname
1	2D
2	3D

Codes table

ID	Codename	For2D	For3D
1	2D	1	0
2	3D	0	1

Now user is able to click button in frontend to add an new entry in Task table. Correspondingly a new column should also be added in the Codes table. For example: New Tasks table:

ID	Taskname
1	2D
2	3D
3	4D

New Codes table

ID	Codename	For2D	For3D	For4D
1	2D	1	0	1
2	3D	0	1	1

For adding column part I can handle with Dapper. But how could I write the query and use Dapper to extract data from table and send to frontend? Because I have no idea how many new rows user will add in the Tasks table.

Currently I has a class

public class DqcDto
{
    public string? DqcCode { get; set; }
    public string? DqcDescription { get; set; }
    public string Project { get; set; }
    public bool? IsFor3D { get; set; }
    public bool? IsFor2D { get; set; }
}

But this won't be used when user add new columns.

Answer 1

You could try below code to query database and get dynamic result using dapper:

using Dapper;
using System.Data.SqlClient;
using System.Dynamic;
using System.Text.Json;


var connectionString = "ConnectionString";
var query = "SELECT * FROM Codes";

using (var connection = new SqlConnection(connectionString))
{
    var dynamicResults = connection.Query<dynamic>(query).ToList();
    foreach (var row in dynamicResults)
    {
        foreach (var property in (IDictionary<string, object>)row)
        {
            Console.WriteLine($"{property.Key}: {property.Value}");
        }
    }
    
    var json = JsonSerializer.Serialize(dynamicResults);
Console.WriteLine(json);// send `json` to the frontend

}

This code will help queries all columns from the Codes table and iterates over each row, treating it as a dynamic object.