This is a migrated thread and some comments may be shown as answers.

Client Side Binding with Foreign Key Columns

10 Answers 938 Views

Grid

This is a migrated thread and some comments may be shown as answers.

andrew asked on 24 Jul 2012, 06:16 AM

Hi,

I am trying to find out how to do client side binding on foreign keys, all the examples I have come across are for server side binding and also I have seen no reference to how to deal with different model scenarios or what your controller actions should look like.

I have been stuck about a week now and basically I have a grid that is showing permissions that references Users, Payrolls and Functions. The idea is that the grid will display the User.NetLogin, Payroll.Title and Function.Title on the grid and in the dropdowns. This is working fine, however the way I passed the data to my model as you will see below I don't think is correct and the main issue is the code controlling the create, edit, delete and save (as this uses batch edit) features. Currently only read works, all the data manipulation fails and this is what I need to get working.

The permissions model has 3 foreign keys and these are refenced in the way the MVC Student Enrolement tutorial described the enrolement model. So on the permissions table the ID of the 3 reference tables are held as well as virtual object:

e.g. public int UserID {get;set}
public virtual User User {get;set;}

On the 3 reference tables a reciprical link is held to the permissions table using:

public virtual ICollection<Permission> Permissions

I don't quite understand why this structure is used perhaps someone could exaplain? But this structure was from a tutorial on the MVC site and it worked perfectly what I have and Index view and 4 views for CRUD actions. The examples I have seen for foreign keys so far only show models with the public User User {get;set;} in them.

I am using entity framework so my permissions model is like

public class User

{

public int UserID { get; set; }

public string NetLogin { get; set; }

[Required(ErrorMessage = "Employee number is required.")]

[Display(Name = "Employee Number")]

[MaxLength(6)]

public string EmpNumber { get; set; }

[Required(ErrorMessage = "First name is required.")]

[Display(Name = "First Name")]

[MaxLength(50)]

public string Forename { get; set; }

[Required(ErrorMessage = "Last name is required.")]

[Display(Name = "Last Name")]

[MaxLength(50)]

public string Surname { get; set; }

public bool Active { get; set; }

public virtual ICollection<Permission> Permissions { get; set; }

}

public class Payroll

{

public int PayrollID { get; set; }

[Required(ErrorMessage = "Payroll title is required.")]

[Display(Name = "Payroll")]

[MaxLength(20)]

public string Title { get; set; }

[Required(ErrorMessage = "C21 description is required.")]

[Display(Name = "C21 Name")]

[MaxLength(30)]

public string C21Name { get; set; }

[Required(ErrorMessage = "Server name is required.")]

[Display(Name = "Server Name")]

[MaxLength(20)]

public string Server { get; set; }

[Required(ErrorMessage = "Port is required.")]

[Display(Name = "Port")]

[MaxLength(5)]

public string Port { get; set; }

[Required(ErrorMessage = "Connection string is required.")]

[Display(Name = "Connection String")]

[MaxLength(200)]

public string Connection { get; set; }

public virtual ICollection<Permission> Permissions { get; set; }

}

public class Function

{

public int FunctionID { get; set; }

[Required(ErrorMessage = "Function description is required.")]

[Display(Name = "Function")]

[MaxLength(20)]

public string Title { get; set; }

public virtual ICollection<Permission> Permissions { get; set; }

}

public class Permission

{

public int PermissionID { get; set; }

[Required(ErrorMessage = "You must select a payroll.")]

public int PayrollID { get; set; }

[Required(ErrorMessage = "You must select a function.")]

public int FunctionID { get; set; }

[Required(ErrorMessage = "You must select a user.")]

public int UserID { get; set; }

[Range(typeof(double), "1", "5")]

[Required(ErrorMessage = "You must enter an access level.")]

public double? Level { get; set; }

public virtual Payroll Payroll { get; set; }

public virtual Function Function { get; set; }

public virtual User User { get; set; }

}

I have managed to get the read and custom dropdown editors working, but I can't find any code examples for the controller or the view using foreign keys like this and entity framework with kendo grid.

Currently the add action creates a javascript runtime error of 'NetLogin' is undefined. (NetLogin is a child entity of the User Object)

The save action for any delete and edit (add add) action litterally does nothing, I have traced it with firebug and it creates no errors and no actions and my knowledge of jquery is minimal to non so I couldn't trace it back.

Here is my controller:

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Entity;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using PayPlate.Models;

using PayPlate.DAL;

using PagedList;

