LS2013 Core Project – Create a database project

This part will be easy. You’ll paste in 100% code and you’ll be off and running.

  1. In Visual Studio
  2. Right click on your solution
  3. Add, New Project
  4. Select SQL Server in the left panel
  5. Select SQL Server Database Project in the right panel
  6. Name the Project: LsCoreProject.Database
  7. We change the location to be with our other solution projects
  8. Once generated, right click on the database project
  9. Add, Script
  10. Select Post-Deployment Script

Due to the length of the Script I won’t put it all in the post as there are 400+ icons. So for this exercise I’ll just show the code to add for the creation of the default tile menus and a small set of icons. For a more thorough example please look at our sample project that you downloaded.

Add the following SQL code into your PostDeployment script

/* ==============================================================*/
/* Comment out the following section before pushing to Azure */
/* As Azure does not support the DBCC and resetting Identity seeds */
/* ==============================================================*/

/* Zero out our tables */
/* ======================================== */
/* ======================================== */
/* Only do this for initial deployment!!!! */
/* Careful as this will destroy on every push */
/* ======================================== */
/* ======================================== */
DELETE FROM Icons
GO
DELETE FROM Menus
GO

/* Add default menus */
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO
SET IDENTITY_INSERT [dbo].[Menus] ON
INSERT [Menus] (Id, [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (1, N'Start', NULL, NULL, NULL, NULL)
INSERT [Menus] (Id, [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (2, N'Security', NULL, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Menus] Off
GO

/* ========================================================================= */
/* Default menu tiles are after the icons table */
/* ========================================================================= */

/* Add Icons to the Icons table */
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO
SET IDENTITY_INSERT [dbo].[Icons] ON
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (1, N'icon-accessibility', NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (2, N'icon-accessibility-2', NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (3, N'icon-address-book', NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (4, N'icon-air', NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (5, N'icon-alarm', NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (6, N'icon-alarm-2', NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (7, N'icon-alarm-cancel', NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (8, N'icon-alarm-clock', NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (9, N'icon-amazon', NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (10, N'icon-anchor', NULL, NULL, NULL, NULL)
INSERT INTO [dbo].[Icons] ([Id], [Name], [CreatedBy], [Created], [ModifiedBy], [Modified]) VALUES (11, N'icon-android', NULL, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Icons] OFF
Go

/* Add default tiles */
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO
SET IDENTITY_INSERT [dbo].[MenuTiles] ON
INSERT INTO [dbo].[MenuTiles] ([Id], [Order], [TileColor], [Type], [Name], [Size], [OnClick], [Status], [StatusBgColor], [StatusFgColor], [Badge], [BadgeBgColor], [BadgeFgColor], [IconBgColor], [IconFgColor], [PermissionId], [CreatedBy], [Created], [ModifiedBy], [Modified], [MenuTile_Icon], [MenuTile_Menu]) VALUES (1, 1, N'#1182c5', N'simple', N'Profile', N'default', N'myapp.showProfile();', N'Profile', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 1)
INSERT INTO [dbo].[MenuTiles] ([Id], [Order], [TileColor], [Type], [Name], [Size], [OnClick], [Status], [StatusBgColor], [StatusFgColor], [Badge], [BadgeBgColor], [BadgeFgColor], [IconBgColor], [IconFgColor], [PermissionId], [CreatedBy], [Created], [ModifiedBy], [Modified], [MenuTile_Icon], [MenuTile_Menu]) VALUES (2, 10, N'#06834e', N'simple', N'Icons', N'default', N'myapp.showIconsBrowse();', N'Icons', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, 2)
INSERT INTO [dbo].[MenuTiles] ([Id], [Order], [TileColor], [Type], [Name], [Size], [OnClick], [Status], [StatusBgColor], [StatusFgColor], [Badge], [BadgeBgColor], [BadgeFgColor], [IconBgColor], [IconFgColor], [PermissionId], [CreatedBy], [Created], [ModifiedBy], [Modified], [MenuTile_Icon], [MenuTile_Menu]) VALUES (3, 8, N'#90258d', N'simple', N'Menus', N'double', N'myapp.showMenusBrowse();', N'Menu Administration', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3, 2)
INSERT INTO [dbo].[MenuTiles] ([Id], [Order], [TileColor], [Type], [Name], [Size], [OnClick], [Status], [StatusBgColor], [StatusFgColor], [Badge], [BadgeBgColor], [BadgeFgColor], [IconBgColor], [IconFgColor], [PermissionId], [CreatedBy], [Created], [ModifiedBy], [Modified], [MenuTile_Icon], [MenuTile_Menu]) VALUES (4, 6, N'#6f6c07', N'simple', N'Permissions', N'default', N'myapp.showPermissionsBrowse();', N'Permissions', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, 2)
INSERT INTO [dbo].[MenuTiles] ([Id], [Order], [TileColor], [Type], [Name], [Size], [OnClick], [Status], [StatusBgColor], [StatusFgColor], [Badge], [BadgeBgColor], [BadgeFgColor], [IconBgColor], [IconFgColor], [PermissionId], [CreatedBy], [Created], [ModifiedBy], [Modified], [MenuTile_Icon], [MenuTile_Menu]) VALUES (5, 4, N'#9f041e', N'simple', N'Roles', N'default', N'myapp.showRolesBrowse();', N'Roles', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 5, 2)
INSERT INTO [dbo].[MenuTiles] ([Id], [Order], [TileColor], [Type], [Name], [Size], [OnClick], [Status], [StatusBgColor], [StatusFgColor], [Badge], [BadgeBgColor], [BadgeFgColor], [IconBgColor], [IconFgColor], [PermissionId], [CreatedBy], [Created], [ModifiedBy], [Modified], [MenuTile_Icon], [MenuTile_Menu]) VALUES (6, 2, N'#1fb800', N'simple', N'Users', N'double', N'myapp.showUserRegistrationsBrowse();', N'User Registrations', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3, 2)
INSERT INTO [dbo].[MenuTiles] ([Id], [Order], [TileColor], [Type], [Name], [Size], [OnClick], [Status], [StatusBgColor], [StatusFgColor], [Badge], [BadgeBgColor], [BadgeFgColor], [IconBgColor], [IconFgColor], [PermissionId], [CreatedBy], [Created], [ModifiedBy], [Modified], [MenuTile_Icon], [MenuTile_Menu]) VALUES (7, 99, N'#ff3b00', N'simple', N'Security', N'double', N'myapp.showSecurity();', N'Security', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 7, 1)
SET IDENTITY_INSERT [dbo].[MenuTiles] Off
GO

Good time to Save the solution and do a build… success yes?
If so, go ahead and run your app… yay!

Oops… blank start screen? Ah… We didn’t tell our LightSwitch app to use the database project. So…

  1. Back in Visual Studio
  2. Open up the LsCoreProject properties
  3. On the General Properties tab
  4. Use the dropdown for SQL Database Project to select ours
  5. Save the solution and build
  6. Now run your app…

Hmmm.. still blank screen? So… our system is based on users and since Visual Studio launches as testuser, you need to create an account in the database for testuser. So fire up your app again, navigate to http://localhost:port/register.aspx and register the username testuser. Once successful you should see your nice tile based screen.

Note that by default our SQL script is not setting icons for the tiles. I’m leaving this to the Admin to complete.
Also, every time you do a build, this SQL script will run, if you want to preserve your data, change your database project to show blank.

Now… before you try and push to your own hosting provider, please review the next module as it has some important instructions when it comes to the Security Data Source and how to configure for production deployment.

Next… How to deploy successfully


Here are the topics and their logical order:

  1. Create the project
  2. Add external CSS
  3. Add external scripts
  4. Update the default.htm
  5. Add LogIn/LogOut/Register/ChangePassword
  6. Create the security data source
  7. Screens for permissions
  8. Screens for role management
  9. Screens for user management
  10. Tables for tile menus
  11. Screens for icon management
  12. Screens for menu management
  13. Create a tile menu screen
  14. — You
  15. How to deploy successfully
  16. Wrap up

4 thoughts on “LS2013 Core Project – Create a database project

  1. Simon Paul says:

    Hi There,

    I did the old tutorial in the past and wanted to use that one, as the tiles per permissions is a very handy feature.
    Though, I’m stuck here, as my screen is still blank. Maybe because I’m using windows auth? No clue yet really…
    Maybe you have an idea!

    Best regards
    Simon

  2. Joe Lee says:

    When i try to add role, I get the error: {“odata.error”:{“code”:”2″,”message”:{“lang”:”en-AU”,”value”:”The current user does not have permission to insert entities into the EntitySet ‘Roles’.</Message

    Also there is on permission to start with. Normally I would get administrator role with SecurityAdministration permission to start with. Then we can add the LeaveMeAlone and TestUser into the Administrator role. Without the SecurityAdministration, how does the security work?

    Any Help will be appreciated

    • dwm9100b says:

      Did you download the sample project and did it work with your version of VSLS?
      As noted you need to add the initial administrator user in the web.config.
      The March 2014 update to VSLS caused some issues which I have not had time to visit. I’ll make an effort to look over the project this next week.

  3. Thomas says:

    Hi Dale,
    please can you explain how to Register the TestUser? On the Register.aspx i must enter a Password. But i cant find anything in the inet how this Password Looks like. Or is it indifferent?

    Regards
    Thomas

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: