CRUD operations code in C#

CRUD Operations in C# Using ADO.NET

CRUD stands for:

• C – Create (Insert)

• R – Read (Select)

• U – Update

• D – Delete

We will use SQL Server and ADO.NET (SqlConnection, SqlCommand, SqlDataAdapter) in this example.

1. Setup SQL Table

CREATE TABLE Students
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50),
    Age INT
);

2. C# CRUD Code

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static string connectionString = "Server=YOUR_SERVER;Database=YOUR_DB;Integrated Security=True;";

    static void Main()
    {
        // Insert
        InsertStudent("John", 20);

        // Read
        GetStudents();

        // Update
        UpdateStudent(1, "John Doe", 21);

        // Delete
        DeleteStudent(1);
    }

    // CREATE
    static void InsertStudent(string name, int age)
    {
        using(SqlConnection con = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO Students (Name, Age) VALUES (@Name, @Age)";
            using(SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Age", age);

                con.Open();
                int rows = cmd.ExecuteNonQuery();
                Console.WriteLine($"{rows} record(s) inserted");
            }
        }
    }

    // READ
    static void GetStudents()
    {
        using(SqlConnection con = new SqlConnection(connectionString))
        {
            string query = "SELECT * FROM Students";
            using(SqlDataAdapter da = new SqlDataAdapter(query, con))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);

                foreach(DataRow row in dt.Rows)
                {
                    Console.WriteLine($"{row["Id"]} - {row["Name"]} - {row["Age"]}");
                }
            }
        }
    }

    // UPDATE
    static void UpdateStudent(int id, string name, int age)
    {
        using(SqlConnection con = new SqlConnection(connectionString))
        {
            string query = "UPDATE Students SET Name=@Name, Age=@Age WHERE Id=@Id";
            using(SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Parameters.AddWithValue("@Name", name);
                cmd.Parameters.AddWithValue("@Age", age);

                con.Open();
                int rows = cmd.ExecuteNonQuery();
                Console.WriteLine($"{rows} record(s) updated");
            }
        }
    }

    // DELETE
    static void DeleteStudent(int id)
    {
        using(SqlConnection con = new SqlConnection(connectionString))
        {
            string query = "DELETE FROM Students WHERE Id=@Id";
            using(SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@Id", id);

                con.Open();
                int rows = cmd.ExecuteNonQuery();
                Console.WriteLine($"{rows} record(s) deleted");
            }
        }
    }
}

Explanation

1. InsertStudent() – Adds a new record to the table.

2. GetStudents() – Reads all records using a DataTable.

3. UpdateStudent() – Updates an existing record by Id.

4. DeleteStudent() – Deletes a record by Id.

 Uses parameterized queries to prevent SQL injection.
 Works for Console, WinForms, or ASP.NET.

Learning CRUD operations in C# is foundational for any aspiring developer. Understanding how to Create, Read, Update, and Delete data opens doors to building dynamic applications. It's a bit like mastering the controls in a game; once you get the hang of it, you can navigate complex challenges with ease. Speaking of games, mastering C# CRUD feels almost as satisfying as conquering a difficult level in Slope Game. 

Hi,

Check the below link to undersatnd the crud operations in Angular, .Net Core 6, Entity Framework Code First Approach and Sql Server.

https://youtu.be/VRaS83eyjnE

Thank you !

Hi,
If you would like to perform CRUD operations using C#, Web API, and Entity Framework, then please refer to this article.

https://www.c-sharpcorner.com/article/build-crud-operation-with-net-core-3-1/

If the latest stack, then you can refer: 

https://www.c-sharpcorner.com/article/beginners-guide-to-crud-operations-in-net-core-8-web-api/

CRUD application using an ASP.NET Core Minimal API with ADO.NET and the repository pattern.

The solution is divided into the following files:

1. database_script.sql: A simple SQL script to set up the database table.

2. Product.cs: The model class representing the Product entity.

3. IProductRepository.cs: The interface that defines the contract for our repository.

4. ProductRepository.cs: The implementation of the repository using ADO.NET to interact with the database.

5. Program.cs: The main entry point for the Minimal API, where endpoints are defined and services are registered for dependency injection.

6. appsettings.json: The configuration file to store your database connection string.

To use this code, you will need to create a new ASP.NET Core Empty project and add these files to the corresponding folders (Models, Repositories, etc.).

Database Script

-- Use this script to create the database table.
-- Replace [YourDatabaseName] with the name of your database.

CREATE TABLE Products (
Id INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
Price DECIMAL(18, 2) NOT NULL
);

Product Model

