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 = "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)
'Use ? for nullable values
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"), _
.BirthDate = w.Field(Of Date?)("BirthDate"), _
.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
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 = $('
<div id="grdWorkers"></div>
');
gridContainer.appendTo($(element));
var dataSource = new kendo.data.DataSource({
type: "odata",
transport: {
read: {
//A URL can only be modified if it is within a function
url: function (options) {
if (contentItem.screen.UserID != undefined) {
return "../api/Workers/" + contentItem.screen.UserID;
}
},
dataType: "json",
success: function (result) {
items.success(result);
},
error: function (result) {
items.error(result);
}
}
},
schema: {
model: {
fields: {
WorkerID: { type: "number" },
FirstName: { type: "string" },
LastName: { type: "string" },
BirthDate: { type: "date" },
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,
//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: "FirstName",
title: "First Name"
}, {
field: "LastName",
title: "Last Name",
minScreenWidth: 600
}, {
field: "BirthDate",
title: "Birth Date",
minScreenWidth: 600
}, {
field: "UserName",
title: "Created By",
minScreenWidth: 800
}]
});
};
DV's blog 
One thought on “Using Kendo UI (Grid) in Visual Studio LightSwitch”