Kendo Grid using Stored Procedures

Question

Hi, I have been trying to use the Kendo Grid with Asp .net MVC framework. I intend to bind with a stored procedure to fetch data and do not prefer using the Entity framework. I am a newbie to .Net development and have tried the resources available with not much luck. Any help on how to bind the kendo grid with a stored procedure (without EF) is much appreciated. Thanks in advance.

Sachin Singh · 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 () .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 Singh · Answer

You have just one mistakes, correct it as follow ,change NonQuery to ExecuteReader() var jsondata=sqlDataToJson(cmd.ExecuteReader()); var emps= JsonConvert.SerializeObject >(jsondata);

Prashanth Ravi · Answer

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 Ravi · Answer

Thanks a lot ! Shall try it and revert back.