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

 

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:

 

http://drc.ideablade.com/xwiki/bin/view/Documentation/stored-procedure-queries - http://drc.ideablade.com/xwiki/bin/view/Documentation/stored-procedure-queries

http://drc.ideablade.com/xwiki/bin/view/Documentation/code-sample-stored-procedure-queries-silverlight - http://drc.ideablade.com/xwiki/bin/view/Documentation/code-sample-stored-procedure-queries-silverlight

 

Regards,

   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 ...

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.

https://github.com/loresoft/EntityFramework.Extended - https://github.com/loresoft/EntityFramework.Extended

 

That EntityFramework.Extended project looks interesting, I'll check it out.

 

Thanks!