ASP.NET Core: CRUD Operation with ADO.NET 

Find this project on Github

Here we are going to discuss about how to perform the Create, Read, Update and Delete also called the CRUD operations in Asp.Net Core with ADO.NET. We will be creating a sample Customer record in order to perform the CRUD operation.

Prerequisites

• Install .NET Core 2.0.0 or above SDK
• Install Visual Studio 2017 Community Edition (Version 15.3.5 or above)
• Install SQL Server

Creating Table and Stored Procedures

We now will be using a Database table for storing all the records of Customers. Also, open the SQL Server and use the following script to create tblCustomer table.

Create Table tblCustomer(    
CustomerId int IDENTITY(1,1) NOT NULL,    
Name Varchar(20) NOT NULL,    
City Varchar(20) NOT NULL,    
Country Varchar(20) NOT NULL,    
Gender Varchar(6) NOT NULL   
)

After the above mentioned steps we will be creating a stored procedure to add, delete, update, and get Customer data.

Stored Procedure to insert a Customer Record

Create procedure usp_AddCustomer     
(    
    @Name VARCHAR(20),     
    @City VARCHAR(20),    
    @Country VARCHAR(20),    
    @Gender VARCHAR(6)    
)    
as     
Begin     
    Insert into tblCustomer (Name,City,Country, Gender)     
    Values (@Name,@City,@Country, @Gender)     
End

Stored Procedure to view all Customer Records

Create procedure usp_GetAllCustomers    
as    
Begin    
    select 
		CustomerId,
		Name,
		City,
		Country,    
		Gender
    from tblCustomer    
End

Stored Procedure to get a single Customer Records

Create procedure usp_GetCustomerByID
(    
    @CustomerId int
)    
as     
Begin     
    SELECT * FROM tblCustomer WHERE CustomerID= @CustomerId
End

Stored Procedure to update a Customer Record

Create procedure usp_UpdateCustomer      
(      
   @CustomerId INTEGER ,    
   @Name VARCHAR(20),     
   @City VARCHAR(20),    
   @Country VARCHAR(20),    
   @Gender VARCHAR(6)    
)      
as      
begin      
   Update tblCustomer       
   set Name=@Name,      
   City=@City,      
   Country=@Country,    
   Gender=@Gender      
   where CustomerId=@CustomerId      
End

Stored Procedure to delete a Customer Record

Create procedure usp_DeleteCustomer     
(      
   @CustomerId int      
)      
as       
begin      
   Delete from tblCustomer where CustomerId=@CustomerId      
End

Now this completes our Database part. Let's then proceed by creating our .Net core MVC application using visual studio. We hence assume that we know how to create a .net core MVC Application. If not Follow this link.

As seen in the picture, we will then be adding our files to folders (Controllers, Models, Views) only.

Now,

• Right-click the Controllers folder
• select Add >> New Item.
• An “Add New Item” dialog box will open.
• Select Web from the left panel,
• Select “MVC Controller Class” from templates panel
• Put the name as CustomerController.cs.
• Press OK.

 

CustomerController has been created. our business logic will put in this controller.

Adding the Model to the Application

Again,

• Right-click Models folder and select Add >> Class.
• Name your class Customer.cs.
• This class will contain our Customer model properties.
• Add one more class file to Models folder.
•  Name it as CustomerDataAccessLayer.cs.
• This class will contain our Database related operations.
• Now, the Models folder has the following structure.

 Add following code in Customer.cs. for the required field validation, we need to add [Required] at the top.

using System.ComponentModel.DataAnnotations;
namespace CustomerCRUD.Models
{
    public class Customer
    {
        public int ID { get; set; }
        [Required]
        public string Name { get; set; }
        [Required]
        public string Gender { get; set; }
        [Required]
        public string City { get; set; }
        [Required]
        public string Country { get; set; }   
    }
}

