Telerik Kendo UI Master Detail Grids with full edits using webapi and ASP.NET Core 2

This was my first time using Telerik and although there are some good things you can do with it, it’s hard to find help whenever you attempt something that is just outside what is shown in their demos and there is so many different ways to hook it up. This post docos what I’ve done when implementing a master-detail grid using KendoUI for an IIS site with windows authentication.

As this is for an intranet I’ve used windows authentication with 2 roles, one for read and another for writes.

Prerequisites

Visual Studio Community 2017

https://www.visualstudio.com/downloads/?dotnetid=2073771372.1490837485

.NET Core 2.0 Runtime

https://www.microsoft.com/net/targeting?utm_source=getdotnetsdk&utm_medium=referral

Telerik

http://docs.telerik.com/aspnet-core/introduction

https://docs.telerik.com/aspnet-core/getting-started/getting-started

New Project

Open VS2017 and create a new project and choose your target (.NET Core or .NET Framework). I’ve chosen the full framework because later I want to use Telerik Reporting (which as yet hasn’t been converted to standard)

001_new_project002_new_project

003_new_project

Nuget

Update all packages to the latest.

004_nuget

Packages for DB

If you targeted standard instead of full in the previous step you could just get away with including the “Microsoft.AspNetCore.All” package. If targeting full include the following nuget packages (in order).

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.Tools
  • Microsoft.EntityFrameworkCore.SqlServer
  • Microsoft.EntityFrameworkCore.SqlServer.Design

Telerik

To setup Telerik Nuget Package Source in VS2017. (ref. https://docs.telerik.com/aspnet-mvc/getting-started/nuget-install#set-up-nuget-package-source)

In the package manager console run:

NuGet Sources Add -Name "telerik.com" -Source "https://nuget.telerik.com/nuget" ^
-UserName "your login email" -Password "your password" ^
-StorePasswordInClearText

Open NuGet.Config from %AppData%\Nuget and it should look similar to this.

005_nuget

Visual Studio should now have another entry in NuGet.

007_nuget

Add the Telerik.UI.for.AspNet.Core package.

007a_nuget

Database

The database is simple with only a few tables. The master/parent table (Tag) and the detail/child (Alarm). Alarms have a few look ups and a boolean and date field, to show different data types.

DB Schema

008_DB

DB Create Script

The script below creates 10000 Tags with 2-5 Alarms each and the lookup tables.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
/*
DROP TABLE [dbo].[Alarm]
DROP TABLE [dbo].[Tag]
DROP TABLE [dbo].[AlarmReviewStatus]
DROP TABLE [dbo].[AlarmType]
DROP TABLE [dbo].[Lorem]
DROP PROCEDURE [dbo].[GetRandomWords]
*/

CREATE TABLE [dbo].[AlarmType](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[AlarmType] [varchar](10) NULL,
	[DisplaySequence] [int] NOT NULL DEFAULT ((0)),
	[TagAlarmFieldSuffix] [varchar](10) NULL,
	CONSTRAINT [PK_AlarmType] PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

CREATE TABLE [dbo].[AlarmReviewStatus](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ReviewStatus] [varchar](30) NULL,
	[DisplaySequence] [int] NOT NULL DEFAULT ((0)),
	CONSTRAINT [PK_AlarmReviewStatus] PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

CREATE TABLE [dbo].[Tag](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TagName] [varchar](30) NOT NULL,
	[TagDescription] [varchar](200) NOT NULL,
	[AreaUnitEquipment] [varchar](50) NULL,
	[DrawingReference] [varchar](50) NULL,
	CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

CREATE TABLE [dbo].[Alarm](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[TagId] [int] NOT NULL,
	[AlarmTagName] [varchar](30) NOT NULL,
	[AlarmDescription] [varchar](max) NULL,
	[AlarmTypeId] [int] NOT NULL,
	[IsValid] [bit] NULL DEFAULT (NULL),
	[ReviewStatusId] [int] NULL,
	[ReviewedDate] [date] NULL,
	CONSTRAINT [PK_Alarm] PRIMARY KEY CLUSTERED ([Id] ASC),
	CONSTRAINT [FK_Alarm_AlarmReviewStatus] FOREIGN KEY([ReviewStatusId]) REFERENCES [dbo].[AlarmReviewStatus] ([Id]),
	CONSTRAINT [FK_Alarm_AlarmType] FOREIGN KEY([AlarmTypeId]) REFERENCES [dbo].[AlarmType] ([Id]),
	CONSTRAINT [FK_Alarm_Tag] FOREIGN KEY([TagId]) REFERENCES [dbo].[Tag] ([Id])
)

GO

--Random Word generator
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Lorem]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[Lorem](
		[id] [int] IDENTITY(1,1) NOT NULL,
		[Word] [varchar](12) NOT NULL,
		[RndSort] int NULL,
		PRIMARY KEY CLUSTERED ([id] ASC)
	)
END
GO

IF NOT EXISTS (SELECT * FROM [dbo].[Lorem])
BEGIN
	INSERT INTO [dbo].[Lorem] (Word) VALUES
	('a'),('ac'),('accumsan'),('ad'),('adipiscing'),('Aenean'),('Aliquam'),('aliquet'),('amet'),('ante'),('aptent'),('arcu'),('at'),('auctor'),('augue'),('bibendum'),('blandit'),('Class'),('commodo'),('condimentum'),('congue'),('consectetuer'),('consequat'),('conubia'),('Cras'),('Cum'),('Curabitur'),('cursus'),('dapibus'),('diam'),
	('dictumst'),('dis'),('dolor'),('Donec'),('dui'),('Duis'),('egestas'),('eget'),('eleifend'),('elit'),('enim'),('erat'),('eros'),('est'),('et'),('Etiam'),('eu'),('euismod'),('fames'),('faucibus'),('felis'),('fermentum'),('feugiat'),('fringilla'),('Fusce'),('gravida'),('habitant'),('habitasse'),('hac'),('hendrerit'),('hymenaeos'),
	('iaculis'),('id'),('imperdiet'),('In'),('inceptos'),('Integer'),('interdum'),('ipsum'),('justo'),('lacinia'),('lacus'),('laoreet'),('lectus'),('leo'),('libero'),('ligula'),('litora'),('lobortis'),('lorem'),('Maecenas'),('magna'),('magnis'),('malesuada'),('massa'),('mattis'),('Mauris'),('metus'),('mi'),('molestie'),('mollis'),
	('montes'),('morbi'),('mus'),('Nam'),('nascetur'),('natoque'),('nec'),('neque'),('netus'),('nibh'),('nisl'),('non'),('nonummy'),('nostra'),('nulla'),('Nullam'),('Nunc'),('odio'),('orci'),('ornare'),('parturient'),('pede'),('pellentesque'),('penatibus'),('per'),('pharetra'),('placerat'),('platea'),('porta'),('porttitor'),
	('posuere'),('potenti'),('Praesent'),('pretium'),('Proin'),('pulvinar'),('purus'),('quam'),('quis'),('Quisque'),('rhoncus'),('ridiculus'),('risus'),('sagittis'),('sapien'),('scelerisque'),('Sed'),('sem'),('semper'),('senectus'),('sit'),('sociis'),('sociosqu'),('sodales'),('sollicitudin'),('Suspendisse'),('taciti'),('tellus'),('tempor'),('tempus'),
	('tincidunt'),('torquent'),('tortor'),('tristique'),('turpis'),('ullamcorper'),('ultrices'),('ultricies'),('urna'),('Ut'),('varius'),('vehicula'),('vel'),('velit'),('venenatis'),('vestibulum'),('Vi'),('vitae'),('Vivamus'),('viverra'),('volutpat'),('vulputate'),('wisi');
END

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetRandomWords]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetRandomWords] AS' 
END
GO


