CRUD Operations in ADO.
NET
CRUD stands for Create, Read, Update, and Delete—the four main database operations.
ADO.NET uses SqlConnection, SqlCommand, and SqlDataAdapter to perform these
operations.
1. Setup: Connection String
Before performing CRUD, define your connection string:
string constr = "server=.;database=YourDatabase;user
id=sa;password=yourpassword";
2. CREATE (Insert Data)
Use SqlCommand with an INSERT statement.
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string constr = "server=.;database=YourDatabase;user
id=sa;password=yourpassword";
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
string query = "INSERT INTO Students (Name, Age) VALUES (@Name,
@Age)";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@Name", "John Doe");
cmd.Parameters.AddWithValue("@Age", 22);
int rows = cmd.ExecuteNonQuery();
Console.WriteLine(rows > 0 ? "Record inserted successfully!" :
"Insert failed.");
}
}
}
}
3. READ (Retrieve Data)
Use SqlCommand and SqlDataReader to fetch data.
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string constr = "server=.;database=YourDatabase;user
id=sa;password=yourpassword";
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
string query = "SELECT * FROM Students";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"ID: {reader["StudentID"]}, Name:
{reader["Name"]}, Age: {reader["Age"]}");
}
}
}
}
}
}
4. UPDATE (Modify Data)
Use UPDATE to modify existing records.
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string constr = "server=.;database=YourDatabase;user
id=sa;password=yourpassword";
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
string query = "UPDATE Students SET Age = @Age WHERE Name =
@Name";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@Age", 25);
cmd.Parameters.AddWithValue("@Name", "John Doe");
int rows = cmd.ExecuteNonQuery();
Console.WriteLine(rows > 0 ? "Record updated successfully!" :
"Update failed.");
}
}
}
}
5. DELETE (Remove Data)
Use DELETE to remove records.
csharp
CopyEdit
using System;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string constr = "server=.;database=YourDatabase;user
id=sa;password=yourpassword";
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
string query = "DELETE FROM Students WHERE Name = @Name";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@Name", "John Doe");
int rows = cmd.ExecuteNonQuery();
Console.WriteLine(rows > 0 ? "Record deleted successfully!" :
"Delete failed.");
}
}
}
}
Summary of CRUD in ADO.NET
Operation SQL Command Method Used
Create INSERT INTO ... ExecuteNonQuery()
Read SELECT * FROM ... ExecuteReader()
Update UPDATE ... SET ... WHERE ... ExecuteNonQuery()
Delete DELETE FROM ... WHERE ... ExecuteNonQuery()
