I have a CRUD with stored procedures and I need to work it with kendo grid. The controllers and the buttons of adding, editing and deleting are working perfectly, but after I add a row in my CRUD, these buttons (except the add) are not working. I mean, after I add, all the buttons start to just add, even if I push the button of edit or delete and I don't know what's wrong.
My controller:
private Entities db = new Entities();
ClientDB cliDB = new ClientDB();
// GET: Client
public ActionResult ClientIndex()
{
return View("ClientIndex");
}
public ActionResult ClientRead([DataSourceRequest]DataSourceRequest request)
{
return Json(cliDB.ListAll().ToDataSourceResult(request));
}
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult ClientCreate([DataSourceRequest]DataSourceRequest request, t_Client t_Client)
{
return Json(cliDB.Add(t_Client), JsonRequestBehavior.AllowGet);
}
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult ClientUpdate([DataSourceRequest]DataSourceRequest request, t_Client t_Client)
{
return Json(cliDB.Update(t_Client), JsonRequestBehavior.AllowGet);
}
public ActionResult ClientDelete([DataSourceRequest] DataSourceRequest request, string c_Id)
{
Guid g = Guid.Parse(c_Id);
return Json(cliDB.Delete(g), JsonRequestBehavior.AllowGet);
}
The class where i call my stored procedures:
public class ClientDB
{
private SqlConnection con;
private void connection()
{
string cs = ConfigurationManager.ConnectionStrings["Entities"].ConnectionString;
con = new SqlConnection(cs);
}
public List<t_Client> ListAll()
{
connection();
List<t_Client> lst = new List<t_Client>();
SqlCommand com = new SqlCommand("SP_SelectClient", con);
com.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(com);
DataTable dt = new DataTable();
con.Open();
da.Fill(dt);
con.Close();
lst = (from DataRow rdr in dt.Rows
select new t_Client()
{
c_Id = (Guid)rdr["c_Id"],
c_Name = rdr["c_Name"].ToString(),
c_NoClients = rdr["c_NoClients"].ToString(),
c_DocType = rdr["c_DocType"].ToString(),
c_DocNumber = rdr["c_DocNumber"].ToString(),
c_TaxpayerType = rdr["c_TaxpayerType"].ToString(),
c_DianClassification = rdr["c_DianClassification"].ToString(),
c_Address = rdr["c_Address"].ToString(),
c_AddrType = rdr["c_AddrType"].ToString(),
c_Telephone = rdr["c_Telephone"].ToString(),
c_BusinessName = rdr["c_BusinessName"].ToString()
}).ToList();
return lst;
}
public int Add(t_Client client)
{
connection();
SqlCommand com = new SqlCommand("SP_InsertUpdateClient", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@c_Id", client.c_Id);
com.Parameters.AddWithValue("@c_NoClients", client.c_NoClients);
com.Parameters.AddWithValue("@c_Name", client.c_Name);
com.Parameters.AddWithValue("@c_DocType", client.c_DocType);
com.Parameters.AddWithValue("@c_DocNumber", client.c_DocNumber);
com.Parameters.AddWithValue("@c_TaxpayerType", client.c_TaxpayerType);
com.Parameters.AddWithValue("@c_DianClassification", client.c_DianClassification);
com.Parameters.AddWithValue("@c_Address", client.c_Address);
com.Parameters.AddWithValue("@c_AddrType", client.c_AddrType);
com.Parameters.AddWithValue("@c_Telephone", client.c_Telephone);
com.Parameters.AddWithValue("@c_BusinessName", client.c_BusinessName);
com.Parameters.AddWithValue("@Action", "Insert");
int i;
con.Open();
i = com.ExecuteNonQuery();
con.Close();
return i;
}
public int Update(t_Client client)
{
connection();
int i;
SqlCommand com = new SqlCommand("SP_InsertUpdateClient", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@c_Id", client.c_Id);
com.Parameters.AddWithValue("@c_NoClients", client.c_NoClients);
com.Parameters.AddWithValue("@c_Name", client.c_Name);
com.Parameters.AddWithValue("@c_DocType", client.c_DocType);
com.Parameters.AddWithValue("@c_DocNumber", client.c_DocNumber);
com.Parameters.AddWithValue("@c_TaxpayerType", client.c_TaxpayerType);
com.Parameters.AddWithValue("@c_DianClassification", client.c_DianClassification);
com.Parameters.AddWithValue("@c_Address", client.c_Address);
com.Parameters.AddWithValue("@c_AddrType", client.c_AddrType);
com.Parameters.AddWithValue("@c_Telephone", client.c_Telephone);
com.Parameters.AddWithValue("@c_BusinessName", client.c_BusinessName);
com.Parameters.AddWithValue("@Action", "Update");
con.Open();
i = com.ExecuteNonQuery();
con.Close();
return i;
}
public int Delete(Guid c_Id)
{
connection();
int i;
SqlCommand com = new SqlCommand("SP_DeleteClient", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@c_Id", c_Id);
con.Open();
i = com.ExecuteNonQuery();
con.Close();
return i;
}
}
The cshtml
@(Html.Kendo().Grid<CRUD5.Models.t_Client>()
.Name("grid")
.HtmlAttributes(new { style = "height:500px; height:800px:" })
.Columns(columns =>
{
columns.Bound(c => c.c_NoClients).Title("Clients").Width("auto");
columns.Bound(c => c.c_Name).Title("Name");
columns.Bound(c => c.c_DocType).Title("Doc Type");
columns.Bound(c => c.c_DocNumber).Title("Doc Number");
columns.Bound(c => c.c_TaxpayerType).Title("Taxpayer Type");
columns.Bound(c => c.c_DianClassification).Title("Dian Classification");
columns.Bound(c => c.c_Address).Title("Address");
columns.Bound(c => c.c_AddrType).Title("AddrType");
columns.Bound(c => c.c_Telephone).Title("Telephone");
columns.Bound(c => c.c_BusinessName).Title("Business Name");
columns.Command(command => { command.Edit(); command.Destroy(); }).Width(200);
})
.ToolBar(toolbar =>
{
toolbar.Create();
})
.Editable(editable => editable.Mode(GridEditMode.PopUp))
.Scrollable()
.Sortable()
.Pageable(pageable => pageable
.Refresh(true)
.PageSizes(true)
.ButtonCount(5))
.DataSource(dataSource => dataSource
.Ajax()
.Model(model => model.Id(p => p.c_Id))
.Read(read => read.Action("ClientRead", "Client"))
.Create(create => create.Action("ClientCreate", "Client"))
.Update(update => update.Action("ClientUpdate", "Client"))
.Destroy(destroy => destroy.Action("ClientDelete", "Client"))
)
)
My stored procedures and my table:
CREATE TABLE t_Client(
c_Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
c_NoClients NVARCHAR(100) NULL,
c_Name NVARCHAR(100) NULL,
c_DocType NVARCHAR(100) NULL,
c_DocNumber NVARCHAR(100) NULL,
c_TaxpayerType NVARCHAR(100) NULL,
c_DianClassification NVARCHAR(100) NULL,
c_Address NVARCHAR(100) NULL,
c_AddrType NVARCHAR(100) NULL,
c_Telephone NVARCHAR(100) NULL,
c_BusinessName NVARCHAR(100) NULL,
c_Action NVARCHAR(10) NULL,
c_Estate NVARCHAR(6) NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
ValidTill DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTill)
)
WITH
(
SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = dbo.t_ClientHistory,
HISTORY_RETENTION_PERIOD = 10 YEARS
)
)
GO
-------------------------------------------------------------------------------------
--Insert and Update Client
Create Procedure SP_InsertUpdateClient
(
@c_Id uniqueidentifier,
@c_NoClients NVARCHAR(100),
@c_Name NVARCHAR(100),
@c_DocType NVARCHAR(100),
@c_DocNumber NVARCHAR(100),
@c_TaxpayerType NVARCHAR(100),
@c_DianClassification NVARCHAR(100),
@c_Address NVARCHAR(100),
@c_AddrType NVARCHAR(100),
@c_Telephone NVARCHAR(100),
@c_BusinessName NVARCHAR(100),
@Action varchar(10)
)
As
Begin
if @Action='Insert'
Begin
insert into t_Client(c_NoClients, c_Name, c_DocType, c_DocNumber, c_TaxpayerType, c_DianClassification , c_Address , c_AddrType , c_Telephone , c_BusinessName )
values (@c_NoClients, @c_Name, @c_DocType, @c_DocNumber, @c_TaxpayerType, @c_DianClassification , @c_Address , @c_AddrType , @c_Telephone , @c_BusinessName )
End
if @Action='Update'
Begin
update t_Client set
c_NoClients = @c_NoClients,
c_Name = @c_Name,
c_DocType = @c_DocType,
c_DocNumber = @c_DocNumber,
c_TaxpayerType = @c_TaxpayerType,
c_DianClassification = @c_DianClassification,
c_Address = @c_Address,
c_AddrType = @c_AddrType,
c_Telephone = @c_Telephone,
c_BusinessName = @c_BusinessName
where c_Id = @c_Id ;
End
End
-------------------------------------------------------------------------------------
--Delete Employee
Create Procedure SP_DeleteClient
(
@c_Id uniqueidentifier
)
as
Begin
if exists (select * from t_Client where c_Id = @c_Id )
update t_Client set c_Estate = 'false' where c_Id = @c_Id;
update t_Client set c_Action = 'Delete' where c_Id = @c_Id;
End
I've figured out what is the problem, it just needed a function that reload the Grid. The things that I've changed are in the .cshtml.
Inside the .DataSource I've added the .Events that calls the function "onRequestEnd"
.DataSource(dataSource => dataSource
.Ajax()
.Model(model => model.Id(p => p.c_Id))
.Read(read => read.Action("ClientRead", "Client"))
.Create(create => create.Action("ClientCreate", "Client"))
.Update(update => update.Action("ClientUpdate", "Client"))
.Destroy(destroy => destroy.Action("ClientDelete", "Client"))
.Events(events =>
{
events.RequestEnd("onRequestEnd");
})
)
and this is the function :
<script>
function onRequestEnd(event) {
console.log(event.type);
if (event.type == "update" || event.type == "create" || event.type == "destroy") {
$("#grid").data("kendoGrid").dataSource.read();
}
}
</script>
Additional to this, I've corrected some things that @CarstenFranke recommended me, so the final controllers are:
private Entities db = new Entities();
ClientDB cliDB = new ClientDB();
// GET: Client
public ActionResult ClientIndex()
{
return View("ClientIndex");
}
public ActionResult ClientRead([DataSourceRequest]DataSourceRequest request)
{
return Json(cliDB.ListAll().ToDataSourceResult(request));
}
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult ClientCreate([DataSourceRequest]DataSourceRequest request, t_Client t_Client)
{
return Json(new[] { cliDB.Add(t_Client) }.ToDataSourceResult(request, ModelState), JsonRequestBehavior.AllowGet);
}
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult ClientUpdate([DataSourceRequest]DataSourceRequest request, t_Client t_Client)
{
return Json(new[] { cliDB.Update(t_Client) }.ToDataSourceResult(request, ModelState), JsonRequestBehavior.AllowGet);
}
public ActionResult ClientDelete([DataSourceRequest] DataSourceRequest request, string c_Id)
{
Guid g = Guid.Parse(c_Id);
return Json(new[] { cliDB.Delete(g) }.ToDataSourceResult(request, ModelState), JsonRequestBehavior.AllowGet);
}