How to find the empty cells in the datatable and replace with 0

Question

I am working with a very large datatable which has 5000 rows and 8000 columns. In the data table there are some empty cells. I use the following code to fill the empty cells with 0. But since the datatable is very large, the speed is really slow!!! I just want to ask if there are some other ways instead of the for loop.

for (int i = 0; i < SP_dt.Rows.Count; i++)
{
    for (int j = 0; j < SP_dt.Columns.Count; j++)
    {
        if (SP_dt.Rows[i][j].ToString() == "")
            SP_dt.Rows[i][j] = 0;
        Console.WriteLine("{0}  {1}",i,j);
    }

}

I don't think there's any faster way if you are using DataTable. Maybe you should consider SQL if your task allows so? Also you may try removing WriteLine - if you call it every time, it will greatly impact performance. — ttaaoossuuuu, Mar 04 '16 at 22:02
I can remove console.writeline. But I don't think this will help much — Daniel, Mar 04 '16 at 22:04
This should be done via SQL. SQL works on set-based operations and is lightning fast compared to anything you can do on the code-side to replicate (and, it's pretty easy... if you have actual T-SQL, `COALESCE(ColumnName, 0)` is the function you need) — jleach, Mar 04 '16 at 23:10
Can you give some more details for SQL. I haven't used that before. Thanks a lot! — Daniel, Mar 04 '16 at 23:20

score 1 · Accepted Answer · edited May 23 '17 at 11:45

1

The problem is better solved in the SQL than in C# code, create a stored procedure for your query, and then use COALESCE system function.

The COALESCE function will basically check a set of elements given in its arguments from the left to right till it finds that it is not NULL and returns it. This way, you could put your default value 0 in the last element of COALESCE:

create procedure spMyProc
as
begin
  select 
    COALESCE(colName1HavingNumericDataTypeToBeChecked, 0),
    COALESCE(colName2HavingNumericDataTypeToBeChecked, 0), 
    COALESCE(colNameNHavingNumericDataTypeToBeChecked, 0) 
  from tableName
end

Then in your C# code, you just call that stored procedure.

If your colName1HavingNumericDataTypeToBeChecked is not NULL, it will return the value in the colName1HavingNumericDataTypeToBeChecked. If it is NULL, then it will look up the next item and find 0 (which is not NULL) and it will return 0.

edited May 23 '17 at 11:45

Community

1
1

answered Mar 05 '16 at 04:01

Ian

30,182
19
69
107

A stored procedure is hardly necessary... `COALESCE()` can be used in any "regular" query as well (that said, it's not necessarily bad to use a sproc, just figured I'd point out that it's not required). – jleach Mar 05 '16 at 20:11
@jdl134679 I agree that stored procedure is not *necessary* here, but it is *more efficient*, as the OP wants. If we use regular query, the execution plan will be created multiple times - and thus adding more time overhead. But if we use stored procedure, the execution plan will only be created once - and thus more efficient. ;) – Ian Mar 06 '16 at 00:47
If the resultant text of the compiled query doesn't change between calls, the plan is only created once and subsequent calls use a cached plan. Of course a procedure does guarantee this, but my argument is more in that OP is clearly not a SQL guru and the idea of a stored procedure might be a step more involved than he'd like :) Cheers – jleach Mar 06 '16 at 08:51

How to find the empty cells in the datatable and replace with 0

1 Answers1