ALTER PROCEDURE [dbo].[GetRandomWords]
	@WordCount int = 100,
	@RandomWords varchar(max) OUTPUT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @RetVal varchar(max)
	DECLARE @WordCounter int = 0
	DECLARE @WordTableRowCount int = 0
	DECLARE @WordTable TABLE (Word varchar(max), SortOrder int, INDEX IXWordTable_SortOrder NONCLUSTERED (SortOrder) )

	WHILE @WordTableRowCount < @WordCount
	BEGIN
		INSERT INTO @WordTable (Word)
		SELECT Word
		FROM [dbo].[Lorem]
	
		SET @WordTableRowCount = @WordTableRowCount + @@ROWCOUNT
	END

	DECLARE @Word varchar(max)
	DECLARE cur CURSOR FOR SELECT Word FROM @WordTable
	OPEN cur
	FETCH NEXT FROM cur INTO @Word
	WHILE @@FETCH_STATUS = 0
	BEGIN
		UPDATE @WordTable
		SET SortOrder = RAND()*(@WordTableRowCount - 1) + 1
		WHERE CURRENT OF cur

		FETCH NEXT FROM cur INTO @Word
	END

	SELECT @RandomWords = 
		STUFF ((
			SELECT ' ' + Word 
			FROM 
			(
				SELECT *, ROW_NUMBER() OVER (ORDER BY SortOrder) AS RowNumber FROM @WordTable
			) t 
			WHERE	t.RowNumber <= @WordCount
			FOR XML PATH('')
		), 1,1,'')
END

GO

