CODEWITHSUNDEEP
c#.net
user101010101
user101010101

Reputation: 1659

Loop through each row in a column find a null value and replace in a datatable c#

I am trying to write a linq query or another way that will loop through all the rows in a column within a data table.

The data column header is known in advanced. As it loops I want to be able to check for a null value in each cell and if found put a default value in, in the case below it is [dob].

Below is an example of a dataTable, I want to able to loop through the [dob] column and look for the null value and replace it with a default value say 01/01/1901.

[firstName],     [lastname],     [dob]
tester,          testerSurname,  null
tester2,         tester2Surname, 25/04/1876



 foreach (DataColumn column in table.Columns)
 {
      if (table.Rows.OfType<DataRow>().Any(r => r.IsNull(column)))
      {

      }

I have started above but could not find away of getting the value and assigning it to another value but could find the null.

Upvotes: 0

Views: 5373

Answers (4)

Shantanu Gupta
Shantanu Gupta

Reputation: 21198

If checking for DBNull, the code from mikeschuld's answer should work:

DataTable dt;
string col = "ColumnIAmCheckingForANullValue";
string anotherValue = "My Default Value for NULL";

foreach (DataRow row in dt.Rows) {
    if (row[col] is DBNull) {
        row[col] = anotherValue;
    }
}

You can remove NULL from data using query 

SELECT ISNULL(col, '') FROM MyTable

Upvotes: 0

ZafarYousafi
ZafarYousafi

Reputation: 10850

If u r using data table then u can also loop through columns in a row

 foreach (DataRow dr in table.Rows)
        {

            foreach (DataColumn clm in table.Columns)
            {
                if (dr[clm] == DBNull.Value)
                {
                    dr[clm] = GetDefaultValueForColumn[clm.ColumnName];
                }
            }
        }

Upvotes: 0

48klocs
48klocs

Reputation: 6103

If you're looking to perform a bulk database update, a Linq query is not the best way to go about it. You're better off writing a SQL query to perform the update - the COALESCE operator will be your friend.

UPDATE tablename
SET dob = COALESCE(dob, '1901/01/01')

Upvotes: 0

mikeschuld
mikeschuld

Reputation: 963

You wouldn't loop through each row in a column, you would just loop through the rows and check that column for each row in the loop:

DataTable dt;
string col = "ColumnIAmCheckingForANullValue";
string def = "My Default Value for NULL";

foreach (DataRow row in dt.Rows) {
    if (string.IsNullOrEmpty(row[col].ToString())) {
        row[col] = def;
    }
}

Upvotes: 2

Related Questions