What Is ADO.NET?
ADO.NET is the data access layer that Classic ASP.NET Web Forms applications use to talk to relational databases such as SQL Server. Rather than keeping a live connection open for the lifetime of a request, ADO.NET is built around a disconnected model: you open a connection just long enough to run a command, and objects like DataSet and DataTable let you carry query results around in memory even after the connection is closed. The core building blocks are the connection object (SqlConnection), the command object (SqlCommand), the fast forward-only SqlDataReader, and the disconnected DataAdapter/DataSet pair, all supplied by a provider-specific namespace such as System.Data.SqlClient.
Cricket analogy: Like a fielder who sprints in from the boundary, takes the catch, and returns to position rather than standing at the stumps all match, ADO.NET opens a SqlConnection only for the moment it needs it, then releases it back to the pool.
Connections and Commands
SqlConnection represents the physical link to the database and is configured with a connection string, typically stored in web.config's <connectionStrings> section rather than hard-coded in a page. SqlCommand wraps a SQL statement or stored procedure call and exposes three main execution methods: ExecuteReader for row-returning SELECT queries, ExecuteNonQuery for INSERT/UPDATE/DELETE statements that return only an affected-row count, and ExecuteScalar for queries that return a single value, such as a COUNT(*). Every command should use SqlParameter objects for any user-supplied value instead of concatenating strings, both for correctness (handling quotes and types properly) and for security against SQL injection.
Cricket analogy: Choosing between a single, a boundary, or asking for a review uses a different signal each time, just as ADO.NET picks ExecuteReader for multi-row SELECTs, ExecuteNonQuery for an UPDATE like adjusting a player's stats, and ExecuteScalar for a single value like the current run total.
using (SqlConnection conn = new SqlConnection(
ConfigurationManager.ConnectionStrings["AppDb"].ConnectionString))
{
string sql = "SELECT ProductId, Name, Price FROM Products WHERE CategoryId = @CategoryId";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@CategoryId", categoryId);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string name = reader["Name"].ToString();
decimal price = (decimal)reader["Price"];
// bind into a list, DataTable, or directly to a GridView
}
}
}
}DataReader vs DataSet/DataAdapter
SqlDataReader is a connected, forward-only, read-only cursor: it is the fastest way to stream rows from the database, but the connection must stay open for the entire read and you can only move forward through the result once. DataAdapter, by contrast, is a bridge that fills a disconnected DataSet or DataTable using its Fill method, then closes the connection automatically; the in-memory data can be sorted, filtered with a DataView, paged, or bound to multiple controls, and updates made in memory can later be pushed back to the database with the adapter's Update method. Web Forms pages commonly favor the DataAdapter/DataSet pattern because a page's lifecycle already discards connections between postbacks, so disconnected data fits the model naturally.
Cricket analogy: Watching a match live on TV gives you a forward-only, real-time feed you can't rewind, like SqlDataReader, whereas recording the full match to rewatch, pause, and replay any over later is like a DataAdapter filling a DataSet for offline analysis.
Always wrap SqlConnection, SqlCommand, and SqlDataReader in using blocks (or call Dispose explicitly). Because ADO.NET relies on connection pooling, forgetting to close a connection doesn't just leak one connection — it can exhaust the entire pool under load and cause timeouts for every other request.
Best Practices
Two habits separate reliable ADO.NET code from code that breaks under real traffic. First, always use parameterized queries (SqlParameter or the shorthand AddWithValue) instead of building SQL by string concatenation — this both prevents SQL injection and lets SQL Server reuse cached execution plans. Second, always dispose connections deterministically with using blocks rather than relying on garbage collection, because connection pooling means an undisposed connection stays checked out of the pool until the finalizer eventually runs, which can be long after the request has finished and can starve concurrent requests of available connections.
Cricket analogy: A captain who sets an unchanging field plan before the over and just adjusts placements per batter, rather than reinventing the whole strategy from scratch each ball, mirrors how parameterized queries let SQL Server reuse a cached execution plan instead of recompiling for every request.
Never build SQL by concatenating user input, e.g. "SELECT * FROM Users WHERE Name = '" + txtName.Text + "'". This is the classic SQL injection vulnerability — a malicious value like ' OR '1'='1 can bypass filtering entirely or destroy data. Always use SqlParameter, and never leave connections open across postbacks, which silently exhausts the connection pool.
- ADO.NET uses a disconnected model: connections are opened briefly per command and closed immediately, not held for the whole request.
- SqlConnection, SqlCommand, SqlDataReader, and DataAdapter/DataSet are the core objects, all in a provider namespace like System.Data.SqlClient.
- ExecuteReader returns multiple rows, ExecuteNonQuery returns an affected-row count, and ExecuteScalar returns a single value.
- SqlDataReader is fast and connected but forward-only; DataSet/DataTable via DataAdapter is disconnected and can be sorted, filtered, and reused across postbacks.
- Always use SqlParameter instead of string concatenation to prevent SQL injection and enable execution plan reuse.
- Always dispose SqlConnection and SqlCommand with using blocks to avoid exhausting the connection pool.
- Store connection strings in web.config's <connectionStrings> section, not hard-coded in code-behind.
Practice what you learned
1. Which ADO.NET object provides a fast, forward-only, connected stream of rows from a query?
2. Which SqlCommand execution method should you use to run an UPDATE statement and find out how many rows were affected?
3. Why should SqlParameter be used instead of string concatenation when building queries with user input?
4. What happens if a SqlConnection is not disposed via a using block or explicit Dispose call?
5. Where should a Web Forms application's database connection string typically be stored?
Was this page helpful?
You May Also Like
Working with SQL in Web Forms
Learn practical patterns for executing SQL from Web Forms code-behind, including parameterized queries, stored procedures, and transactions.
Data Binding in Web Forms
Understand how Web Forms server controls bind to data sources, the difference between declarative and programmatic binding, and when to use Repeater, GridView, or ListView.
Caching in Web Forms
Learn how output caching, fragment caching, and the Cache API reduce database and rendering load in Classic ASP.NET Web Forms applications.
Related Reading
Related Study Notes in Microsoft Technologies
Browse all study notesWindows 10 / UWP Development Study Notes
.NET · 30 topics
Microsoft TechnologiesWindows Batch Scripting Study Notes
Batch · 30 topics
Microsoft TechnologiesMFC (Microsoft Foundation Classes) Study Notes
C++ · 30 topics
Microsoft TechnologiesSilverlight Study Notes
.NET · 30 topics
Microsoft TechnologiesXAML Study Notes
.NET · 30 topics
Microsoft TechnologiesWPF (Windows Presentation Foundation) Study Notes
.NET · 30 topics