C# - Remove Columns that contains null values in Data Table
Remove all columns with no data from C# DataTable
If all the items for a particular column are empty or null and you want to remove that column from the DataTable. The following example demonstrates how to delete column that contains null values in C# Datatable. Find the c# code below :-
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="RemoveColumnInDataTable._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Removing Columns in DataTable</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lbl" Text="Before" runat="server" Style="font-size: small">
</asp:Label>
<br />
<asp:GridView ID="GridView1" runat="server" Width="30%" BackColor="White" BorderColor="#CCCCCC"
BorderStyle="None" BorderWidth="1px" CellPadding="3">
<RowStyle ForeColor="#000066" />
<FooterStyle BackColor="White" ForeColor="#000066" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
</asp:GridView>
<br />
<asp:Button ID="OnBtnClick" runat="server" Text="Remove Null/Empty Value Column"
OnClick="OnBtnClick_Click" />
<br />
<br />
<asp:Label ID="Label1" Text="After" runat="server" Style="font-size: small">
</asp:Label>
<asp:GridView ID="GridView2" runat="server" Width="30%" BackColor="White" BorderColor="#CCCCCC"
BorderStyle="None" BorderWidth="1px" CellPadding="3">
<RowStyle ForeColor="#000066" />
<FooterStyle BackColor="White" ForeColor="#000066" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
Default.aspx.cs
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
namespace RemoveColumnInDataTable
{
/// <summary>
/// Author etechpulse
/// To remove a particular column in a DataTable if all values in the row of that column are null or empty.
/// </summary>
public partial class _Default : System.Web.UI.Page
{
DataTable dtTemp = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
//print the Columns in the DataTable to see if what column(s) are deleted
dtTemp = StaticTable();
GridView1.DataSource = dtTemp;
GridView1.DataBind();
}
private DataTable StaticTable()
{
DataTable _dt = new DataTable();
DataRow _dr = null;
//Create the Columns Definition
_dt.Columns.Add(new DataColumn("Column1", typeof(string)));
_dt.Columns.Add(new DataColumn("Column2", typeof(string)));
_dt.Columns.Add(new DataColumn("Column3", typeof(string)));
//Add the first Row to each columns defined
_dr = _dt.NewRow();
//Add dummy values to each rows
_dr["Column1"] = "A";
_dr["Column2"] = string.Empty; // but it will be converted to empty
_dr["Column3"] = "C";
_dt.Rows.Add(_dr);
//Add the second Row to each columns defined
_dr = _dt.NewRow();
_dr["Column1"] = null;
_dr["Column2"] = null;// but it will be converted to empty
_dr["Column3"] = "F";
_dt.Rows.Add(_dr);
//You can continue adding rows here
return _dt;
}
private DataTable CheckDataTableColumn()
{
// DataTable dt = StaticTable();
bool flag = false;
int counter = 0;
EXIT:
for (int i = counter; i < dtTemp.Columns.Count; i++)
{
for (int x = 0; x < dtTemp.Rows.Count; x++)
{
if (string.IsNullOrEmpty(dtTemp.Rows[x][i].ToString()))
{
flag = true; //means there is an empty value
}
else
{
//means if it found non null or empty in rows of a particular column
flag = false;
counter = i + 1;
goto EXIT;
}
}
if (flag == true)
{
dtTemp.Columns.Remove(dtTemp.Columns[i]);
i--;
}
}
return dtTemp;
}
protected void OnBtnClick_Click(object sender, EventArgs e)
{
dtTemp = CheckDataTableColumn();
if (dtTemp.Rows.Count > 0)
{
foreach (DataColumn dc in dtTemp.Columns)
{
//Response.Write(dc.ColumnName + "<BR/>");
}
}
else
{
Response.Write("No data returned from the DataTable");
}
GridView2.DataSource = dtTemp;
GridView2.DataBind();
}
}
}
I hope you will enjoy the development tip while programming with C# and DataTable. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.
Also If you like this article, don't forget to share this article with your friends and colleagues.
C# - Remove Columns that contains null values in Data Table
Reviewed by Ravi Kumar
on
3:10 PM
Rating:
Reviewed by Ravi Kumar
on
3:10 PM
Rating:
If the idea is to exclude NULL values from Data Table columns then the below will be more apt:
ReplyDelete.DefaultView.RowFilter = " IS NOT NULL";
DataTable dtFiltered = .DefaultView.ToTable();
should be replaced with the actual Data Table variable
and with the Column Name.