SQL Server CRUD Operations with C#

Posted on Sep 12, 2025 in Service Computing

Complete CRUD Operations in SQL Server

1. Create SQL Server Database and Table

First, create the database and the Students table.

CREATE DATABASE CrudDB;
GO

USE CrudDB;
GO

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

2. C# Application for CRUD Operations

This C# code demonstrates how to perform Create, Read, Update, and Delete operations on the Students table.

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

class Program
{
    static string connectionString = "Server=localhost;Database=CrudDB;Trusted_Connection=True;";

    static void Main()
    {
        while (true)
        {
            Console.WriteLine("\n--- CRUD Operations ---");
            Console.WriteLine("1. Insert");
            Console.WriteLine("2. Read");
            Console.WriteLine("3. Update");
            Console.WriteLine("4. Delete");
            Console.WriteLine("5. Exit");
            Console.Write("Choose option: ");
            string option = Console.ReadLine();

            switch (option)
            {
                case "1":
                    InsertStudent();
                    break;
                case "2":
                    ReadStudents();
                    break;
                case "3":
                    UpdateStudent();
                    break;
                case "4":
                    DeleteStudent();
                    break;
                case "5":
                    return;
                default:
                    Console.WriteLine("Invalid option.");
                    break;
            }
        }
    }

    static void InsertStudent()
    {
        Console.Write("Enter Name: ");
        string name = Console.ReadLine();
        Console.Write("Enter Age: ");
        int age = Convert.ToInt32(Console.ReadLine());
        Console.Write("Enter Email: ");
        string email = Console.ReadLine();

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO Students (Name, Age, Email) VALUES (@Name, @Age, @Email)";
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Age", age);
            cmd.Parameters.AddWithValue("@Email", email);
            conn.Open();
            cmd.ExecuteNonQuery();
            Console.WriteLine("Inserted Successfully.");
        }
    }

    static void ReadStudents()
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "SELECT * FROM Students";
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            Console.WriteLine("\n--- Students List ---");
            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}, Age: {reader["Age"]}, Email: {reader["Email"]}");
            }
        }
    }

    static void UpdateStudent()
    {
        Console.Write("Enter ID to Update: ");
        int id = Convert.ToInt32(Console.ReadLine());
        Console.Write("Enter New Name: ");
        string name = Console.ReadLine();
        Console.Write("Enter New Age: ");
        int age = Convert.ToInt32(Console.ReadLine());
        Console.Write("Enter New Email: ");
        string email = Console.ReadLine();

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "UPDATE Students SET Name=@Name, Age=@Age, Email=@Email WHERE Id=@Id";
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@Age", age);
            cmd.Parameters.AddWithValue("@Email", email);
            conn.Open();
            int rows = cmd.ExecuteNonQuery();
            if (rows > 0)
                Console.WriteLine("Updated Successfully.");
            else
                Console.WriteLine("Student not found.");
        }
    }

    static void DeleteStudent()
    {
        Console.Write("Enter ID to Delete: ");
        int id = Convert.ToInt32(Console.ReadLine());

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "DELETE FROM Students WHERE Id=@Id";
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@Id", id);
            conn.Open();
            int rows = cmd.ExecuteNonQuery();
            if (rows > 0)
                Console.WriteLine("Deleted Successfully.");
            else
                Console.WriteLine("Student not found.");
        }
    }
}