Aim

To store and display student records using ADO.NET's disconnected layer — DataSet, DataTable, DataColumn and DataRow — modelling a relational database entirely in memory.

Theory

ADO.NET has two halves. The connected layer (SqlConnection, SqlCommand, SqlDataReader) streams rows from a live database over an open connection. The disconnected layer used here mirrors relational structure in memory: a DataSet is an in-memory database (a collection of tables plus relations), and a DataTable is one table carrying both a strongly typed schema (its DataColumn objects each bind a name to a CLR Type) and its data (DataRow objects). Assigning students.PrimaryKey builds a unique constraint plus an internal index that enforces entity integrity — adding a second row with Id = 1 throws ConstraintException, exactly like a PK violation in SQL Server. Internally each row stores its values as object slots, so value types are boxed on insertion and must be cast (unboxed) on the way out — the type-safety gap that later motivated typed DataSets, LINQ to DataSet and ultimately Entity Framework. The professional payoff of the disconnected model: a SqlDataAdapter.Fill(dataSet) can hydrate this identical structure from a real database and adapter.Update can push changes back — every line of DataTable code in this practical stays unchanged; only the plumbing differs. That is why the pattern is ideal for lab work without a database server.

Requirements

• .NET SDK 8.0+ — the System.Data types ship in the base class library, no NuGet package needed.
• Optional real-DB extension: SQL Server Express/LocalDB plus the Microsoft.Data.SqlClient package.

Procedure

1. Create the project: dotnet new console -n AdoStudents, cd AdoStudents, paste the code.
2. Run dotnet run — three student rows print.
3. Prove the constraint: add students.Rows.Add(1, "X", "Y") and observe ConstraintException from the primary key.
4. Extension: replace the manual Rows.Add calls with new SqlDataAdapter("SELECT * FROM Students", conn).Fill(collegeDb, "Students") against LocalDB and re-run unchanged display code.

Explanation of the Code

• DataSet collegeDb = new DataSet("CollegeDB"); — the in-memory database container, named for diagnostics and XML serialisation.
• students.Columns.Add("Id", typeof(int)); — schema definition: each DataColumn binds a column name to a CLR type, so inserting the string "abc" into Id throws ArgumentException at insert time (type checking at the gate).
• students.PrimaryKey = new[] { students.Columns["Id"] }; — an ARRAY because composite keys are allowed; this creates the uniqueness constraint and the index that also powers fast Rows.Find lookups.
• students.Rows.Add(1, "Aman", "BCA"); — positional insert matching the column order; the int 1 is boxed into the row's object storage.
• collegeDb.Tables.Add(students); attaches the table to the DataSet; the display loop foreach (DataRow row in collegeDb.Tables["Students"].Rows) navigates DataSet → table → rows, with row["Name"] performing an indexer lookup by column name.

Expected Output

Students stored successfully:
1 | Aman | BCA
2 | Riya | BBA
3 | Karan | BCom

🎯 Viva Questions

1. Connected vs disconnected ADO.NET? — Command/reader streaming over a live connection versus DataSet/DataTable snapshots that work offline.
2. What enforces the uniqueness of Id? — The PrimaryKey assignment creates a UniqueConstraint backed by an internal index.
3. Why is PrimaryKey an array? — Composite primary keys spanning multiple columns are supported.
4. What happens when a wrong-typed value is inserted? — ArgumentException; DataColumn types are validated when the row is added.
5. Where does boxing occur here? — Value types (the int Id) are wrapped into objects because rows store their values as object slots.
6. Which class bridges a DataSet and a real database? — The DataAdapter — Fill loads, Update persists.