SandboxM

Recently, I created a dashboard which displays, let’s say account balances, by month. I wrote a query that pivoted the data in the format that I wanted (totals by month). My SQL query returned 12 columns, one for each month, but the database contains data for only the past 6 months. Since my 12 columns were fixed or “non-dynamic”, the result was that the GridView showed 6 empty columns.

I googled a lot to remove the null columns but all results referenced the column by name or ID but I had to do this dynamically. Lot of results talked implementing the logic in the GridView_RowDataBound method but after a lot of head-scratching, it hit me! I was trying to fix the problem at the GridView end. Why not deal with the problem when the columns are returned from the database and before the datatable is attached to GridView’s data source? And so that’s what I did. Looped thru each column and subsequently looped thru each row one by one, if any of the rows contain a valid value, do not remove the column. Simple.

Here’s the C# code:

protected void Page_Load(object sender, EventArgs e)
{
GridView GridView1 = new GridView();
DataTable myDataTable = GetDatafromDB();
myDataTable = RemoveNullColumns(myDataTable);
GridView1.DataSource = myDataTable;
GridView1.Databind();
}

private DataTable RemoveNullColumns(DataTable dt)
{
for (int c = 0; dc < dt.Columns.Count; c++)
{
string colName = dt.Columns[c].ColumnName;
bool allRowsempty = true;
for (int r = 0; r < dt.Rows.Count; r++)
{
string tempVal = dt.Rows[r][c].ToString();
if (tempVal != "") //Can also use Convert.DbNull here
{
allRowsempty = false;
}
}
if (allRowsempty)
{
dt.Columns.Remove(colName);
c--;
}
}
return dt;
}

Hope this saves you some time and effort!