--USAGE:
--declare @RandWds varchar(max)
--exec [dbo].[GetRandomWords] @WordCount = 500, @RandomWords = @RandWds OUTPUT
--print @RandWds

--populate data
SET NOCOUNT ON
SET IDENTITY_INSERT [dbo].[AlarmType] ON
INSERT INTO [dbo].[AlarmType] 
	([Id], [AlarmType], [DisplaySequence], [TagAlarmFieldSuffix]) 
VALUES 
	(1, 'HI', 1, '.HI'),
	(2, 'LO', 2, '.LO'),
	(3, 'HIHI', 3, '.HIHI'),
	(4, 'LOLO', 4, '.LOLO'),
	(5, 'DISCRETE', 5, '.OUT')
SET IDENTITY_INSERT [dbo].[AlarmType] OFF
GO
SET IDENTITY_INSERT [dbo].[AlarmReviewStatus] ON
INSERT INTO [dbo].[AlarmReviewStatus]
	([Id], [ReviewStatus], [DisplaySequence])
VALUES
	(1, 'COMPLETE', 1),
	(2, 'PENDING', 2),
	(3, 'MONITORING', 3),
	(4, 'PROD MGR TO APPROVE', 4),
	(5, 'READY TO IMPLEMENT', 5)
SET IDENTITY_INSERT [dbo].[AlarmReviewStatus] OFF
GO

DECLARE @TagId int, @AlarmTypeId int, @ReviewStatusId int
DECLARE @TagCounter int = 0, @TagCount int = 10000, @AlarmCounter int = 0, @AlarmCount int = 2
DECLARE @RandomWords varchar(max), @RandomWordCount int
WHILE @TagCounter < @TagCount
BEGIN
	SET @TagCounter = @TagCounter + 1
	PRINT 'CREATING TAG ' + CONVERT(VARCHAR, @TagCounter) + ' OF ' + CONVERT(VARCHAR, @TagCount)

	INSERT INTO [dbo].[Tag] 
		([TagName], [TagDescription], [AreaUnitEquipment], [DrawingReference])
	VALUES (
		'Tag'+ CONVERT(VARCHAR, @TagCounter),
		'Tag Desc ' + CONVERT(VARCHAR, @TagCounter),
		'Area ' + CONVERT(VARCHAR, (@TagCounter % 4)),
		'DWG-' + CONVERT(VARCHAR, (@TagCounter % 45))
		)
	
	SET @TagId = SCOPE_IDENTITY()
	SET @AlarmCount = RAND()*(5-2)+2 --(RAND BETWEEN 2 AND 5)
	SET @AlarmTypeId = RAND()*(5-1)+1 --(RAND BETWEEN 1 AND 5)
	SET @ReviewStatusId = RAND()*(5-0)+0 --(RAND BETWEEN 0 AND 5)
	SET @AlarmCounter = 0
	
	WHILE @AlarmCounter < @AlarmCount
	BEGIN
		SET @AlarmCounter = @AlarmCounter + 1
		PRINT ' - CREATING ALARM ' + CONVERT(VARCHAR, @AlarmCounter) + ' OF ' + CONVERT(VARCHAR, @AlarmCount)
		SET @RandomWordCount = RAND()*(100-5)+5 --5-100 RANDOM WORDS
		EXEC [dbo].[GetRandomWords] @WordCount = @RandomWordCount, @RandomWords = @RandomWords OUTPUT
		
		INSERT INTO [dbo].[Alarm]
			([TagId], [AlarmTagName], [AlarmDescription], [AlarmTypeId], [IsValid], [ReviewStatusId], [ReviewedDate])
		SELECT
			@TagId,
			'ALARM' + CONVERT(VARCHAR, @TagId) + '-' + CONVERT(VARCHAR, @AlarmCounter),
			@RandomWords,
			@AlarmTypeId,
			CASE @ReviewStatusId
				WHEN 1 THEN 1
				WHEN 3 THEN 1
				WHEN 5 THEN 1
				WHEN 2 THEN 0
				ELSE NULL
			END,
			CASE 
				WHEN @ReviewStatusId = 0
				THEN NULL
				ELSE @ReviewStatusId
			END,
			CASE 
				WHEN @ReviewStatusId = 1
				THEN DATEADD(DAY, (@AlarmCount + @AlarmTypeId + @ReviewStatusId) * -1, GETDATE())
				ELSE NULL
			END
	END


END 

SET NOCOUNT OFF
GO

SELECT 
	(select count(*) from [dbo].[Tag]) +
	(select count(*) from [dbo].[Alarm]) AS TOTAL_ROW_COUNT

Generate Model Classes

In the Package Manager Console run the following.

