Theory
The cast of ADO.NET objects
ADO.NET does its job through a handful of objects, each with a clear role. Once you know who does what, the code reads like a small play: connect, command, read, or fill.
This lesson names the cast, the provider, Connection, Command, DataReader, DataAdapter and CommandBuilder, and shows how they cooperate to move FestConnect's data between the database and your code. Learn the roles and the code that follows will make immediate sense.
At a glance
| Object | Role |
|---|---|
| Data provider | A set of classes for one kind of database (SqlClient for SQL Server, OleDb for others) |
| Connection | Opens and closes the link to the database, using a connection string |
| Command | Holds a SQL statement (or stored procedure) and runs it |
| DataReader | Streams rows back, forward-only and read-only (the connected model) |
| DataAdapter | Bridges the database and a DataSet: Fill to load, Update to save |
| CommandBuilder | Auto-generates the INSERT, UPDATE and DELETE commands a DataAdapter needs to save changes |
Theory
Providers: the right classes for your database
A data provider is a family of these classes written for one kind of database. For SQL Server you use the SqlClient provider, whose objects are named SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter. For other databases there is the OleDb provider (OleDbConnection, and so on).
The roles are identical across providers; only the prefix changes. Learn the pattern once with SqlClient and you can talk to any supported database by switching the provider. FestConnect, on SQL Server, uses SqlClient throughout.
Theory
How they cooperate
For a quick read (connected): the Connection opens the line, a Command carries your SELECT, and calling ExecuteReader returns a DataReader you stream rows from, then you close.
For an in-memory copy (disconnected): a DataAdapter wraps a Command, and its Fill method opens the connection, loads the rows into a DataSet, and closes the connection for you. Later, its Update method saves changes back, and a CommandBuilder can generate the INSERT, UPDATE and DELETE commands that Update needs, so you do not have to write them by hand.
Practical
The two patterns in outline
// Connected: Connection -> Command -> DataReader
using (var conn = new SqlConnection(connectionString))
{
var cmd = new SqlCommand("SELECT Name FROM Events", conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader(); // stream rows
while (reader.Read()) { /* use reader["Name"] */ }
} // connection closed automatically
// Disconnected: DataAdapter fills a DataSet, then closes
var adapter = new SqlDataAdapter("SELECT * FROM Events", connectionString);
var ds = new DataSet();
adapter.Fill(ds); // opens, loads into memory, closes for you
// work with ds in memory; adapter.Update(ds) saves changes backQuiz
In ADO.NET, which object's job is to hold your SQL statement and execute it against the database?
- The Connection, because it runs the SQL
- The Command, which holds a SQL statement or stored procedure and executes it
- The DataReader, because it sends the SQL
- The CommandBuilder, because it has 'Command' in its name
Show the answer
The Command, which holds a SQL statement or stored procedure and executes it
The Command object holds a SQL statement (or stored procedure) and executes it, via ExecuteReader for a SELECT, ExecuteNonQuery for INSERT/UPDATE/DELETE, or ExecuteScalar for a single value. Option A is wrong: the Connection only opens and closes the link to the database; it does not carry or run your SQL. Option C is wrong: the DataReader RECEIVES and streams the rows a Command produces; it does not send the SQL. Option D is a trap on the name: the CommandBuilder does not run your query, it auto-generates the INSERT/UPDATE/DELETE commands a DataAdapter needs to save changes. Each object has one role: Connection connects, Command commands, Reader reads.
Think first
What does the CommandBuilder actually save you from?
A DataAdapter can Fill a DataSet on its own. So what is the CommandBuilder for, and why is it convenient? Then tap.
Show the answer
A DataAdapter can READ with just a SELECT, but to SAVE changes back, its Update method needs three more commands: an INSERT for new rows, an UPDATE for changed rows, and a DELETE for removed rows. Writing those three SQL statements by hand, matching every column and key, is tedious and error-prone. The CommandBuilder inspects your SELECT and AUTO-GENERATES those INSERT, UPDATE and DELETE commands for you, so Update just works without you writing them. That is the convenience: for a simple single-table scenario, you attach a CommandBuilder and the adapter can save edits, insertions and deletions from the DataSet straight back to the database. The trade-off is that it only handles straightforward cases (one table, a key defined); for complex queries or joins you still write the commands yourself, often as stored procedures. So the CommandBuilder is a time-saver for the common simple case, not a universal tool. It writes the boring SQL so you do not have to.
Summary
Key takeaways
- ADO.NET works through a small cast: provider, Connection, Command, DataReader, DataAdapter, CommandBuilder.
- A data provider is the class family for one database kind: SqlClient for SQL Server, OleDb for others; only the prefix changes.
- Connection opens and closes the link (via a connection string); Command holds and executes your SQL.
- Command execution: ExecuteReader for SELECT, ExecuteNonQuery for INSERT/UPDATE/DELETE, ExecuteScalar for one value.
- DataReader streams rows in the connected model; DataAdapter's Fill loads a DataSet and Update saves changes in the disconnected model.
- A CommandBuilder auto-generates the INSERT, UPDATE and DELETE commands a DataAdapter needs to save simple changes.
- Memory hook: Connection connects, Command commands, Reader reads, Adapter fills, Builder writes the boring SQL.