ADO.NET Data Access: Connections, Readers, DataSets

Last Updated: Nov 06, 2025
4 min read
Legacy Archive
Legacy Guidance: This article preserves historical web development content. For modern .NET 8+ best practices, visit our Tutorials section.

Overview

ADO.NET is the data access stack for .NET. It gives you a clean way to connect to a database, run commands, and move data between tiers. You work with two main styles. Streaming with a DataReader for fast, forward only reads. Disconnected with a DataSet for cached data and updates.

DataSet and DataAdapter

A DataSet is an in memory view of one or more tables. It can hold relations, constraints, and views. You fill it with a DataAdapter and you can save it as XML when needed.

Fill a DataSet and push updates 
using System.Data;
using System.Data.SqlClient;

var cs = "Server=.;Database=Northwind;Integrated Security=true";
using var conn = new SqlConnection(cs);
using var da = new SqlDataAdapter("SELECT Id, Name FROM dbo.Categories", conn);

// Build insert/update/delete commands automatically for demo use
using var cb = new SqlCommandBuilder(da);

var ds = new DataSet();
da.Fill(ds, "Categories");

// Edit in memory
var table = ds.Tables["Categories"];
table.Rows[0]["Name"] = "Beverages";

// Send only changed rows
var changed = ds.GetChanges();
if (changed != null)
{
    da.Update(changed, "Categories");
    ds.Merge(changed);
}

Tables, Columns, and Relations

Inside a DataSet you get DataTable objects. A table has a DataColumnCollection that defines the schema. Relations between tables are handled by DataRelation and can enforce unique and foreign key rules.

Create schema and relation 
var ds = new DataSet();

var customers = new DataTable("Customers");
customers.Columns.Add("CustomerId", typeof(int));
customers.Columns.Add("Name", typeof(string));
customers.PrimaryKey = new[] { customers.Columns["CustomerId"] };

var orders = new DataTable("Orders");
orders.Columns.Add("OrderId", typeof(int));
orders.Columns.Add("CustomerId", typeof(int));

ds.Tables.Add(customers);
ds.Tables.Add(orders);

// Enforce parent child rules
var rel = new DataRelation("FK_Customer_Orders",
    customers.Columns["CustomerId"], orders.Columns["CustomerId"], true);
ds.Relations.Add(rel);

Fast Reads with DataReader

Use DataView to sort or filter a table for display. Use DataReader when you just need to stream rows quickly. The reader is forward only and read only which makes it very fast.

Stream rows 
using var conn = new SqlConnection(cs);
using var cmd = new SqlCommand("SELECT TOP 5 Id, Name FROM dbo.Categories", conn);
conn.Open();
using var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
    Console.WriteLine($"{rdr.GetInt32(0)}: {rdr.GetString(1)}");
}

Transactions

A Connection holds the link to the database. Commands run SQL or stored procedures. You can start a transaction from a connection when a change spans multiple statements.

All or nothing 
using var conn = new SqlConnection(cs);
conn.Open();
using var tx = conn.BeginTransaction();

try
{
    var insert = new SqlCommand(
        "INSERT INTO dbo.Categories(Name) VALUES(@n)", conn, tx);
    insert.Parameters.AddWithValue("@n", "New Category");
    insert.ExecuteNonQuery();

    var update = new SqlCommand(
        "UPDATE dbo.Categories SET Name=@n WHERE Id=@id", conn, tx);
    update.Parameters.AddWithValue("@n", "Updated");
    update.Parameters.AddWithValue("@id", 1);
    update.ExecuteNonQuery();

    tx.Commit();
}
catch
{
    tx.Rollback();
    throw;
}

Providers

Providers sit between your code and the data source. SqlClient talks to SQL Server. Odbc works with ODBC sources. OleDb targets OLE DB providers. OracleClient targets Oracle in older apps. XML support lives under System.Xml.

Provider switch 
using System.Data.Common;

// Create provider neutral code
string provider = "System.Data.SqlClient"; // or System.Data.Odbc, System.Data.OleDb
DbProviderFactory f = DbProviderFactories.GetFactory(provider);

using var conn = f.CreateConnection();
conn.ConnectionString = "...";
conn.Open();

XML Integration

ADO.NET works well with XML. You can read and write XML from a DataSet, and load or save the schema as XSD.

Read and write XML 
var ds = new DataSet();
ds.ReadXml("orders.xml");      // load data
ds.WriteXml("orders-out.xml"); // save data
ds.WriteXmlSchema("orders.xsd"); // save schema

Send Only Changes

In multi tier apps, send only the changed rows. Call GetChanges on the DataSet to collect edits, then push them with a DataAdapter. When the server returns new values, merge them back into the original DataSet with Merge.

Practical Tips

  • Open connections late and close early.
  • Use pooling for busy apps.
  • Use a DataReader for fast lists and reports.
  • Use a DataSet for cached data and relations.
  • Plan for optimistic concurrency with row versions.

Open a connection late and close it early. Use pooling. Prefer a DataReader for simple lists. Use a DataSet when you need caching, relations, or offline edits. Handle concurrency with timestamps or row versions.

FREQUENTY ASKED QUESTIONS (FAQ)

When should I use a DataReader vs a DataSet?

Use a DataReader for fast, forward only reads. Use a DataSet when you need caching, relations, or offline edits.

How do I push DataSet changes back to the database?

Collect edited rows with GetChanges, update with a DataAdapter, then merge server values with Merge.

Do I need a transaction for multiple statements?

Yes when a set of statements must succeed as one unit. Begin a transaction, attach it to commands, then commit or roll back.

How does XML fit with ADO.NET?

A DataSet can read and write XML and XSD which makes it useful for exchange and batch work.

Back to Articles