Geeks With Blogs

News

Microsoft MVP


Moderator at CodeASP.NET


Quiz Master




free counters
Free counters
Added on January 19,2012


Follow Me @vmsdurano

A bit About Me



Disclaimer
The opinions expressed herein are my own personal opinions and does not represent the opinions of my employers. Nor does it represent the opinion of my dog, because I don’t have one.


Vinz' Blog (ProudMonkey) "Code, Beer and Music ~ my way of being a programmer"

Recently, one of the members in the forum (http://forums.asp.net) is asking if how to remove a particular column in a DataTable if all values in the row of that column are null or empty. So I decided to post the solution that I have provided in that thread as a reference to others who encounter the same problem.

 

Just for the simplicity of this demo, I created a sample DataTable with a dummy data on it just for us to test. Here’s the code block below:

 

    private DataTable CreateDataTable()

    {

        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;

 

    }

 

As you can see, we set an empty and null value to the rows of Column2. Now let’s go ahead and create the method for checking the row values of a particular column in a DataTable. Here’s the code block below:

 

    private DataTable CheckDataTableColumn()

    {

        DataTable dt = CreateDataTable();

        bool flag = false;

        int counter = 0;

 

        EXIT:

            for (int i = counter; i < dt.Columns.Count; i++)

            {

                for (int x = 0; x < dt.Rows.Count; x++)

                {

                    if (string.IsNullOrEmpty(dt.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)

                {

                    dt.Columns.Remove(dt.Columns[i]);

                    i--;

                }

            }

        return dt;

    }

 

As you can see, we simply loop through the columns and rows in the DataTable to check if there is a null or empty value in a particular row in a particular column in DataTable. If a certain row has non null values then it exits the looping of the rows using the goto function, else it continue looping through the rows to check if all the row values are nulls or empty. If all row values are null or empty then that particular column which contains those rows will be deleted using the Remove method of the DataTable Columns.

 

Wheew… Ok now let’s try to test it. To test it then lets check if what columns are being deleted by looping through the columns in the DataTable. See the code block below:

 

    protected void Page_Load(object sender, EventArgs e)

    {

        //print the Columns in the DataTable to see if what column(s) are deleted

        DataTable dtNew = CheckDataTableColumn();

        if (dtNew.Rows.Count > 0)

        {

            foreach (DataColumn dc in dtNew.Columns)

            {

                Response.Write(dc.ColumnName + "<BR/>");

            }

        }

        else

        {

            Response.Write("No data returned from the DataTable");

        }

    }

 

 

Running the code above will display the following column names in the page:

 

Column1

Column3

 

Oh where is Column2? Obviously it was deleted.. It’s because all row values in column 2 are null or empty.

 

That’s it! Hope you will find this example useful.

 

Technorati Tags: ,,,
Posted on Tuesday, August 11, 2009 8:32 PM ADO.NET , ASP.NET , C# , Tips&Tricks | Back to top


Comments on this post: Removing Columns in DataTable

# re: Removing Columns in DataTable
Requesting Gravatar...
I'm using this code and check my issue here
http://stackoverflow.com/questions/2268805/what-is-wrong-with-this-loop
Left by Rahul on Feb 16, 2010 5:21 AM

comments powered by Disqus

Copyright © Vincent Maverick Durano | Powered by: GeeksWithBlogs.net | Join free