Data Storage in Visual Studio LightSwitch

With the recent release of Visual Studio LightSwitch Beta 1 to MSDN Subscribers, I have gotten a few questions. As questions come in, I’ll do my best to answer them here. One of the first questions I got was about how LightSwitch applications manage and store data source connection information – aka connection strings.

 

Question: In a Visual Studio LightSwitch application, where is the connection string to the database being stored?

LightSwitch applications can work with several types of data, including “local” application data, external SQL Server or SQL Azure data (or any other database supported by the .NET Framework), SharePoint data, and other external data exposed through WCF RIA Services. If you choose to get data from an external source, the Attach Data Source Wizard shows you your options.

On the other hand, the “local” application data is a reference to a SQL Server Express database that is created if you choose the “Create new table” option as shown here.

This application data is defined and stored in a local SQL Server Express database. For example, in the Vision Clinic demo we have used, the first step was to create a new table to store Patient data. in fact, in the demo we are defining an entity model, and creating a Patients (plural) table in the local application database using SQL Server Express. Notice in the following image that in the Solution Explorer there is a Data Sources node containing an ApplicationData node containing a Patients node. Patients is the table that was created in the ApplicationData database.

 

The ApplicationData.mdf file is a SQL Server Express file that is created and stored in the $ApplicationRoot\bin\Data directory. When connected to with Server Explorer, you can see that the Patients table is there, along with the ASP.NET membership tables.

So where is the connection string to this database stored? In a Web.config file that is created in the $ApplicationRoot\bin\release directory.

<connectionStrings>
  <add name=”_IntrinsicData” connectionString=”
    Data Source=.\SQLEXPRESS;
   AttachDbFilename=’c:\users\seven\documents\
   visual studio 2010\Projects\VisionClinicDemo\
   VisionClinicDemo\Bin\Data\
   ApplicationDatabase.mdf‘;
    Integrated Security=True;Connect Timeout=30;
    User Instance=True;MultipleActiveResultSets=True” />
</connectionStrings>

When the application is published (Build | Publish menu), the Publish Application Wizard asks if you want to publish the database directly to an existing database, or if you want to generate a script file to install and configure the database

If you choose to publish to a database, you are then prompted to provide the database information.

If you choose to create an install script file, you are prompted to describe the database that will be scripted.

All in all this is the standard approach to managing database connections in a multi-tiered application. The Web.config in the application tier contains the database connection information. As will all Web.config files, you may encrypt the connection information.

 

But what about the connections to other data sources, like SQL Azure or SharePoint?

A connection string is a connection string is a connection string. They all get stored in a the Web.config file.

<connectionStrings>

<add name=”_IntrinsicData” connectionString=”Data Source=.\SQLEXPRESS;AttachDbFilename=’e:\my documents\visual studio 2010\Projects\VisionClinicDemo\
VisionClinicDemo\Bin\Data\ApplicationDatabase.mdf’;
Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True” />

<add name=”PrescriptionContosoData” connectionString=”Data Source=server01.data.int.mscds.com;Initial Catalog=PrescriptionContoso;User ID=[removed];Password=[removed]” />

</connectionStrings>

Of course, all of the data in a LightSwitch application is represented by entities. The entities are defined in the ApplicationDefinition.lsml file (stored in $ApplicationRoot\Data). This is simply a reference to the data source(s) represented by the entities in the application. Each data source is described in this file, and the entities representing the data objects are described here as well.

<EntityContainerGroupProperty EntityContainer=”VisionClinicDemo:ApplicationData”
Name=”ApplicationData” />
<EntityContainerGroupProperty EntityContainer=”VisionClinicDemo:PrescriptionContosoData”
Name=”PrescriptionContosoData” />
</EntityContainerGroup>

…

<DataService DataProvider=”EntityFrameworkDataProvider”
EntityContainer=”:PrescriptionContosoData” Name=”PrescriptionContosoDataDataService”>
<DataService.ConnectionProperties>
<ConnectionProperty Name=”DataProviderName” Value=”91510608-8809-4020-8897-fba057e22d54″ />
<ConnectionProperty Name=”DataSourceName” Value=”067ea0d9-ba62-43f7-9106-34930c60c528″ />
<ConnectionProperty Name=”ProviderInvariantName” Value=”System.Data.SqlClient” />
<ConnectionProperty Name=”SafeConnectionString” Value=”Data Source=server01.data.int.mscds.com;Initial Catalog=PrescriptionContoso;User ID=admin01@server01″ />
<ConnectionProperty Name=”ConnectionStringGuid” Value=”1e9905dc-b519-4003-9387-1272a768b256″ />
<ConnectionProperty Name=”ProviderManifestToken” Value=”2008″ />
</DataService.ConnectionProperties>
…
</DataService>

 

Summary

All in all, LightSwitch applications are built using standard best practices. In the case of connection strings, they are stored in Web.config files in the application tier, and have all the support of ASP.NET configuration files, including encryption.

D7