I have a WinForm that asks to insert an email in a textbox and after clicking on search button , an sql query will search for the record in a table and display the result in a DataGridView. Here the code of the search button

private void RegUnregSearchButton_Click(object sender, EventArgs e)
    {
        var dataAccess = new DataAccess();
        var output = dataAccess.BindGrid(EmailTextBox.Text);

        DataGridView1.DataSource = output;
    }

The purpose of BindGrid is to fills automatically the columns and the rows of a datatable from the result of the query but right now I'm doing it manually because I can't understand how to retrieve the columns from the Dapper result.

public class DataAccess
{
    public DataTable BindGrid(string param)
    {
        DataTable dataTable = new DataTable();
        
        //Helper.connString("SqlConnectionString") will take the connection string from an app.config and return the value. Connection works.
        using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.connString("SqlConnectionString")))
        {
            var result = connection.Query("SELECT Company, Email, Role, Id, Status from table WHERE Email = '{param}'").ToList();
                    
            RegUnregUser user = new RegUnregUser();
            user.company = result.Select(x => x.Company).FirstOrDefault();
            user.email = result.Select(x => x.Email).FirstOrDefault();
            user.role = result.Select(x => x.Role).FirstOrDefault();
            user.id = result.Select(x => x.Id).FirstOrDefault();
            user.status = result.Select(x => x.Status).FirstOrDefault();

            dataTable.Columns.Add("Company");
            dataTable.Columns.Add("Email");
            dataTable.Columns.Add("Role");
            dataTable.Columns.Add("Id");
            dataTable.Columns.Add("Status");
            dataTable.Rows.Add(user.company, user.email, user.role, user.id, user.status);
            
            return dataTable;
        }
    }
}

The RegUnregUser class:

public class RegUnregUser
{
    public string company { get; set; }
    public string email { get; set; }
    public string role { get; set; }
    public object id { get; set; }
    public string status { get; set; }
}

CodePudding user response：

Dapper is for mapping to custom objects, not for use with DataTables. That being said, you should able to do it something like this:

public class DataAccess
{
    public DataTable BindGrid(string param)
    {
        //Helper.connString("SqlConnectionString") will take the connection string from an app.config and return the value. Connection works.
        using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.connString("SqlConnectionString")))
        {
            var reader = connection.ExecuteReader("SELECT Company, Email, Role, Id, Status from table WHERE Email = @Email", 
               new {Email=param});
            return new DataTable().Load(reader);
        }
    }
}

Notice I'm using a parameterized query. Your code is vulnerable to SQL injection.