Latest

random

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 :-

Remove Columns that contains null values in Data Table
Remove Null Columns
Default.aspx
<%@ 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. 

1 comment:

  1. If the idea is to exclude NULL values from Data Table columns then the below will be more apt:

    .DefaultView.RowFilter = " IS NOT NULL";
    DataTable dtFiltered = .DefaultView.ToTable();

    should be replaced with the actual Data Table variable
    and with the Column Name.

    ReplyDelete

All Rights Reserved by Etechpulse © 2015 - 2016
Powered By Blogger

Contact Form

Name

Email *

Message *

Powered by Blogger.