Hi, I'm trying to write an silverlight app which operates on database through a web service. For now I don't know how many tables will be in the database but I think it will be about 25. Every insert,update,delete on the database should be overriden by my algorithms but almost the same for every table.
a) If I insert data as a result I should get the inserted row.
b) If I update data the algorithm should mark previous row as updated (by a flag column) and insert new one (for example by function a) )
c) If I delete data the algorithm should mark previous row as updated and insert a new one with flag marked as deleted
Two possibilities comes to my mind:
One option is to write 3 stored procedures for every table and execute them throgh entity framework. This would take time to write those procedures but maybe I could generate them by some small program. I think refactoring wouldn't be a problem becouse of the generator or Visual Studio's 2010 refactoring possibilities - I'm using a DataBase project in VS2010 and .NET 4.0 so it supports refactoring database objects. This solution would be easy for me to write becouse the logic would be on server side and I know how to write it in sql.
The second option is to write a universal function on the webservice which takes as a parameter my entity object and operates on it. The problem is that I don't know how to write a transaction in entity framework. For example it should update old record, insert new and return whole inerted data. It should also lock the table from modifinig by other users.
Please help me. What is the best solution for this problem?