CODEWITHSUNDEEP
c#datatable
Sora
Sora

Reputation: 2551

Delete rows that contains null value from dataTable

I have a DataTable that contains 7800 rows each row contains 3 column that may have Null values if the second column is null i am deleting the entire row the problem is that even when i am deleting the row the DataTable still contain the same amount of row number wish is 7800

the code : 

 string query = "Select STORE_NAME, STORE_LATITUDE, STORE_LONGTITUDE "
  +" From stores inner join contact_information on stores.STORE_ID=contact_information.CONTACT_ID "
    +" where CONTACT_TYPE_ID=1 "
      +" AND CONTACT_COUNTRY_ID="+Country
         +" AND CONTACT_CASA_ID="+Casa
            +" AND CONTACT_TOWN_ID="+Town;
    DataTable dt = new SQLHelper(SQLHelper.ConnectionStrings.KernelConnectionString).getQueryResult(query);
    dt = removeNullColumnFromDataTable(dt);

the function :

    public static DataTable removeNullColumnFromDataTable(DataTable dt)
   {
    for (int i = dt.Rows.Count - 1; i >= 0; i--)
      {
       if (dt.Rows[i][1].ToString() == null)
           dt.Rows[i].Delete();
      }
     return dt;
   }

Upvotes: 2

Views: 21381

Answers (3)

meliweb
meliweb

Reputation: 1

List<DataRow> changedNotNull = (from r1 in changed
                                where r1.ItemArray[0] != DBNull.Value
                                select r1).ToList();

did the trick for me !

Upvotes: 0

sqladmin
sqladmin

Reputation: 2199

you can change first row to this (for sql server):

string query = "Select STORE_NAME, isnull(STORE_LATITUDE,'') as STORE_LATITUDE, STORE_LONGTITUDE "

and just check for empty string (if field type is char, varchar ... etc..)

dt.Rows[i][1].ToString() == ""

or use isnull(STORE_LATITUDE,0) and check for zero dt.Rows[i][1].ToString() == "0" if field type is numeric

Upvotes: 1

Ahmed KRAIEM
Ahmed KRAIEM

Reputation: 10427

public static void RemoveNullColumnFromDataTable(DataTable dt)
{
    for (int i = dt.Rows.Count - 1; i >= 0; i--)
    {
        if (dt.Rows[i][1] == DBNull.Value)
            dt.Rows[i].Delete();
    }
    dt.AcceptChanges();
}

Upvotes: 8

Related Questions