namespace PayPlate.Controllers

{

public class PermissionController : Controller

{

UnitOfWork unitOfWork = new UnitOfWork();

public ActionResult Index()

{

return View();

}

/// <summary>

/// Creates new products by inserting the data posted by the Kendo Grid in the database.

/// </summary>

/// <param name="products">The products created by the user.</param>

/// <returns>The inserted products so the Kendo Grid is aware of the database generated ProductID</returns>

[HttpPost]

public ActionResult Create(IEnumerable<Permission> perms)

{

var result = new List<Permission>();

//Iterate all created products which are posted by the Kendo Grid

foreach (var permission in perms)

{

var perm = new Permission

{

UserID = permission.UserID,

PayrollID = permission.PayrollID,

FunctionID = permission.FunctionID,

Level = permission.Level

};

// store the product in the result

result.Add(perm);

// Add the entity

unitOfWork.PermissionRepository.Insert(perm);

}

// Insert all created products to the database

unitOfWork.Save();

// Return the inserted products - the Kendo Grid needs their ProductID which is generated by SQL server during insertion

return Json(result.Select(r => new

{
PermissionID = r.PermissionID, UserID = r.UserID, PayrollID =
r.PayrollID, FunctionID = r.FunctionID, Level = r.Level, NetLogin =
r.User.NetLogin, PayrollTitle = r.Payroll.Title, FunctionTitle =
r.Function.Title }));

/*

return Json(result.Select(p => new Permission

{

PermissionID = p.PermissionID,

UserID = p.UserID,

PayrollID = p.PayrollID,

FunctionID = p.FunctionID,

Level = p.Level

})

.ToList());

*/

}

/// <summary>

/// Reads the available products to provide data for the Kendo Grid

/// </summary>

/// <returns>All available products as JSON</returns>

[HttpPost]

public ActionResult Read()

{

var perms2 = unitOfWork.PermissionRepository.Get()

.Select(r => new { PermissionID = r.PermissionID,

UserID = r.UserID, PayrollID = r.PayrollID,

FunctionID = r.FunctionID, Level = r.Level,

NetLogin = r.User.NetLogin,

PayrollTitle = r.Payroll.Title,

FunctionTitle = r.Function.Title });

var perms1 = unitOfWork.PermissionRepository.Get()

// Use a view model to avoid serializing internal Entity Framework properties as JSON

.Select(p => new Permission

{

PermissionID = p.PermissionID,

UserID = p.UserID,

PayrollID = p.PayrollID,

FunctionID = p.FunctionID,

Level = p.Level

}).ToList();

return Json(perms2);

}

/// <summary>

/// Updates existing products by updating the database with the data posted by the Kendo Grid.

/// </summary>

/// <param name="products">The products updated by the user</param>

[HttpPost]

public ActionResult Update(IEnumerable<Permission> perms)

{

//Iterate all created products which are posted by the Kendo Grid

foreach (var permission in perms)

{

// Attach the entity

unitOfWork.PermissionRepository.Update(permission);

}

// Save all updated products to the database

unitOfWork.Save();

//Return emtpy result

return Json(null);

}

/// <summary>

/// Destroys existing products by deleting them from the database.

/// </summary>

/// <param name="products">The products deleted by the user</param>

[HttpPost]

public ActionResult Destroy(IEnumerable<Permission> perms)

{

//Iterate all destroyed products which are posted by the Kendo Grid

foreach (var permission in perms)

{

// Delete the entity

unitOfWork.PermissionRepository.Delete(permission);

}

// Delete the products from the database

unitOfWork.Save();

//Return emtpy result

return Json(null);

}

Here is my razor view:

@section CustomHeader {

@* kendo.common.min.css contains common CSS rules used by all Kendo themes *@

<link href="http://cdn.kendostatic.com/2012.2.710/styles/kendo.common.min.css" rel="stylesheet" />

@* kendo.silver.min.css contains the "Blue Opal" Kendo theme *@

<link href="http://cdn.kendostatic.com/2012.2.710/styles/kendo.silver.min.css" rel="stylesheet" />

<script src="http://cdn.kendostatic.com/2012.2.710/js/kendo.all.min.js"></script>

}

<h2>Permissions</h2>

<h3>Page Size: <div id="comboBox"></div></h3>

<script>

$("#comboBox").kendoComboBox({

dataTextField: "text",

dataValueField: "value",

dataSource: [

{ text: 10 },

{ text: 25 },

{ text: 50 },

{ text: 100 },

{ text: 500 }

],

change: function (e) {

var grid = $("#grid").data("kendoGrid");

grid.dataSource.pageSize(this.value());

}

});

</script>

@* The DIV where the Kendo grid will be initialized *@

<div id="grid"></div>

<script>

$(document).ready(function () {

var dataSource = new kendo.data.DataSource({

transport: {

create: {

url:
"@Url.Action("Create", "Permission")", //specify the URL which should
create new records. This is the Create method of the HomeController.

type: "POST" //use HTTP POST request as the default GET is not allowed for ASMX

},

read: {

url:
"@Url.Action("Read", "Permission")", //specify the URL which should
return the records. This is the Read method of the HomeController.

type: "POST", //use HTTP POST request as by default GET is not allowed by ASP.NET MVC

},

update: {

url:"@Url.Action("Update",
"Permission")", //specify the URL which should update the records. This
is the Update method of the HomeController.

type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC

},

destroy: {

url:
"@Url.Action("Destroy", "Permission")", //specify the URL which should
destroy the records. This is the Destroy method of the HomeController.

type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC

},

parameterMap: function(data, operation) {

if (operation != "read") {

// post the products so the ASP.NET DefaultModelBinder will understand them:

var result = {};

for (var i = 0; i < data.models.length; i++) {

var perm = data.models[i];

for (var member in perm) {

result["perms[" + i + "]." + member] = perm[member];

}

return result;

}

},

batch: true, // enable batch editing - changes will be saved when the user clicks the "Save changes" button

pageSize: 20,

schema: {

model: { // define the model of the data source. Required for validation and property types.

id: "PermissionID",

fields: {

PermissionID: { editable: false, nullable: true },

NetLogin: "User",

PayrollTitle: "Payroll",

FunctionTitle: "Function",

Level: { type: "number", validation: { required: true, min: 1} }

}

}});

$("#grid").kendoGrid({

dataSource: dataSource,

pageable: true,

height: 425,

sortable: true,

filterable: true,

groupable: true,

resizable: true,

reorderable: true,

toolbar: ["create", "save", "cancel"], // specify toolbar commands

columns: [

{ field: "NetLogin", width: "70px", editor: userDropDownEditor },

{ field: "PayrollTitle", width: "70px", editor: payrollDropDownEditor },

{ field: "FunctionTitle", width: "70px", editor: functionDropDownEditor },

{ field: "Level", width: "70px" },

{ command: "destroy", title: "Delete", width: "60px", groupable: false, filterable: false }],

editable: true // enable editing

});

function userDropDownEditor(container, options) {

$('<input data-text-field="NetLogin" data-value-field="NetLogin" data-bind="value:' + options.field + '"/>')

.appendTo(container)

.kendoDropDownList({

autoBind: false,

dataSource: {

transport: {

read: {

url:
"@Url.Action("Read", "User")", //specify the URL which should return
the records. This is the Read method of the HomeController.

type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC

}

});

}

function payrollDropDownEditor(container, options) {

$('<input data-text-field="Title" data-value-field="Title" data-bind="value:' + options.field + '"/>')

.appendTo(container)

.kendoDropDownList({

autoBind: false,

dataSource: {

transport: {

read: {

url:
"@Url.Action("Read", "Payroll")", //specify the URL which should return
the records. This is the Read method of the HomeController.

type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC

}

});

}

function functionDropDownEditor(container, options) {

$('<input data-text-field="Title" data-value-field="Title" data-bind="value:' + options.field + '"/>')

.appendTo(container)

.kendoDropDownList({

autoBind: false,

dataSource: {

transport: {

read: {

url:
"@Url.Action("Read", "Function")", //specify the URL which should
return the records. This is the Read method of the HomeController.

type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC

}

});

}

</script>

Any help would be really appreciated. I will make a quick example project when I get to work to assist.

Many thanks,

Andy

10 Answers, 1 is accepted

answered on 24 Jul 2012, 09:35 AM

Please find attached a sample project. If you go to the Admin tab there are link to the 4 views for users, payrolls, functions and permisions.

The Grid in question is the permissions grid.

There is also another problem on the users form where I wanted to make a Master and Details grid showing Users, then you select a user and it displays the payrolls that user has permissions for in the details grid. Then another level of you can select a payroll and see what function the user can perform and at what level within the selected payroll for the selected user.

This thread primarily addresses the foreign key column issue.

I hope you can help.

Andy

EDIT: File is attached lower down

answered on 24 Jul 2012, 05:19 PM

OK I got it working just fine now, Ive used it in Firefox, as well as IE. there was an issue though in IE, where there was an extra comma (,) that was tripping up IE when displaying the grid. See the Index.cshtml file under Permission. Line 89 I think under the read: statement... it should have NO comma after the - type: "POST"

Mine reads now-
read: { url: "@Url.Action("Read", "Permission")", //specify the URL which should return the records. This is the Read method of the HomeController.
type: "POST" //use HTTP POST request as by default GET is not allowed by ASP.NET MVC
},

The only thing I find now is when adding a new record, then an error gets generated. Have you not implemented that part, or is that the crux of you problem?

Dave

commented on 14 Dec 2022, 03:10 PM

I found the solution for myself in the UserController.cs. This is what I need for the Tag Help transport, shown lower down this post. I was looking for how to populate the combo box and the Example1.zip file farther up was what I needed to get me across the line!

        [HttpPost]
        public ActionResult Read()
        {
            var _users = unitOfWork.UserRepository.Get()
                // Use a view model to avoid serializing internal Entity Framework properties as JSON
                .Select(p => new User
                {
                    UserID = p.UserID,
                    NetLogin = p.NetLogin,
                    EmpNumber = p.EmpNumber,
                    Forename = p.Forename,
                    Surname = p.Surname,
                    Active = p.Active
                })
                .ToList();

            return Json(_users);
        }

This is the Tag Help Kendo UI Grid.

    <kendo-grid name="Budgets" navigatable="true">
            <datasource type="DataSourceTagHelperType.Ajax"
                        server-operation="false"
                        batch="true"
                        on-error="error_handler">
                <schema data="Data">
                    <model id="Id">
                        <fields>
                            <field name="Id" type="number" editable="false"></field>
                            <field name="ProjectId" type="number" editable="false"></field>
                            <field name="AccountCodeId" type="number"></field>
                            <field name="ClientAccountCode" type="string"></field>
                            <field name="WorkDescription" type="string" editable="false"></field>
                            <field name="BudgetDescription" type="string"></field>
                            <field name="BudgetAmount" type="number"></field>
                        </fields>
                    </model>
                </schema>
                <transport>
                    <read type="GET" url="@Url.Action("EditingInline_Read", "Budgets", new{id = Model.Id, sortByCA = Model.SortByClientAccountCode, sortByBD = Model.SortByBudgetDescription })" />
                    <update url="@Url.Action("EditingInline_Update", "Budgets")" />
                    <create url="@Url.Action("EditingInline_Create", "Budgets")" />
                    <destroy url="@Url.Action("EditingInline_Delete", "Budgets")" />
                </transport>
            </datasource>
            <columns>
                <foreign-key-column field="AccountCodeId" title="Account Codes" width="150"
                                    value-field="Id"
                                    text-field="AccountCode">
                    <datasource>
                        <transport>
                            <read url="@Url.Action("AccountCodes", "Budgets")"/>
                        </transport>
                    </datasource>
                </foreign-key-column>
                <column field="ClientAccountCode" title="Client AC" />
                <column field="WorkDescription" title="Work Description" />
                <column field="BudgetDescription" title="Budget Description" />
                <column field="BudgetAmount" title="Budget Amount" format="{0:C}" />
                <column template="@rowTemplate" title="Actions" width="100" />
            </columns>
            <toolbar>
                <toolbar-button name="create"></toolbar-button>
                <toolbar-button name="save"></toolbar-button>
            </toolbar>
            <editable mode="incell" />
            <pageable enabled="false" />
            <sortable enabled="false" />
            <scrollable enabled="true" />
        </kendo-grid>

Dave

commented on 14 Dec 2022, 03:12 PM

BTW, my actual Controller was Budgets and the Action is AccountCodes. This is the code I wrote and now it populated the combo box.

        public ActionResult AccountCodes()
        {
            var accountCodes = _context.TblAccountCodes
                .Select(b => new TblAccountCode()
                {
                    Id = b.Id,
                    AccountCode = b.AccountCode,
                    Description = b.Description
                })
                .OrderBy(e => e.AccountCode).ToList();

            return Json(accountCodes);
        }

Neli

commented on 19 Dec 2022, 07:44 AM

Hi Dave,

Thank you very much for sharing your solution with the community. We always appreciate sharing information about solutions and different implementations. I am sure it will be helpful to the other users in the Forum.

Regards,

Neli