I need to use C# methods to set the values of some DataColumns in a table. DataColumn provides an Expression property, but that only allows a subset of SQL style calculations on the dataset. What I need to do is call a C# method to perform calculations too complex for the Expression syntax and then put the data into a row’s column. I would like that value to be set when the data table is loaded and again whenever the current row is updated, at least.
Is there any way to implement a true calculated field in ADO.NET? When I used Delphi, you could use the designer to add a calculated field to a table and define the event which updates it, and the event would be called as needed. I can’t seem to find an equivalent in ADO.NET.
DataRow, DataRowView and DataColumn have no events of use. I am not allowed to edit data in the DataTable.RowChanging event. If I edit in the RowChanged event it causes an endless loop because you’re changing the row again. The various events of the BindingSource only work on the currently selected record (useful for the second case, at least).
The only solution I have found to date is to call my method in BindingSource.CurrentChanged. That only fills the current record, so I must step through all of the rows in my DataTable immediately after a Fill operation to trigger that event to set the initial values. That’s a bit inefficient when there a lot of rows in the table.
Is there a more efficient way to get a value set?
Would it perhaps be possible to modify your SQL query to do the work for you?
You could do the initial query, then do a UNION ALL using the query that does the calculations. That way, you would have one single result set, and the last row should be the calculations that you require.
I can't think of any other way to do what you're asking, aside from how you've described it. You could iterate through all of the rows in the DataTable, get the values of each row in the desired column, then perform your calculations. But essentially, that sounds like what you're doing already...
My applications are targeted to aquatic sciences, and some of the logic and equations involved are not the kind of thing you'd want to do in SQL. Also, I need to keep open the option of handling alternate databases which do not support the operations required, or even stored procedures at all.
So, I guess the way I'm doing it is all that can be done. And yes, I am indeed iterating the table (right after Fill), updating the calculated data with the same event which is used during the rest of the session. It works, it just introduces a lot of overhead when a form opens if the dataset is large.
Anyway, it never hurts to check.
I guess
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.