Delete rows from a datatable when a specific column has a null value

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

Answer 1

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.

http://authors.aspalliance.com/aspxtreme/sys/Data/datarowcollectionclassremove.aspx

Hope this helps

Answer 2

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

Answer 3

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

Answer 4

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