Bulk updates in OpenAccess?

7 posts, 0 answers
  1. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 03 Jul 2011 Link to this post

    Is there any way to perform a bulk update on entities in OpenAccess, without having to result to direct SQL? Can something like this be done in OQL, so it would maintain the state of any entities that might already be loaded?

    What I would like to be able to do is make changes to a set of items with a bulk update, similar to the following direct SQL command:

                    db.ExecuteNonQuery(@"
                        update " + Tables.Products + @"
                        set locations_id = @1
                        where locations_id = @2", newLocationID, oldLocationID);

    This command is very efficient, because it updates all the items with the old location to the new location in one quick SQL update command. If we have to do this at the ORM level, currently it seems the only way is to iterate through all the products linked to the old location (which includes loading them into the entity working set), updating the location to point to the new location, and then saving the changes to disk. The end result is a whole lot more SQL going on and the performance is a lot slower than doing the direct SQL above.

    If we did drop to direct SQL to do this, which is one option, how can we flush the context so that it will read from the database next time?
  2. Serge
    Admin
    Serge avatar
    375 posts

    Posted 06 Jul 2011 Link to this post

    Hi Kendall Bennett,

     Unfortunately there is no way other than direct SQL to do such a thing. Even if there were some way OpenAccess would still not be able to know whether changes have been made to the database. We are currently working on a ADO like API that will allow you to execute sql using OpenAccess that will be released with Q2. 

    However in order to get fresh data to the context you need to perform a context.ClearChanges() which will erase the context level cache and reload everything from the database. 

    I hope this is helpful.

    Best wishes,
    Serge
    the Telerik team

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

  3. DevCraft banner
  4. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 07 Jul 2011 Link to this post

    Ok great, I look forward to testing this when Q2 is released.
  5. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 13 Jul 2011 Link to this post

    Q2 is out and I love the new ADO.NET API! It is going to be perfect for my needs.
  6. Serge
    Admin
    Serge avatar
    375 posts

    Posted 15 Jul 2011 Link to this post

    Hello Kendall Bennett,

     We are glad you like it, please don't hold anything back and let us know if you face any trouble or see room for improvement. 

    All the best,
    Serge
    the Telerik team

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

  7. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 16 Jul 2011 Link to this post

    Have you considered implementing a bulk updates API similar to that provided by Mindscape Lightspeed? They have support for bulk updates and bulk deletes at the API level, that would be nice to have in OpenAccess.
  8. Serge
    Admin
    Serge avatar
    375 posts

    Posted 18 Jul 2011 Link to this post

    Hi Kendall Bennett,

     While it seems like a bit nicer API LightSpeed's is not much different than deleting the objects with our ADO.NET API, both bypass caching and any full-text indexes. We will take this into account in our next planning however it does not seem very likely that we will implement such an API.

    Nevertheless I would like to thank you for taking the time to suggest a new feature.

    Greetings,
    Serge
    the Telerik team

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

Back to Top
DevCraft banner