Loop Problems - UnsupportedException

6 posts, 0 answers
  1. Greg Lynne
    Greg Lynne avatar
    31 posts
    Member since:
    Mar 2010

    Posted 05 May 2010 Link to this post

     

     

    I am experiencing a few isssues when attempting to iterate through a hierachy of collections. I thought I had resolved these issues by doing a begin/commit after each process but that causes its own set of errors, but without this, I get an error after (X)number of iterations.

    The below code works exactly as expected right up until the very last iteration, and then it will cause a Telerik.OpenAccess.Exceptions.UnsupportedException, I think when delving into this particular exception it is reporting that the result set has changed but I can confirm that if neccessary.

    I am using Visual Studio 2010 and the latest version of ORM and a SSCE database.

    Could you please look at my code below and let me know if I am not doing this correctly.

     

    IObjectScope scope;
    public Program()

    {

        WIESScopeProvider.AdjustForDynamicLoad();  

        scope = WIESScopeProvider.GetNewObjectScope();

        versions = scope.GetOqlQuery<WIESVersion>().ExecuteList();

    }

    public void Execute()

    {
        try

         {
           using (SqlCeConnection conn = new SqlCeConnection(connstring))

             {

                conn.Open();

               foreach (var version in versions)

                {

                   IList<WIESSection> sections = version.Sections;

                    foreach (var section in sections)

                    {

                        IList<WIESSubSection> subsections = section.SubSections;

                        foreach(var subsection in subsections)

                        {

                            IList<WIESCalculation> calculations = subsection.Calculations;
                            foreach(var calculation in calculations) 

                            {
                                //this can take up to 10 minutes depending on the amount of data within a table

                                ExecuteCalculation(conn, calculation);

                                //refresh the scope

                                scope.Transaction.Begin();

                                scope.Transaction.Commit();

                            }

                        }

                    }

                }

                conn.Close();

            }

        }

        catch (Exception e) //todo 

        {

        }

    }

     

  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 06 May 2010 Link to this post

    Hello Andrew Brown,

    The error arises when you try to refresh the data by calling the ITransaction.Commit() method on the last iteration step.
    You could use the IObjectScope instance for CRUD or other calculation operations instead of opening a new SqlCeConnetion and using it. When you use an IObjectScope instance on IObjectScope.Transaction.Commit within the ExecuteCalculation method you will be able to update the data.

    Regards,
    Damyan Bogoev
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  3. DevCraft banner
  4. Greg Lynne
    Greg Lynne avatar
    31 posts
    Member since:
    Mar 2010

    Posted 06 May 2010 Link to this post

    Hi Damyan,

    Thanks for your prompt response. The thing is that within my ExecuteCalculation method I am performing database transactions on dynamic statements where the classes/tables that are not bound to my application. ie. The tables have been created by a user at run time and they now wish to update them. I am using the TelerikORM to store information about these tables (columns, type and various other things).

    Inside my ExecuteCalculation I am forming an "sql string" that is used to update a particular field based on a select statement. Can I still perform ExecuteNonQuery from the IObjectScope?

    Please see below my ExecuteCalculation and the reason I perhaps still need my SqlCeConnection.

     private void ExecuteCalculation(SqlCeConnection conn, Calculation calculation)
     {
                if (calculation.Column == null||calculation.Action==0||calculation.RunCalculation==0)
                    return;

                //build the query string
                string query = CalculationQuery(calculation);

                try
                {
                    SqlCeCommand cmd = new SqlCeCommand(query, conn);
                    int rowsaffected = cmd.ExecuteNonQuery();

                }//catch missing
    }

    public string CalculationQuery(Calculation calculation)
            {
                StringBuilder stringBuilder = null;

                if (calculation.Action == Operators.Update)
                {
                    stringBuilder = new StringBuilder(
                       Operators.ActionAsString(calculation.Action) +
                       " " + calculation.Column.Table.TableName +
                       " SET " + calculation.Column.ColumnName + "=" + calculation.UpdateValue);   
                }else //we are deleteing
                {
                    stringBuilder = new StringBuilder(
                       Operators.ActionAsString(calculation.Action) +
                       " FROM " + calculation.Column.Table.TableName);
                }

                if (calculation.Expressions.Count > 0)
                {
                    stringBuilder.AppendFormat("{0}", " WHERE ");
                    foreach (var expr in calculation.Expressions)
                    {
                        BuildExpression(expr, ref stringBuilder);
                    }
                }

                var query = stringBuilder.ToString() + ";";

                return query;
            }

  5. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 10 May 2010 Link to this post

    Hi Andrew Brown,

    Yes, you could use the IObjectScope to execute a sql statement directly. To achieve this you should use the IObjectScope.GetSqlQuery method:

    string queryString = "Select * from Customers where CompanyName like ?";            
    IQuery query = scope.GetSqlQuery(queryString, typeof(Customer), "varchar CompanyName");            
    IQueryResult result = query.Execute("Ana%");

    Additional information about executing SQL queries using can be found in the following article - "Direct SQL and Stored Procedures".

    Hope that helps.

    All the best,
    Damyan Bogoev
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  6. Greg Lynne
    Greg Lynne avatar
    31 posts
    Member since:
    Mar 2010

    Posted 10 May 2010 Link to this post

    Thanks for your response, however I am performing UPDATES not SELECT, and I have noted from your documentation : "Currently OpenAccess ORM only supports stored procedure calls and SELECT statements. Statements such as : "DELETE FROM Table WHERE ..." or "INSERT INTO Table ..." are not supported.".

    So this solution would still not solve my loop issue.

    Thanks.
    Andrew
  7. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 13 May 2010 Link to this post

    Hi Andrew Brown,

    You could use two different approaches in this scenario in order to avoid the error behavior:
    1. You could call the ITransaction.Begin and ITransaction.Commit methods outside the loop statements instead of calling them on each iteration:

    foreach (var version in versions) 
    {
        IList<WIESSection> sections = version.Sections; 
        foreach (var section in sections) 
        {
            IList<WIESSubSection> subsections = section.SubSections; 
            foreach(var subsection in subsections) 
            {
                IList<WIESCalculation> calculations = subsection.Calculations; 
                foreach(var calculation in calculations)  
                
                    //this can take up to 10 minutes depending on the amount of data within a table
                    ExecuteCalculation(conn, calculation); 
                
            
        
    }
    //refresh the scope 
    scope.Transaction.Begin();
    scope.Transaction.Rollback();

    2. Before retrieving any persistent capable objects within you application logic you should call the ITransaction.Begin method. It will cause OpenAccess to retrieve the objects accessed from the database instead of the L1 cache that each IObjectScope instance has. 



    Kind regards,
    Damyan Bogoev
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
Back to Top
DevCraft banner