HowTo: Add a record thru code AndAlso Get Current User with Visual Studio LightSwitch

In my BI journey these last several years, I had to face a common need: provide a way for business analysts to maintain (add, modify, delete) records on a backend table. It’s very common that users need to classify/aggregate the data from your transactional systems with taxonomies not in those source systems, and thus the need to provide them a way to maintain the records for these taxonomies.

I’d been using Visual Studio LightSwtich for this, mostly the VB’s SilverLight project types.

During the last few months, I had to take these skills up several notches, in order to develop a couple more complex applications for my team. The first still focused on the Windows Client thru SilverLight on a 2 Tier deployment. The last one had stringent requirements, including having to run on other browsers other than IE (so, no SilverLight), and enable users from outside the company (not in our AD domain)… so I jumped to learn about the Visual Studio LightSwitch world of HTML5 projects on a 3 Tier deployment… Enter JavaScript & CSS & DOM, asynch, promises, etc…

So after going thru all of LightSwitch's documentation, tutorials, blogs, the help website, community forum, and many others… I started researching the more advanced challenges. And found great & lengthy discussions around a few common use cases, such as creating records on a table thru code, and get in the UI information about the current user; which were very informative, but didn’t totally solve the problems I was particularly facing. So let me share here what I learnt.

Requirements

These were the key design requirements for the solution:

1. Backend on Azure SQL DB.
2. Deployment on an Azure Web Site.
3. Support for Modern Browsers.
4. Users may be from outside the company.
5. Track usage of the app.
6. Users can only see what they own (per role).

So these led me to decide for HTML5 (Req. #2, #3, #4), 3 Tier Deployment (Req. #1, #2, #3), using Forms Authentication (Req. #1, #2, #4). To reuse my previous experience, the server code would be on VB.

Track Usage & Getting the User’s login

I see Req. #5 as a common need for having some instrumentation to know basic things on usage of the app. Req. #6 drove the need to know a lot more about the logged user than just it’s login alias/name.

This depicts kind of what I needed:

1. Get the user’s name.
2. Get the user’s login.
3. Get the user’s role(s) in the app.
4. If the role is “Partner”, then get the Company Name.
5. Get appropriate counts from across related tables filtered for this Company.

So, I created a screen for Home page of type “Browse” with no data associated to it.

• Added a Local Property (thru the “Add Data Item” button in the screen designer), of type String named “UserInfoString” that will show the welcome string message top right.
• Added Local Properties of type Integer for the counts (named “CntLocations” and “CntOffers”).
• Dropped these properties into the designer, at the top:
  
• For example, the UserInfoString is formatted to not show the name and right aligned (there’s a bit of CSS applied to it for color & format):
  

Now, to get the appropriate values into these Local Properties, I had to write code in the “created” method for the screen:

1. Open a connection to the data.
2. A pointer “varTrackThisVisit” to a new record on the “AppUsersSet” table in the “ApplicationData” data source (under the Server project of the solution). [This is the table where I track screen’s usage (instrumentation), and while doing that, I collect info about the user; see below.]
3. The “Screen” field of this new record (referenced by “varTrackThisVisit.Screen”) receives the DisplayName of this screen (in this case is the Home screen, but you can use this code in other screens).
4. After passing the screen’s name, now I save this new record thru the connection opened in step 1:
   1. Explained below: when I save the record, I take the opportunity that the “Inserting” method runs in the server, to collect info about the user.
   2. I use the “then()” promise to ensure the data gets saved before continuing the processing for 5, 6, 7.
5. Once the record finished saving (thanks to the “then”), I start concatenating the string into the Local Property for the user info.
6. If the user has a role (checking that the role is NOT null or empty, by going thru the ELSE of the IF statement), I add the role portion of the string (by using the “+=”).
7. If the role of the user is “Partner”, and the company name is not null (the UserPartner field), then I finish concatenating the company’s name to the string.
8. I get the counts of Locations and Offers into the other two Local Properties created earlier in the screen.
   1. Explained below: to get the appropriate count of records for the user logged in, I’ve a FILTER condition in these tables.

That will give me what I needed to show in the screen. Before I cover how I get the User’s info, let me show the CSS code I used for the formatting of the “UserInfoString” Local Property in the Home screen, added as part of the “PostRender” method:

Now, the “AppUsers” table is defined as:

On the “Inserting” method of this table:

1. When you’re saving a new record (from the “saveChanges()” in the earlier code) the “Inserting” method launches. The “entity” refers to this new record, so in this step we get the PersonID (login name) and FullName from the User of the Application (as defined when users are created for the Forms Authentication (the SilverLight project to manage the auth)). [You could also use User.Name instead of User.PersonID].
2. Here we need to concatenate the possible various roles a user may have.
3. So, we go thru all the roles.
4. Concatenating into the ThisUserRoles variable.
5. And now we assign the roles to the UserRole field of this new record.
6. Here we get the Company’s name for this user. There’s a table in the model named “PartnersSet” that have info about the company, as well as the user login for this company. The PartnersSet table has a FILTER for the current user (see below).
7. So, if there’s any company for this user:
8. We assign the company’s name to the UserPartner field of this new record. [Have to use “first()” because it’s a set of records, however it’ll have only one record.]

So, the FILTER for the PartnersSet table is:

1. The Partners table with some of its fields shown, related to the company.
2. Get to the FILTER to enter the code.
3. The field “SecurityLogin” will relate to the user’s login from the Forms Authentication entry (the security/app administration needs to link these with the same login, in the screen that that maintains this table).

1. Will filter the records to only the ones this user can see, but only if this user is a Partner (by having the “MSPartners” permission). If not, then no filter to the table gets applied, so administrators can access all records).
2. The field “SecurityLogin” is used to filter by the User.Name logged in the app.

Similar filters were applied to the related tables “PartnersLocationsSet” and “PartnersOffersSet”, so we can have the appropriate count of records. But as these are related tables to Partners, the field used in each table needs to navigate thru the relationships:

1. The “PartnersLocationsSet” table relates directly to the “Partners” table, so the navigation to the “SecurityLogin” field in order to apply the filter has one hop.
2. The “PartnersOffersSet” table relates to the “Partners” table thru the “PartnersLocations” table, so the navigation to the “SecurityLogin” field in order to apply the filter has two hops.

This covers the objective I had. From this foundation, a lot more now can be done to extend functionality. Hope this helps.