Change Column from DataGridView in runtime

Question

Hi,
This is probably easy, but I couldn't find an answer anywhere. I'm filling a previously created DataGridView on an ASP.NET Web Form dynamically with a DataTable, but I want to change the name of the columns that appear on that DataGridView, and I simply can't figure out what I'm doing wrong. Below is the code:

DataTable dtUsuarios = DBManager.RunSqlGetDataTable(
  @"select b.UserName, c.Email, c.IsLockedOut, c.LastLoginDate, 
      case 
        when e.RoleName is not null then 1
        else 0 end Admin
    from dbo.aspnet_Applications a join dbo.aspnet_Users b
      on a.ApplicationId = b.ApplicationId
    join dbo.aspnet_Membership c
      on b.ApplicationId = c.ApplicationId
      and b.UserId = c.UserId
    left join dbo.aspnet_UsersInRoles d
      on d.UserId = b.UserId
    left join dbo.aspnet_Roles e
      on d.RoleId = e.RoleId
    where a.ApplicationName = 'Mont Blanc Catalogo'");
dtUsuarios.Columns["UserName"].Caption = "Nome Usuário";
dtUsuarios.Columns["Email"].Caption = "E-mail";
dtUsuarios.Columns["IsLockedOut"].Caption = "Bloqueado";
dtUsuarios.Columns["LastLoginDate"].Caption = "Último Login";
dtUsuarios.Columns["Admin"].Caption = "Administrador";
DataView dvUsuarios = new DataView(dtUsuarios) { Sort = "UserName" };
gdvUsuarios.DataSource = dvUsuarios;
gdvUsuarios.DataBind();
//gdvUsuarios.Columns[0].HeaderText = "Nome Usuário";
//gdvUsuarios.Columns[1].HeaderText = "E-mail";
//gdvUsuarios.Columns[2].HeaderText = "Bloqueado";
//gdvUsuarios.Columns[3].HeaderText = "Último Login";
//gdvUsuarios.Columns[4].HeaderText = "Administrador";

Setting the values changing the caption of the DataTable doesn't work, and after the DataBind, the Columns.Count property for the DataGridView return zero columns! After executing the code, everything appears normally at my asp page, but the captions are wrong. What I am doing wrong?
Tks

Answer 1

You could try column aliasing, depending of course on your particular sql application / version.

DataTable dtUsuarios = DBManager.RunSqlGetDataTable( 
@"select 
  b.UserName [Nome Usuário], 
  c.Email [E-mail], 
  c.IsLockedOut [Bloqueado], 
  c.LastLoginDate [Último Login],  
  case  
    when e.RoleName is not null then 1 
    else 0 end [Administrador] 
from dbo.aspnet_Applications a join dbo.aspnet_Users b 
  on a.ApplicationId = b.ApplicationId 
join dbo.aspnet_Membership c 
  on b.ApplicationId = c.ApplicationId 
  and b.UserId = c.UserId 
left join dbo.aspnet_UsersInRoles d 
  on d.UserId = b.UserId 
left join dbo.aspnet_Roles e 
  on d.RoleId = e.RoleId 
where a.ApplicationName = 'Mont Blanc Catalogo'"); 

DataView dvUsuarios = new DataView(dtUsuarios) { Sort = "[Nome Usuário]" }; 
gdvUsuarios.DataSource = dvUsuarios; 
gdvUsuarios.DataBind(); 

Answer 2

If you want to rename the columns within your DataTable, simply do (ColumnName instead of Caption):

dtUsuarios.Columns["UserName"].ColumnName= "Nome Usuário"

If you want to change the ouput that you're displaying to the user, you probably want to do that within the markup. If it's a GridView, specify a HeaderText on the BoundColumn

Answer 3

I thought I had dug enough, but not enough, apparently. The answer I found here: http://stackoverflow.com/questions/686544/any-way-to-manipulate-the-columns-in-gridview-with-autogeneratecolumns-true

Below is the resulting code:

public partial class admin_CadUsuarios : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dtUsuarios = DBManager.RunSqlGetDataTable(
          @"select b.UserName, c.Email, c.IsLockedOut, c.LastLoginDate, 
              case 
                when e.RoleName is not null then 1
                else 0 end Admin
            from dbo.aspnet_Applications a join dbo.aspnet_Users b
              on a.ApplicationId = b.ApplicationId
            join dbo.aspnet_Membership c
              on b.ApplicationId = c.ApplicationId
              and b.UserId = c.UserId
            left join dbo.aspnet_UsersInRoles d
              on d.UserId = b.UserId
            left join dbo.aspnet_Roles e
              on d.RoleId = e.RoleId
            where a.ApplicationName = 'Mont Blanc Catalogo'");
        DataView dvUsuarios = new DataView(dtUsuarios) { Sort = "UserName" };
        gdvUsuarios.DataSource = dvUsuarios;
        gdvUsuarios.DataBind();
    }

    protected void gdvUsuarios_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            e.Row.Cells[0].Text = "Nome Usuário";
            e.Row.Cells[1].Text = "E-mail";
            e.Row.Cells[2].Text = "Bloqueado";
            e.Row.Cells[3].Text = "Último Login";
            e.Row.Cells[4].Text = "Administrador";
        }
    }
}

The RowDataBound did the trick.