- Introduction
- Targeting .NET 4.5
- New Structure and Solution Explorer Enhancements
- Improved Team Development
- Easier Navigation
- Enhanced Views with Multiple Designers
- HTML Client: JavaScript IntelliSense and DOM Explorer Improvements
- Intrinsic Database Management with Linked Database Projects
- Conclusions
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Intrinsic Database Management with Linked Database Projects
When you create tables in a LightSwitch application, a SQL Server database (the intrinsic database) is created for you. Then you can publish the database along with the application via the deployment wizard or with SQL scripts. Visual Studio 2013 brings an interesting new opportunity: You can link the intrinsic database to a SQL Server database project. This feature opens an incredible number of scenarios, such as writing custom scripts or adding stored procedures into the intrinsic database.
Typically you'll follow the post-deployment scripting approach, meaning that you write SQL scripts against the database, and the scripts are executed immediately after the database has been published. To understand how this process works, imagine that you have an empty LightSwitch project (whether you're using the HTML client or the Silverlight client). Before adding new tables and entities, you can add a SQL Server database project, so any data you add will be sent to the database with some scripts. To add the project, select the solution name in Solution Explorer; then right-click or choose File, and select Add New Project. In the Add New Project dialog, locate the SQL Server node and select the SQL Server Database Project template, as shown in Figure 6.
)
Figure 6 Adding a SQL Server database project to the LightSwitch solution.
If you're working on a brand new solution with no tables, you won't be able to associate the database project to the LightSwitch application, so your first step is creating a new table. For the sake of simplicity, create an entity called Contact (the resulting table will automatically be named Contacts), like the one shown in Figure 7.
)
Figure 7 Creating a new table inside the intrinsic database.
The next step is associating the SQL database project you created earlier with the LightSwitch solution. To accomplish this goal, open the LightSwitch project's properties and select the desired SQL database project in the appropriate drop-down list, as shown in Figure 8.
)
Figure 8 Associating the SQL database project with a LightSwitch app.
The next step is adding a SQL script. There are infinite scenarios you might want to address, such as providing sample data for testing purposes or even adding stored procedures. The latter scenario is a bit complex, so I'll focus on the first instead. (For an example about injecting stored procedures into a LightSwitch application with a SQL database project, read Beth Massi's blog post.) To add a new script, select the SQL database project in Solution Explorer; then right-click and select Add New Item. In the Add New Item dialog, search for the Post-Deployment Script template, as shown in Figure 9.
)
Figure 9 Adding a post-deployment script.
At this point, the SQL code editor will appear. Enter the script shown in Listing 1, which populates the Contacts table with some sample information (see the comments in the listing).
Listing 1—Adding sample data via a SQL post-deployment script.
-- Enable identity insertion SET IDENTITY_INSERT Contacts ON; -- Merge the following values into the Contacts table MERGE INTO Contacts AS Target USING (VALUES (1, 'Del Sole', 'Alessandro', '1977-05-10T08:00:00'), (2, 'Cattaruzza', 'Diego', '1949-12-28T11:45:00'), (3, 'Marzaro', 'Renato', '1967-05-25T09:00:00'), (4, 'Catucci', 'Antonio', '1977-05-23T08:00:00') ) AS Source(Id, LastName, FirstName, DateOfBirth) ON Target.Id = Source.Id -- Update matched rows WHEN MATCHED THEN UPDATE SET FirstName = Source.FirstName, LastName = Source.LastName, DateOfBirth = Source.DateOfBirth -- Insert new rows WHEN NOT MATCHED BY TARGET THEN INSERT (Id, LastName, FirstName, DateOfBirth) VALUES (Id, LastName, FirstName, DateOfBirth) -- Delete rows that are in the target but not the source WHEN NOT MATCHED BY SOURCE THEN DELETE; -- Disable identity insertion SET IDENTITY_INSERT Contacts OFF; GO
When you test the application by pressing F5, the SQL Server intrinsic database is published to the Local DB development environment. When the database is being published along with the application to a web server, the database itself is published to an instance of SQL Server. In both cases, after the database has been published, the SQL script is executed. Assuming that you have a screen whose data source is the Contacts table, you'll see your sample data in that screen. Figure 10 shows an example based on the new HTML client, but everything is available for the Silverlight client as well.
)
Figure 10 Showing the sample data in a LightSwitch HTML app.
As you can imagine, the opportunity to link the intrinsic database to a SQL database project allows for deep control over the database, so you can solve complex problems such as adding stored procedures directly from within Visual Studio.