CRUD Operations Using ADO.NET in C#
- Get link
- X
- Other Apps
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
usingstatement - 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”
- Get link
- X
- Other Apps
Comments
Post a Comment