Theory
Five workers between the till and the vault
Between ShopKeeper's form and the ShopDB file sits a small crew, and every database program you write in .NET hires the same 5 workers.
One holds the phone line open. One carries your SQL sentence. One reads answers back to you while the line is live. One does a full pickup-and-delivery so you can hang up. And one you already know: the in-memory register from Unit 3.
Name the 5, know each one's single job, and this unit's theory questions are already answered.
Theory
Calling the wholesaler
Think of ShopDB as the wholesaler across town.
The Connection is the phone line: dialled with a number (the connection string), opened, eventually hung up. The Command is the sentence you speak: an order, in SQL. The DataReader is listening on the live call, scribbling items as they are read out, no going back, no editing. The DataAdapter is the delivery boy: one trip, brings the whole order home, where it sits in your own register (the DataSet) for you to work on after the call ends.
At a glance
The ADO.NET object model (learn this table)
| Object | One job | Key members |
|---|---|---|
| Connection | The open line to the database | ConnectionString, Open(), Close() |
| Command | Carries one SQL statement | CommandText, ExecuteReader, ExecuteNonQuery, ExecuteScalar |
| DataReader | Streams result rows, connected | Read(), item access rd("Amount") |
| DataAdapter | Bridge to the disconnected world | Fill(ds), Update(ds) |
| DataSet | In-memory, offline copy (Unit 3) | Tables, Rows |
Theory
Two working styles
The 5 workers form 2 teams, and the split is THE concept of this unit:
- Connected: Connection + Command + DataReader. The line stays open while you read; fastest, always-current, but the reader is forward-only and read-only, and the database holds your call the whole time.
- Disconnected: Connection + DataAdapter + DataSet. Fetch everything in one trip, hang up, then browse, edit and bind grids offline; Update() syncs changes back later.
ShopKeeper prints a bill from a reader (quick pass), but the day-report screen wants the adapter (browse offline, no line held open).
Theory
Providers: same crew, different uniforms
ADO.NET ships one crew per database brand, called a provider:
- System.Data.SqlClient for SQL Server: SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter
- System.Data.OleDb for Access files: OleDbConnection, OleDbCommand, and so on
Same 5 roles, same members, only the prefix changes. And notice which object has NO prefix: DataSet is provider-neutral, because once the data is in memory, its origin no longer matters. That detail is a recurring 1-marker.
Quiz
Which ADO.NET object is connected, forward-only and read-only?
- DataSet
- DataAdapter
- DataReader
- Command
Show the answer
DataReader
That 3-word signature (connected, forward-only, read-only) IS the DataReader's exam fingerprint: it streams rows off a live connection, cannot rewind, cannot edit. The DataSet is its near-opposite on every word: disconnected, freely navigable, editable. The DataAdapter is the courier between the 2 worlds, not a reader of anything itself. The Command merely carries the SQL; it returns a reader but is not one. Memorise the fingerprint phrase: examiners quote it verbatim.
Think first
Staff the 2 screens
Screen A: cashier reprints bill 42, one quick pass through its rows. Screen B: the owner browses a month of bills at home, editing remarks, syncing later. Assign each screen its team of objects before tapping.
Show the answer
Screen A: connected team: SqlConnection (open), SqlCommand (SELECT ... WHERE BillNo = 42), SqlDataReader (one forward pass, print, close): minimal, fast, done. Screen B: disconnected team: SqlDataAdapter fills a DataSet in one trip, the connection closes, the owner works offline in a bound grid, and da.Update(ds) posts the edited remarks when convenient. Choosing rule: quick single pass = reader; browse, edit or work offline = adapter + DataSet.
Watch out
Model mix-ups that cost marks
DataReader vs DataSet: the pair exams love to swap. Reader = connected stream; DataSet = offline copy. Never write that a DataSet is forward-only.
DataAdapter as a container: it holds nothing; it MOVES data (Fill in, Update out).
One Command, one statement: its CommandText is a single SQL sentence; 3 different queries means 3 commands (or rewriting CommandText between calls).
Theory
The model is Unit 3's missing half
Remember the register-and-glass lesson: we promised the DataSet would one day be filled from a real database without the grid noticing. The DataAdapter is that promise's delivery mechanism, and next lesson executes it in code: open, query, read, insert, fill, bind: the complete ShopKeeper save-and-reload cycle, with BCA205's SQL riding inside the Command objects.
Summary
Key takeaways
- Five objects, one job each: Connection (line), Command (SQL), DataReader (connected stream), DataAdapter (courier), DataSet (offline copy).
- Connected style: Connection + Command + DataReader: fast, live, forward-only, read-only.
- Disconnected style: DataAdapter fills a DataSet, connection closes, work offline, Update syncs back.
- Command's 3 executes: ExecuteReader (rows), ExecuteNonQuery (affected count), ExecuteScalar (single value).
- Providers rename the crew: Sql for SQL Server, OleDb for Access; DataSet stays provider-neutral.
- Quick pass = reader; browse/edit/offline = adapter + DataSet.
- Memory hook: line, sentence, listener, courier, register.