Manage security in Visual Studio LightSwitch HTML Client without using SilverLight(Desktop) client

Usually we add a desktop client to manage security in an HTML client app. However there are other ways to do it, I am going to provide code for one of the ways we can manage security without being dependent on SilverLight. It is required to avoid SilverLight in a web app because modern browsers including Microsoft’s own Edge Browser don’t support SilverLight any more.

In this post I am combining contents from two brilliant articles. All I am doing is just providing you the code, but to understand how it all works you need to read the articles mentioned below:

Using the SecurityData service in LightSwitch – This is a must read article to understand how we can control the security. I have copied most of the code directly from here.

Using LightSwitch ServerApplicationContext and WebAPI to Get User Permissions – Implement the controller “UserPermissionsController” by following this.

Because it is related to security I am  being cautious and posting this note again from the above article:

Some important notes regarding having access to your application’s security data:
In a running LightSwitch application, users which do not have the SecurityAdministration permission are only allowed to read security data; they cannot insert, update, or delete it.  In addition, those users are only able to read security data that is relevant to themselves.  So if a user named Bob, who does not have the SecurityAdministration permission, queries the UserRegistrations entity set, he will only see a UserRegistration with the name of Bob.  He will not be able to see that there also exists a UserRegistration with the name of Kim since he is not logged in as Kim.  Similarly with roles, if Bob queries the Roles entity set, he can see that a role named SalesPerson exists because he is assigned to that role.  But he cannot see that a role named Manager exists because he is not assigned to that role.

The security implementation in asp.net is Role Based. We start by defining permissions, for example CanAddEmployee, CanEditEmployee, CanViewEmployee, CanDeleteEmployee etc. These permissions can be assigned to a role, typical examples of roles could be Standard User, Manager, Administrator etc. Eventually a role is assigned to the users. To achieve this first of all we’ll have to create some tables to our database, follow the diagram and create tables accordingly:

Security Schema

After the tables are created add required permissions to the project. To get detail understanding on user access control refer to this article: Beginning LightSwitch in VS 2012 Part 5: May I? Controlling Access with User Permissions.

Add permissions to the project.

Now add same permissions to tblPermissions, make sure “Name” under Access Control matches with “PermissionName” in tblPermissions:

Permissions

The next step is to keep asp.net security tables in sync with our tables, which can be accomplished by using the Inserting, Updating and Deleted data events (more info: How to: Handle Data Events) provided by LightSwitch, here is the code:


#Region " Manage Security "

 'Keep System's Security tables in sync with database tables using Inserting, Updating & Deleted method
 'Note: Deleted method is used instead of Deleting to make sure the record is deleted without any errors,
 'otherwise the Deleting method will delete the record from system table in spite of the it is not
 'deleted from database because of any error.
 'Temporary variable (DeletedRoleName) is used to store the IDs/Objects before deletion, because
 'Deleted method will lost the foreign key id

