column expression??

  • Thread starter Thread starter perspolis
  • Start date Start date
P

perspolis

I have 3 columns in my datatabel name Total,unit,Price.
I use a column expression in my project..and in this
expression i multiplied two column...
for example MyTable.Columns[3].Expression="Price*Unit";
but when i want to update my datatable an exception arise and
says can not update computed column...:(
how can i do this???
 
I'm assuming that you want to create a fourth column that contains
price times unit, and then write this fourth column back to the
database. I don't believe there's any way to do this other than to
create the fourth column / read it from the database, then keep it in
synch in your code:

decimal unit = (decimal)MyRow["unit"];
decimal price = (decimal)MyRow["Price"];
MyRow["TotalPrice"] = price * unit;

On the other hand, if you just want to write the table back to the
database WITHOUT the computed column, and ADO.NET doesn't understand
this, then you probably have to look into data adapters and make some
adjustments there.

Which is it? Do you want a stored column that you compute in ADO.NET,
or do you want a computed column that doesn't get stored back to the
database?
 
thx for ur helping....
but i wanted to know if there is a way to store computed column without
managing myself..?
 
I did some reading over in microsoft.public.dotnet.framework.adonet
about DataColumn and Expression, and came up with the following.

Expression (calculated) columns in ADO.NET are intended for temporary
client-side use. You can't assign an Expression to a DataColumn that
already has data in it, and you can't persist a calculated column to
the database. In effect, a DataColumn with an Expression is a phantom
column that exists only as long as the DataColumn exists in your
ADO.NET dataset. You can't fill it from the database, and you can't
persist it.

This makes sense if you think about it from ADO.NET's point of view.
Let's suppose that you could persist a calculated column. Then,
presumably, you could read it back in next time. So, you have a column
full of data, and you tell ADO.NET, myColumn.Expression="unit*price".
What should ADO.NET do at this point? Should it run through all of the
rows, making sure that every data item in the column conforms to the
expression? If there are a lot of rows, that's a lot of work. What if
the contents of some rows doesn't conform to the expression? What
should it do then? This is perfectly reasonable, because ADO.NET can't
control what happens to the column in the database, and so some other
program may have changed some of the data items in it to be something
completely unexpected.

In general, the whole approach just doesn't make sense because what
most people are after most of the time is data integrity in the
database. Yes, data integrity in the client application is important
too, but the database is the heavy hitter. That's why most people
wanting calculated columns add the calculated column into the database
table rather than on the ADO.NET side.

Of course, one would want to add the calculation to ADO.NET as well, so
that new rows would contain the correct calculated value in the column.
The only way to do that is to add the column as a calculated column in
your database (assuming that your database supports phantom, calculated
columns), then either read the column into ADO.NET with no expression
on the column (if you're just doing reporting or lookup), or don't read
the column and reproduce it in ADO.NET with an Expression (if you're
changing data rows) and take care not to write it back to the database
(because the database has its own calculated column that will be
automatically updated when the other columns' data is written back).

Either that, or if your database doesn't support calculated columns,
you have to do the legwork yourself in code. Fortunately events make
that fairly straightforward.
 
Back
Top