put following code to handle database operations on CustomerDataAccessLayer.cs.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace CustomerCRUD.Models
{
    public class CustomerDataAccessLayer
    {
        string connectionString = "Put Your Connection string here";
        //string connectionString = "Data Source=192.168.1.68,5810;Initial Catalog=Customers;Integrated Security=True";
        //To View all Customers details    
        public IEnumerable<Customer> GetAllCustomers()
        {
            List<Customer> lstCustomer = new List<Customer>();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("usp_GetAllCustomers", con);
                cmd.CommandType = CommandType.StoredProcedure;

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Customer Customer = new Customer();

                    Customer.ID = Convert.ToInt32(rdr["CustomerID"]);
                    Customer.Name = rdr["Name"].ToString();
                    Customer.Gender = rdr["Gender"].ToString();
                    Customer.Country = rdr["Country"].ToString();
                    Customer.City = rdr["City"].ToString();

                    lstCustomer.Add(Customer);
                }
                con.Close();
            }
            return lstCustomer;
        }

        //To Add new Customer record    
        public void AddCustomer(Customer Customer)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("usp_AddCustomer", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@Name", Customer.Name);
                cmd.Parameters.AddWithValue("@Gender", Customer.Gender);
                cmd.Parameters.AddWithValue("@Country", Customer.Country);
                cmd.Parameters.AddWithValue("@City", Customer.City);

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        //To Update the records of a particluar Customer  
        public void UpdateCustomer(Customer Customer)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("usp_UpdateCustomer", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@CustomerId", Customer.ID);
                cmd.Parameters.AddWithValue("@Name", Customer.Name);
                cmd.Parameters.AddWithValue("@Gender", Customer.Gender);
                cmd.Parameters.AddWithValue("@Country", Customer.Country);
                cmd.Parameters.AddWithValue("@City", Customer.City);

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        //Get the details of a particular Customer  
        public Customer GetCustomerData(int? id)
        {
            Customer Customer = new Customer();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                
                SqlCommand cmd = new SqlCommand("usp_GetCustomerByID", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@CustomerId", id);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Customer.ID = Convert.ToInt32(rdr["CustomerID"]);
                    Customer.Name = rdr["Name"].ToString();
                    Customer.Gender = rdr["Gender"].ToString();
                    Customer.Country = rdr["Country"].ToString();
                    Customer.City = rdr["City"].ToString();
                }
            }
            return Customer;
        }

        //To Delete the record on a particular Customer  
        public void DeleteCustomer(int? id)
        {

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("usp_DeleteCustomer", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@CustomerId", id);

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}   

Adding Views to the Application

We therefore need to create a folder inside views for our controller class with the same name as controller. Then add our views to that folder.

• Right-click the Views folder,
• Add >> New Folder and name the folder as Customer.
• Now right-click the Views/Customer folder,
• Select Add >> New Item.
• “Add New Item” dialog box will open.
• Select Web from the left panel,
• Select “Razor View” from templates panel
• put the name as Index.cshtml.
• Press OK.

Similarly, we add the following,

• Create.cshtml
• Delete.cshtml
• Details.cshtml
• Edit.cshtml.

Now, our Views folder will look like this

Let us put our codes in views and controller to perform the CRUD operation.

Create View: i.e. Create.cshtml

• This view is for Adding new Customer data to the database. 

@model CustomerCRUD.Models.Customer
@{
    ViewData["Title"] = "Create";
}
<h2>Create</h2>
<h4>Customers</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form asp-action="Create">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="Name" class="control-label"></label>
                <input asp-for="Name" class="form-control" />
                <span asp-validation-for="Name" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Gender" class="control-label"></label>
                <select asp-for="Gender" class="form-control">
                    <option value="">-- Select Gender --</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                </select>
                <span asp-validation-for="Gender" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Country" class="control-label"></label>
                <input asp-for="Country" class="form-control" />
                <span asp-validation-for="Country" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="City" class="control-label"></label>
                <input asp-for="City" class="form-control" />
                <span asp-validation-for="City" class="text-danger"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </form>
    </div>
</div>
<div>
    <a asp-action="Index">Back to List</a>
</div>
@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

 Let us add some business logic to our controller and create an object of CustomerDataAccessLayer class inside the CustomerController class in order to handle the database operations.

public class CustomerController: Controller {
 CustomerDataAccessLayer objCustomerDAL = new CustomerDataAccessLayer(); // GET: /<controller>/
 public IActionResult Index() {
  return View();
 }
}

Add following two methods to your controller.

[HttpGet]
public IActionResult Create() {
 return View();
}

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create([Bind] Customer objCustomer) {
 if (ModelState.IsValid) {
  objCustomerDAL.AddCustomer(objCustomer);
  return RedirectToAction("Index");
 }
 return View(objCustomer);
}

Index View: i.e. Index.cshtml

This view is for displaying all the Customer records present in the database. Also, all the operation; Edit, Details and Delete on each record can be done form this view.

Add the following codes on Index.cshtml.

@model IEnumerable<CustomerCRUD.Models.Customer>
@{
    ViewData["Title"] = "Index";
}
<h2>Index</h2>
<p>
    <a asp-action="Create">Create New</a>
</p>
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Gender)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Country)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.City)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.Name)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Gender)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Country)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.City)
                </td>
                <td>
                    <a asp-action="Edit" asp-route-id="@item.ID">Edit</a> |
                    <a asp-action="Details" asp-route-id="@item.ID">Details</a> |
                    <a asp-action="Delete" asp-route-id="@item.ID">Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

