Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

Lightswitch: Working with Views

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
18 Apr 2016CPOL6 min read 20.1K   5
Here, I will explain how you can work with SQL views in lightswitch and how you can save data on screens which are created on Views.

Introduction

In this article, I would be talking about how you can have a Browse screen whose underlying data is coming from a table and how you can have an edit screen whose underlying data is coming from a view. So, the next question arises when saving data, how will the save workflow work? Because if you know how lightswitch works, then you would definitely understand what I am trying to solve here. For those who do not know, the edit and save workflow works on the table through which we created the Browse screen. But in our case how will you make this scenario work.

Background

Well technology wise, I don't want to explain in details here. You can go through the basics of lightswitch once. It’s pretty easy. Since this is an advanced article, I would directly jump to the problem statement.

I am using Visual Studio Lightswitch 2013 project and using SQL Server Management Studio 2013. I have a table called tblFamily whose create statement would be something like this:

SQL
CREATE TABLE [dbo].[tblFamily](

    [FamilyID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [FamilyTypeID] [int] NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [OffsetFromMaxAsAtDate] [tinyint] NULL DEFAULT ((1)),
    [TimeDataExpires] [time](7) NULL,
    [ExcludeZeroNumberOfUnits] [bit] NOT NULL DEFAULT ((1)),
    [PrefilterConstituents] [bit] NULL DEFAULT ((0)),
    [LimitEndDate] [bit] NOT NULL DEFAULT ((0)),
    [IgnoreForPricing] [bit] NOT NULL DEFAULT ((0)),
    [AutoAddOddLots] [bit] NULL DEFAULT ((0)),[AutoAddMissingListing] [bit] NOT NULL DEFAULT ((0)),
 CONSTRAINT [PK_tblFamily] PRIMARY KEY CLUSTERED (    [FamilyID] ASC)
) ON [PRIMARY]

With this table, I would be creating my browse screen. The main point of interest is the TimeDataExpires property which is of datatype time.

Now to create a view, I would be doing this. I will explain later in the article as to why I am creating this view and what is its purpose. In this view, I am casting the TimeDataExpires to string datatype.

SQL
CREATE view [dbo].[vwFamilyProcessData]

as

select FamilyID,
OffsetFromMaxAsAtDate as OffsetFromMaxAsAtDate,
ExcludeZeroNumberOfUnits as ExcludeZeroNumberOfUnits,
PrefilterConstituents as PrefilterConstituents,
LimitEndDate as LimitEndDate,
IgnoreForPricing as IgnoreForPricing,
AutoAddMissingListing as AutoAddMissingListing,
AutoAddOddLots as AutoAddOddLots,
cast(TimeDataExpires as varchar(20)) as TimeDataExpires

from tblFamily

Using the Code

Create a new lightswitch project. Create a browse screen with screen data as tblFamilies (All entities would show as plural – concept of entity framework). Also create a button to edit this data. Hence, select the tblFamilies.editSelected and navigate to a new screen and click ok. Your screen layout should look like the below screen. Please ignore the hidden button, I will explain that later. Now run the project.

Image 1

Now click on the Edittbl Family button. And try to edit the Time Data Expires field. When trying to edit this field, it will close the edit screen automatically. Anyhow, you won't be able to edit this screen. You can also try to view this data on browse screen by laying out your screen as shown below:

Image 2

Also if you run this screen in your browser, you will notice that no data gets rendered for Time Data Expires property.

So to solve this problem, we would be using views to render this property on screen. Now create an Add/Edit Details screen with screen data as vwFamilyProcessData and click ok. A screen with edit dialog will be created like the screen shown below:

Image 3

Note: Please ignore extra fields which are there. They were part of my view.

Now go back to Browse Family screens and add a button like shown below. When creating the button, don’t choose any existing method. Instead choose Write my own method and method name as SaveFamilyProcessData.

Image 4

Right click on the Save Family Process Data button. Select on Edit Execute Code. Add the following lines of code:

JavaScript
myapp.BrowseFamily.SaveFamilyProcessData_execute = function (screen) {

myapp.showEditvwFamilyProcessData(screen.vwFamilyProcessData, {       

        afterClosed: function (addEditScreen, navigationAction) {
            if (navigationAction === msls.NavigateBackAction.commit) {
                screen.tblFamilies.refresh();
            }
        }
    });
};

myapp.BrowseFamily.SaveFamilyProcessData_execute we can specify what action can be taken on the click of SaveFamilyProcessData button.

myapp.showEditvwFamilyProcessData() specifies that on click of SaveFamilyProcessData button, open EditvwFamilyProcessData screen. We have passed screen.vwFamilyProcessData as first parameter because this specifies what data needs to be shown on that screen. If you don’t pass anything, then it will open EditvwFamilyProcessData screen with blank fields. Thankfully, lightswitch will figure out that whichever FamilyID is selected, display that data on Edit screen. We don’t have to bother about that part.

We have added afterClosed handler to refresh the screen after a successful save operation. We need to add this piece of code manually because after our save operation, the base screen won't get refreshed.

Now the main problem arrives. How to save the data from view. Since I have solved this problem for you, so it might look very easy now. But believe me it took me more than 2 weeks to solve this issue.

Go to the Server project and double click on vwFamilyProcessData.Isml file under your Data Source.

Image 5

Now go to Write Code and select the Updating method. Refer to the below screen:

Image 6

Add this piece of code:

JavaScript
partial void vwFamilyProcessDatas_Updating(vwFamilyProcessData entity)
        {
            if(entity.Details.EntityState.ToString() == "Modified")
            {
                var AutoAddMissingListing = entity.AutoAddMissingListing;
                var AutoAddOddLots = entity.AutoAddOddLots;
                var DefaultFilterValue = entity.DefaultFilterValue;
                var ExcludeZeroNumberOfUnits = entity.ExcludeZeroNumberOfUnits;
                var IgnoreForPricing = entity.IgnoreForPricing;
                var LimitEndDate = entity.LimitEndDate;
                var OffsetFromMaxAsAtDate = entity.OffsetFromMaxAsAtDate;
                var PrefilterConstituents = entity.PrefilterConstituents;
                var TimeDataExpires = entity.TimeDataExpires;

                tblFamily objFamily = tblFamilies.Where(f => f.FamilyID == entity.FamilyID).Single();
                objFamily.AutoAddMissingListing = AutoAddMissingListing;
                objFamily.AutoAddOddLots = AutoAddOddLots;
                objFamily.DefaultFilterValue = DefaultFilterValue;
                objFamily.ExcludeZeroNumberOfUnits = ExcludeZeroNumberOfUnits;
                objFamily.IgnoreForPricing = IgnoreForPricing;
                objFamily.LimitEndDate = LimitEndDate;
                objFamily.OffsetFromMaxAsAtDate = OffsetFromMaxAsAtDate;
                objFamily.PrefilterConstituents = PrefilterConstituents;
                objFamily.TimeDataExpires = TimeSpan.Parse(TimeDataExpires);

                entity.Details.DiscardChanges();
            }
}

I will try to explain the code. The lightswitch follows a Save pipeline process. It follows a specific set of rules and follows the same pipeline always. The save pipeline is automatically generated with every LightSwitch application. Hence, it is very important to understand this concept.

For detailed understanding of the concept, go through this article.

Lightswitch calls the General methods listed in the Write Code section in the order that you can see them on the screen. Also since we are trying to edit the populated data on screen, so it makes more sense to use the Updating method.

The Updating method would be called whenever there is a change in the Add/Edit screen which is made of vwFamilyProcessData. So when we open the screen EditvwFamilyProcessData, and try to edit data on this screen, lightswitch captures this change and includes the changed data to the save pipeline.

So the entity of type vwFamilyProcessData captures all the data on the screen. With this line of code, if(entity.Details.EntityState.ToString() == "Modified"), we check whether the entity has been modified or not. If the EntityState is Modified, then it would go inside the if loop. We capture all the data on the screen in local variables. Then, we load the Family details using the FamilyID that we are editing and store the values in tblFamily object, objFamily.

Now comes the tricky part. Notice I did Discard Changes to the entity. I had to do this because lightswitch understands the TimeDataExpires property as a computed or derived property. Refer to the image below:

Image 7

Also, it makes sense in a simple way if you think. In view, TimeDataExpires is a string data type and in tblFamily, which is the actual table, is a Time data type. Since there is a data type mismatch, so obviously lightswitch won’t allow data to be saved. Had there not been a data type mismatch, then lightswitch would save this data successfully.

So we have to discard the entity changes made to the vwFamilyProcessData and save the entity changes made to the tblFamily. Lightswitch will capture the information that there were some changes to the tblFamily and process this information in the Save pipeline. Hence, the data would directly be saved to tblFamily table for the specific FamilyID.

Do Save all and run the project on the browser. Select one family and try to edit it by clicking on Save Family Process Data button. The EditvwFamilyProcessData screen will appear with the data populated of selected FamilyID. Try to edit any data on screen and click the Save button on top. The edit screen will close after successful Save operation, also the screen will get refreshed because we have added code in the afterClosed handler.

 

Very very very important NOTE : DO not delete the button that we created first, Edittbl Family button. This button is used to bind your data somehow on the page load of Browse Family screens. In case if you delete this button, then when you first time try to click on your Save Family Process Data button, it will give you a blank popup screen. After closing the popup, if you again click on SaveFamilyProcessData button and try to open the popup screen for the second time, it will load your data successfully. Hence to avoid this problem, dont delete Edittbl Family button but make it invisible by unchecking the IsVisible property of the button. Refer the figure below :

Image 8

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
I have more than 4 years of work experience in .Net technology. I have worked extensively in ASP.NET MVC, C#, Entity Framework. I have basic knowledge in Javascript, Jquery, HTML, CSS, SQL. Recently I am exploring Lightswitch 2013. Although I am a software developer by profession but I am a huge fan of Shreya Ghoshal, Lata Mangeshkar, Kishore Kumar, Sonu Nigam, Shantanu Moitra, R D Burman, Salil Choudhury and Madan Mohan Music's.

Comments and Discussions

 
QuestionRegarding about this article Pin
Tridip Bhattacharjee11-Apr-16 21:56
professionalTridip Bhattacharjee11-Apr-16 21:56 
AnswerRe: Regarding about this article Pin
Ankita Biswas Bhattacharya12-Apr-16 0:20
Ankita Biswas Bhattacharya12-Apr-16 0:20 
GeneralRe: Regarding about this article Pin
Tridip Bhattacharjee14-Apr-16 22:08
professionalTridip Bhattacharjee14-Apr-16 22:08 
GeneralRe: Regarding about this article Pin
Ankita Biswas Bhattacharya18-Apr-16 21:29
Ankita Biswas Bhattacharya18-Apr-16 21:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.