none
Performing a Exponential Calculation in a Datatable RRS feed

  • Question

  • Hi Everyone,

    I've been wrestling this issue for a while, and though I found an unelegant solution I was wondering if the experts knew of a better solution to my problem.

    Here's the scenario. I have a datatable with the columns Column1, Column2, and Column3. The value I'd like to get in Column3 is Column1^Column2 (^ for exponential, just in case). The ideal solution would do this in an expression, but I know that expressions can't handle exponential calculations, so I threw that idea out. Next, I found some posts online that suggested that I should capture a change event in Column1 and Column2 and recalculate Column3. That works ok.

    The problem is, Column1 and Column2 are both values calculated via expressions, and I haven't found an event that captures the value change in the DataColumn via an expression. The only events I could find only caught events when the value is changed by code or by the user. I could go upstream in the datatable and capture the change event for all of the columns that are not expressions and could affect the value in either Column1 or Column2, but there are about 50 columns that could affect either Column1 or Column2 and trying to catch them all is very undesireable.

    I was hoping that someone knew of a way I could get around this so that I don't need to try to capture events on 50 columns, and just catch the change events in Column1 or Column2. I know this isn't realistic since datatable expressions do not support user defined functions, but if I could create a "Exp(a,b)" function and use that in the expression that would be beyond ideal.

    Thanks for your help
    Thursday, November 5, 2009 5:43 PM

Answers

  • Hi HsARAI,

     

    Welcome to ADO.NET DataSet forum!

     

    Based on the MSDN documentation, Handling DataTable Events (ADO.NET):

    “Changes to expression columns never raise DataTable events.  Changes to expression columns only raise DataView and DataRowView events”.

     

    I used the DataView.ListChanged event to detect the modification of expression columns. 

     

    Here is some sample codes.  The column type are all integers. 

     

    Create DataTable, set ListChanged event handler, and bind it to DataGridView

    =========================================================================
                DataTable dt = new DataTable();           

                dt.Columns.Add("Column1", typeof(int));

                dt.Columns.Add("Column2", typeof(int));

                dt.Columns.Add("Column3", typeof(int));

                dt.Columns.Add("Column4", typeof(int));

                dt.Columns.Add("Column5", typeof(int));

     

                dt.Columns[2].Expression = "Column1 + Column2";

                dt.Columns[3].Expression = "Column1 * 2";

     

                dt.DefaultView.ListChanged += new ListChangedEventHandler(DefaultView_ListChanged);

               

                dataGridView1.DataSource = dt;
    =========================================================================

     

    ListChanged event handler and custom exponential calculation:

    =========================================================================
            bool isFire = true;

     

            void DefaultView_ListChanged(object sender, ListChangedEventArgs e)

            {

                if (e.ListChangedType == ListChangedType.ItemChanged)

                {

                    if (isFire)

                    {

                        if (dt.Rows[e.NewIndex]["Column3"] is int && dt.Rows[e.NewIndex]["Column4"] is int)

                        {

                            isFire = false;

                            dt.Rows[e.NewIndex]["Column5"] = Power((int)dt.Rows[e.NewIndex]["Column3"], (int)dt.Rows[e.NewIndex]["Column4"]);

                        }

                    }

                    else

                        isFire = true;

                }

            }

     

            private int Power(int a, int b)

            {

                int result = 1;

                for (int i = 0; i < b; i++)

                {

                    result *= a;

                }

                return result;

            }
    =========================================================================

    The Boolean value isFire is a flag to record whether this event is fired since we manually set the Column5 values. 

     

     

    Please have a test, if you have any questions, please feel free to let me know.

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, November 6, 2009 6:39 AM
    Moderator

All replies

  • Hi HsARAI,

     

    Welcome to ADO.NET DataSet forum!

     

    Based on the MSDN documentation, Handling DataTable Events (ADO.NET):

    “Changes to expression columns never raise DataTable events.  Changes to expression columns only raise DataView and DataRowView events”.

     

    I used the DataView.ListChanged event to detect the modification of expression columns. 

     

    Here is some sample codes.  The column type are all integers. 

     

    Create DataTable, set ListChanged event handler, and bind it to DataGridView

    =========================================================================
                DataTable dt = new DataTable();           

                dt.Columns.Add("Column1", typeof(int));

                dt.Columns.Add("Column2", typeof(int));

                dt.Columns.Add("Column3", typeof(int));

                dt.Columns.Add("Column4", typeof(int));

                dt.Columns.Add("Column5", typeof(int));

     

                dt.Columns[2].Expression = "Column1 + Column2";

                dt.Columns[3].Expression = "Column1 * 2";

     

                dt.DefaultView.ListChanged += new ListChangedEventHandler(DefaultView_ListChanged);

               

                dataGridView1.DataSource = dt;
    =========================================================================

     

    ListChanged event handler and custom exponential calculation:

    =========================================================================
            bool isFire = true;

     

            void DefaultView_ListChanged(object sender, ListChangedEventArgs e)

            {

                if (e.ListChangedType == ListChangedType.ItemChanged)

                {

                    if (isFire)

                    {

                        if (dt.Rows[e.NewIndex]["Column3"] is int && dt.Rows[e.NewIndex]["Column4"] is int)

                        {

                            isFire = false;

                            dt.Rows[e.NewIndex]["Column5"] = Power((int)dt.Rows[e.NewIndex]["Column3"], (int)dt.Rows[e.NewIndex]["Column4"]);

                        }

                    }

                    else

                        isFire = true;

                }

            }

     

            private int Power(int a, int b)

            {

                int result = 1;

                for (int i = 0; i < b; i++)

                {

                    result *= a;

                }

                return result;

            }
    =========================================================================

    The Boolean value isFire is a flag to record whether this event is fired since we manually set the Column5 values. 

     

     

    Please have a test, if you have any questions, please feel free to let me know.

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, November 6, 2009 6:39 AM
    Moderator
  • Hi HsARAI,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, November 10, 2009 12:58 AM
    Moderator
  • Hi Lingzhi,

    Sorry for the late reply, I had to translate the code in to VB.NET, and a few other tasks came in that I needed to handle. So far, it looks like the code is working beautifully.

    If I'm reading the documentation correctly, it looks like there's nothing in the ListChangedEventArgs that tells you which column and row had changed, correct? It's not a big deal but I've found it to be somewhat inefficient since the code is run each and every time something changed in the table, and if you're not careful you could fall in to an infinite loop since each time the code updates the cell, the event calls the code again.

    Thanks!
    Thursday, November 12, 2009 5:42 PM
  • Hi HsARAI,

     

    You are correct!  We need to be careful about the codes to avoid any infinite loop here.   Besides, thanks for marking my reply as the answer.  I am glad that the problem has been resolved.  :)


    Have a nice weekend!
     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, November 13, 2009 1:50 AM
    Moderator