You and Trent are suggesting that I need to relocate the business logic back into the database.
I'm not exactly suggesting that and I doubt Trent is (but he'll have to speak up for himself of course).
The SF framework is very flexible. It doesn't force you to do much of anything...though there are many things that are much easier if you do it certain way. In this case, it is really a matter of what you need.
You can put all the logic in the BO. You just need to understand that the Filter property works with the underlying DataTable (er...DataView actually) of the BO. If the data isn't in that DataTable, it can't work. So, you have to get the data in there somehow. A bunch of ways to do that, including Fill methods that add the extra data. Or you could write code to add the columns, then set the data for each row. However, if you want the BO to filter or perform some other logical action (like calculating a total) on the records, it has to have all the data it needs. Which usually means more data over the wire. If this is OK, then great. If it isn't (as is my case)....
Then you need to get the DB to do some work for you. You could use sprocs, views or you can build the SQL in the app (which is putting the logic in the BO...but the work is on the db server). The db needs to filter the data for you. Generally, this will be faster than .NET code...db servers are optimized for set based actions (like select queries). Sprocs/views are going to be faster that SQL your app generates because there is already a query plan in place (it's compiled, sql style), so if speed is really important, I use those. Most of the time I just do SQL (using a SqlCommand object), until it obvious I need to do something else.
I'd like to address your posted code. First, you
are using the db, when you call the sprocs in the OnWorkOrder and OnSalesOrder properties. If you just relocated this logic to the query you use to do the fill, which is defined in the BO, these column could then be within the data table. Likewise, the RequiredQty could be moved to the query also. It just a matter of were you want the work to be done and how many calls to the db you want. Currently, you'd have a call to fill the BO, a call each for OnWorkOrder and OnSalesOrder, plus another two calls for RequiredQty (it references OnWorkOrder and OnSalesOrder, so the db would get called again) for
every row, whenever the row is accessed. This might be fine, depending on the connection of the client and how much data is being pulled across and how often the custom properties are being accessed. If you cached the custom properties after the first retrieval, you would reduce the db hits significantly. However, if you just let the DB do the work, you'd have one call and the values would already be cached.
Note, the
logic to get the data is still in the BO if you're using SQL there, but the
work is done on the db.
So, I'm not suggesting that you have to put your logic in any particular place. Rather, I'm trying to help you understand what you can do and why you might do it that way. I'm sure I've just touched on some of the things done by the various SF developers out there. In this particular case, you just need to understand that the Filter property works with data that is already on the client and it must be in the DataTable of the BO. If you do it this way, then any data needed for any calculated fields must be pulled before the calculations can happen. This could be slow if the client network connection is slow and/or there is a lot of data. Give these constraints, there are other ways to accomplish the same thing. I hope this is starting to make sense to you. If not, keep the posts coming!
P.S. While writing this Peter posted a nice example of way that this can be done. I like the generic sproc approach, which is likely quite fast. I also like the wrapper around the fill to handle retries, error handling. As to the concern about sprocs for each custom property, you certainly don't need this. Here are some options:
- As you're doing, use a sproc
- Use SQL in the custom property (logic in BO).
- Use another BO...i.e. if you have a customers and an order BO and you want a custom property on the customers bo that indicates the total of outstanding orders, create a function in the order bo that does the work. This method could use a sproc or SQL to do the work
- Calculate result based on values in current row
- Calculate result based on values in other rows that are in BO currently, which could be the limited to the current view or the entire data table.
- Add caching, so you retrieve the value once, the cache it. Retrieve using favorite method (sproc, sql, another bo, walking exiting data in current bo)
- Mix and match for even more fun!
Good luck!