Maintaining scope after an exception

[Tomasz M.Lipiński]

Hi,
Let's look at the following code:

    public object myFunc(IObjectScope pScope)
    {
      try
      {
        Query<myType> query = pScope.GetSqlQuery<myType>(...);
        QueryResultEnumerable<myType> res = query.ExecuteEnumerable(...);
        return res.ToList();
      }
      catch
      {
        IQuery otherQuery = pScope.GetSqlQuery(...);
        IQueryResult otherRes = otherQuery.Execute(...);
        int a = otherRes.Count;  // execute!
        return null;
      }
    }

While executing "query" some SQL exception is raised (for example, it's a call to a stored procedure and a string is supplied (as a parameter) instead of a number). Then, "catch" is activated but when trying to execute "otherQuery" another exception is raised:
The server failed to resume the transaction. Desc:3c00000002.
The question is: what should I do with pScope to make it usable (using another scope is not a solution)? The transaction is not activated before calling myFunc and is still not active when trying to execute otherQuery.

Regards
Tomasz

[PetarP]

Hello Tomasz M.Lipiński,

It seems that this is a problem on your side as we were not able to reproduce the failing behavior on our side. Here is the code that we used:

IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();
            scope.Transaction.Begin();
            try
            {
                Query<Employee> query = scope.GetSqlQuery<Employee>("Select * from Employees where employeeID = ?", "Integer s1");
                QueryResultEnumerable<Employee> res = query.ExecuteEnumerable("sda");
                var result = res.ToList();
            }
            catch (Exception e)
            {
                IQuery otherQuery = scope.GetSqlQuery("Select * from Employees where employeeID = 1", null, null);
                IQueryResult otherRes = otherQuery.Execute();
                int a = otherRes.Count;  // execute!

            }

We attempted the same with stored procedures as well. Again, everything worked as expected. If possible, please send us a small sample that exposes the problem so we can debug it on our side. We are looking forward to your reply.

Best wishes,
Petar
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.

[Tomasz M.Lipiński]

Hi,
You are partially right but the problem is more complex.
In my case, a stored procedure is called. This procedure takes input parameters and creates a dynamic SQL statement. This generated statement looks like this:

SELECT vw.* from someView as vw WHERE ID in (Select IntValue from dbo.CsvToInt('1;2;3'))

The csv string ('1;2;3') comes directly from a parameter.
It is finally executed as: execute (@query)
(@query contains this generated statement)
dbo.CsvToInt is a table-value user function that converts numbers given as a semicolon separated string to a table of integer values.

If I supply an invalid parameter (e.g. 'aqq' instead of '1;2;3') the procedure throws an exception: "Conversion failed when converting the varchar value 'aqq' to data type int.". After this exception my application behaves as I've described - a query executed in the "catch" section fails.
But if I slightly modify the procedure, for example to refer to a nonexisting view (".... from someViewX ....") the procedure throws another exeception: "Invalid object name 'someViewx'." (regardless the quality of the parameters). And after this exception my "catch" section runs succesfully.

I've also done some other experiment, replacing OpenAccess with standard connection, data adapter and other objects from System.Data.SqlClient namespace. In this case, my procedure supplied with "aqq" has also failed, of course, but the "catch" section has been able to execute the required query.

Think it over, please.

Regards
Tomasz

[PetarP]

Hi Tomasz M.Lipiński,

We have tested this with stored procedures as well. We attempted passing invalid parameters and an exception was indeed thrown, however the transaction was resumed and we could successfully execute the code in the catch block. Maybe this is something specific to your stored procedure code? If it is appropriate, it would be best if you can send us a small project that represents this behavior. We are looking forward to your reply.

Greetings,
Petar
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.