namespace CrudApp.Models
{
    // The Product class represents a product entity in our application.
    // It's a simple POCO (Plain Old C# Object) for data transfer.
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public decimal Price { get; set; }
    }
}

Product Repository Interface

namespace CrudApp.Repositories
{
    // IProductRepository defines the contract for our data access operations.
    // Using an interface decouples the API from the specific data access implementation.
    public interface IProductRepository
    {
        Task> GetAllAsync();
        Task GetByIdAsync(int id);
        Task CreateAsync(Product product);
        Task UpdateAsync(Product product);
        Task DeleteAsync(int id);
    }
}

Product Repository Implementation

using System.Data;
using System.Data.SqlClient;
using CrudApp.Models;
using Microsoft.Extensions.Configuration;

namespace CrudApp.Repositories
{
    // ProductRepository implements the IProductRepository using ADO.NET.
    // It contains all the logic for database interactions.
    public class ProductRepository : IProductRepository
    {
        private readonly string _connectionString;

        // The repository receives the connection string via dependency injection.
        public ProductRepository(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DefaultConnection") 
                                ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
        }

        public async Task> GetAllAsync()
        {
            var products = new List();
            const string sql = "SELECT Id, Name, Price FROM Products";
            
            await using (var connection = new SqlConnection(_connectionString))
            {
                await using (var command = new SqlCommand(sql, connection))
                {
                    await connection.OpenAsync();
                    await using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            products.Add(new Product
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1),
                                Price = reader.GetDecimal(2)
                            });
                        }
                    }
                }
            }
            return products;
        }

        public async Task GetByIdAsync(int id)
        {
            const string sql = "SELECT Id, Name, Price FROM Products WHERE Id = @Id";
            
            await using (var connection = new SqlConnection(_connectionString))
            {
                await using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    await connection.OpenAsync();
                    await using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            return new Product
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1),
                                Price = reader.GetDecimal(2)
                            };
                        }
                    }
                }
            }
            return null;
        }

        public async Task CreateAsync(Product product)
        {
            const string sql = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price); SELECT SCOPE_IDENTITY();";
            
            await using (var connection = new SqlConnection(_connectionString))
            {
                await using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Name", product.Name);
                    command.Parameters.AddWithValue("@Price", product.Price);
                    await connection.OpenAsync();
                    // Get the new ID from the database
                    var newId = await command.ExecuteScalarAsync();
                    product.Id = Convert.ToInt32(newId);
                }
            }
            return product;
        }

        public async Task UpdateAsync(Product product)
        {
            const string sql = "UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id";
            
            await using (var connection = new SqlConnection(_connectionString))
            {
                await using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", product.Id);
                    command.Parameters.AddWithValue("@Name", product.Name);
                    command.Parameters.AddWithValue("@Price", product.Price);
                    await connection.OpenAsync();
                    var rowsAffected = await command.ExecuteNonQueryAsync();
                    return rowsAffected > 0;
                }
            }
        }

        public async Task DeleteAsync(int id)
        {
            const string sql = "DELETE FROM Products WHERE Id = @Id";
            
            await using (var connection = new SqlConnection(_connectionString))
            {
                await using (var command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    await connection.OpenAsync();
                    var rowsAffected = await command.ExecuteNonQueryAsync();
                    return rowsAffected > 0;
                }
            }
        }
    }
}

Minimal API and Dependency Injection

using CrudApp.Models;
using CrudApp.Repositories;
using Microsoft.AspNetCore.Mvc;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddScoped();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

// --- Minimal API Endpoints for CRUD Operations ---

// GET All Products
app.MapGet("/products", async ([FromServices] IProductRepository repository) =>
{
    var products = await repository.GetAllAsync();
    return Results.Ok(products);
}).WithOpenApi();

// GET Product by ID
app.MapGet("/products/{id}", async ([FromServices] IProductRepository repository, int id) =>
{
    var product = await repository.GetByIdAsync(id);
    return product is null ? Results.NotFound() : Results.Ok(product);
}).WithOpenApi();

// POST a new Product
app.MapPost("/products", async ([FromServices] IProductRepository repository, [FromBody] Product product) =>
{
    var createdProduct = await repository.CreateAsync(product);
    return Results.Created($"/products/{createdProduct.Id}", createdProduct);
}).WithOpenApi();

// PUT (Update) a Product
app.MapPut("/products/{id}", async ([FromServices] IProductRepository repository, int id, [FromBody] Product product) =>
{
    if (id != product.Id)
    {
        return Results.BadRequest("ID in URL must match ID in the request body.");
    }
    
    var updated = await repository.UpdateAsync(product);
    return updated ? Results.NoContent() : Results.NotFound();
}).WithOpenApi();

