Click here to Skip to main content
12,951,983 members (62,119 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi everybody,

I have a doubt in handling the DataTable. I have a DataTable, in my datatable i have 100 rows. But out 100 , only 5 rows having data remaining all rows are empty.

Now my question is i want to remove all remaining 95 rows. which row doesn't have a data.

How to do can anybody help me regarding this issue...
Posted 15-Feb-13 1:16am
Comments
Simon_Whale 28-Jan-15 6:07am
   
Can I ask why are there so many blank rows in your datatable?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi, in flowing example your actual data is stored in dt.

 DataTable dt1=dt.Clone();
        var rows =from row in dt.AsEnumerable()
            where row.Field<string>("columnname") != null
            select row;
        foreach (DataRow dr in rows)
        {
            dt1.Rows.Add(dr);
        }
</string>


you can create copy of dt to dt1 and using link you can find row which dose not have null value
  Permalink  
Comments
naveensenagasetti 15-Feb-13 8:21am
   
but i'm not using LINQ
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

If you need to do it without linq, you can try this.
NOTE: I did just hammer this out here, I haven't thoroughly checked the syntax.
private void RemoveEmptyRows(DataTable source)
   for( int i = source.Rows.Count; i >= 0; i-- )
   {
      DataRow currentRow = source.Rows[i];   
      foreach( var colValue in currentRow.ItemArray)
      {
         if( !string.IsNullOrEmpty(colValue) )
            break;
 
         // If we get here, all the columns are empty
         source.Rows[i].Delete();
      }
   }
}
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

It is corrected version of answer from fjdiewornncalwe. I removed logical & syntax errors i faced while using that code.

private void RemoveEmptyRows(DataTable source)
{
   for (int i = source.Rows.Count; i >= 1; i--)
                {
                    DataRow currentRow = source.Rows[i - 1];
                    foreach (var colValue in currentRow.ItemArray)
                    {
                        if (!string.IsNullOrEmpty(colValue.ToString()))
                            break;
 
                        // If we get here, all the columns are empty
                        source.Rows[i - 1].Delete();
                    }
                }
}
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

Try this:
dataTable = dataTable.Rows.Cast<datarow>().Where(row => !row.ItemArray.All(field => 
field is System.DBNull || string.Compare((field as string).Trim(),  
string.Empty) == 0)).CopyToDataTable();
  Permalink  
v2
Comments
CHill60 23-Oct-14 10:34am
   
The question is nearly 1.5 years old!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 6

Try this modification of code from above (fjdiewornncalwe)...

private static DataTable RemoveEmptyRows(DataTable source
{
  DataTable dt1 = source.Clone(); //copy the structure 
  for (int i = 0; i <= source.Rows.Count-1; i++) //iterate through the rows of the source
    {
      DataRow currentRow = source.Rows[i];  //copy the current row 
      foreach( var colValue in currentRow.ItemArray)//move along the columns 
       {
         if (!string.IsNullOrEmpty(colValue.ToString())  ) // if there is a value in a column, copy the row and finish
             {                      
                  dt1.ImportRow(currentRow);
                  break; //break and get a new row                        
              }                   
         }
              }
    return dt1;
 }
  Permalink  
v5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,239
CHill60 3,490
Maciej Los 3,083
Jochen Arndt 1,975
ppolymorphe 1,880


Advertise | Privacy | Mobile
Web01 | 2.8.170525.1 | Last Updated 28 Jan 2015
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100