#Region " Manager Users "
 Private Sub tblUsers_Inserting(entity As tblUser)
 Dim strUserName As String = entity.UserName
 Dim plainPassword As String = entity.Password

 'Encrypt the password
 'Firt create a new Guid for the user. This will be unique for each user
 'and work as a key which will require to dycript the password.
 entity.UserGuid = Guid.NewGuid()
 'Create the wrapper using a Key
 Dim wrapper As New StringEncryption(entity.UserGuid.ToString())
 'Encrypt the password
 entity.Password = wrapper.EncryptData(entity.Password)

 Try
 'Check if user exists in the intrinsic database
 Dim user = (From u In Me.DataWorkspace.SecurityData.UserRegistrations
 Where u.UserName = strUserName
 Select u).SingleOrDefault
 'Create a user if not exist
 If IsNothing(user) Then
 Dim newUser = Me.DataWorkspace.SecurityData.UserRegistrations.AddNew()
 With newUser
 .UserName = strUserName
 .FullName = entity.Forenames & " " & entity.Surname
 .Password = plainPassword
 End With
 Me.DataWorkspace.SecurityData.SaveChanges()
 End If
 Catch vx As ValidationException
 Throw
 Catch ex As Exception
 Throw
 End Try
 End Sub

 Private Sub tblUsers_Updating(entity As tblUser)
 Try

 Dim strUserName As String = entity.UserName
 Dim plainPassword As String = entity.Password

 'Get the user
 Dim user = (From u In Me.DataWorkspace.SecurityData.UserRegistrations
 Where u.UserName.ToLower = strUserName
 Select u).SingleOrDefault

 'Change Password
 If Not IsNothing(user) Then
 'Encrypt the updated password
 Dim wrapper As New StringEncryption(entity.UserGuid.ToString())
 entity.Password = wrapper.EncryptData(plainPassword)

 Dim oldPass As String = entity.Details.Properties.Password.OriginalValue
 If oldPass <> entity.Password Then
 'Asp.net security requires the old password to change a password
 'Dycript the old password and send it to asp.net security table
 Dim plainOldPassword As String = wrapper.DecryptData(oldPass)

 Me.DataWorkspace.SecurityData.ChangePassword(strUserName, plainOldPassword, plainPassword)
 End If
 End If
 Catch vx As ValidationException
 Throw
 Catch ex As Exception
 Throw
 End Try
 End Sub

 Private Sub tblUsers_Deleted(entity As tblUser)
 Try
 Dim strUserName As String = entity.UserName.ToLower

 Dim user = (From u In Me.DataWorkspace.SecurityData.UserRegistrations
 Where u.UserName.ToLower = strUserName.ToLower
 Select u).SingleOrDefault
 If Not IsNothing(user) Then
 user.Delete()
 Me.DataWorkspace.SecurityData.SaveChanges()
 End If
 Catch vx As ValidationException
 Throw
 Catch ex As Exception
 Throw
 End Try
 End Sub
#End Region

#Region " Manager Roles "

 Private Sub tblRoles_Inserting(entity As tblRole)
 Try

 Dim strRoleName As String = entity.RoleName.ToLower
'Check if role exists in the intrinsic database
 Dim role = (From r In Me.DataWorkspace.SecurityData.Roles
 Where r.Name = strRoleName
 Select r).SingleOrDefault

 If IsNothing(role) Then
 Dim newRole = Me.DataWorkspace.SecurityData.Roles.AddNew
 With newRole
 .Name = strRoleName
 End With
 Me.DataWorkspace.SecurityData.SaveChanges()
 End If
 Catch vx As ValidationException
 Throw
 Catch ex As Exception
 Throw
 End Try
 End Sub

 ''Role name can not be changed, use Read Only control in UI
 'Private Sub tblRoles_Updating(entity As tblRole)
 'End Sub

 Private Sub tblRoles_Deleting(entity As tblRole)
 Try
 If String.IsNullOrEmpty(DeletedRoleName) Then
 DeletedRoleName = entity.RoleName.ToLower 
 End If
 Catch ex As Exception
 Throw
 End Try
 End Sub

 Private Sub tblRoles_Deleted(entity As tblRole)
 Try

 If DeletedRoleName <> "" Then
 Dim strRoleName As String = DeletedRoleName
 DeletedRoleName = ""

 Dim role = (From r In Me.DataWorkspace.SecurityData.Roles
 Where r.Name = strRoleName
 Select r).SingleOrDefault
 If (Not IsNothing(role)) Then
 role.Delete()
 Me.DataWorkspace.SecurityData.SaveChanges()
 End If
 End If
 Catch vx As ValidationException
 Throw
 Catch ex As Exception
 Throw
 End Try
 End Sub
#End Region

