Question Re: Lightswitch using sqlanywhere data source in Technology Q&A

Lightswitch using sqlanywhere data source

Former Member — Mon, 08 Aug 2011 10:36:10 GMT

Has anyone been able to create a silverlight application with SQL Anywhere? I can create an application only on the same machine running the database. In addition I cannot update... the error is:

Cannot begin a transaction because the connection is already enlisted in a transaction

Re: Lightswitch using sqlanywhere data source

Former Member — Wed, 10 Aug 2011 11:03:07 GMT

I am able to use sqlAnywhere 12 with Microsoft's New Visual Studio lightswitch only using WCF RIA service as the datasource running in 3 tier deployment to the SqlAncywhere server running the IIS server it is deployed on. However, I can run a silverlight 3 tier WCF Ria service and connect to a remote SQLAnywhere database server. I continue to have no luke running Lightswitch against a remote database server either in 2 tier or 3 tier (WCF Ria servce data source). On two tier I also continue to get the transaction cannot beging because it is already enlisted. Beign that VS Lightswitch is suppose to be using underlying silverlight EF, MVVM, WCF Ria technologies, I am not sure why I continue to experience this.

Re: Lightswitch using sqlanywhere data source

MCMartin — Wed, 10 Aug 2011 12:25:13 GMT

Are you explicitly begining a transaction or is this done by some magic hidden stuff in Lightswitch?

Re: Lightswitch using sqlanywhere data source

Former Member — Wed, 10 Aug 2011 12:30:46 GMT

It is hidden Lightswitch stuff of which I have no visibility. 3 tier update works thru WCF Ria datasource (but only ls running on the db server) but 2 tier update does not. There must be a difference on how Microsoft is creating the transaction againt the ria data source verses the 2 tier database entities under the covers.

Re: Lightswitch using sqlanywhere data source

VolkerBarth — Wed, 10 Aug 2011 16:54:11 GMT

I'm not familiar with Lightswitch (nor EF). - But can you somewhat check whether you are getting connected to the database server at all - particularly when the database server is running on a different machine?

calling sa_conn_info() from dbisql(c) might help to find out more...

Re: Lightswitch using sqlanywhere data source

MCMartin — Thu, 11 Aug 2011 02:15:36 GMT

I have had a similar problem using ODBC inside a Workflow Foundation project in .net 3.5 Do you have nested transactions in your project?

Re: Lightswitch using sqlanywhere data source

MCMartin — Thu, 11 Aug 2011 02:58:25 GMT

Doing a quick web search it seems that your problem is affecting multiple database products not only SQL Anywhere.

A solution I have found but have not tested is:

You must define a transaction handling for your Data Source.

Switch to the File View of your LightSwitch Solution.
Select the "Server" Project and add a reference to "System.Transactions".
Open the Code for your Data Source (Right-Click on your Data Source --> Show/View Code)
Declare a using for System.Transactions: using System.Transactions;
Enter following code snippet:
List item

public partial class YOURDATASOURCE { private TransactionScope _tscope;

partial void SaveChanges_Executing() { _tscope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }); }

partial void SaveChanges_Executed() { _tscope.Complete(); _tscope.Dispose(); } }

In general if this doesn't work directly it seems to be a good idea to dig into the possibilities you have with using the TransactionScope class from .net to influence the enlistment of DataSources.

Re: Lightswitch using sqlanywhere data source

Former Member — Mon, 03 Oct 2011 06:01:10 GMT

I'm having the exact same problem as Busch: retrieving data from an sql anywhere db in a lightswitch app is fine. But as soon as I save a new or edited entity, I get "Cannot begin a transaction because the connection is already enlisted in a transaction.". @Busch: are you working with a normal or an OEM database? (Mine is an OEM.)

Re: Lightswitch using sqlanywhere data source

MCMartin — Thu, 06 Oct 2011 07:31:43 GMT

@Cheeta: Have you had a chance to check out the solution from my answer: creating a TransactionScope?