CRUD Operations Using ADO.NET in C#

This is one of the most asked interview programs for .NET developers.

CRUD means:

• C → Create (Insert)
• R → Read (Select)
• U → Update
• D → Delete

---

Step 1: Create SQL Table

CREATE TABLE Employee
(
    Id INT PRIMARY KEY IDENTITY,
    Name VARCHAR(100),
    Salary DECIMAL(10,2),
    Department VARCHAR(50)
)

---

Step 2: Create C# Console Application

Namespace Required

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

---

Step 3: Connection String

string connectionString =
"Server=YOUR_SERVER_NAME;Database=YOUR_DATABASE_NAME;Trusted_Connection=True;";

Example:

string connectionString =
"Server=DESKTOP-123;Database=CompanyDB;Trusted_Connection=True;";

---

Step 4: INSERT Operation (Create)

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString =
        "Server=DESKTOP-123;Database=CompanyDB;Trusted_Connection=True;";

        using (SqlConnection con = new SqlConnection(connectionString))
        {
            string query =
            "INSERT INTO Employee(Name, Salary, Department) VALUES(@Name,@Salary,@Department)";

            SqlCommand cmd = new SqlCommand(query, con);

            cmd.Parameters.AddWithValue("@Name", "John");
            cmd.Parameters.AddWithValue("@Salary", 50000);
            cmd.Parameters.AddWithValue("@Department", "IT");

            con.Open();

            int rows = cmd.ExecuteNonQuery();

            Console.WriteLine(rows + " Record Inserted");

            con.Close();
        }
    }
}

---

Step 5: SELECT Operation (Read)

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString =
        "Server=DESKTOP-123;Database=CompanyDB;Trusted_Connection=True;";

        using (SqlConnection con = new SqlConnection(connectionString))
        {
            string query = "SELECT * FROM Employee";

            SqlCommand cmd = new SqlCommand(query, con);

            con.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine(
                    reader["Id"] + " " +
                    reader["Name"] + " " +
                    reader["Salary"] + " " +
                    reader["Department"]);
            }

            con.Close();
        }
    }
}

---

Step 6: UPDATE Operation

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString =
        "Server=DESKTOP-123;Database=CompanyDB;Trusted_Connection=True;";

        using (SqlConnection con = new SqlConnection(connectionString))
        {
            string query =
            "UPDATE Employee SET Salary=@Salary WHERE Id=@Id";

            SqlCommand cmd = new SqlCommand(query, con);

            cmd.Parameters.AddWithValue("@Salary", 70000);
            cmd.Parameters.AddWithValue("@Id", 1);

            con.Open();

            int rows = cmd.ExecuteNonQuery();

            Console.WriteLine(rows + " Record Updated");

            con.Close();
        }
    }
}

---

Step 7: DELETE Operation

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString =
        "Server=DESKTOP-123;Database=CompanyDB;Trusted_Connection=True;";

        using (SqlConnection con = new SqlConnection(connectionString))
        {
            string query =
            "DELETE FROM Employee WHERE Id=@Id";

            SqlCommand cmd = new SqlCommand(query, con);

            cmd.Parameters.AddWithValue("@Id", 1);

            con.Open();

            int rows = cmd.ExecuteNonQuery();

            Console.WriteLine(rows + " Record Deleted");

            con.Close();
        }
    }
}

---

Important ADO.NET Classes

Class	Purpose
SqlConnection	Open DB Connection
SqlCommand	Execute SQL Queries
SqlDataReader	Read Data
SqlDataAdapter	Fill DataSet/DataTable
DataSet	In-memory Data
SqlTransaction	Handle Transactions

---

Common Interview Questions

1. What is ADO.NET?

ADO.NET is a data access technology in .NET used to connect applications with databases.

---

2. Difference Between ExecuteNonQuery, ExecuteReader, ExecuteScalar

Method	Use
ExecuteNonQuery	Insert/Update/Delete
ExecuteReader	Read Multiple Rows
ExecuteScalar	Read Single Value

---

3. What is SQL Injection?

Injecting malicious SQL into queries.

Wrong

"SELECT * FROM User WHERE Name='" + name + "'"

Correct

cmd.Parameters.AddWithValue("@Name", name);

---

Best Practices

• Use using statement
• Use Parameterized Queries
• Close Connections Properly
• Handle Exceptions
• Use Stored Procedures in Production

---

Stored Procedure Example

SQL Procedure

CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employee
END

C# Code

SqlCommand cmd =
new SqlCommand("GetEmployees", con);

cmd.CommandType = CommandType.StoredProcedure;

---

Advanced CRUD Topics for Experienced Candidates

• Async Database Operations
• Repository Pattern
• Unit of Work
• Transaction Handling
• Bulk Insert
• Entity Framework vs ADO.NET
• Dapper vs ADO.NET
• Connection Pooling
• Generic Repository
• Dependency Injection

---

Most Asked Real-Time Interview Scenario

“Create Employee CRUD using:

• SQL Server
• ADO.NET
• Stored Procedures
• Layered Architecture
• Exception Handling”

This was part of Interview Preparation With Bipin — Let’s Crack It!