Scaffold-DbContext -Connection "Server=(local);Database=MasterDetailSample;Trusted_Connection=True;" -Provider "Microsoft.EntityFrameworkCore.SqlServer" -OutputDir "Models\Data" -Tables ("Alarm","AlarmReviewStatus","AlarmType","Tag") -Force -DataAnnotations

009_DB

The project should now have these classes.

010_DB

Delete the OnConfiguring method in the DbContext class.

011_DB

Add a constructor, which is used for dependency injection.

012_DB

Connection String

In appsettings.json add the connection string.

013_DB

Register the DB Context

In Startup.cs register the db context. (note: add the using statement for Microsoft.EntityFrameworkCore)

NOTE: use the new db context pool only if your context class does not store any state within it.

As this example doesn’t store any state the AddDbContextPool method is used, otherwise fall back to services.AddDbContext().

014_DB

Authorization

Authorization policies are more flexible than just decorating a controller/method with [Authorize(“ADGroup”)] and allow easy changes within appsettings.json. The following will create 2 model classes (AuthPolicy and AuthMember) and a settings class which will get its values from appsettings.json. It will also show how to initialize the settings in Startup.cs and how to inject it into controllers and views. The Settings class has 2 policies, one for READ and one for WRITE.

Auth Classes

Create a new folder at the project root called Auth, and add a new class file Auth.cs, with the following code.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
public class AuthPolicySettings
{
	public AuthPolicy ReadAccess { get; set; }
	public AuthPolicy WriteAccess { get; set; }

	public const string ReadAccessName = "ReadAccess";
	public const string WriteAccessName = "WriteAccess";

	internal void InitAuth(IServiceCollection services)
	{
		AddAuthorization(services, ReadAccess, ReadAccessName);
		AddAuthorization(services, WriteAccess, WriteAccessName);
	}

	private void AddAuthorization(IServiceCollection services, AuthPolicy authPolicy, String policyName)
	{
		services.AddAuthorization(options =>
		{
			options.AddPolicy(policyName, policy =>
			{
				policy.RequireAuthenticatedUser();
				foreach (Auth.AuthMember authMember in authPolicy.Members)
				{
					policy.RequireRole(authMember.Values);
				}
			});
		});
	}
}

public class AuthPolicy
{
	public List<AuthMember> Members { get; set; }
}

public class AuthMember
{
	public List<String> Values { get; set; }
}

Note: add the using statement for Microsoft.Extensions.DependencyInjection.

Config

Add the following to appsettings.json.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
"AuthPolicySettings": {
  "ReadAccess": {
    "Members": [
  	{
  	  "Values": [ "mydomain\\Domain Users" ]
  	}
    ]
  },
  "WriteAccess": {
    "Members": [
  	{
  	  "Values": [ "mydomain\\user1", "mydomain\\WriteAccessGroup" ]
  	}
    ]
  }
}

Replace the values with your AD Groups/users. The above settings give read access to all Domain Users, while write access is restricted to user1 OR members of the WriteAccessGroup.

To restrict the policy to allow members which belong to 2 groups, use the members array. i.e.

"WriteAccess": {
  "Members": [
    { "Values": [ "mydomain\\OfficeStaff" ]},
    { "Values": [ "mydomain\\EngineeringStaff" ]}
  ]
}

Meaning WriteAccess is given to users who belong to OfficeStaff AND EngineeringStaff.

Initialize the AuthPolicySettings

In ConfigureServices method of Startup.cs add the following code at the start of the method.

1
2
3
4
var authSection = Configuration.GetSection("AuthPolicySettings");
Auth.AuthPolicySettings authPolicySettings = new Auth.AuthPolicySettings();
authSection.Bind(authPolicySettings);
authPolicySettings.InitAuth(services);

Line 1 reads the settings out of appsettings.json. Line 2 and 3 instantiates the classes and line 4 sets it up for dependency injection.

Auth Usage

This Auth can be used 3 ways.

Attributes

Decorate controller classes with attribute. This example lets ReadAccess users to view Index but not the About page. Note: this is only an example, you wouldn’t really expect the About page to be restricted to the write group.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
[Authorize(Policy = Auth.AuthPolicySettings.ReadAccessName)]
public class HomeController : Controller
{
	public IActionResult Index()
	{
		return View();
	}

	[Authorize(Policy = Auth.AuthPolicySettings.WriteAccessName)]
	public IActionResult About()
	{
		ViewData["Message"] = "Your application description page.";

		return View();
	}

Dependency Injection into Controller

Include IAuthorizationService parameter into a controller.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
public class HomeController : Controller
{
	private readonly IAuthorizationService _authSvc;

	public HomeController(IAuthorizationService authSvc)
	{
		_authSvc = authSvc;
	}

