How to Connect Visual Studio LightSwitch to MySQL

Microsoft Visual Studio LightSwitch, first released in 2011, quickly became popular as it allows quick and flexible development of busyness data-oriented applications.

As Devart always strives to support the newest cutting-edge-data oriented technologies in its products, our dotConnect ADO.NET providers supported Visual Studio LightSwitch since its first beta.

This Microsoft Visual Studio LightSwitch tutorial helps you to quickly get started with creating LightSwitch apps connecting to MySQL with dotConnect for MySQL ADO.NET provider. To complete this tutorial, you will need to install Visual Studio LightSwitch and dotConnect for MySQL Trial or Professional edition.

To create an application in Visual Studio LightSwitch with MySQL Connection, perform the following steps:

1. Create a new LightSwitch project. To do this, select File -> New Application from the Visual Studio menu. In the displayed New Project dialog box under Installed Templates select LightSwitch and then select a project template for the preferred programming language and click OK.

   

2. Click Attach to external Data Source and select Database in the displayed dialog box. Click the Next button.

   

3. Select MySQL in the Data source list, select dotConnect for MySQL in the Data provider drop-down list, and click the Continue button.

   

4. Specify connection settings and click OK.

   

5. Select a table or view to work with data from, specify the DataSource name if you need, and click Finish. In our tutorial we have selected the Dept table. Now you have created a new external data source.

   

6. Create a new screen for this data source by clicking the Screen button (highlighted on the previous screenshot) or press CTRL+SHIFT+E. Select Editable Grid Screen in the Select a screen template list, and then select the newly created data source under Screen Data. Then click OK.

   

7. Now a simple LightSwitch application with MySQL connection is ready. However it throws an exception when you try to update data because of the unsupported default LightSwitch transaction isolation level RepeatedRead.

   

To avoid the exception about transaction isolation level, perform the following steps:

8. Switch from Logical View to File View in Solution Explorer.
9. Add reference to System.Transactions to the Server project.
10. Return to the Logical View.
11. Right-click the DataSource and select View Code from the shortcut menu.

12. Add the following line to the using statements in the beginning of the file:

    using System.Transactions;

13. Add the following code to the partial class:

    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();
    }

14. Now our LightSwitch application is ready and you can run it.

    

Deployment of LightSwitch applications, using dotConnect for MySQL, has the same requirements as deployment of Entity Framework applications, using dotConnect for MySQL. LightSwitch specific requirements to deployment are described here . dotConnect for MySQL specific requirements to deployment are described here.

dotConnect for MySQL LightSwitch Support Limitations

As the LightSwitch technology was designed with Microsoft SQL Server in mind, support for Visual Studio LightSwitch in MySQL (and other) data providers has some certain limitations. By default, LightSwitch uses transactions with isolation level RepeatableRead, which are not supported by MySQL (and most other databases). dotConnect for MySQL throws an exception when LightSwitch application tries to update data. In this tutorial we provide the steps to avoid this issue (see above).

Another limitation is that you cannot import database objects from a schema containing two tables that have several relations between them, and one of these relations is a many-to-many association via an juction table. Attach Data Source Wizard cannot create a valid model in such case and produces errors when it comes to choosing database objects to import. Unfortunately, the only solution is to connect as a user that doesn't have enough privileges to see the juction table (or at least one of the two table with many-to-many association).