MVC (Model View Controller) is a web application design pattern that is widely used in application development. Here, we are creating an MVC application that connects to the SQL Server with the help of the ADO.NET framework.
we can start a crud operation,
Step-1 Create a Database and use the following table Customer with the schema as follow.
step-2 Manually Insert records into the Database
step-3 Open a visual studio and choose an Asp.Net Web Application(.Net Framework)
step-4 Choose the Empty and select the MVC application
step-5 Create a new model
step-6 Create a new Controller and add business logic
public class CustomerController : Controller
{
public ActionResult Index()
{
List<Customers> CustomerModel = new List<Customers>();
string constr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
string query = "select * from Customers";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while(sdr.Read())
{
CustomerModel.Add(new Customers
{
CustomerId = Convert.ToInt32(sdr["CustomerId"]),
Name = Convert.ToString(sdr["Name"]),
Country = Convert.ToString(sdr["Country"])
});
}
}
con.Close();
}
}
if(CustomerModel.Count == 0)
{
CustomerModel.Add(new Customers());
}
return View(CustomerModel);
}
[HttpPost]
public JsonResult InsertCustomer(Customers Customer)
{
//string query = "Insert into Customers values(@Name,@Country)";
//query += "SELECT SCOPE_IDENTITY()";
string constr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Insert into Customers values(@Name,@Country)",con))
{
{
cmd.Parameters.AddWithValue("@Name", Customer.Name);
cmd.Parameters.AddWithValue("@Country", Customer.Country);
con.Open();
//cmd.ExecuteNonQuery();
Customer.CustomerId = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
}
}
return Json(Customer);
}
[HttpPost]
public JsonResult UpdateCustomer(string CustomerId,string Name,string Country)
{
string constr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
using(SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("UPDATE Customers SET Name=@Name, Country=@Country WHERE CustomerId=@CustomerId",con))
{
cmd.Parameters.AddWithValue("@Name", Name);
cmd.Parameters.AddWithValue("@Country", Country);
cmd.Parameters.AddWithValue("@CustomerId", Convert.ToInt32(CustomerId));
con.Open();
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();
}
}
return Json("Record Updated");
}
[HttpPost]
public ActionResult DeleteCustomer(int CustomerId)
{
string constr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM Customers WHERE CustomerId=@CustomerId",con))
{
cmd.Parameters.AddWithValue("@CustomerId", CustomerId);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
return Json("Record Deleted");
}
}
step-7 Create a view
@model IEnumerable<CrudOperationUsingADODOTNET.Models.Customers>
@{
ViewBag.Title = "Index";
}
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<h1 class="text-center">Customer History</h1><br /><br />
<html>
<head>
<title>Crud Operation using ADO.NET</title>
<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">Action</th>
</tr>
@foreach (CrudOperationUsingADODOTNET.Models.Customers 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 btn btn-success" 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 btn btn-danger" href="javascript:;">Delete</a>
</td>
</tr>
}
</table>
<br />
<br />
<h1 class="text-center">Add New Customer</h1>
<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>
</body>
</head>
</html>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script>
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);
};
$("body").on("click", "#btnAdd", function () {
var name = $("#txtName");
var country = $("#txtCountry");
$.ajax({
type: "POST",
url: "/Customer/InsertCustomer",
data: '{name: "' + name.val() + '",country: "' + country.val() + '"}',
contentType: "application/json",
datatype: "json",
success: function (r) {
var row = $("#tblCustomer tr:last-child");
if ($("#tblCustomer tr:last-child span").eq(0).html() != " ") {
row = row.clone();
}
AppendRow(row, r.customerId, r.Name, r.Country);
name.val("");
country.val("");
}
});
});
$("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 = {};
var customerId = row.find(".CustomerId").find("span").html();
var name = row.find(".Name").find("span").html();
var country = row.find(".Country").find("span").html();
$.ajax({
type: "POST",
url: "/Customer/UpdateCustomer?CustomerId=" + customerId+"&Name="+name+"&Country="+country,
contentType: "application / json",
dataType: "json",
success: function (response) {
alert("Record Update");
if ($("#tblCustomers tr").length > 2) {
} else {
row.find(".Edit").hide();
row.find(".Delete").hide();
row.find("span").html(' ');
}
}
});
});
//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();
});
//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: "/Customer/DeleteCustomer?CustomerId=" + CustomerId,
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>
step -9 see the output as follows
