ADO.NET: Database Programming, Connected vs Disconnected, LINQ, CRUD

ADO.NET Overview: The Bridge Between C# and Databases

ADO.NET is Microsoft’s data access technology for connecting C# applications to databases (SQL Server, MySQL, Oracle, etc.). It provides a disconnected architecture (unlike older connected models) to improve performance by caching data in memory (DataSet) before syncing with the database.

Why ADO.NET?

• Database Agnostic: Works with SQL Server, MySQL, Oracle, etc.
• Disconnected Model: Reduces network traffic by caching data locally.
• LINQ Integration: Query databases using C# syntax (instead of raw SQL).
• Transaction Support: Ensures atomicity in multi-step operations.

---

ADO.NET Architecture: Connected vs Disconnected

ADO.NET supports two architectures for database access:

1. Connected Architecture (Direct Database Access)

• Uses SqlConnection, SqlCommand, and SqlDataReader to directly query the database.
• Pros: Simple for small operations, low memory usage.
• Cons: Keeps the connection open (inefficient for large datasets).

Example: Fetching a single employee record.

using System.Data.SqlClient;

string connectionString = "Server=localhost;Database=Company;User=sa;Password=123";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand("SELECT * FROM Employees WHERE Id=1", conn);
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(reader["Name"]);
    }
}

---

2. Disconnected Architecture (Using DataSet)

• Uses SqlDataAdapter to fill a DataSet (in-memory cache) and then sync changes back to the database.
• Pros: Efficient for large datasets, works offline.
• Cons: Slightly complex due to DataAdapter and DataSet management.

Example: Fetching and updating employee data.

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Employees", conn);
DataSet ds = new DataSet();
adapter.Fill(ds, "Employees");

// Modify data in DataSet
ds.Tables["Employees"].Rows[0]["Salary"] = 50000;

// Update database
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(ds, "Employees");

---

Key ADO.NET Classes

---

CRUD Operations with ADO.NET

1. Insert (ExecuteNonQuery)

SqlCommand cmd = new SqlCommand("INSERT INTO Employees (Name, Salary) VALUES (@Name, @Salary)", conn);
cmd.Parameters.AddWithValue("@Name", "John Doe");
cmd.Parameters.AddWithValue("@Salary", 45000);
int rowsAffected = cmd.ExecuteNonQuery(); // Returns number of affected rows

2. Select (ExecuteReader)

SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"{reader["Id"]}: {reader["Name"]}");
}
reader.Close();

3. Update/Delete (ExecuteNonQuery)

SqlCommand cmd = new SqlCommand("UPDATE Employees SET Salary=50000 WHERE Id=1", conn);
int affected = cmd.ExecuteNonQuery(); // Returns 1 if successful

---

Transactions in ADO.NET

Ensures all operations succeed or fail together (atomicity).

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    SqlTransaction transaction = conn.BeginTransaction();
    try
    {
        SqlCommand cmd1 = new SqlCommand("UPDATE Account SET Balance=Balance-1000 WHERE Id=1", conn, transaction);
        SqlCommand cmd2 = new SqlCommand("UPDATE Account SET Balance=Balance+1000 WHERE Id=2", conn, transaction);
        cmd1.ExecuteNonQuery();
        cmd2.ExecuteNonQuery();
        transaction.Commit(); // All changes saved
    }
    catch
    {
        transaction.Rollback(); // Undo all changes
    }
}

Real-World Example:

• Khalti/Pathao: When you transfer money, both sender and receiver accounts are updated atomically (either both succeed or both fail).

---

LINQ to SQL: Query Databases with C#

LINQ (Language Integrated Query) allows querying databases without writing SQL.

using System.Linq;
using System.Data.Linq;

```figure
{"type":"graph","fns":[{"expr":"x^2","label":"SQL Query (traditional)"},{"expr":"sin(x) + 2","label":"LINQ Query (C#)"}],"x":[-5,5],"caption":"Conceptual difference: SQL (imperative) vs LINQ (declarative)"}

DataContext db = new DataContext(connectionString); Table<Employee> employees = db.GetTable<Employee>();

// Query: Employees in Kathmandu with salary > 20000 var result = from emp in employees where emp.Salary > 20000 && emp.Address == "Kathmandu" select emp;

foreach (var emp in result) { Console.WriteLine(emp.Name); }

**Equivalent SQL**:
```sql
SELECT * FROM Employees WHERE Salary > 20000 AND Address = 'Kathmandu'

---

Binding Data to WinForms

Display database results in a DataGridView:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Employees", conn);
DataSet ds = new DataSet();
adapter.Fill(ds, "Employees");
dataGridView1.DataSource = ds.Tables["Employees"];

---

In the Real World

1. eSewa (Nepal):

   • Uses ADO.NET transactions to ensure bill payments are deducted from your bank atomically (either the bill is paid and your account is debited, or neither happens).

2. Daraz (Nepal):

   • Uses disconnected DataSet to cache product listings in memory before displaying them, reducing server load during peak hours.

3. Nepal Rastra Bank (NRB):

   • Uses LINQ queries to analyze loan applications (e.g., "Select all loans with interest > 10% and tenure > 5 years").

---

Exam Tip

1. Architecture Difference:

   • Connected: Uses SqlDataReader (fast, low memory).
   • Disconnected: Uses DataSet (slower but works offline).

2. LINQ vs SQL:

   • LINQ is type-safe (compiler checks syntax), while SQL is string-based (prone to SQL injection).

3. Transactions:

   • Always use try-catch with BeginTransaction(), Commit(), and Rollback().

4. Common Mistakes:

   • Forgetting to close connections (using block auto-closes).
   • Not disposing SqlDataReader (memory leaks).
   • Using string concatenation in SQL (risk of SQL injection; use Parameters instead).

---

Worked Example: Employee Registration System

Task: Create a WinForm with fields for Id, Name, Salary, and buttons to Insert and Select data.

Step 1: Database Setup

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name NVARCHAR(50),
    Salary INT
);

Step 2: C# Code (Insert + Select)

private void btnInsert_Click(object sender, EventArgs e)
{
    string query = "INSERT INTO Employees VALUES (@Id, @Name, @Salary)";
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand(query, conn);
        cmd.Parameters.AddWithValue("@Id", txtId.Text);
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
        conn.Open();
        cmd.ExecuteNonQuery();
        MessageBox.Show("Inserted!");
    }
}

private void btnSelect_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Employees", conn);
        adapter.Fill(dt);
    }
    dataGridView1.DataSource = dt;
}

Step 3: Trace (Variable States)

---

Comparison: ExecuteReader vs ExecuteNonQuery

---

Final Checklist for Exams

✅ Architecture: Know when to use connected (SqlDataReader) vs disconnected (DataSet). ✅ LINQ: Write queries like from emp in db.Employees where emp.Salary > X select emp. ✅ Transactions: Always use try-catch with Commit()/Rollback(). ✅ Security: Use parameters (@Name) instead of string concatenation. ✅ Binding: Use DataAdapter.Fill(DataSet) to populate WinForms controls.