locked
Calculations on DataColumns in a DataTable RRS feed

  • Question

  • I have a data table with a column . I need to do calculations on column1 data and add the result to the column 2. I need to subtract the current row and previous row of column1 and divide by the previous row value.

    column2[i]=(column1.Row[i]-column1.Row[i-1])/column1.Row[i-1] 

    I am getting the data using ADO.net logic in the class: DataLayer.

              

    dLayer.dt.Columns.Add("Column2") foreach(DataRow row in dataLayer.dt.Rows ) { // dLayer.dt.Columns["column2"] }






    • Edited by wpf_xam1 Saturday, August 29, 2015 1:43 PM
    Saturday, August 29, 2015 12:37 PM

Answers

  • You could convert the values in columns i and i - 1 to the appropriate data type (int, double, etc.) and use a for loop, e.g.:

                dLayer.dt.Columns.Add("Column2");
    
                dLayer.dt.Rows[0]["column2"] = 0;
                for (int i = 1; i < dLayer.dt.Rows.Count; ++i)
                {
                    int a = Convert.ToInt32(dLayer.dt.Rows[i]["column1"]);
                    int b = Convert.ToInt32(dLayer.dt.Rows[i - 1]["column1"]);
                    int c = a - b / b;
                    dLayer.dt.Rows[i]["column2"] = c;
                }



    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.




    • Marked as answer by wpf_xam1 Saturday, August 29, 2015 1:55 PM
    • Edited by Magnus (MM8)MVP Saturday, August 29, 2015 1:58 PM code change
    Saturday, August 29, 2015 1:30 PM

All replies

  • You could convert the values in columns i and i - 1 to the appropriate data type (int, double, etc.) and use a for loop, e.g.:

                dLayer.dt.Columns.Add("Column2");
    
                dLayer.dt.Rows[0]["column2"] = 0;
                for (int i = 1; i < dLayer.dt.Rows.Count; ++i)
                {
                    int a = Convert.ToInt32(dLayer.dt.Rows[i]["column1"]);
                    int b = Convert.ToInt32(dLayer.dt.Rows[i - 1]["column1"]);
                    int c = a - b / b;
                    dLayer.dt.Rows[i]["column2"] = c;
                }



    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.




    • Marked as answer by wpf_xam1 Saturday, August 29, 2015 1:55 PM
    • Edited by Magnus (MM8)MVP Saturday, August 29, 2015 1:58 PM code change
    Saturday, August 29, 2015 1:30 PM
  • A datacolumn in a datatable can be calculated using an expression:

    https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

    However, that expression has to act on columns in the current row.

    You could use a combination of an expression and code. Add a column which has the previous row's column1 value in it.

    Or you could just loop and do the calculation.

    Something like:

    double LastOne = 0.0;
    
    foreach(DataRow row in dataLayer.dt.Rows )
    {
      if (LastOne > 0)
          row["column2"] = ( row["column1"] - LastOne ) / LastOne;
      LastOnce = row["column1"];
    }


    Saturday, August 29, 2015 1:34 PM
  • See the edited post
    Saturday, August 29, 2015 1:44 PM
  • See the edited post

    You too :)

    The (slightly updated) code sample I provided iterates through all but the first row in the DataTable and sets the value of the second column to the value of column1 of the current row minus the value of column1 divided by the value of column1 of the previous row. Row number 0 has no previous row so you need to handle this one specifically. In my sample code I have just set its column2 to 0.

    The provided code code sample should get you the idea.

    Note that you should use Convert.ToDouble or Convert.ToDecimal instead of Convert.ToInt32 if your columns contains decimal values.

    Hope that helps.

    Please remember to mark all helpful posts as answer to close the tread and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Saturday, August 29, 2015 1:57 PM