// DELETE a Product
app.MapDelete("/products/{id}", async ([FromServices] IProductRepository repository, int id) =>
{
    var deleted = await repository.DeleteAsync(id);
    return deleted ? Results.NoContent() : Results.NotFound();
}).WithOpenApi();


app.Run();

Connection String Configuration

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB;Database=YourDatabaseName;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

To run it, make sure you have SQL Server Express (LocalDB) installed, create a new database, run the database_script.sql to create the Products table, and then update the appsettings.json file with your specific database name. The minimal API will be accessible via Swagger UI when you run the application, allowing you to easily test all the CRUD endpoints.

This example uses an in-memory list to store the data, which is a great way to understand the core logic without the added complexity of a database connection. The code is structured with a simple Product data model and a ProductRepository class to encapsulate the CRUD logic.

// A detailed C# console application demonstrating basic CRUD operations.
// This example uses an in-memory List to store data,
// making it easy to understand the core logic of each operation.

using System;
using System.Collections.Generic;
using System.Linq;

// 1. Data Model: A simple class representing the data we'll be working with.
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }

    public override string ToString()
    {
        return $"[ID: {Id}] Name: {Name}, Price: {Price:C}";
    }
}

// 2. Repository: A class to manage the collection and implement the CRUD methods.
public class ProductRepository
{
    private readonly List _products = new List();
    private int _nextId = 1;

    // CREATE: Adds a new product to the collection.
    public void Create(Product product)
    {
        product.Id = _nextId++; // Assign a new, unique ID.
        _products.Add(product);
        Console.WriteLine($"Created: {product}");
    }

    // READ (All): Retrieves all products from the collection.
    public IEnumerable GetAll()
    {
        return _products;
    }

    // READ (By Id): Retrieves a single product by its unique ID.
    public Product GetById(int id)
    {
        // Use LINQ's FirstOrDefault to find the product.
        return _products.FirstOrDefault(p => p.Id == id);
    }

    // UPDATE: Modifies an existing product's details.
    public bool Update(Product updatedProduct)
    {
        var existingProduct = GetById(updatedProduct.Id);
        if (existingProduct == null)
        {
            Console.WriteLine($"Update failed: Product with ID {updatedProduct.Id} not found.");
            return false;
        }

        // Update the properties of the existing product.
        existingProduct.Name = updatedProduct.Name;
        existingProduct.Price = updatedProduct.Price;
        Console.WriteLine($"Updated: {existingProduct}");
        return true;
    }

    // DELETE: Removes a product from the collection by its ID.
    public bool Delete(int id)
    {
        var productToDelete = GetById(id);
        if (productToDelete == null)
        {
            Console.WriteLine($"Delete failed: Product with ID {id} not found.");
            return false;
        }

        _products.Remove(productToDelete);
        Console.WriteLine($"Deleted product with ID: {id}");
        return true;
    }
}

// 3. Main Application Logic: Demonstrates the use of the repository.
public class Program
{
    public static void Main(string[] args)
    {
        var repository = new ProductRepository();

        Console.WriteLine("--- CREATE Operations ---");
        repository.Create(new Product { Name = "Laptop", Price = 1200.50m });
        repository.Create(new Product { Name = "Mouse", Price = 25.00m });
        repository.Create(new Product { Name = "Keyboard", Price = 75.25m });

        Console.WriteLine("\n--- READ ALL Operations ---");
        var allProducts = repository.GetAll();
        foreach (var product in allProducts)
        {
            Console.WriteLine(product);
        }

        Console.WriteLine("\n--- READ by ID Operation ---");
        var product1 = repository.GetById(1);
        if (product1 != null)
        {
            Console.WriteLine($"Found product with ID 1: {product1}");
        }

        Console.WriteLine("\n--- UPDATE Operation ---");
        var productToUpdate = new Product { Id = 2, Name = "Wireless Mouse", Price = 35.50m };
        repository.Update(productToUpdate);

        Console.WriteLine("\n--- READ ALL (After Update) ---");
        allProducts = repository.GetAll();
        foreach (var product in allProducts)
        {
            Console.WriteLine(product);
        }

        Console.WriteLine("\n--- DELETE Operation ---");
        repository.Delete(3);

        Console.WriteLine("\n--- READ ALL (After Delete) ---");
        allProducts = repository.GetAll();
        foreach (var product in allProducts)
        {
            Console.WriteLine(product);
        }

        Console.WriteLine("\n--- Attempting to delete a non-existent item ---");
        repository.Delete(99);
    }
}