In order to handle the business logic of Index view, we have to add some code on Index method of CustomerController.cs.

public IActionResult Index() {
 List < Customer > customers = new List < Customer > ();
 customers = objCustomerDAL.GetAllCustomers().ToList();
 return View(customers);
}

Edit View: Edit.cshtml

This view will enable us to edit an existing Customer data.

@model CustomerCRUD.Models.Customer

@{
    ViewData["Title"] = "Edit";
}
<h2>Edit</h2>
<h4>Customer</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form asp-action="Edit">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <input type="hidden" asp-for="ID" />
            <div class="form-group">
                <label asp-for="Name" class="control-label"></label>
                <input asp-for="Name" class="form-control" />
                <span asp-validation-for="Name" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Gender" class="control-label"></label>
                <select asp-for="Gender" class="form-control">
                    <option value="">-- Select Gender --</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                </select>
                <span asp-validation-for="Gender" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Country" class="control-label"></label>
                <input asp-for="Country" class="form-control" />
                <span asp-validation-for="Country" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="City" class="control-label"></label>
                <input asp-for="City" class="form-control" />
                <span asp-validation-for="City" class="text-danger"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </form>
    </div>
</div>
<div>
    <a asp-action="Index">Back to List</a>
</div>
@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

To handle the logic of Edit view, add following method on CustomerController.cs.

public IActionResult Edit(int ? id) {
 if (id == null) {
  return NotFound();
 }
 Customer customer = objCustomerDAL.GetCustomerData(id);

 if (customer == null) {
  return NotFound();
 }
 return View(customer);
}

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Edit(int id, [Bind] Customer objcustomer) {
 if (id != objcustomer.ID) {
  return NotFound();
 }
 if (ModelState.IsValid) {
  objCustomerDAL.UpdateCustomer(objcustomer);
  return RedirectToAction("Index");
 }
 return View(objCustomerDAL);
}

As we can see that we have two Edit action methods,the first one is for HttpGet and the second one is for HttpPost. The HttpGet Edit action method will fetch the Custoemer data and fills the fields of edit view. After the user clicks Save by editing the record, a Post request will be generated which is handled by HttpPost Edit action method.

Details View: Details.cshtml

This view will display the details of a particular Customer. Add following codes on Details.cshtml.

@model CustomerCRUD.Models.Customer

@{
    ViewData["Title"] = "Details";
}
<h2>Details</h2>
<div>
    <h4>Customer</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Name)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Name)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Gender)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Gender)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Country)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Country)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.City)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.City)
        </dd>
    </dl>
</div>
<div>
    <a asp-action="Edit" asp-route-id="@Model.ID">Edit</a> |
    <a asp-action="Index">Back to List</a>
</div>

To handle the business logic of Details view, open CustoemrController.cs and add following code to it.

[HttpGet]
public IActionResult Details(int ? id) {
 if (id == null) {
  return NotFound();
 }
 Customer objcustomer = objCustomerDAL.GetCustomerData(id);

 if (objcustomer == null) {
  return NotFound();
 }
 return View(objcustomer);
}

Delete View

This view will help us remove Custoemr data. Open Delete.cshtml and put following code into it.

@model CustomerCRUD.Models.Customer

@{
    ViewData["Title"] = "Delete";
}
<h2>Delete</h2>
<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>Customer</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Name)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Name)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Gender)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Gender)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Country)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Country)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.City)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.City)
        </dd>
    </dl>

    <form asp-action="Delete">
        <input type="hidden" asp-for="ID" />
        <input type="submit" value="Delete" class="btn btn-default" /> |
        <a asp-action="Index">Back to List</a>
    </form>
