Search code examples
visual-studiovisual-studio-lightswitchsql-viewlightswitch-2013

SQL View is not showing up in LightSwitch HTML client project

I would like to use a view instead of a table as a data item in a screen, but the view is not showing up in the Data Source->ApplicationData in my Solution Explorer. So, it is of course not showing up in the data item list during screen editing.

I am using the latest IDE version: MS Visual Studio Professional 2013, Verison 12 Update 4.

What I did

a) Created a test table (the code is just to show what LightSwitch did, I used the Solution Explorer to create the table)

CREATE TABLE [dbo].[TestPersons] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (255) DEFAULT ('') NOT NULL,
    [RowVersion] ROWVERSION     NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

b) Created a test view (I used the SQL Server Object Explorer for this)

CREATE VIEW [dbo].[TestPersonView]
    AS SELECT Id, Name FROM [TestPersons]

-> I added some test data over the SQL Server Object Explorer and it is shown correctly.

I already tried this

° explicit cast (Lightswitch Datasource views missing from list)

CREATE VIEW [dbo].[TestPersonView]
    AS SELECT DISTINCT
    CAST(Id as INT) AS Id,
    CAST(Name as NVARCHAR(255)) AS Name
    FROM [TestPersons]

° explicit cast and not-null handling

CREATE VIEW [dbo].[TestPersonView]
    AS SELECT DISTINCT
    ISNULL(CAST(Id as INT), 1) AS Id,
    ISNULL(CAST(Name as NVARCHAR(255)), '-') AS Name
    FROM [TestPersons]

° refreshed main project

° closed visual studio

° rebuild the server project

Appendix, 20. Nov 2014

° On other posts I read about "updating the data source" or "importing a view" but this function is not available (anymore?) in my project: Where is the "Update Data Source" function?

Solution

  • It looks like views, added directly to (localdb)\v11.0 over the SQL Server Object Explorer are not synced back into to the ApplicationData object in the Solution Explorer.

    Workaround to get views from localDB into your project (LightSwitch, HTML client):

    • add the DB file itself from the /bin folder as an external data source to your server project, in the Solution Explorer, by selecting "Add Data Source..."
    • during this process, it is ok if you only select the views
    • now, they are available available views over an external data source

    I would be happy to hear about something better, to get views into localDB for beeing able to use them during the development phase. It is very ugly to have a localDB as data source and a 2nd external data source that points to the same DB, just to get access to the views.