#Region " Manage Role-Permissions"

 Private Sub tblRolePermissions_Inserting(entity As tblRolePermission)
 Try

 Dim strRoleName As String = entity.tblRole.RoleName.ToLower 

 Dim role = (From r In Me.DataWorkspace.SecurityData.Roles
 Where r.Name = strRoleName
 Select r).SingleOrDefault

 'Get Permission
 Dim strPID As String = IIf(entity.tblPermission.PermissionName = "SecurityAdministration",
 "Microsoft.LightSwitch.Security:" & entity.tblPermission.PermissionName,
 "LightSwitchApplication:" & entity.tblPermission.PermissionName)
 Dim permission = (From p In Me.DataWorkspace.SecurityData.Permissions
 Where p.Id = strPID
 Select p).SingleOrDefault

 If role IsNot Nothing AndAlso permission IsNot Nothing Then
 'Check if this permission assigned
 Dim rolepermission = (From rp In Me.DataWorkspace.SecurityData.RolePermissions
 Where rp.Role.Name = strRoleName And rp.Permission.Id = strPID
 Select rp).SingleOrDefault
 If IsNothing(rolepermission) Then
 Dim rolePermision = Me.DataWorkspace.SecurityData.RolePermissions.AddNew
 With rolePermision
 .Role = role
 .Permission = permission
 End With
 Me.DataWorkspace.SecurityData.SaveChanges()
 End If
 End If
 Catch vx As ValidationException
 Throw
 Catch ex As Exception
 Throw
 End Try
 End Sub

 Private Sub tblRolePermissions_Deleting(entity As tblRolePermission)
 Try

 Dim strRoleName As String = entity.Details.Properties.tblRole.OriginalValue.RoleName.ToLower 

 'Get Permission ID
 Dim strPID As String = IIf(entity.tblPermission.PermissionName = "Security Administrator",
 "Microsoft.LightSwitch.Security:" & entity.tblPermission.PermissionName,
 "LightSwitchApplication:" & entity.tblPermission.PermissionName)

 Dim rolepermission = (From rp In Me.DataWorkspace.SecurityData.RolePermissions
 Where rp.Role.Name = strRoleName And rp.Permission.Id = strPID
 Select rp).SingleOrDefault

 If Not IsNothing(rolepermission) Then
 rolepermission.Delete()
 Me.DataWorkspace.SecurityData.SaveChanges()
 End If
 Catch vx As ValidationException
 Throw
 Catch ex As Exception
 Throw
 End Try
 End Sub
#End Region

#Region " Manage Role-Assignments "

 Private Sub tblRoleAssignments_Inserting(entity As tblRoleAssignment)
 Try
 'Get User
 Dim strUserName As String = entity.tblUser.UserName.ToLower
 Dim user = (From u In Me.DataWorkspace.SecurityData.UserRegistrations
 Where u.UserName = strUserName
 Select u).SingleOrDefault

 'Get Role
 Dim strRoleName As String = entity.tblRole.RoleName.ToLower 

 Dim role = (From r In Me.DataWorkspace.SecurityData.Roles
 Where r.Name = strRoleName
 Select r).SingleOrDefault

 If user IsNot Nothing AndAlso role IsNot Nothing Then

 'Check if role is already assigned
 Dim assignment = (From a In Me.DataWorkspace.SecurityData.RoleAssignments
 Where a.RoleName = strRoleName And a.UserName = strUserName
 Select a).SingleOrDefault
 If IsNothing(assignment) Then
 Dim newAssignment = Me.DataWorkspace.SecurityData.RoleAssignments.AddNew
 With newAssignment
 .User = user
 .Role = role
 End With
 Me.DataWorkspace.SecurityData.SaveChanges()
 End If
 End If
 Catch vx As ValidationException
 Throw
 Catch ex As Exception
 Throw
 End Try
 End Sub

 Private Sub tblRoleAssignments_Deleting(entity As tblRoleAssignment)
 Try

 'Get UserName
 Dim strUserName As String = entity.Details.Properties.tblUser.OriginalValue.UserName.ToLower

 'Get RoleName
 Dim strRoleName As String = entity.Details.Properties.tblRole.OriginalValue.RoleName.ToLower 

 Dim assignment = (From a In Me.DataWorkspace.SecurityData.RoleAssignments
 Where a.RoleName = strRoleName And a.UserName = strUserName
 Select a).SingleOrDefault

 If Not IsNothing(assignment) Then
 assignment.Delete()
 Me.DataWorkspace.SecurityData.SaveChanges()
 End If
 Catch vx As ValidationException
 Throw
 Catch ex As Exception
 Throw
 End Try
 End Sub

#End Region

#End Region


One more important issue to consider is user passwords- as a good security practice passwords should not be saved as a plain text. If you have noticed I am encrypting the password before saving it to the database. Here is the “StringEncryption” class which is used to encrypt and decrypt a string (Ref: Walkthrough: Encrypting and Decrypting Strings in Visual Basic) :


Imports System.Security.Cryptography
Imports System.Text

Public Class StringEncryption
 Private TripleDes As New TripleDESCryptoServiceProvider