	public IActionResult About()
	{
		if (_authSvc.AuthorizeAsync(User, AuthPolicySettings.WriteAccessName).Result.Succeeded)
		{
			ViewData["Message"] = "Your application description page. Viewed by user with WRITE ACCESS";
		}
		else
		{
			ViewData["Message"] = "Your application description page.";
		}
		return View();
	}
}

Inject into View

Add the using and inject statements at the top of the razor page.

Call the AuthorizeAsync method on the service. Similar to above in the controller.

015_Auth

Web API

This next step changes the code generator template for “API Controller with actions, using Entity Framework” so that it matches the Kendo signature. This technique will allow complete control over the code generation just for the project.

Create a new folder structure at the solution root.
Templates\ControllerGenerator
Copy “ApiControllerWithContext.cshtml” from “C:\Program Files\dotnet\sdk\NuGetFallbackFolder\microsoft.visualstudio.web.codegenerators.mvc\2.0.0\Templates\ControllerGenerator” into the newly created folder. Note: the file can also be found at C:\Users\*YOU*\.nuget\packages\microsoft.visualstudio.web.codegenerators.mvc\1.1.1\Templates\ControllerGenerator

Replace the contents with the following. (hint: do it outside of VS so that it bypasses formatting)

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
//Updated from C:\Users\*YOU*\.nuget\packages\microsoft.visualstudio.web.codegenerators.mvc\1.1.1\Templates\ControllerGenerator
// where *YOU* is your username
@inherits Microsoft.VisualStudio.Web.CodeGeneration.Templating.RazorTemplateBase
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Kendo.Mvc.UI;
using Kendo.Mvc.Extensions;
using Microsoft.AspNetCore.Authorization;
@{
    
    foreach (var namespaceName in Model.RequiredNamespaces)
    {
@:using @namespaceName;
    }
}

namespace @Model.ControllerNamespace
{
@{
    string routePrefix = "api/" + Model.ControllerRootName;
    var entitySetName = Model.ModelMetadata.EntitySetName;
    var primaryKeyName = Model.ModelMetadata.PrimaryKeys[0].PropertyName;
    var primaryKeyShortTypeName = Model.ModelMetadata.PrimaryKeys[0].ShortTypeName;
    var primaryKeyType = Model.ModelMetadata.PrimaryKeys[0].TypeName;
    var primaryKeyIsAutoGenerated = Model.ModelMetadata.PrimaryKeys[0].IsAutoGenerated;
    
}
    [Produces("application/json")]
    [Route("@routePrefix")]
    [Authorize]
    public class @Model.ControllerName : Controller
    {
        private readonly @Model.ContextTypeName _context;

        public @(Model.ControllerName)(@Model.ContextTypeName context)
        {
            _context = context;
        }

        // GET: @routePrefix
        [HttpGet]
        [Authorize(Policy = Auth.AuthPolicySettings.ReadAccessName)]
        public DataSourceResult List([DataSourceRequest]DataSourceRequest request)
        {
            return _context.@(entitySetName)
                .ToDataSourceResult(request);
        }

        // GET: @routePrefix/5
        [HttpGet("{id}")]
        [Authorize(Policy = Auth.AuthPolicySettings.ReadAccessName)]
        public async Task<IActionResult> Get([FromRoute] @primaryKeyShortTypeName id)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            var @Model.ModelVariable = await _context.@(entitySetName).SingleOrDefaultAsync(m => m.@primaryKeyName == id);

            if (@Model.ModelVariable == null)
            {
                return NotFound();
            }

            return Ok(@(Model.ModelVariable));
        }

        // PUT: @routePrefix/5
        [HttpPut("{id}")]
        [Authorize(Policy = Auth.AuthPolicySettings.WriteAccessName)]
        public async Task<IActionResult> Update([FromRoute] @primaryKeyShortTypeName id, @(Model.ModelTypeName) @Model.ModelVariable)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            if (id != @(Model.ModelVariable).@primaryKeyName)
            {
                return BadRequest();
            }

            _context.Entry(@(Model.ModelVariable)).State = EntityState.Modified;

            try
            {
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!@(Model.ModelTypeName)Exists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return NoContent();
        }

        // POST: @routePrefix
        [HttpPost]
        [Authorize(Policy = Auth.AuthPolicySettings.WriteAccessName)]
        public async Task<IActionResult> Create(@(Model.ModelTypeName) @Model.ModelVariable)
        {
@{ 
    if (primaryKeyIsAutoGenerated)
    {
        @:@(Model.ModelVariable).@primaryKeyName = 0;
    }
}            
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            _context.@(entitySetName).Add(@(Model.ModelVariable));
@{
    if (primaryKeyIsAutoGenerated)
    {
            @:await _context.SaveChangesAsync();
    }
    else
    {
            @:try
            @:{
                @:await _context.SaveChangesAsync();
            @:}
            @:catch (DbUpdateException)
            @:{
                @:if (@(Model.ModelTypeName)Exists(@(Model.ModelVariable).@primaryKeyName))
                @:{
                    @:return new StatusCodeResult(StatusCodes.Status409Conflict);
                @:}
                @:else
                @:{
                    @:throw;
                @:}
            @:}
    }
}
            return new OkObjectResult(new DataSourceResult { Data = new[] { @Model.ModelVariable }, Total = 1 });
        }

