CODEWITHSUNDEEP
c#linqdatatable
Harshit
Harshit

Reputation: 5157

datatable replace zero with null or empty string via linq

I have a datatable which contains some values with 0s. Now I want to replace all the 0s with null or "".

Sample Datatable

A       B       C
6       0       7
0       7       0
5       0       4

Expected

A       B       C
6               7
        7        
5               4

I can do it via for loop but is it possible to do it via LINQ ?

Upvotes: 2

Views: 8030

Answers (2)

Ian
Ian

Reputation: 30813

Since LINQ supports querying and not updating, the best you could do is make use of its querying to get all the column info of a DataTable and then update your cell one-by-one.

One way to do it is by implementing a method whose input is DataTable to do the task:

private void dtChangeZeroToNull (DataTable dataTable){
    List<string> dcNames = dataTable.Columns
                            .Cast<DataColumn>()
                            .Select(x => x.ColumnName)
                            .ToList(); //This querying of the Column Names, you could do with LINQ
    foreach (DataRow row in dataTable.Rows) //This is the part where you update the cell one by one
        foreach (string columnName in dcNames)
            row[columnName] = (int)row[columnName] == 0 ? DBNull.Value : row[columnName];               
}

And there you go.

Now, to change each cell whose value is 0 to DBNull in a DataTable, you can simply call the method:

dtChangeZeroToNull (dataTable);

Note: Seems like the DataTable.Rows and DataTable.Columns do not implement LINQ Select. Otherwise we could immediately do this

List<string> dtcolumnNames = dataTable.Columns.Select(x => x.ToString());

Without a need of Cast<DataColumn>

Upvotes: 2

Kosala W
Kosala W

Reputation: 2143

You have to do something like this.

 //I am constructing a data table here. You already have this I guess.
        var dataTable = new DataTable();
        dataTable.Columns.Add(new DataColumn("A", typeof(int)) { AllowDBNull = true });
        dataTable.Columns.Add(new DataColumn("B", typeof(int)) { AllowDBNull = true });
        dataTable.Columns.Add(new DataColumn("C", typeof(int)) { AllowDBNull = true });

//Assign values
        DataRow row1 = dataTable.NewRow();
        row1["A"] = 6;
        row1["B"] = 0;
        row1["C"] = 7;
        dataTable.Rows.Add(row1);

        DataRow row2 = dataTable.NewRow();
        row2["A"] = 0;
        row2["B"] = 7;
        row2["C"] = 0;
        dataTable.Rows.Add(row2);

        DataRow row3 = dataTable.NewRow();
        row3["A"] = 5;
        row3["B"] = 0;
        row3["C"] = 4;
        dataTable.Rows.Add(row3);

//This is what you need.
        foreach (DataRow row in dataTable.Rows)
        {
            if ((int)row["A"] == 0)
            {
                row["A"] = DBNull.Value;
            } 
            if ((int) row["B"] == 0)
            {
                row["B"] = DBNull.Value;
            }
            if ((int) row["C"] == 0)
            {
                row["C"] = DBNull.Value;
            }
        }

//test the changes
        foreach (DataRow row in dataTable.Rows)
        {
            Console.WriteLine("" + row["A"] + "; " + row["B"] + "; " + row["C"]);
        }

Upvotes: 4

Related Questions