Sub New(ByVal key As String)
 ' Initialize the crypto provider.
 TripleDes.Key = TruncateHash(key, TripleDes.KeySize \ 8)
 TripleDes.IV = TruncateHash("", TripleDes.BlockSize \ 8)
 End Sub

Private Function TruncateHash(ByVal key As String, ByVal length As Integer) As Byte()

Dim sha1 As New SHA1CryptoServiceProvider

' Hash the key.
 Dim keyBytes() As Byte = Encoding.Unicode.GetBytes(key)
 Dim hash() As Byte = sha1.ComputeHash(keyBytes)

' Truncate or pad the hash.
 ReDim Preserve hash(length - 1)
 Return hash
 End Function

Public Function EncryptData(
 ByVal plaintext As String) As String

' Convert the plaintext string to a byte array.
 Dim plaintextBytes() As Byte =
 System.Text.Encoding.Unicode.GetBytes(plaintext)

' Create the stream.
 Dim ms As New System.IO.MemoryStream
 ' Create the encoder to write to the stream.
 Dim encStream As New CryptoStream(ms,
 TripleDes.CreateEncryptor(),
 CryptoStreamMode.Write)

' Use the crypto stream to write the byte array to the stream.
 encStream.Write(plaintextBytes, 0, plaintextBytes.Length)
 encStream.FlushFinalBlock()

' Convert the encrypted stream to a printable string.
 Return Convert.ToBase64String(ms.ToArray)
 End Function

Public Function DecryptData(
 ByVal encryptedtext As String) As String

' Convert the encrypted text string to a byte array.
 Dim encryptedBytes() As Byte = Convert.FromBase64String(encryptedtext)

' Create the stream.
 Dim ms As New System.IO.MemoryStream
 ' Create the decoder to write to the stream.
 Dim decStream As New CryptoStream(ms,
 TripleDes.CreateDecryptor(),
 CryptoStreamMode.Write)

' Use the crypto stream to write the byte array to the stream.
 decStream.Write(encryptedBytes, 0, encryptedBytes.Length)
 decStream.FlushFinalBlock()

' Convert the plaintext stream to a string.
 Return Encoding.Unicode.GetString(ms.ToArray)
 End Function
End Class

All done, for all those who have been following LightSwitch for a while now there is nothing new here, but those who are new to it or exploring the possibilities and hitting the road block because of SilverLight may find this information helpful.

Thanks.

Implementing Kendo ComboBox in Visual Studio LightSwitch

LightSwitch has a very nice Details Picker control but sometimes we need a simple combo box that we all are used to, instead of a details picker.

To get the understanding on custom controls in LightSwitch, you can read this fantastic article: Custom Controls and Data Binding in the LightSwitch HTML Client (Joe Binder)

Imagine we have two tables: Contact and ContactAddress, the relationship is one Contact can have many Addresses. The AddEdit screen of ContactAddress would be similar to below:

ContactAddressDetails

We can see LS has noticed the relationship and applied a Details Picker control for Contact, which we want to change to a Combobox. To do this click on Contact and change its type to a custom control:

CreatingCustomControl

Go to properties of the control and click on “Edit Render Code” which will allow us to write code to change the control type and specifying data-binding.

RenderEvent

Apply this code in the Render event and run the application:


myapp.ContactAddressDetail.Contact_render = function (element, contentItem) {

//Get data before creating the combo
myapp.activeDataWorkspace.ApplicationData.Contacts.load()
.then(function (result) {

var data = [];
result.results.forEach(function (item) {
data.push({
value: item.Id,
stringValue: item.FirstName
});
});

//Clear any existing combo from DOM
//as sometimes it has rendering issues.
if ($("#contacts").getKendoComboBox()) {
$("#contacts").getKendoComboBox().destroy();
$("#contacts").remove();
}

//Create inputbox control and add it to the element
var contacts = $('<input id="contacts" />');
contacts.appendTo($(element));

//Create kendo combo box from inputbox
$("#contacts").kendoComboBox({
dataTextField: "stringValue",
dataValueField: "value",
dataSource: data,
select: onSelect
});

//Apply data-binding to read the data from database
//and update combo box's value
contentItem.dataBind("value", function (newValue) {
var combobox = $("#contacts").data("kendoComboBox");
combobox.value(newValue.details.properties.Id.value);
});
});

//Apply data-binding to listen to the changes made in
//the combo box and update the contentItem, which will
//eventually update the database when Save button is clicked.
function onSelect(e) {
var dataItem = this.dataItem(e.item.index());

myapp.activeDataWorkspace.ApplicationData.Contacts_SingleOrDefault(dataItem.value)
.execute().then(function (result) {
if (result.results[0] != undefined) {
contentItem.value = result.results[0];
}
});
}
};

