Creating a CRUD (Create, Read, Update, Delete) application using ASP.NET Core, C#, SQL, and ADO.NET.

Creating a CRUD (Create, Read, Update, Delete) application using ASP.NET Core, C#, SQL, and ADO.NET. We'll start from scratch and build the application step by step.

Here's a step-by-step guide:

Step 1: Set up the Project Create a new ASP.NET Core web application project in Visual Studio. Choose the "Empty" template and make sure to select the option for "ASP.NET Core 3.1" or higher.

Step 2: Install Required Packages In Visual Studio, open the NuGet Package Manager Console and install the following packages:

• Microsoft.Data.SqlClient: This package provides the necessary components for working with SQL Server using ADO.NET.
• Dapper: This package simplifies database operations by providing a lightweight object mapper.

Step 3: Create Database Create a new SQL Server database or use an existing one. For demonstration purposes, let's assume we have a database named "SampleDB" with a table called "Employees". The "Employees" table should have columns such as "Id", "Name", "Email", and "Salary".

Step 4: Model and Connection String Create a model class to represent an employee. In your project, create a new folder called "Models" and add a new class called "Employee.cs". Define properties corresponding to the columns in the "Employees" table.

csharp
public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public decimal Salary { get; set; }
}

In the project's appsettings.json file, add a connection string to connect to the database.

json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=<server_name>;Database=SampleDB;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  // ...
}

Replace <server_name> with your SQL Server instance name or connection string.

Step 5: Create Repository Create a new folder called "Data" and add a class called "EmployeeRepository.cs". This class will handle database operations using ADO.NET and Dapper.

csharp
using Dapper;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

public class EmployeeRepository
{
    private readonly IConfiguration _configuration;
    private readonly string _connectionString;

    public EmployeeRepository(IConfiguration configuration)
    {
        _configuration = configuration;
        _connectionString = _configuration.GetConnectionString("DefaultConnection");
    }

    public IEnumerable<Employee> GetAllEmployees()
    {
        using (IDbConnection connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            return connection.Query<Employee>("SELECT * FROM Employees");
        }
    }

    public Employee GetEmployeeById(int id)
    {
        using (IDbConnection connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            return connection.Query<Employee>("SELECT * FROM Employees WHERE Id = @Id", new { Id = id }).FirstOrDefault();
        }
    }

    public void AddEmployee(Employee employee)
    {
        using (IDbConnection connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            connection.Execute("INSERT INTO Employees (Name, Email, Salary) VALUES (@Name, @Email, @Salary)", employee);
        }
    }

    public void UpdateEmployee(Employee employee)
    {
        using (IDbConnection connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            connection.Execute("UPDATE Employees SET Name = @Name, Email = @Email, Salary = @Salary WHERE Id = @Id", employee);
        }
    }

    public void DeleteEmployee(int id)
    {
        using (IDbConnection connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            connection.Execute("DELETE FROM Employees WHERE Id = @Id", new { Id = id });
        }
    }
}

Step 6: Create Controller and Views Create a new folder called "Controllers" and add a new controller called "EmployeeController.cs". This controller will handle the HTTP requests and interact with the repository to perform CRUD operations.

csharp
using Microsoft.AspNetCore.Mvc;

public class EmployeeController : Controller
{
    private readonly EmployeeRepository _repository;

    public EmployeeController(EmployeeRepository repository)
    {
        _repository = repository;
    }

    public IActionResult Index()
    {
        var employees = _repository.GetAllEmployees();
        return View(employees);
    }

    public IActionResult Create()
    {
        return View();
    }

    [HttpPost]
    public IActionResult Create(Employee employee)
    {
        _repository.AddEmployee(employee);
        return RedirectToAction("Index");
    }

    public IActionResult Edit(int id)
    {
        var employee = _repository.GetEmployeeById(id);
        return View(employee);
    }

    [HttpPost]
    public IActionResult Edit(Employee employee)
    {
        _repository.UpdateEmployee(employee);
        return RedirectToAction("Index");
    }

    public IActionResult Delete(int id)
    {
        _repository.DeleteEmployee(id);
        return RedirectToAction("Index");
    }
}

Step 7: Create Views Create the corresponding views for the actions in the "Views" folder. In the "Employee" folder, create the following views:

• Index.cshtml: Display the list of employees.

html
@model IEnumerable<Employee>

<table>
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Email</th>
            <th>Salary</th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var employee in Model)
        {
            <tr>
                <td>@employee.Id</td>
                <td>@employee.Name</td>
                <td>@employee.Email</td>
                <td>@employee.Salary</td>
                <td>
                    <a asp-action="Edit" asp-route-id="@employee.Id">Edit</a> |
                    <a asp-action="Delete" asp-route-id="@employee.Id">Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

<a asp-action="Create">Create New Employee</a>

• Create.cshtml: Create a new employee.

html
@model Employee

<form asp-action="Create" method="post">
    <div>
        <label>Name:</label>
        <input asp-for="Name" />
    </div>
    <div>
        <label>Email:</label>
        <input asp-for="Email" />
    </div>
    <div>
        <label>Salary:</label>
        <input asp-for="Salary" />
    </div>
    <div>
        <input type="submit" value="Create" />
    </div>
</form>

<a asp-action="Index">Back to List</a>

• Edit.cshtml: Edit an existing employee.

html
@model Employee

<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="Id" />
    <div>
        <label>Name:</label>
        <input asp-for="Name" />
    </div>
    <div>
        <label>Email:</label>
        <input asp-for="Email" />
    </div>
    <div>
        <label>Salary:</label>
        <input asp-for="Salary" />
    </div>
    <div>
        <input type="submit" value="Save" />
    </div>
</form>

<a asp-action="Index">Back to List</a>

Step 8: Configure Routing In the "Startup.cs" file, configure routing by adding the following code to the Configure method:

csharp
app.UseRouting();

app.UseEndpoints(endpoints =>
{
    endpoints.MapControllerRoute(
        name: "default",
        pattern: "{controller=Employee}/{action=Index}/{id?}");
});

Step 9: Run the Application Build and run the application. You should now have a working CRUD application using ASP.NET Core, C#, SQL, and ADO.NET.

Note: This example uses Dapper for simplicity, but you can use other ORMs (Object-Relational Mappers) like Entity Framework Core for more advanced scenarios.

That's it! You have successfully created a CRUD application using ASP.NET Core, C#, SQL, and ADO.NET.