How to Connect Visual Studio LightSwitch to SQLite

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 SQLite with dotConnect for SQLite ADO.NET provider. To complete this tutorial, you will need to install Visual Studio LightSwitch and dotConnect for SQLite Trial or Professional edition.

To create an application in Visual Studio LightSwitch with SQLite 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 SQLite in the Data source list, select dotConnect for SQLite 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 SQLite 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 SQLite, has the same requirements as deployment of Entity Framework applications, using dotConnect for SQLite. LightSwitch specific requirements to deployment are described here . dotConnect for SQLite specific requirements to deployment are described here.

dotConnect for SQLite LightSwitch Support Limitations

As the LightSwitch technology was designed with Microsoft SQL Server in mind, support for Visual Studio LightSwitch in SQLite (and other) data providers has some certain limitations. By default, LightSwitch uses transactions with isolation level RepeatableRead, which are not supported by SQLite (and most other databases). dotConnect for SQLite 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).