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 ?
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"]);
}
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>
DataTablebut there's nothing wrong with using a loop. Or change the query that populates theDataTable.