</div>

To handle the business logic of Delete view, open CustomerController.cs and add following code to it.

 [HttpGet]
 public IActionResult Delete(int ? id) {
  if (id == null) {
   return NotFound();
  }
  Customer objcustomer = objCustomerDAL.GetCustomerData(id);

  if (objcustomer == null) {
   return NotFound();
  }
  return View(objcustomer);
 }

 [HttpPost, ActionName("Delete")]
 [ValidateAntiForgeryToken]
 public IActionResult DeleteConfirmed(int ? id) {
  objCustomerDAL.DeleteCustomer(id);
  return RedirectToAction("Index");
 }

To perform the Delete operation we need two Delete methods accepting same parameter (Customer Id). But both the methods with same name and method signature can create a compile time error and if we rename the Delete method then routing will not be able to find it as asp.net maps URL segments to action methods by its name. So, to resolve this issue we put ActionName("Delete") attribute to the DeleteConfirmed method. That attribute performs mapping for the routing system so that a URL that includes /Delete/ for a POST request will find the DeleteConfirmed method.

When we click Delete link on the Index page, it will send a Get request and return a View of the Customer using HttpGet Delete method. When we click Delete on this view, it will send a Post request to delete the record which is handled by the HttpPost DeleteConfirmed method. Performing a delete operation in response to a Get request (or for that matter, performing an edit operation, create operation, or any other operation that changes data) opens up a security hole. Hence, we have two separate methods.

And that’s the wrap up to it. We have created our first ASP.NET Core MVC web application. Before launching the application, we will configure route URLs. We should Open the Startup.cs file to set the format for routing.WE then scroll down to the app. We then use Mvc method, where we can set the route URL.

Make sure that your route URL is set i the following manner

app.UseMvc(routes => {
 routes.MapRoute(
  name: "default",
  template: "{controller=Customer}/{action=Index}/{id?}");
});

This URL pattern sets CustomerController as default controller and Index method as default action method, whereas Id parameter is optional. Default and optional route parameters need not be present in the URL path for a match. If we do not append any controller name in the URL then it will take CustomerController as default controller and Index method of CustomerController as default action method. Similarly, if we append only Controller name in the URL, it will navigate to Index action method of that controller.

Note: Before running your Web application, you must put your Database connection string on CustomerDataAccessLayer class.

For example

 string connectionString = "Put Your Connection string here";
//string connectionString = "Data Source=192.168.1.68,5810;Initial Catalog=Customers;Integrated Security=True";

Output:

Let us check our output by running a project. Here we can see our output

Click Create New to navigate to Create view. Add a new Customer record as shown in the image below.

We can check by missing any field while crating Customer Record, we can see required field validation error message.

After inserting the data in all the fields, click "Create". The new Customer record will be created and you will be redirected to the Index view, displaying records of all the Customers. Here, we can also see action methods Edit, Details, and Delete.

If we want to edit an existing Customer record, then click Edit action link. It will open Edit View as below where we can change the Customer data.

Here we have changed the Country and city of Customer Anisha from USA & New York to South Africa & Johannesburg. Click "Save" to return to the Index view to see the updated changes as highlighted in the image below.

If we miss any fields while editing Customer records, then Edit view will also throw required field validation error message.

If you want to see the details of any Customer, then click Details action link, which will open the Details view, as shown in the image below.

Click "Back to List" to go back to Index view. Now, we will perform Delete operation on a Customer named Venkat. Click Delete action link which will open Delete view asking for a confirmation to delete.

Once we click Delete, it will send HttpPost request to delete Customer record and we will be redirected to the Index view. Here, we can see that the Customer with name Anisha has been removed from our record.

Conclusion

We have learned about creating a sample ASP.Net Core 2.0 web application using ADO.NET and SQL server with the help of Visual Studio 2017. We have used stored procedures to handle CRUD operations at the database level. If you need full project then send me mail on ppjee13@hotmail.com.

 

Also Read:

• Cookie in ASP.NET Core
• How to use ADO.NET in .NET Core 2.x for Performance Critical Applications
• How to Set Connection String in Production Application for best Performance
• AspNet MVC Core 2.0 Model validations with custom attribute
• How to Upload File in ASP.NET Core with ViewModel?
• How to read appSettings JSON from Class Library in ASP.NET Core?