In this article I will explain with an example, how to perform select, insert, edit, update, delete in MySQL database in ASP.MVC.
This process is also known as CRUD i.e. Create, Read, Update and Delete with MySQL database backend in ASP.Net MVC.
 
 

Using MySQL with ASP.Net

For more details on using and connecting MySQL database in ASP.Net MVC, please refer my article Using MySql Database with MySql Connector in ASP.Net MVC Razor Tutorial with example.
 
 

Database

I have made use of the following table Customers with the schema as follows.
ASP.Net MVC MySQL CRUD: Select Insert Edit Update and Delete in MySQL database
 
I have already inserted few records in the table.
ASP.Net MVC MySQL CRUD: Select Insert Edit Update and Delete in MySQL database
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 

Model

The Model class consists of following properties.
public class CustomerModel
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}
 
 

Controller

The Controller consists of following Action methods.

Action method for handling GET operation

Inside this Action method, the GetCustomers method is called.
Inside the GetCustomers method, the Generic List collection of CustomerModel class is created.
Then, the records are fetched from the Customers table of the database using DataReader (MySqlDataReader) and set to the Generic List collection of CustomerModel class.
Finally, the Generic List collection of CustomerModel class is returned to the view.
 

Action method for Inserting

Inside this Action method,the Customer object is received as parameter.
Next, the name and the country values are fetched from their respective TextBoxes.
Then, the received Customer object is inserted into the Customers table.
Finally, the updated Customer object with generated CustomerId is returned back to the View.
 

Action method for Updating

Inside this Action method, the Customer object is received as parameter.
Then, the CustomerId value of the received Customer object is used to reference the Customer record in the database.
Once the record is referenced, the values of Name and Country are updated and the changes are updated into the Customers table using ExecuteNonQuery method.
Finally, an EmptyResult object (NULL) is returned.
 

Action method for Deleting

Inside this Action method, the CustomerId value is received as parameter.
Next, the CustomerId value is used to reference the Customer record in the database table.
Once the record is referenced, the Customer record is deleted from the Customers table.
Finally, the CustomerId is returned back to the View.
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); SELECT LAST_INSERT_ID();";
            using (MySqlCommand cmd = new MySqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@Name", customer.Name);
                cmd.Parameters.AddWithValue("@Country", customer.Country);
                con.Open();
                customer.CustomerId = Convert.ToInt32(cmd.ExecuteScalar());
                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, con))
            {
                cmd.Parameters.AddWithValue("@CustomerId", customer.CustomerId);
                cmd.Parameters.AddWithValue("@Name", customer.Name);
                cmd.Parameters.AddWithValue("@Country", customer.Country);
                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, con))
            {
                cmd.Parameters.AddWithValue("@CustomerId", customerId);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
        return Json(customerId);
    }
 
    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, 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

Inside the View, in the very first line the CustomerModel class is declared as Model for the View.

Display

For displaying the records, an HTML Table is used. A FOREACH loop will be executed over the Model which will generate the HTML Table rows with the Customer records.
 

Insert

When the Add button is clicked, the name and the country values are fetched from their respective TextBoxes and then passed to the InsertCustomer Action method using jQuery AJAX call.
Once the response is received, a new row is appended to the HTML table using the AppendRow function.
 
Edit
When the Edit Button is clicked, the reference of the HTML Table row is determined and the HTML SPAN elements are made hidden while the TextBoxes are made visible in the Name and Country columns of the HTML Table.
 

Update

When the Update Button is clicked, the reference of the HTML Table row is determined and the updated values are fetched from the respective TextBoxes of Name and Country columns while the CustomerId is determined from the HTML SPAN element of the CustomerId column.
The values of CustomerId, Name and Country are passed to the UpdateCustomer Action method using jQuery AJAX call.
Once the response is received, the HTML SPAN elements are made visible and the TextBoxes are made hidden for the Name and Country columns of the HTML Table row.
 

Cancel

When the Cancel Button is clicked, the reference of the HTML Table row is determined and the HTML SPAN elements are made visible while the TextBoxes are made hidden in the Name and Country columns of the HTML Table row.
 

Delete

When the Delete Button is clicked, the reference of the HTML Table row is determined and the value of the CustomerId is fetched and passed to the DeleteCustomer Action method using jQuery AJAX call.
Once the response is received the respective row is removed from the HTML Table row.
@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://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/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('&nbsp;');
            }
        });
 
        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() != "&nbsp;") {
                        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('&nbsp;');
                        }
                    }
                });
            }
        });
    </script>
</body>
</html>
 
 

Screenshot

ASP.Net MVC MySQL CRUD: Select Insert Edit Update and Delete in MySQL database
 
 

Downloads

Download Code