I have got a current website (according to .internet 1.1), that calls web-services (according to .internet 1.1) to gain access to data. The net-services call saved methods to operate the SQL.

I've got a parent object (letter) with numerous child objects (comments) (5-25) and I have to update one column on each one of the child objects to a different value - setting a flag around the comments to create the status as approved for example - this rentals are around the comments, not the letter, as further unapproved comments could be added later.

The typical approach we'll take is to produce a new webmethod e.g. ApproveComments and pass within the letterID, to create all of the comments presently around the letter to approved. Then an ApproveCommentsOnLetter saved procedure is known as to UPDATE the ApprovedFlag to True where letterID = the passed in parameter.

This method implies that with time we often finish up with a lot of very specific webservice calls and incredibly specific saved methods as new functionality is put in a specific part of the system. This works ok, but means we are always writing new code and I am considering the way we could do things in a different way. I can not alter the .internet version for which right now is really a small change, but am thinking about how to overcome things in a different way once we continue upgrading this application with time.

The concept I have had would be to rather produce a more generic webmethod known as UpdateComments, that might be employed for all updates to comments. I'd pass a range of Comments objects into this, which may have get/set qualities. Within this situation I'd loop through increase the Status property on each to True before delivering them to the net-method, but later on other posts might be occur an identical way, staying away from creating new web-techniques.

I am then tripping a little using the database section. It feels as though the next phase is always to come with an UpdateComment saved procedure. I'd then need to refer to this as multiple occasions, once for every comment consequently to become up-to-date. I am concerned that creating numerous repeated round-outings towards the database of these calls (5-25 presently) is not extremely powerful and merely generally feels bad. Another approach though will be able to see is perfect for these 'bulk' updates to return to getting a particular web-method and saved procedure devoted to upgrading confirmed property at any given time, however I am back where I began with creating new web-techniques and saved methods for every new functionality we increase the front-finish.

I'd be thankful for ideas regarding how to approach this - I can not go and add an ORM / latest version of .internet / etc. so have to consider just how to approach this in the technologies I curently have being used.

the next phase is always to come with an UpdateComment saved procedure. I'd then need to refer to this as multiple occasions

That's just it - the database doesn't handle collections of rows, as the webservice is mainly thinking about an accumulation of rows.

If you're able to range from saved methods to application-controlled data modification, there is nothing that states you cannot package multiple update claims right into a SqlCommand's text.

Or maybe that isn't a choice, there is nothing that states you cannot call a saved proc many occasions in a single round trip.

@Durator

Personally I'd continue lower the road of creating specific calls in to the web service instead of try to make generic types of calls. It might be a little of labor but over time its smart off because the system is much more maintainable and actually you've provided an abstraction layer that's business centric. That's, from the business process perspective the operation is to approve comments for any given letter. A particular method to achieve that causes it to be very obvious and maps towards the business process.

Now so far as multiple round outings are worried... Your internet service could accept a range of comment ids together with the letter id. This way, you have made one call over the wire that consists of the letter id and a range of comment ids you need to update.

The implementation of that may be completed in a couple of ways. 1. Send your saved proc the variety of ids delimited with commas and them within the sp, split the ids and perform the updates.

If you are using MS SQL server 2008, then there's a brand new variable type known as a TableValueParameter or TVP. Basically, you are able to send your storped proc b .Internet DataTable that consists of the records you need to send (posts and rows) as well as in the SP you treat the parameter like a "table" so that you can do joins onto it it utilize it to complete your updates.