New Posts New Posts RSS Feed: Updating a single column on a table - best practices?
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Updating a single column on a table - best practices?

 Post Reply Post Reply
Author
TonyHansen View Drop Down
Newbie
Newbie


Joined: 18-May-2012
Location: Adelaide
Posts: 7
Post Options Post Options   Quote TonyHansen Quote  Post ReplyReply Direct Link To This Post Topic: Updating a single column on a table - best practices?
    Posted: 21-May-2012 at 2:28am
Hi All,
 
I'm trialling DevForce, and looking at porting an application that has thousands and thousands of hard-coded SQL strings.  Scary stuff.
 
I'm basically looking at different scenarios, and trying to work out how to implement those in DevForce / Entity Framework.
 
For example, the 2-tier app I'm looking at currently would run something like this:
 
UPDATE
PURCHASEORDERS
SET
ORDER_AMOUNT = @p1,
ORDER_TAX = @p2
WHERE
ORDER_ID = @p3
 
Now, this might update 1000+ rows in the database.
 
I don't want to have to read all those entities into memory, set the values, then write them back out to the database ... that seems like overkill, and a performance black hole.
 
So how do I do this in general?  Do I:
 
1.   Setup a stored procedure to do the work, and somehow call that through DevForce / EF? ;
2.   Call this directly from the client? ;
3.   Setup a procedure on the server to do the work via a stored procedure, and call this from the client, passing the 3 parameters?
 
I'm looking for best practice suggestions / examples.  Can anyone help out?
 
Thanks!

T
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786
Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 21-May-2012 at 12:34pm
Hi Tony,
 
In this case, the best approach would be setting a stored procedure to do the work.
You can find detailed information about working with stored procs in DevForce at:
 
 
Regards,
   Silvio.
Back to Top
TonyHansen View Drop Down
Newbie
Newbie


Joined: 18-May-2012
Location: Adelaide
Posts: 7
Post Options Post Options   Quote TonyHansen Quote  Post ReplyReply Direct Link To This Post Posted: 21-May-2012 at 7:42pm
Thanks Silvio!
 
So basically, any time I'm looking to do mass updates on entities, especially if it's only a couple of columns, it's better to use Stored Procedures?
 
I'm not sure that's a viable long-term solution, given the application in question supports 4 different database backends ... methinks the principal may baulk big-time if I tell him we need to maintain 4 sets of thousands and thousands of stored procedures as well.
 
Is there no way to, say, hit the database with pure "UPDATE" SQL statements from the BOS?  So the client could call (maybe via an RPC) a procedure on the server, which then builds the SQL statement and runs it directly on the database?
 
I'm okay with the stored procedures solution, I thought that's where we'd end up.  Just want to check all the options ...
Back to Top
mgood View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 18-Nov-2010
Location: Emeryville, CA
Posts: 583
Post Options Post Options   Quote mgood Quote  Post ReplyReply Direct Link To This Post Posted: 21-May-2012 at 11:54pm
Tony,
Unfortunately, that's where things are. EF doesn't have support for bulk updates/deletes. I believe that's still a feature request that Microsoft is sitting on. I understand the lack of enthusiasm for stored procedures. 

There's an open source project called EntityFramework.Extended that adds bulk updates/deletes to EF. I've never used it myself, so I can't tell you if it works. It won't work with DevForce, so basically you have to bypass DevForce and do the bulk updates/deletes on the server directly against EntityFramework, for example from within a DevForce server method.

Back to Top
TonyHansen View Drop Down
Newbie
Newbie


Joined: 18-May-2012
Location: Adelaide
Posts: 7
Post Options Post Options   Quote TonyHansen Quote  Post ReplyReply Direct Link To This Post Posted: 22-May-2012 at 12:26am
Amazing that they can be sitting on something like this ... it's kinda like having crippled UD in CRUD :-D
 
That EntityFramework.Extended project looks interesting, I'll check it out.
 
Thanks!
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down