Tutorial: How to Connect Visual Studio LightSwitch to SQLite with dotConnect for
SQLite Data Provider
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:
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.
Click Attach to external Data Source and select Database in the displayed
dialog box. Click the Next button.
Select SQLite in the Data source list, select dotConnect for SQLite
in the Data provider drop-down list, and click the Continue button.
Specify connection settings and click OK.
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.
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.
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
- Switch from Logical View to File View in Solution Explorer.
- Add reference to System.Transactions to the Server project.
- Return to the Logical View.
- Right-click the DataSource and select View Code from the shortcut menu.
Add the following line to the using statements in the beginning of the file:
Add the following code to the partial class:
private TransactionScope _tscope;
partial void SaveChanges_Executing()
_tscope = new TransactionScope(TransactionScopeOption.Required,
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
partial void SaveChanges_Executed()
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
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).