Hi, hope you can help.
I have a Data Access Layer that uses DataView to provide data to my UI. I have a simple grid on my UI to display this data. The table is concerned with scheduled maintenance jobs and it has lots of fields for recurrence patterns. However, in my UI I want to display a human-readable version of this pattern. For instance:
My table looks like this:
TaskId
FirstActionType
FirstActionDays
FirstActionDistance
RecurringActionType
RecurringActionDays
RecurringActionDistance
But in my grid, I want something like this:
ACTION RECURRENCE PATTERN
Job A Starts in 30 days or 1000 miles, whichever is sooner, repeating again every 60 days or 5000 miles, which ever is sooner
Job B Starts in 45 days and repeats every 75 days thereafter
How can I add a calculated field to my DAL in this way The built in "Expression" functionality is not advanced enough for this purpose. I need to drop down to C# code to calculate the string.
Any advice would be most appreciated.
Regards,
Sigol.
Calculated fields?
EmekaAwagu
Aha! Now that might be the answer. I'll check it out and let you know...
Thanks Charles!
Jeff Weber
Hi Charles,
Thank you kindly for your reply. I am assuming that to use your code above, I would add a new column to the DataTable in memory and iterate through all records.
That's fine - I could certainly do that, but the problem is that I may have a large number of records. It would be more efficient to calculate 'on demand'. i.e. when the row scrolled in to view in the grid.
In Delphi (my background), you could create a calculated field using an event - add a new column to the table but then consume an event in the column like "OnCalculate".
The event is fired when the UI asks for the content of the column. Therefore, only records that are displayed need to go through the calculation phase
Once again, thanks for your help on this matter.
Regards,
Sigol.
YCDC.CN
Hi,
What about this:
foreach ( DataRow dr in jobTable.Rows ) {
StringBuilder sb = new StringBuilder();
//start
if (dr["FirstActionDays"] != DBNull.Value &&
dr["FirstActionDistance"] != DBNull.Value)
{
sb.AppendFormat( "Starts in {0} days or {1} miles, whichever is sooner. ",
dr[ "FirstActionDays" ], dr[ "FirstActionDistance" ] );
}
else if (dr["FirstActionDays"] != DBNull.Value) {
sb.AppendFormat( "Starts in {0} days. ", dr[ "FirstActionDays" ] );
}
else if (dr["FirstActionDistance"] != DBNull.Value) {
sb.AppendFormat( "Starts in {0} miles. ", dr[ "FirstActionDistance" ] );
}
else {
sb.Append( "Invalid start. " );
}
//repeat
if ( dr[ "RecurringActionDays" ] != DBNull.Value &&
dr[ "RecurringActionDistance" ] != DBNull.Value )
{
sb.AppendFormat( "Repeats again every {0} days or {1} " +
"miles, whichever is sooner. ",
dr[ "RecurringActionDays" ],
dr[ "RecurringActionDistance" ] );
}
else if ( dr[ "RecurringActionDays" ] != DBNull.Value ) {
sb.AppendFormat( "Repeats every {0} days thereafter. ",
dr[ "RecurringActionDays" ] );
}
else if ( dr[ "RecurringActionDistance" ] != DBNull.Value ) {
sb.AppendFormat( "Repeats every {0} miles thereafter. ",
dr[ "RecurringActionDistance" ] );
}
else {
sb.Append( "Invalid recurence. " );
}
//store value
dr[ "Pattern" ] = sb.ToString();
}
Charles
johnvarney
Hi Dreedle,
Thank you for your help. Yes I certainly could do that - and that may well be an option. The only time this wouldn't help that much is when the calculation algorithm requires some form of data from other layers in the application.
For example, in a point of sale application where users log in and out frequently as they serve customers, they do not have time to do a windows CTRL-ALT-DEL login/out. Therefore, the application would contain a 'soft login/out' facility which basically just records the current user in memory somewhere.
In the same application, it may be useful to show a grid of customer records, along with the date that they were last served by the currently-logged in user. This is information that the database cannot know since the current user is simply stored as a variable in the application somewhere.
I know that I could pass params to the stored proc, but I have decided to keep all my business rules in my data access layer, written in C#.
In this example, it would be useful to build a data access layer that can query the database (along with traditional calculated fields ala your suggestion) and then 'embelish' the returned records with more calculated fields that derive from application-level data.
Sorry, probably not very good at explaining what I mean.
But in any case, yours and Charles's solutions give me the tools I need.
Thanks for your help,
Sigol.
Vijay R
Hi Charles,
Thanks for your continued help (and apologies for my delay).
That solution wasn't quite what I was looking for. I would have preferred the calculation to occur in the data access layer rather than in the presentation layer, so that it is a shared business rule with other modules in the solution. In Delphi, the OnCalculate event (or whatever it was called - I can't remember anymore!!!) was on the TTable or TQuery component, which enabled you to centralise calculation (business rules).
It seems a bit of a limitation if ADO.NET/.NET 2.0 does not support on-demand calculation in the data layer. What if your data source contains millions of records
Perhaps one solution might to be construct a bindable component that consumes a datasource and allows extra [calculated on demand] columns to be added and served up to the presentation layer.
Once again, thanks for your help.
Best wishes,
Sigol.
nickwilliams
Another possibility is to create a new .net property in your DataView derived class that calculates the value.
The VS form designer will not pick up that column but you can still bind on it with code.
Charles
mobigital
You can also do it just in time in .net.
Look into the CellFormatting event of the grid control. You have to detect that it's asking a value for the correct column, then you have a reference to the value that was bound in that cell and the rowIndex. So if you bind the primary key on the column you want the calculated expression, you just have to do a lookup in the datasource for the object with the cell's primary key and then you can run the little algorithm to generate the text then assign the text to the cell's value.
Charles
WinFormsUser13232
Calculated fields are something the database should be doing. It will do it faster - it's what it's built to do.
To do it you will have to write a stored procedure in T-SQL - basically a select statement that will format and return your two columns. Then call the stored proc using SqlDataAdapter.SelectCommand.