Happy Coding!!!

 

Kendo UI (Grid) in LightSwitch for CRUD operations

How about if we can combine the super fast application development from LightSwitch with a powerful data grid. Following the previous article on how we can use the Kendo grid in Visual Studio LightSwitch for browsing the data, in this article I am going to provide the code for the CRUD operations for batch addition, modification and deletion.

Web API Class:


Imports System.Net
Imports System.Web.Http

Imports System.Collections
Imports Microsoft.LightSwitch
Imports LightSwitchApplication

Imports System.Web.Http.ModelBinding
Imports System.Web.Http.OData
Imports System.Web.Http.OData.Query
Imports System.Web.Http.OData.Routing

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Net.Http
Imports Newtonsoft.Json.Linq

'Add reference of System.Data.DatasetExtentions to the project to use
'AsEnumerable() for a DataTable

<EnableQuery> _
Public Class WorkersController
Inherits ApiController

Public Function GetValue(id As Integer) As Object
Try
Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings(
context.DataWorkspace.TestData.Details.Name).ConnectionString)

Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandText = "uspGetWorkersByUser"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@intUserID", id))
cmd.Connection.Open()

Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = cmd
Dim ds As DataSet = New DataSet
da.Fill(ds)
Dim workers As DataTable = ds.Tables(0)

Dim query = From w In workers.AsEnumerable() _
Select New With _
{ _
.WokerID = w.Field(Of Integer?)("WorkerID"), _
.FirstName = w.Field(Of String)("FirstName"), _
.LastName = w.Field(Of String)("LastName"), _
.UserID = w.Field(Of Integer)("UserID"), _
.UserName = w.Field(Of String)("UserName") _
}

Return query
End Using
End Using
Catch ex As Exception
Throw ex
End Try
End Function

' POST/NEW RECORDS
Public Function PostValue(ByVal workers As IEnumerable(Of WorkersForCrud)) As HttpResponseMessage

Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

For Each worker As WorkersForCrud In workers
Dim wh = context.DataWorkspace.TestData.tblWorkers.AddNew()
wh.FirstName = worker.FirstName
wh.LastName = worker.LastName
Next

Try
context.DataWorkspace.TestData.SaveChanges()
Catch ux As DBConcurrencyException
Return Request.CreateErrorResponse(HttpStatusCode.NotFound, ux)
Catch ex As Exception
Throw ex
End Try
End Using

Return Request.CreateResponse(HttpStatusCode.OK)

End Function

' PUT/UPDATE
Public Function PutValue(ByVal workers As IEnumerable(Of WorkersForCrud)) As HttpResponseMessage
Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

For Each worker As WorkersForCrud In workers
Dim wh = context.DataWorkspace.TestData.tblWorkers_SingleOrDefault(worker.WokerID)

If IsNothing(wh) Then
Return Request.CreateResponse(HttpStatusCode.BadRequest)
End If

With wh
.FirstName = worker.FirstName
.LastName = worker.LastName
End With
Next

Try
context.DataWorkspace.TestData.SaveChanges()
Catch ux As DBConcurrencyException
Return Request.CreateErrorResponse(HttpStatusCode.NotFound, ux)
Catch ex As Exception
Throw ex
End Try

Return Request.CreateResponse(HttpStatusCode.OK)
End Using
End Function

' DELETE
Public Function DeleteValue(ByVal workers As IEnumerable(Of WorkersForCrud)) As HttpResponseMessage

Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()
For Each worker As WorkersForCrud In workers
Dim wh = context.DataWorkspace.TestData.tblWorkers_SingleOrDefault(worker.WokerID)

If IsNothing(wh) Then
Return Request.CreateResponse(HttpStatusCode.NotFound)
End If

wh.Delete()
Next
Try
context.DataWorkspace.TestData.SaveChanges()
Catch ux As DBConcurrencyException
Return Request.CreateErrorResponse(HttpStatusCode.NotFound, ux)
Catch ex As Exception
Throw ex
End Try

