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.