GRID DAPPER

[Wandelson]

Hi,
I'm building SPA using kendo ui and angularjs for client-side and asp.net web api for server-side. Customer wants server paging, filtering and sorting in grids. I know, how to configure kendo grid, so I only need to implement it on my server.
I found solution to use .ToDataSourceResult() with the DataSourceRequest parameter, but the problem is that I'm not using entity framework for all db stuff - the requirement is to use dapper https://github.com/StackExchange/dapper-dot-net, so I should generate sql queries on the fly.
Are there some code samples or even already written components for this scenario?

[Kiril Nikolov]

Hello Wandelson,

 

I am afraid that we do not have such examples available at the moment. All the server side examples are available here:

 

https://github.com/telerik/ui-for-aspnet-mvc-examples

 

Regards,
Kiril Nikolov
Telerik

[Antony]

After a few failed attempts to move from WebForms to MVC over recent years, I am finally succeeding - mainly because I've decided to use Dapper and SQL rather than Entity Framework, LINQ etc. This is long after Wandelson's post, but for the benefit of others interested in this approach, here are some code snippets illustrating the use of Dapper with an MVC Kendo grid. I intend on expanding on this, somewhere, to cover development of a complete application, but the following snippets may help you for now. In order to have full functionality of the grid, including filtering and sorting, I have used an ajax data source configuration. The model for the grid can be different to the model for the view. In the code I've included below however the only data in the view is within the grid, so there is no other model required for the view. I do not use the grid's inbuilt functionality for creating a new record, nor for editing - I tried but could not get it to work the way I wanted (including dropdown lists populated from my view model); instead I use links to other views.

Snippet of my Main model:

using System;
using System.ComponentModel.DataAnnotations;

namespace ClinicianLeave.Models
{
    public class Main
    {
        public int Id { get; set; }

        [Required(ErrorMessage = "Required")]
        [DataType(DataType.Text)]
        public int EmployeeNo { get; set; }

        [Display(Name = "First Name")]
        [Required(ErrorMessage = "Required")]
        public string FirstName { get; set; }

        [Display(Name = "Last Name")]
        [Required(ErrorMessage = "Required")]
        public string LastName { get; set; }

 

Code used by the controller to get data for the grid (often called the Repository - but I call it Data Access):

using System;

using System.Collections.Generic;

using System.Linq;

using System.Data;

using System.Data.SqlClient;

using Dapper;

using System.Configuration;

using System.Web;

 

namespace ClinicianLeave.Models

{

    public class MainDA

    {

        private IDbConnection AppsConn;

        public MainDA()

        {

            AppsConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppsConn"].ConnectionString);

        }

        public List<Main> GetAll()

        {

            string sql = @"SELECT m.*, s.SpecialtyName as Unit, r.Item as Role, t.Item as LeaveType

                           FROM ClinLeav.Main m

                           left join Specialty s on m.UnitId = s.Id

                           left join ClinLeav.Menu r on m.RoleId = r.Id

                           left join ClinLeav.Menu t on m.LeaveTypeId = t.Id";

            return AppsConn.Query<Main>(sql).ToList();

        }

 

Snippet of the Controller:

using System;

using System.Web.Mvc;

using Kendo.Mvc.Extensions;

using Kendo.Mvc.UI;

using ClinicianLeave.Models;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

 

namespace ClinicianLeave.Controllers

{

    public class MainController : Controller

    {

        private MainVM mainVM;

        private MainDA mainDA;

 

        IDbConnection AppsConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppsConn"].ConnectionString);

 

        public MainController()

        {

            mainDA = new MainDA();

            mainVM = new MainVM();

            ViewBag.User = UserName();

        }

 

        // GET: Request

        public ActionResult Index()

        {

            return View();

        }

 

        public ActionResult MainList([DataSourceRequest]DataSourceRequest dsRequest)

        {

            try

            {

                var result = mainDA.GetAll();

                return Json(result.ToDataSourceResult(dsRequest));

            }

            catch (Exception ex)

            {

                ModelState.AddModelError("ex", ex.ToString());

                return Json("".ToDataSourceResult(dsRequest, ModelState));

            }

        }

My Index view:

@{
    ViewBag.Title = "Index";
}

<h2>Leave requests in process</h2>

<p>
    @Html.ActionLink("Add request", "Create", null, new { @class = "btn btn-primary" })
</p>

@(Html.Kendo().Grid<ClinicianLeave.Models.Main>()
.Name("MainGrid")
.Columns(columns =>
{
columns.Bound(p => p.Id).Width(70);
columns.Bound(p => p.FirstName);
columns.Bound(p => p.LastName);
columns.Bound(p => p.Unit).Width(250);
columns.Bound(p => p.LeaveStart);
columns.Bound(p => p.LeaveEnd).Format("{0:dd/MM/yyyy HH:mm}");

columns.Template(@<text></text>).ClientTemplate(
    "<a href='" + Url.Action("Edit", "Main") + "/#= Id #'" + " class='btn btn-info' >Edit</a> " +
    "<a href='" + Url.Action("Details", "Main") + "/#= Id #'" + " class='btn btn-warning' >Clinics</a> " +
    "<a href='" + Url.Action("Theatre", "Main") + "/#= Id #'" + " class='btn btn-success' >Theatre</a>"
    ).Width(240);
})
.Sortable()
.Filterable()
.DataSource(dataSource => dataSource
    .Ajax()
    .Read(read => read.Action("MainList", "Main"))
    )
)

[Marlo]

Thanks for the snippet. 7 years later, I'm moving to Dapper.  Much simplier way to read/write data.

[Anton Mironov]

Hi Marlo,

If further assistance or information is needed, do not hesitate to contact me and the Team.

 

Kind Regards,
Anton Mironov

[Viktor Tachev]

Hello Anthony,


Thank you for sharing your custom solution with the community. This can help someone trying to implement similar scenario.


Regards,
Viktor Tachev
Telerik by Progress

[Richard]

Your solution will not work well if you have a lot of data. By calling .ToList() in GetAll, your query will return all items in the database every time it runs. Ideally, the query would return just the items required for the current page of the display.