Return Request.CreateResponse(HttpStatusCode.OK)
End Using
End Function
End Class

Public Class WorkersForCrud
Public WokerID As Integer
Public FirstName As String
Public LastName As String
Public UserID As Integer
Public UserName As String
Public Total As Integer

End Class

Render event:


myapp.SelectedWorkers.grdWorkers_render = function (element, contentItem) {
if ($("#grdWorkers").getKendoGrid()) {
$("#grdWorkers").getKendoGrid().destroy();
$("#grdWorkers").remove();
}

var gridContainer = $('
<div id="grdSelectedWorkers" />');

<span style="font-size: 16px; line-height: 1.5;">gridContainer.appendTo($(element));</span>

var dataSource = new kendo.data.DataSource({
type: "odata",
transport: {
read: {
url: function (options) {
return "../api/Workers/" + contentItem.screen.UserID;
},
dataType: "json",
success: function (result) {
items.success(result);
},
error: function (result) {
items.error(result);
}
},
update: {
url: "../api/Workers",
dataType: "json",
type: "PUT",
contentType: "application/json"
},
destroy: {
url: "../api/Workers",
dataType: "json",
type: "DELETE",
contentType: "application/json"
},
create: {
url: "../api/Workers",
dataType: "json",
type: "POST",
contentType: "application/json"
},

//parameterMap is not required for CRUD but it is used
//so that we can send a JArray (options.models) instead of
//a JObject (options) to Web API. If not used we can still
//receive a JObject at server side and do the conversion from
//JObject to a class type.

parameterMap: function (options, operation) {
if (operation !== "read" && options.models) {
return JSON.stringify(options.models) ;
} else {
return options;
}
}
},
schema: {
model: {

//Important to specify an id field so that the grid
//can make appropriate requests for POST and PUT.

id: "WokerID",
fields: {
WokerID: { editable: false, nullable: true },
FirstName: { type: "string" },
LastName: { type: "string" },
UserID: { type: "number" },
UserName: { type: "string" }
}
},
data: function (data) {
return data;
},
total: function (data) {
if (data != undefined) {
if (data.length > 0) { return data[0].Total; }
return data.length;
}
}
},
batch: true,
pageSize: 10,
serverPaging: true,
serverFiltering: true,
serverSorting: true
});

gridContainer.kendoGrid({
dataSource: dataSource,
navigatable: true,
toolbar: ["create", "save", "cancel"],
editable: true,
saveChanges: function(e) {
if (!confirm("Are you sure you want to save all changes?")) {
e.preventDefault();
} else {
this.dataSource.read();
}
},
pageable: true,
columns: [{
field: "FirstName",
title: "First Name"
}, {
field: "LastName",
title: "Last Name"
}, {
field: "UserName",
title: "Created By",
filterable: {
extra: false,
ui: userFilter
}
}, {
command: ["destroy"], title: " ", width: 150
}]
});

function userFilter(element) {
element.kendoDropDownList({
dataSource: {
transport: {
read: "../api/Users/"
}
}
});
}
};

Using Kendo UI (Grid) in Visual Studio LightSwitch

Hi,

A little exited about my first ever blog, so pardon me for any mistakes. I am not an expert in Visual Studio LightSwitch but I found it as one of the best tools available for RAD. It took me a little while to figure out how to integrate the Kendo grid with all its feature like server side filtering, paging, grouping etc. in LightSwitch. I am using a Web API method which uses a stored procedure to return a list of workers. At the moment I am using the grid only for browsing & not for any editing.

Assuming that you already know how to use Web API in LightSwitch (if not you can follow this link: http://blogs.msdn.com/b/lightswitch/archive/2012/11/19/a-new-api-for-lightswitch-server-interaction-the-serverapplicationcontext.aspx), my main focus is to provide the code for the Get method of Web API & client side Javascript to use Kendo UI Grid.

Web API class:


Imports System.Net
Imports System.Web.Http

Imports System.Collections
Imports Microsoft.LightSwitch
Imports LightSwitchApplication

Imports System.Web.Http.ModelBinding
Imports System.Web.Http.OData
Imports System.Web.Http.OData.Query
Imports System.Web.Http.OData.Routing

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

'Add a reference of System.Data.DatasetExtentions to the project to use AsEnumerable() for a DataTable

'EnableQuery statement will enable the query to accept
'OData query parameters ie. inlinecount, top, orderby etc.
_
Public Class WorkersController
Inherits ApiController

' GET api/
''' Accepts a UserID and returns a list of workers created by the user

Public Function GetValue(id As Integer) As Object
Try
Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings(
context.DataWorkspace.TestData.Details.Name).ConnectionString)

Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandText = &quot;uspGetWorkersByUser&quot;
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter(&quot;@intUserID&quot;, id))
cmd.Connection.Open()

Dim da As SqlDataAdapter = New SqlDataAdapter
da.SelectCommand = cmd
Dim ds As DataSet = New DataSet
da.Fill(ds)
Dim workers As DataTable = ds.Tables(0)

'Use ? for nullable values
Dim query = From w In workers.AsEnumerable() _
Select New With _
{ _
.WokerID = w.Field(Of Integer)(&quot;WorkerID&quot;), _
.FirstName = w.Field(Of String)(&quot;FirstName&quot;), _
.LastName = w.Field(Of String)(&quot;LastName&quot;), _
.BirthDate = w.Field(Of Date?)(&quot;BirthDate&quot;), _
.UserID = w.Field(Of Integer)(&quot;UserID&quot;), _
.UserName = w.Field(Of String)(&quot;UserName&quot;) _
}

Return query
End Using
End Using
Catch ex As Exception
Throw ex
End Try
End Function

End Class

You will have to include the necessary CSS & JS files from Kendo UI to default.htm (You can check this link: http://blogs.msdn.com/b/lightswitch/archive/2013/04/22/create-dashboard-reports-with-lightswitch-webapi-and-serverapplicationcontext.aspx and go to “Add Kendo UI Controls” to see how to add these references.)

 

Once the references are added just create a custom control on a screen and use below code in its render event.

Render event of a custom control:


myapp.WorkersBrowse.grdWorkers_render = function (element, contentItem) {

var gridContainer = $('
&lt;div id=&quot;grdWorkers&quot;&gt;&lt;/div&gt;
');

gridContainer.appendTo($(element));

var dataSource = new kendo.data.DataSource({

type: &quot;odata&quot;,

transport: {

read: {

//A URL can only be modified if it is within a function

url: function (options) {

if (contentItem.screen.UserID != undefined) {

return &quot;../api/Workers/&quot; + contentItem.screen.UserID;

}

},

dataType: &quot;json&quot;,

success: function (result) {

items.success(result);

},

error: function (result) {

items.error(result);

}

}

},

schema: {

model: {

fields: {

WorkerID: { type: &quot;number&quot; },

FirstName: { type: &quot;string&quot; },

LastName: { type: &quot;string&quot; },

BirthDate: { type: &quot;date&quot; },

UserID: { type: &quot;number&quot; },

UserName: { type: &quot;string&quot; }

}

},

data: function (data) {

return data;

},

total: function (data) {

if (data != undefined) {

if (data.length &gt; 0) { return data[0].Total; }

return data.length;

}

}

},

batch: true,

pageSize: 10,

//Enable server side operations

serverPaging: true,

serverFiltering: true,

serverSorting: true

});

gridContainer.kendoGrid({

dataSource: dataSource,

groupable: true,

scrollable: true,

pageable: true,

filterable: true,

sortable: true,

resizable: true,

selectable: true,

groupable: true,

reorderable: true,

change: function (e) {

//Get the WorkerID of selcted row

var selectedRow = this.select();

contentItem.screen.WorkerID = this.dataItem(selectedRow).WorkerID;

},

//minScreenWidth property is introduced in Kendo UI 2015 Q1 version.

//It makes the column autohide if the container's width is less than

//the specified width, so the grid doesn't look messy on small devices.

columns: [{

field: &quot;FirstName&quot;,

title: &quot;First Name&quot;

}, {

field: &quot;LastName&quot;,

title: &quot;Last Name&quot;,

minScreenWidth: 600

}, {

field: &quot;BirthDate&quot;,

title: &quot;Birth Date&quot;,

minScreenWidth: 600

}, {

field: &quot;UserName&quot;,

title: &quot;Created By&quot;,

minScreenWidth: 800

}]

});

};