Bug to popular a Data Gridview in Window Forms

Question

To popular my grid the values are not aligned correctly, as the image below.

This is my Load Form code:

DatabaseRepository db = new DatabaseRepository();
var colunas = db.GetColumnsName();
var resultado = db.Get();

DataTable dataTable = new DataTable();

foreach (IDictionary<string, object> columns in colunas)
{
    var coluna = columns.Values.FirstOrDefault().ToString();
    dataTable.Columns.Add(coluna);

    foreach (IDictionary<string, object> rows in resultado)
    {
        foreach (var pair in rows)
        {
            if (pair.Key == coluna)
            {
                DataRow dataRow = dataTable.NewRow();
                dataRow[coluna] = pair.Value;
                dataTable.Rows.Add(dataRow);
            }
        }
    }
}

dataGridView1.DataSource = dataTable;

And that access code to my database:

public class DatabaseRepository
{
    private Connection _Connection = new Connection();

    public DatabaseRepository()
    {

    }

    public List<dynamic> Get()
    {
        using(var Sql = _Connection.GetConnection())
        {
            Sql.Open();
            var resultado = Sql.Query<dynamic>("SELECT * FROM [seguranca].[tb_acao]").ToList();
            return resultado;
        }
    }

    public List<dynamic> GetColumnsName()
    {
        using(var Sql = _Connection.GetConnection())
        {
            Sql.Open();
            string query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + "tb_acao" +"'";
            var resultado = Sql.Query<dynamic>(query).ToList();
            return resultado;
        }
    }

}

The idea is to take this data and columns directly from the database, and when listing concatenate the columns with the values to mount the grid.

Answer 1

Generally speaking, none of this is being done is necessary.

It is possible to simply assign a list as the grid data source, so each item in the list will be a table row and each property of each object will represent a column.

Note that this approach of creating a generic table is not very good in itself, eventually it will be necessary to make some adaptation in a specific case and this can start to get quite confusing.

var itens = new[]
{
    new { Id = 1, Nome = "Roberval", Idade = 35 },    
    new { Id = 2, Nome = "Joaquina", Idade = 29 },    
    new { Id = 3, Nome = "Marineide", Idade = 60 },    
    new { Id = 4, Nome = "Ericledes", Idade = 55 }
};

dataGridView1.DataSource = itens;

This code produces the following result:

---

How do you plan to play grid the data coming from Dapper dynamically, will need to somehow clarify the columns, in this case it will not be possible to use a list as data source of the grid because Datagridview looks for concrete elements to mount the table.

In this OS post has an extension method for enumerable dynamics that "transforms" the collection into a DataTable.

Note that now the idea seems even worse, it would be much easier to have a class representing each model, even if you do not want to have a grid for each case...

Your code would look something like this:

public class DatabaseRepository
{
    private Connection _Connection = new Connection();

    public DatabaseRepository() { }

    public IEnumerable<dynamic> Get()
    {
        using(var Sql = _Connection.GetConnection())
        {
            Sql.Open();
            return Sql.Query("SELECT * FROM [seguranca].[tb_acao]");
        }
    }
}

var db = new DatabaseRepository();
dataGridView1.DataSource = db.Get().ToDataTable();

public static class EnumerableExtensions
{
    public static DataTable ToDataTable(this IEnumerable<dynamic> items)
    {
        var data = items.ToArray();
        if (!data.Any()) return null;

        var dt = new DataTable();
        foreach (var key in ((IDictionary<string, object>)data[0]).Keys)
        {
            dt.Columns.Add(key);
        }

        foreach (var d in data)
        {
            dt.Rows.Add(((IDictionary<string, object>)d).Values.ToArray());
        }
        return dt;
    }
}