none
Delete rows from a datatable when a specific column has a null value RRS feed

  • Question

  • I am uploading from excel and often end up with a bunch of empty rows in my datatable, can i delete from a datatable so how like

    dt.delete where columnOne=""

    ?

    or do i have to iterate through the rows?  Is there a way to get the non ""  rows by using a dataview and then saving those rows to a new datatable?
    McC
    Friday, October 30, 2009 4:18 PM

Answers

  • I dont think you can delete multiple rows directly from a datatable. You will have to load the selected rows into a datarowcollection and delete it from there. Ofcourse the alternate is to loop through the datatable and delete it row by row.


    Hope this helps


    • Marked as answer by Yichun_Feng Friday, November 6, 2009 1:47 AM
    Sunday, November 1, 2009 3:59 AM
  • There's always more than one way to skin a cat, but deleting muliple rows at once isn't an option.

    Yes, you can iterate through the Rows and delete one at a time (be sure to iterate backwards through the collection):

    for (int i = dt.Rows.Count-1; i >= 0; i--)
    {
        // whatever your criteria is
        if (dt.Rows[i]["mycolumn"].ToString() == "")
            dt.Rows[i].Delete();
    }

    Yes, you can create a DataTable from a DataView (a DataView has a .ToTable() method for this purpose).
    DataView dv = new DataView(dt);
    dv.RowFilter = "mycolumn = ''";
    DataTable dtNew = dv.ToTable();
     
    // you could also use the DefaultView of the table dt
    dt.DefaultView.RowFilter = "mycolumn = ''";
    DataTable dtNew = dt.DefaultView.ToTable();

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Yichun_Feng Friday, November 6, 2009 1:47 AM
    Sunday, November 1, 2009 9:57 PM

  • Hello Bonnie,

    You have created view where for the null value column!!!

    I think in your above code filter should be like Not equal instead

    DataView dv = new
     DataView(dt);
    dv.RowFilter = "mycolumn <> ''"
    ;
    DataTable dtNew = dv.ToTable();
     
    // you could also use the DefaultView of the table dt
    
    dt.DefaultView.RowFilter = "mycolumn <> ''"
    ;
    DataTable dtNew = dt.DefaultView.ToTable();


    Tejas Mer
    • Marked as answer by Yichun_Feng Friday, November 6, 2009 1:47 AM
    Tuesday, November 3, 2009 10:01 AM

All replies

  • I dont think you can delete multiple rows directly from a datatable. You will have to load the selected rows into a datarowcollection and delete it from there. Ofcourse the alternate is to loop through the datatable and delete it row by row.


    Hope this helps


    • Marked as answer by Yichun_Feng Friday, November 6, 2009 1:47 AM
    Sunday, November 1, 2009 3:59 AM
  • There's always more than one way to skin a cat, but deleting muliple rows at once isn't an option.

    Yes, you can iterate through the Rows and delete one at a time (be sure to iterate backwards through the collection):

    for (int i = dt.Rows.Count-1; i >= 0; i--)
    {
        // whatever your criteria is
        if (dt.Rows[i]["mycolumn"].ToString() == "")
            dt.Rows[i].Delete();
    }

    Yes, you can create a DataTable from a DataView (a DataView has a .ToTable() method for this purpose).
    DataView dv = new DataView(dt);
    dv.RowFilter = "mycolumn = ''";
    DataTable dtNew = dv.ToTable();
     
    // you could also use the DefaultView of the table dt
    dt.DefaultView.RowFilter = "mycolumn = ''";
    DataTable dtNew = dt.DefaultView.ToTable();

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Yichun_Feng Friday, November 6, 2009 1:47 AM
    Sunday, November 1, 2009 9:57 PM

  • Hello Bonnie,

    You have created view where for the null value column!!!

    I think in your above code filter should be like Not equal instead

    DataView dv = new
     DataView(dt);
    dv.RowFilter = "mycolumn <> ''"
    ;
    DataTable dtNew = dv.ToTable();
     
    // you could also use the DefaultView of the table dt
    
    dt.DefaultView.RowFilter = "mycolumn <> ''"
    ;
    DataTable dtNew = dt.DefaultView.ToTable();


    Tejas Mer
    • Marked as answer by Yichun_Feng Friday, November 6, 2009 1:47 AM
    Tuesday, November 3, 2009 10:01 AM
  • Yes, you're absolutely right. I guess I just typed too fast. Thanks for correcting my typo. =0)
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, November 3, 2009 8:51 PM