        // DELETE: @routePrefix/5
        [HttpDelete("{id}")]
        [Authorize(Policy = Auth.AuthPolicySettings.WriteAccessName)]
        public async Task<IActionResult> Delete([FromRoute] @primaryKeyShortTypeName id)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            var @Model.ModelVariable = await _context.@(entitySetName).SingleOrDefaultAsync(m => m.@primaryKeyName == id);
            if (@Model.ModelVariable == null)
            {
                return NotFound();
            }

            _context.@(entitySetName).Remove(@Model.ModelVariable);
            await _context.SaveChangesAsync();

            return Ok(@Model.ModelVariable);
        }

        private bool @(Model.ModelTypeName)Exists(@primaryKeyShortTypeName id)
        {
            return _context.@(entitySetName).Any(e => e.@primaryKeyName == id);
        }
    }
}
@*
    /*
    List Model Properties
        @{
            foreach (System.ComponentModel.PropertyDescriptor prop in System.ComponentModel.TypeDescriptor.GetProperties(Model.GetType()))
            {
                @: @(prop.Name) (@(prop.PropertyType.FullName));
            }
        }
    */
*@

Test it out by creating a WebApi for the AlarmReviewStatus model.

Create a folder WebApi under Controllers. Right click it and choose Controller… (Note: if this is the first time then follow the prompts and add MVC dependencies). When the Add Scaffold screen appears select “API Controller with actions, using Entity Framework”.

016_WebApi

Select AlarmReviewStatus for the model and your db context. Leave the default for the controller name.

017_WebApi

The following file should be created.

018_WebApi

Test it out by running the project and browsing to /api/AlarmReviewStatus. Should be pretty similar to the following.

019_WebApi

Continue to do the same for the other models.

  • AlarmTypes
  • Tag
  • Alarm

The WebApi folder should now look like this.

020_WebApi

Setup Kendo and Json for use in the views

Startup.cs

In Startup.cs register Kendo and set the Json options.

026_KendoSetup

View Imports

In Views\_ViewImports.cshtml add using statement.

027_KendoSetup

Kendo UI Client-side Resources

Create a folder “kendo-ui” under wwwroot\lib, and copy js and styles from Telerik source. (mine at the time of writing was C:\Program Files (x86)\Progress\Telerik UI for ASP.NET Core R3 2017)

028_KendoSetup

Styles and Javascript files

In Views\Shared\_Layout.cshtml remove the scripts at the bottom and replace them at the top. (I found this easier than remembering to use deferred() for the kendo controls)

029_KendoSetup

You could use the <environment> tag to use the full files for Development and the minified ones for Production. I didn’t do that here because it’s only really useful when troubleshooting.

