I have a simple form, order header. It has a related table called order detail, I need to change a field value for all records in order detail for the current order when someone changes a value in the header. Best way to do this?



I have to do this because SQL Compact doesn't support multi table queries when using synchronization, so I have to set a flag on the detail records to base my query



My gut says to write a simple stored proc and call via 'ExecuteStoredProcedure', but I am asking because I think I might be overusing stored procs now that I understand them better, they are just so easy and lightweight.



Does ExecuteStoredProcedure respect CRUD/concurrency settings when called against a datasource? I also have two BO's on the form for the order header and detail records, is there a "execute this SQL statement against this BO" function I should be using instead of a stored proc?



Thanks.












Keith Chisarik

Keith,



My understanding is that you have some field in the header, that when changed, needs to cause an update in all the details related to that header. Here are some thoughts on the matter:



- If the data is already loaded into the BOs on the form, you might best be served by just dealing with it there. Then you get concurrency handling and all the other goodness of SF. I.e. a method on the detail BO that walks the appropriate details and updates the data. You'd update the field(s) and save it programmatically. You could wrap the whole thing in a transaction (that would be wise). This would necessarily handle keeping the UI updated as well. I'd think this would be the best for maintenance and the simplest to code (i.e. less bugs). If it is really slow though, you might try a sproc...



- As you are using SQL Compact, I'm guessing that everything is local. In that case, I might also use a sproc, which is likely faster than a method on a BO, which has to walk the data, as opposed to using a set manipulation (using an update statement will manipulate the data as a set and is much faster than doing a table walk (generally, assuming that you have indexed the FK on the details table)). You could still wrap it in a transaction here too. However, you'd still have to handle updating the BO and the UI, causing a need for more code on the client to handle the sproc updated data. I'd likely only use this option if it was faster than a BO method.



- A trigger could also be used (if SQL Compact can use triggers), but this gets even messier. You'd still have to update the BO/UI, but since you don't instigate the update on the details (the trigger does, automatically), this could get interesting. It also separates your business logic, making it harder to maintain.



- If there is a ton of data and it's not already on the client anyway, a sproc is the way to go. I don't think this is the case, but thought I'd through it out. It would be very hard to check concurrency and SF wouldn't do anything for you. You'd also have to update you're detail BO to get the changes made by the sproc (and to avoid concurrency issues). I tend to use sprocs when I'm messing with data that isn't on the client already and there is a lot of data. In these cases, no reason to pull it down, when SQL Server will do the work faster anyway.



Hope this helps.

Thank you very much Greg for the reply, good stuff there.

Keith Chisarik

Any time! Hope it helps.