how to reference value in DataColumn Expressions

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 ?

Answer 1

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

Answer 2

the question is about how  to reference another table/row/column in an Expression ?

I am familiar with DataRelation

Answer 3

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

Answer 4

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

Answer 5

Hi Bonnie,

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

---

Regards, Nico

Answer 6

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

Answer 7

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