Aim

To perform the full CRUD cycle — insert, update/modify and delete — on an ADO.NET DataTable, and to understand the row-state change-tracking model behind AcceptChanges.

Theory

Every DataRow carries a RowState that drives ADO.NET's change tracking: Added (new since the last accept), Modified, Deleted, Unchanged and Detached. Critically, Delete() does NOT remove the row from the collection — it marks the row Deleted while retaining its original values, so that a later DataAdapter.Update can generate the correct DELETE statement against the real database. Rows also keep multiple DataRowVersions (Original, Current, Proposed): editing row["Name"] updates the Current version while Original still holds the pre-edit value — this pairing is exactly what optimistic-concurrency SQL (WHERE clauses comparing original values) is generated from. AcceptChanges() commits the tracking log: Added/Modified rows become Unchanged, Deleted rows are physically removed, and Original versions are refreshed from Current. Calling it BEFORE DataAdapter.Update is a classic real-world bug — the adapter then sees no pending changes and writes nothing. Also note Rows.Find(2) performs an indexed lookup through the primary-key constraint rather than a linear scan — which is precisely why the PrimaryKey assignment matters. In a connected implementation each phase maps to a parameterised SqlCommand (INSERT INTO Students VALUES (@id, @name, @course)) — parameters, never string concatenation, are the defence against SQL injection.

Requirements

• .NET SDK 8.0+ (System.Data is built in) or the RUN CODE button.
• Extension: Microsoft.Data.SqlClient plus LocalDB for the connected version.

Procedure

1. Create the project: dotnet new console -n AdoCrud, cd AdoCrud, paste the code.
2. Run dotnet run and match every console line to its CRUD phase.
3. Comment out students.AcceptChanges(); and print students.Rows.Count — the deleted row is STILL counted (state Deleted), proving deletion is logical until accepted.
4. Add temporary Console.WriteLine(row.RowState) calls after each phase to watch the states flip Added → Modified → Deleted → Unchanged.

Explanation of the Code

• The schema block plus students.PrimaryKey = new[] { students.Columns["Id"] }; mirrors Practical 12; the PK also powers Rows.Find.
• Insert: two students.Rows.Add(...) calls create rows in state Added; the count line confirms 2 rows.
• Update/Modify: DataRow row = students.Rows.Find(2); locates Riya via the PK index; assigning row["Name"] = "Riya Sharma"; and row["Course"] = "MBA"; flips the state to Modified while the Original version still remembers "Riya".
• A third row (Vikas) is inserted mid-cycle to show inserts and edits can interleave freely.
• Delete: students.Rows.Find(1).Delete(); marks Aman's row Deleted — logically gone, physically retained.
• students.AcceptChanges(); commits the log: the deleted row disappears for real and the survivors become Unchanged; the final foreach therefore prints only rows 2 and 3.

Expected Output

After Insert: 2 rows
Updated Id=2
Deleted Id=1

Final Table:
2 | Riya Sharma | MBA
3 | Vikas | BCom

🎯 Viva Questions

1. Name the five DataRowStates. — Added, Modified, Deleted, Unchanged, Detached.
2. Why doesn't Delete() remove the row immediately? — The Deleted mark plus the Original version let a DataAdapter emit the matching SQL DELETE later.
3. What does AcceptChanges do? — Commits tracking: resets states to Unchanged, physically removes Deleted rows, refreshes Original versions.
4. Why is calling AcceptChanges before DataAdapter.Update a bug? — Update relies on row states; after accepting, there is "nothing to save".
5. How does Rows.Find avoid a linear scan? — It uses the index created by the PrimaryKey constraint.
6. How are real SQL updates protected from injection? — Parameterised commands with placeholders like @name — never concatenated SQL text.