Hi yeemiedahn,
Using this article i have created the sample.
Refer below sample.
Model
public class CustomerModel
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string Country { get; set; }
}
Controller
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
List<CustomerModel> customers = this.GetCustomers();
//Add a Dummy Row.
customers.Insert(0, new CustomerModel());
return View(customers);
}
[HttpPost]
public JsonResult InsertCustomer(CustomerModel customer)
{
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
string query = "INSERT INTO Customers (Name, Country) VALUES (@Name, @Country)";
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Parameters.AddWithValue("@Name", customer.Name);
cmd.Parameters.AddWithValue("@Country", customer.Country);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
return Json(customer);
}
[HttpPost]
public ActionResult UpdateCustomer(CustomerModel customer)
{
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
string query = "UPDATE Customers SET Name = @Name, Country = @Country WHERE CustomerId = @CustomerId";
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Parameters.AddWithValue("@CustomerId", customer.CustomerId.ToString());
cmd.Parameters.AddWithValue("@Name", customer.Name);
cmd.Parameters.AddWithValue("@Country", customer.Country);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
return new EmptyResult();
}
[HttpPost]
public ActionResult DeleteCustomer(int customerId)
{
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
string query = "DELETE FROM Customers WHERE CustomerId = @CustomerId";
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Parameters.AddWithValue("@CustomerId", customerId);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
return new EmptyResult();
}
private List<CustomerModel> GetCustomers()
{
List<CustomerModel> customers = new List<CustomerModel>();
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
string query = "SELECT CustomerId, Name, Country FROM Customers";
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (MySqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(new CustomerModel
{
CustomerId = Convert.ToInt32(sdr["CustomerId"]),
Name = sdr["Name"].ToString(),
Country = sdr["Country"].ToString()
});
}
}
con.Close();
}
}
return customers;
}
}
View
@using MySql_MVC.Models
@model IEnumerable<CustomerModel>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<table id="tblCustomers" class="table" cellpadding="0" cellspacing="0">
<tr>
<th style="width:100px">Customer Id</th>
<th style="width:150px">Name</th>
<th style="width:150px">Country</th>
<th style="width:150px"></th>
</tr>
@foreach (CustomerModel customer in Model)
{
<tr>
<td class="CustomerId">
<span>@customer.CustomerId</span>
</td>
<td class="Name">
<span>@customer.Name</span>
<input type="text" value="@customer.Name" style="display:none" />
</td>
<td class="Country">
<span>@customer.Country</span>
<input type="text" value="@customer.Country" style="display:none" />
</td>
<td>
<a class="Edit" href="javascript:;">Edit</a>
<a class="Update" href="javascript:;" style="display:none">Update</a>
<a class="Cancel" href="javascript:;" style="display:none">Cancel</a>
<a class="Delete" href="javascript:;">Delete</a>
</td>
</tr>
}
</table>
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td style="width: 150px">
Name<br />
<input type="text" id="txtName" style="width:140px" />
</td>
<td style="width: 150px">
Country:<br />
<input type="text" id="txtCountry" style="width:140px" />
</td>
<td style="width: 200px">
<br />
<input type="button" id="btnAdd" value="Add" />
</td>
</tr>
</table>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
//Remove the dummy row if data present.
if ($("#tblCustomers tr").length > 2) {
$("#tblCustomers tr:eq(1)").remove();
} else {
var row = $("#tblCustomers tr:last-child");
row.find(".Edit").hide();
row.find(".Delete").hide();
row.find("span").html(' ');
}
});
function AppendRow(row, customerId, name, country) {
//Bind CustomerId.
$(".CustomerId", row).find("span").html(customerId);
//Bind Name.
$(".Name", row).find("span").html(name);
$(".Name", row).find("input").val(name);
//Bind Country.
$(".Country", row).find("span").html(country);
$(".Country", row).find("input").val(country);
row.find(".Edit").show();
row.find(".Delete").show();
$("#tblCustomers").append(row);
};
//Add event handler.
$("body").on("click", "#btnAdd", function () {
var txtName = $("#txtName");
var txtCountry = $("#txtCountry");
$.ajax({
type: "POST",
url: "/Home/InsertCustomer",
data: '{name: "' + txtName.val() + '", country: "' + txtCountry.val() + '" }',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {
var row = $("#tblCustomers tr:last-child");
if ($("#tblCustomers tr:last-child span").eq(0).html() != " ") {
row = row.clone();
}
AppendRow(row, r.CustomerId, r.Name, r.Country);
txtName.val("");
txtCountry.val("");
}
});
});
//Edit event handler.
$("body").on("click", "#tblCustomers .Edit", function () {
var row = $(this).closest("tr");
$("td", row).each(function () {
if ($(this).find("input").length > 0) {
$(this).find("input").show();
$(this).find("span").hide();
}
});
row.find(".Update").show();
row.find(".Cancel").show();
row.find(".Delete").hide();
$(this).hide();
});
//Update event handler.
$("body").on("click", "#tblCustomers .Update", function () {
var row = $(this).closest("tr");
$("td", row).each(function () {
if ($(this).find("input").length > 0) {
var span = $(this).find("span");
var input = $(this).find("input");
span.html(input.val());
span.show();
input.hide();
}
});
row.find(".Edit").show();
row.find(".Delete").show();
row.find(".Cancel").hide();
$(this).hide();
var customer = {};
customer.CustomerId = row.find(".CustomerId").find("span").html();
customer.Name = row.find(".Name").find("span").html();
customer.Country = row.find(".Country").find("span").html();
$.ajax({
type: "POST",
url: "/Home/UpdateCustomer",
data: '{customer:' + JSON.stringify(customer) + '}',
contentType: "application/json; charset=utf-8",
dataType: "json"
});
});
//Cancel event handler.
$("body").on("click", "#tblCustomers .Cancel", function () {
var row = $(this).closest("tr");
$("td", row).each(function () {
if ($(this).find("input").length > 0) {
var span = $(this).find("span");
var input = $(this).find("input");
input.val(span.html());
span.show();
input.hide();
}
});
row.find(".Edit").show();
row.find(".Delete").show();
row.find(".Update").hide();
$(this).hide();
});
//Delete event handler.
$("body").on("click", "#tblCustomers .Delete", function () {
if (confirm("Do you want to delete this row?")) {
var row = $(this).closest("tr");
var customerId = row.find("span").html();
$.ajax({
type: "POST",
url: "/Home/DeleteCustomer",
data: '{customerId: ' + customerId + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
if ($("#tblCustomers tr").length > 2) {
row.remove();
} else {
row.find(".Edit").hide();
row.find(".Delete").hide();
row.find("span").html(' ');
}
}
});
}
});
</script>
</body>
</html>
Screenshot
