Lightswitch using sqlanywhere data source

[Former Member]

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

[MCMartin]

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

[Former Member]

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.

[MCMartin]

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?

[Former Member]

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.)

[MCMartin]

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

[MCMartin]

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.

1. Switch to the File View of your LightSwitch Solution.
2. Select the "Server" Project and add a reference to "System.Transactions".
3. Open the Code for your Data Source (Right-Click on your Data Source --> Show/View Code)
4. Declare a using for System.Transactions: using System.Transactions;
5. Enter following code snippet:
6. 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.

[Former Member]

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.

[VolkerBarth]

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...