Rank 1
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
{
}
}
5 Answers, 1 is accepted
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.
Rank 1
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;
}
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.
Rank 1
So this solution would still not solve my loop issue.
Thanks.
Andrew
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.