Kendo Grid using Stored Procedures

you need some additional step because kendo Grid understands list or json not SqlDataReader.

step 1. Create a database and table (copy paste below script using SQL server Management studio) and a stored procedure as shown below

Create database Test
go
use Test
go
CREATE TABLE [dbo].[Department] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[Employee] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NULL,
[Salary] INT NULL,
[DepartmentId] INT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
);
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (1, N'HR')
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (2, N'Development')
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (3, N'Database')
INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (4, N'Business')
SET IDENTITY_INSERT [dbo].[Department] OFF
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (1, N'sachin', 100000, 1)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (2, N'arjun', 23000, 1)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (3, N'vikash', 30000, 2)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (4, N'pocha', 43000, 3)
INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (5, N'Nivedita', 24000, 4)
SET IDENTITY_INSERT [dbo].[Employee] OFF
create procedure spGetEmployees
as
begin
select * from Employee ;
end

step 2.step 2.Create a class in model folder

public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public int Salary { get; set; }
public int DepartmentId {get;set;}
}

step 3.create action method and Serialize method as shown below

Note :- install newtonsoft.json from nuget to use JsonConvert

step 2 create an Action method as shown below
public ActionResult GetEmployees(DataSourceRequest req)
{
List emps=new List();
using (SqlConnection con = new SqlConnection(cs))
{
//Create the SqlCommand object
SqlCommand cmd = new SqlCommand("spGetEmployees",con);
//Specify that the SqlCommand is a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
//open the connection
con.Open();
//Execute the command
emps=sqlDataToJson(cmd.ExecuteReader());
}
return Json(emps.ToDataSourceResult(req),JsonRequestBehavior.AllowGet);
}
private List DataTableToList(SqlDataReader dataReader)
{
var dataTable = new DataTable();
dataTable.Load(dataReader);
var convertedList = (from rw in dataTable.AsEnumerable()
select new MyObj()
{
Id = Convert.ToInt32(rw["Id"]),
Name = Convert.ToString(rw["Name"])
Salary = Convert.ToInt32(rw["Salary"]),
}).ToList();
return convertedList ;
}

step 4.create kendo Grid in view as shown below

@(Html.Kendo().Grid <@Model.Employee>()
.Name("grid")
.Columns(columns =>
{
columns.Bound(c => c.Id).Title("Id").Visible(false);
columns.Bound(c => c.Name).Title("Name").Width(150);
columns.Bound(c => c.Salary).Title("Salary").Width(150).Filterable(false);
}).HtmlAttributes(new { style = "height: 550px;" })
.Scrollable()
.Groupable()
.Sortable()
.DataSource(dataSource => dataSource
.Ajax()
.Model(model => model.Id(p => p.Id))
.Read(read => read.Action("GetEmployees", "Home"))
.PageSize(20)
)
)

just follow all the steps and you are done.

hope it helped.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
you need some additional step because kendo Grid understands list or json not SqlDataReader.
step 1. Create a database and table (copy paste below script using SQL server Management studio) and a stored procedure as shown below
Create database Test
   go
   use Test
   go
  CREATE TABLE [dbo].[Department] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
  );

  CREATE TABLE [dbo].[Employee] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50) NULL,
    [Salary]       INT          NULL,
    [DepartmentId] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
  );
  SET IDENTITY_INSERT [dbo].[Department] ON
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (1, N'HR')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (2, N'Development')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (3, N'Database')
  INSERT INTO [dbo].[Department] ([Id], [Name]) VALUES (4, N'Business')
  SET IDENTITY_INSERT [dbo].[Department] OFF
  SET IDENTITY_INSERT [dbo].[Employee] ON
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (1, N'sachin', 100000, 1)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (2, N'arjun', 23000, 1)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (3, N'vikash', 30000, 2)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (4, N'pocha', 43000, 3)
  INSERT INTO [dbo].[Employee] ([Id], [Name], [Salary], [DepartmentId]) VALUES (5, N'Nivedita', 24000, 4)
  SET IDENTITY_INSERT [dbo].[Employee] OFF

create procedure spGetEmployees
   as
   begin
   select * from Employee ;

  end
step 2.step 2.Create a class in model folder

public class Employee
     {
        public int Id { get; set; }
        public string Name { get; set; }

        public int Salary { get; set; }
       public int DepartmentId {get;set;}
     }
step 3.create action method and Serialize method as shown below
Note :- install newtonsoft.json from nuget to use JsonConvert
step 2 create an Action method as shown below
      public ActionResult GetEmployees(DataSourceRequest req)
        {
             List emps=new List();
           using (SqlConnection con = new SqlConnection(cs))
            {
               //Create the SqlCommand object
                SqlCommand cmd = new SqlCommand("spGetEmployees",con);
                 //Specify that the SqlCommand is a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;
//open the connection
             con.Open();
//Execute the command

           emps=sqlDataToJson(cmd.ExecuteReader());

            }
           return Json(emps.ToDataSourceResult(req),JsonRequestBehavior.AllowGet);
        }
private List DataTableToList(SqlDataReader dataReader)
{
    var dataTable = new DataTable();
    dataTable.Load(dataReader);


   var convertedList = (from rw in dataTable.AsEnumerable()
        select new MyObj()
        {
            Id = Convert.ToInt32(rw["Id"]),
            Name = Convert.ToString(rw["Name"])

            Salary = Convert.ToInt32(rw["Salary"]),
        }).ToList();

return convertedList ;
}
step 4.create kendo Grid in view as shown below
@(Html.Kendo().Grid <@Model.Employee>()
            .Name("grid")
            .Columns(columns =>
            {
                columns.Bound(c => c.Id).Title("Id").Visible(false);
                columns.Bound(c => c.Name).Title("Name").Width(150);

                columns.Bound(c => c.Salary).Title("Salary").Width(150).Filterable(false);

            }).HtmlAttributes(new { style = "height: 550px;" })
           .Scrollable()
        .Groupable()
        .Sortable()
        .DataSource(dataSource => dataSource
        .Ajax()
       .Model(model => model.Id(p => p.Id))
       .Read(read => read.Action("GetEmployees", "Home"))
            .PageSize(20)
        )
   )
just follow all the steps and you are done.
hope it helped.
Sachin Singh5y ago
You have just one mistakes, correct it as follow ,change NonQuery to ExecuteReader()
var jsondata=sqlDataToJson(cmd.ExecuteReader());
var emps= JsonConvert.SerializeObject>(jsondata);
Prashanth Ravi5y ago
Hi Sachin,

Can you share all the references that you include? I have included the Newtonsoft.json Nuget. I get an error with the SerializableObject.Json as shown below. Where am I going wrong? Thanks in advance.
Prashanth Ravi5y ago
Thanks a lot ! Shall try it and revert back.