none
how to reference value in DataColumn Expressions RRS feed

  • Question

  • I have an existing expression like this

        DataTable1.Columns.Add("value", typeof(double), "rate * loan_amt");

    in a datatable with 10000 rows where rate is same for all rows and loan_amt varies

    When the rate changes, it changes for all
    currently that means iterating through all rows like so

         foreach(DataRow dr in DataTable1.Rows) dr["rate"] = new_rate;

    wondering if there's a better way using a ReferenceTable (with only 1 row ) in the same DataSet and linking it somehow like so

         DataTable1.Columns.Add("value", typeof(double), "RefTable.Row0.rate * loan_amt");

    so changing the rate would be as simple as

         RefTable.Rows[0]["rate"] = new_rate;


    Or is there any other way ?

    • Edited by kumarnyc Friday, March 2, 2012 10:14 PM
    Friday, March 2, 2012 10:13 PM

Answers

  • Hi,

    you'd have to create a parent table with the one rate record (using a relation),

    create all your records refrencing the one record,

    and in the expression use:

    Parent.Rate * loan_amt

    Regards, Nico


    • Edited by Nico Boey Monday, March 5, 2012 8:38 AM
    • Proposed as answer by BonnieBMVP Monday, March 12, 2012 2:44 PM
    • Marked as answer by Alan_chenModerator Tuesday, March 13, 2012 6:23 AM
    Monday, March 5, 2012 8:37 AM

All replies

  • Yea that is why there are datasets.

    A dataset is a wrapper around more datatables where beside a collection of datatables is a collection of relations.

    So your problem would be solved with two tables, where is one for the rate. 

    If you create it like that in your database and you select both then the desinger (wizard) will creates all those relations in your dataset and you can even use it in a very nice way strongly typed. 

    Otherwise you can set the relation by using the primary keys. 

    Very simple to do like you can see on this page.

    http://msdn.microsoft.com/en-US/library/ay82azad(v=vs.100).aspx


    Success
    Cor

    Saturday, March 3, 2012 8:47 AM
  • the question is about how  to reference another table/row/column in an Expression ?

    I am familiar with DataRelation

    Saturday, March 3, 2012 5:11 PM
  • Hi,

    you'd have to create a parent table with the one rate record (using a relation),

    create all your records refrencing the one record,

    and in the expression use:

    Parent.Rate * loan_amt

    Regards, Nico


    • Edited by Nico Boey Monday, March 5, 2012 8:38 AM
    • Proposed as answer by BonnieBMVP Monday, March 12, 2012 2:44 PM
    • Marked as answer by Alan_chenModerator Tuesday, March 13, 2012 6:23 AM
    Monday, March 5, 2012 8:37 AM
  • Hi Nico -- I tried playing around with this and I just couldn't find the right syntax to make it work. Can you post a working code snippet that shows setting the Relationship and the Expression you'd use based on that Relationship?

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, March 12, 2012 1:06 AM
  • Hi Bonnie,

    I posted a sample here: http://code.msdn.microsoft.com/A-datacolumn-expression-4adcd177


    Regards, Nico

    Monday, March 12, 2012 9:40 AM
  • See here a very short sample on our website. 

    I see you use C# but the code is so simple it should be usable for everybody who know C#

    http://www.vb-tips.com/DataGridRelation.aspx

    If not here a snippet converter from VB to C#

    http://www.tangiblesoftwaresolutions.com/Product_Details/Instant_CSharp.html


    Success
    Cor

    Monday, March 12, 2012 10:10 AM
  • Thanks guys!!

    Cor -- the problem wasn't creating the relationship. I know how to do that. The OP knows how to do that. The problem for me was figuring out the proper syntax needed in the Expression.  LOL

    Nico -- when you used Parent.Rate in your code snippet, I didn't realize that "Parent" was a key word. I thought it was either the name of your DataTable or the name of your Relationship. That's why I wanted to see a full set of code snippets. As soon as I changed my expression to use the actual word "Parent", it worked fine.

    I've been using .Net for 10 years now and I just learned something new that has probably been in .NET from the beginning. (Probably never learned it because I never really used relationships much).


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, March 12, 2012 2:44 PM