Hi rani,

Refer below example.

For reading connection string refer below article.

.Net Core: Read Connection String inside Startup.cs from AppSettings.json file

Database

I have made use of table Customers with the schema as follows. CustomerId is an Auto-Increment (Identity) column.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.         

Download SQL file

Namespaces

using System.Data;
using System.Data.SqlClient;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;

Model

using System.ComponentModel.DataAnnotations;

namespace ASP.Net_Core_MVC_CRUD.Models
{
    public class Customer
    {
        public int Id { set; get; }
        [Required(ErrorMessage = "Name Required!")]
        public string Name { set; get; }
        [Required(ErrorMessage ="Country Required!")]
        public string Country { set; get; }
    }
}

Controller

public class HomeController : Controller
{
    private IConfiguration Configuration;
    public HomeController(IConfiguration _configuration)
    {
        Configuration = _configuration;
    }
    private string GetConnectionString()
    {
        return this.Configuration.GetConnectionString("MyConn");
    }
    public List<Customer> GetCustomers(int? id)
    {
        List<Customer> customers = new List<Customer>();
        using (SqlConnection con = new SqlConnection(GetConnectionString()))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Clear();
                if (id != null)
                {
                    cmd.CommandText = "SELECT * FROM Customers WHERE CustomerId = @Id";
                    cmd.Parameters.AddWithValue("@Id", id);
                }
                else
                {
                    cmd.CommandText = "SELECT * FROM Customers";
                }
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    customers.Add(new Customer()
                    {
                        Id = Convert.ToInt32(rdr["CustomerId"]),
                        Name = rdr["Name"].ToString(),
                        Country = rdr["Country"].ToString()
                    });
                }
                con.Close();
            }
        }

        return customers;
    }
    public void AddUpdateDeleteCustomer(Customer customer, string action)
    {
        using (SqlConnection con = new SqlConnection(GetConnectionString()))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                cmd.Parameters.Clear();
                if (action == "Insert")
                {
                    cmd.CommandText = "INSERT INTO Customers VALUES (@Name,@Country)";
                    cmd.Parameters.AddWithValue("@Name", customer.Name);
                    cmd.Parameters.AddWithValue("@Country", customer.Country);
                }
                else if (action == "Update")
                {
                    cmd.CommandText = "UPDATE Customers SET NAME = @Name,Country = @Country WHERE CustomerId = @Id";
                    cmd.Parameters.AddWithValue("@Id", customer.Id);
                    cmd.Parameters.AddWithValue("@Name", customer.Name);
                    cmd.Parameters.AddWithValue("@Country", customer.Country);
                }
                else if (action == "Delete")
                {
                    cmd.CommandText = "DELETE FROM Customers WHERE CustomerId = @Id";
                    cmd.Parameters.AddWithValue("@Id", customer.Id);
                }
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
    public IActionResult Index()
    {
        IEnumerable<Customer> customers = GetCustomers(null);
        return View(customers);
    }
    public IActionResult Create()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Create(Customer customer)
    {
        if (ModelState.IsValid)
        {
            AddUpdateDeleteCustomer(customer, "Insert");
            return RedirectToAction("Index");
        }
        else
        {
            return View(customer);
        }
    }
    public IActionResult Edit(int id)
    {
        return View(GetCustomers(id).FirstOrDefault());
    }
    [HttpPost]
    public IActionResult Edit(Customer customer)
    {
        if (ModelState.IsValid)
        {
            AddUpdateDeleteCustomer(customer, "Update");
            return RedirectToAction("Index");
        }
        else
        {
            return View(customer);
        }
    }
    public IActionResult Delete(int id)
    {
        AddUpdateDeleteCustomer(new Customer { Id = id }, "Delete");
        return RedirectToAction("Index");
    }
}

View

Index

@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@model IEnumerable<ASP.Net_Core_MVC_CRUD.Models.Customer>

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link href="~/lib/bootstrap/dist/css/bootstrap.css" rel="stylesheet" />
</head>
<body>
    <div class="container">
        <div class="row">
            <div class="col-lg-12">
                <h4>Customers</h4>
                <table class="table table-responsive mx-auto w-auto">
                    <thead>
                        <tr>
                            <th>
                                @Html.DisplayNameFor(model => model.Name)
                            </th>
                            <th>
                                @Html.DisplayNameFor(model => model.Country)
                            </th>
                            <th></th>
                            <th></th>
                        </tr>
                    </thead>
                    <tbody>
                        @foreach (var item in Model)
                        {
                            <tr>
                                <td>
                                    @Html.DisplayFor(modelItem => item.Name)
                                </td>
                                <td>
                                    @Html.DisplayFor(modelItem => item.Country)
                                </td>
                                <td>
                                    @Html.ActionLink("Edit", "Edit", new { id = item.Id }, new { @class = "btn btn-outline-primary btn-sm" })
                                </td>
                                <td>
                                    @Html.ActionLink("Delete", "Delete", new { id = item.Id }, new { onclick = "return confirm('Are you sure you wish to delete this Customer?');", @class = "btn btn-outline-danger btn-sm" })
                                </td>
                            </tr>
                        }
                    </tbody>
                </table>
                <p>
                    <a class="btn btn-outline-success btn-sm" asp-action="Create">Add Customer</a>
                </p>
            </div>
        </div>
    </div>
</body>
</html>

Create

@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@model ASP.Net_Core_MVC_CRUD.Models.Customer

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Create</title>
    <link href="~/lib/bootstrap/dist/css/bootstrap.css" rel="stylesheet" />
</head>
<body>
    <div class="container">
        <h4>Add Customer</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="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">
                        <input type="submit" value="Add" class="btn btn-outline-success btn-sm" />
                    </div>
                </form>
            </div>
        </div>
        <p>
            <a class="btn btn-outline-secondary btn-sm" asp-action="Index">Back to List</a>
        </p>
    </div>
</body>
</html>

Edit

@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@model ASP.Net_Core_MVC_CRUD.Models.Customer

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Edit</title>
    <link href="~/lib/bootstrap/dist/css/bootstrap.css" rel="stylesheet" />
</head>
<body>
    <div class="container">
        <h4>Update Customer</h4>
        <hr />
        <div class="row">
            <div class="col-md-4">
                <form asp-action="Edit">
                    <div asp-validation-summary="ModelOnly" class="text-danger"></div>
                    <div class="form-group">
                        <label asp-for="Id" class="control-label"></label>
                        <input asp-for="Id" class="form-control" readonly="readonly" />
                    </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="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">
                        <input type="submit" value="Update" class="btn btn-outline-success btn-sm" />
                    </div>
                </form>
            </div>
        </div>
        <p>
            <a asp-action="Index" class="btn btn-outline-secondary btn-sm">Back to List</a>
        </p>
    </div>
</body>
</html>

Screenshot