The 3 kendo styles (highlighted) can be replaced with 1 if you use the theme builder from Telerik (http://themebuilder.telerik.com/aspnet-core).

Editor Templates

Copy all the razor templates from the Telerik source to Views\Shared\EditorTemplates. (mine at the time of writing was C:\Program Files (x86)\Progress\Telerik UI for ASP.NET Core R3 2017\wrappers\aspnetcore\EditorTemplates\razor).

030_KendoSetup

Globalization

In Startup.Configure() set it to use Request Localization.

031_KendoSetup

In _Layout.cshtml include the kendo culture javascript file after the other js files.

1
2
3
4
5
@{ var culture = System.Globalization.CultureInfo.CurrentCulture.ToString(); }
<script src='@Url.Content("~/lib/kendo-ui/js/cultures/kendo.culture." + culture + ".min.js")'></script>
<script>
        kendo.culture("@culture");
</script>

032_KendoSetup

The View

Create a new view “Tags” in the Views\Home folder and change the HomeController to suit.

021_View

022_View023_View

024_View

Build and run it to check that all is well.

025_View

The Tags (Parent) Grid

Now add the Tags grid. In the view, start with the bare minimum to get the Kendo grid doing something. In Tags.cshtml add the following.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
@(Html.Kendo().Grid<KendoMasterDetailGrid.Models.Data.Tag>()
    .Name("TagGrid")
    .DataSource(ds => ds
        .WebApi()
        .Model(model =>
        {
            model.Id(vm => vm.Id);
            model.Field(vm => vm.Id).Editable(false);
        })
        .Read(read => read.Action("List", "Tags"))
        .Events(ev =>
        {
            ev.Error("function(e){alert('Error occurred with DataSource. See log for more info.'); console.log(e); e.sender.cancelChanges();}");
        })
    )
    .Columns(c => c.AutoGenerate(true))
    .Pageable(p => p
        .Enabled(true)
    )
 )

Line 1 creates a kendo grid for the Tag model.
Line 2 gives it a name so that it can be referenced with jQuery.
Lines 3-15 set the data source.
Line 10 sets up the read action to the List method of the TagsController.
Line 13 hooks the error event. Doesn’t do too much but logs the error to console, which is really helpful when trouble shooting.
Line 16 shows all the columns by default. Expect to change this soon as you’ll want to specifically state your columns and widths. But for now it’s a nice quick start to get something on the screen.
Lines 17-19 sets up paging.

Run the project. The result should be similar to this.

033_View

Add some of the other functionality like sorting and filtering.

034_View

Add Refresh and bigger page sizes.

035_View

The view with extra functionality.

036_View

Add the edit/create/delete functionality. First in DataSource add the Update action, then add the command column (before or after the data columns, depending on what you feel like).

038_View

The grid should now look like this, with updates and saves.

039_View

040_View

The default option for edit mode is InLine, but there are other options. InLine or PopUp works well with the command buttons on each row. InCell is good for a batch save, but you have to do other stuff to handle that. This example doesn’t go into that. To change the mode to PopUp, add the Editable method on the grid.

043_View

Out of the box it looks like this.

044_View

This can be made prettier by adding an EditorTemplate for Tag. For now set the edit mode back to InLine and continue.

Updates should work successfully if you added yourself to the WriteAccess group. Change the config so that you are not in the WriteAccess group and try to update again. Have a look at the console log and drill down into the responseText of the xhr object. You’ll see the error.

041_View

Seeing the user can’t save, the edit button (or column) can be hidden. Change the command column to the following code (ref above in Auth Usage for the view).

042_View

Include yourself back into the WriteAccess group and add the create functionality. First add the create action to the DataSource, then add the create button on the tool bar of the grid.

045_View

046_View

Adding a new record.

047_View

After Save.

048_View

Add the delete functionality in the same way. First add it to the DataSource, then add it next to the edit command.

049_View

If you try to delete right now it will fail because of the foreign key constraint. At least you get a hint of that from the Error event of the DataSource.

050_View

You could turn on cascade deletes or do an extra check on the TagsController to return a BadRequest if the Tag has children. Leave it for now until after the details grid.

The Alarms (Detail) Grid

Make use of the Kendo templates. Start a script tag and include a kendo grid for the Alarm model. Name it “TagGrid_#=Id#”. In order for the template grid to work properly the last method called on the detail grid must be ToClientTemplate(). Include the template in the parent grid with ClientDetailTemplateId().

051_View

If you run the above code, you’ll see the beginnings of the master-detail layout. Notice the id of the detail grid matches the Tag Id.

052_View

Continue adding the DataSource and columns to the Alarms grid. (Remember to keep the ToClientTemplate() as the last call).

To make it a bit easier to return Alarms filtered by a Tag, I’ve added an extra parameter “ParentTagId” to the List and Create methods on the AlarmsController.

The full details grid as follows.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<script id="AlarmsTemplate" type="text/x-kendo-template">
@(Html.Kendo().Grid<KendoMasterDetailGrid.Models.Data.Alarm>()
    .Name("TagGrid_#=Id#")
    .DataSource(ds => ds
        .WebApi()
        .Model(model =>
        {
            model.Id(vm => vm.Id);
            model.Field(vm => vm.Id).Editable(false);
        })
        .Read(read => read.Action("List","Alarms", new { ParentTagId = "#=Id#" }))
        .Create(create => create.Action("Create","Alarms", new { ParentTagId = "#=Id#" }))
        .Update(update => update.Action("Update","Alarms", new { id = "{0}" }))
        .Destroy(destroy => destroy.Action("Delete", "Alarms", new { id = "{0}" }))
        .Events(e =>
        {
            e.Error("function(e){alert('Error occurred with DataSource. See log for more info.'); console.log(e); e.sender.cancelChanges();}");
        })
        )
    .ToolBar(tools =>
    {
        if (_authSvc.AuthorizeAsync(User, AuthPolicySettings.WriteAccessName).Result.Succeeded)
        {
            tools.Create().Text("Add New Alarm");
        }
    })
    .Columns(c =>
    {
        c.AutoGenerate(true);

        if (_authSvc.AuthorizeAsync(User, AuthPolicySettings.WriteAccessName).Result.Succeeded)
        {
            c.Command(command =>
            {
                command.Edit().Text("&nbsp;").CancelText("&nbsp;").UpdateText("&nbsp;");
                command.Destroy().Text("&nbsp;");
            });
        }
    })
    .Editable(e => e.Mode(GridEditMode.InLine))
    .Navigatable()
    .Filterable()
    .Sortable()
    .Pageable(p =>
    {
        p.Refresh(true);
        p.PageSizes(true);
        p.Enabled(true);
    })
    .ToClientTemplate()
)
</script>

The modifications to the AlarmsController

053_View

054_View

The results.

055_View

About time to explicitly code the columns. Remove the c.AutoGenerate(true); line and replace with the following. Start with the text fields first.

056_View

The description column takes up a lot of room on the grid, so we’ll drop that on this screen and change the edit mode to PopUp and show it there.

057_View

Remove the AlarmDescription column and add the lookups, AlarmType and ReviewStatus.
Firstly inject the db context to the Home Controller and use the ViewBag to hold the SelectList for AlarmType.

058_View

Add the column to the view.

059_View

The results for the view and InLine edit mode.

060_View

061_View

If you add a UIHint and Display attributes to the model class, it will flow through to the PopUp edit as well.

062_View

063_View

Do the same for ReviewStatusId (except bind it to a ComboBox instead of a DropDown).

Setup the data in the HomeController.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
public IActionResult Tags()
{
	//load select lists
	//Alarm Lookup
	ViewBag.AlarmTypeId_Data = new Microsoft.AspNetCore.Mvc.Rendering.SelectList(_dbContext
		.AlarmType
		.OrderBy(t => t.DisplaySequence)
		.ToList()
		, "Id", "AlarmType1");

	//ReviewStatus Lookup
	ViewBag.ReviewStatusId_Data = new Microsoft.AspNetCore.Mvc.Rendering.SelectList(_dbContext
		.AlarmReviewStatus
		.OrderBy(t => t.DisplaySequence)
		.ToList()
		, "Id", "ReviewStatus");

	return View();
}

Create a new EditorTemplate named ComboBox. The content will be very similar to the Telerik GridForeignKey.cshtml file.

1
2
3
4
5
6
7
@model object

@(
 Html.Kendo().ComboBoxFor(m => m)
        .BindTo((SelectList)ViewData[ViewData.TemplateInfo.GetFullHtmlFieldName("") + "_Data"])
        .ValuePrimitive(true)
)

Decorate the ReviewStatusId property of the Alarm model.

065_View

Add the column to the child grid.

066_View

The results should be something similar to this, where Review Status can have a null value.

067_View

Tidy up those labels using the Display attribute on the Alarm model. At the same time hide the Id fields by setting the ScaffoldColumn attribute to false and show the ReviewedDate as a Date instead of DateTime.

068_View

If the checkbox has the extra label, you could get rid of it, seeing as the label is already there on the edit form. Change the Boolean editor template to have a blank label.

069_View

Last thing to fix is the long description should be bound to a bigger input box. As KendoUI don’t have a suitable control, create an editor template named TextArea.cshtml with the following content.

1
2
3
@model String

@(Html.TextAreaFor(m => m, new { Class = "k-textbox", Rows = "8" }))

This will style a TextArea element to match the rest of the Kendo controls.

Set the UIHint for the AlarmDescription in the Alarm model.

070_View

And the results should be similar to this.

071_View

One thing that did tick me off about the Kendo grid was that this works absolutely fine for US date formats. When saving a date in other locales it fails because the datasource serializes it as US format. To fix this you need to hook the Create and Update methods of the datasource. In Tags.cshtml add the following javascript to the bottom.

1
2
3
4
5
6
<script>
    function onAlarmDataSourceSending(data) {
        data.ReviewedDate = kendo.toString(data.ReviewedDate, 'yyyy-MM-dd');

    }
</script>

Add a Data method to the Create and Update actions of the Alarm Datasource.

072_View

Next Steps

Custom editor templates to replace the out-of-the-box edit popups and stretching the edit screen to full width.

Showing/hiding columns based on screen width.

Validation should be expanded to handle multiple column validation and multiple objects. And displaying those validation fails to the end user.

Telerik Reports project with some reports for Tags and Alarms and linked from the grids.

Summary

This has shown how to display parent/child relationships in a table like fashion with edits and simple validation using web api with authentication over the top of a SQL database.

I hope this example serves as a nice entry point to Kendo controls, web api and .NET Core.

Telerik Kendo UI Master Detail Grids with full edits using webapi and ASP.NET Core 2
Tagged on:             

Leave a Reply

Your email address will not be published. Required fields are marked *