ActiveX Data Object (ADO).NET provides consistent data access from systems like SQL Server, Oracle, and MySQL. It’s designed for web apps with a disconnected architecture, XML integration, a common data representation, and optimized database interactions.
A typical setup includes a data store (SQL Server, Oracle, XML, Access) and your application’s UI. To reach the data, you work with two pillars: a Data Provider (connections, commands, adapters, readers) and a DataSet (in-memory, relational data container).
A Data Provider is intentionally lightweight, minimizing layers between your code and the source to deliver performance without sacrificing functionality.
ADO.NET offers three primary providers:
System.Data.SqlClient)System.Data.OleDb)System.Data.Odbc)Each provider exposes four core objects:
using System.Data.SqlClient;
public class DataProviderExample
{
private string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;";
public void DemonstrateProviderObjects()
{
// Connection object
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Command object
SqlCommand command = new SqlCommand(
"SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country",
connection);
// Add parameter to command
command.Parameters.AddWithValue("@Country", "USA");
connection.Open();
// DataReader object
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"{reader["CustomerID"]}: {reader["CompanyName"]}");
}
}
}
}
}
The Connection object opens a link to your data source. Always dispose connections quickly with using.
using System.Data.SqlClient;
public void WorkWithConnection()
{
string connString = "Server=localhost;Database=MyDB;User Id=sa;Password=pass123;";
using (SqlConnection conn = new SqlConnection(connString))
{
try
{
conn.Open();
Console.WriteLine("Connection opened successfully!");
Console.WriteLine($"Database: {conn.Database}");
Console.WriteLine($"Server Version: {conn.ServerVersion}");
Console.WriteLine($"State: {conn.State}");
}
catch (SqlException ex)
{
Console.WriteLine($"Connection error: {ex.Message}");
}
} // Connection automatically closed here
}
Use Command to run SQL or stored procedures against your source—supports non-queries, scalars, and result sets.
using System.Data;
using System.Data.SqlClient;
public void ExecuteCommands()
{
string connectionString = "Server=localhost;Database=MyDB;Integrated Security=true;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Execute non-query (INSERT, UPDATE, DELETE)
SqlCommand insertCmd = new SqlCommand(
"INSERT INTO Products (ProductName, UnitPrice) VALUES (@Name, @Price)",
connection);
insertCmd.Parameters.AddWithValue("@Name", "New Product");
insertCmd.Parameters.AddWithValue("@Price", 29.99);
int rowsAffected = insertCmd.ExecuteNonQuery();
Console.WriteLine($"{rowsAffected} row(s) inserted");
// Execute scalar (get single value)
SqlCommand scalarCmd = new SqlCommand(
"SELECT COUNT(*) FROM Products",
connection);
int productCount = (int)scalarCmd.ExecuteScalar();
Console.WriteLine($"Total products: {productCount}");
// Call stored procedure
SqlCommand spCmd = new SqlCommand("GetProductsByCategory", connection);
spCmd.CommandType = CommandType.StoredProcedure;
spCmd.Parameters.AddWithValue("@CategoryID", 1);
using (SqlDataReader reader = spCmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["ProductName"]);
}
}
}
}
DataReader streams rows efficiently (forward-only/read-only). Ideal when displaying results and not editing data.
using System.Data.SqlClient;
public void UseDataReader()
{
string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;";
string query = "SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
// Check if there are rows
if (reader.HasRows)
{
// Read column schema
Console.WriteLine($"Columns: {reader.FieldCount}");
// Read data
while (reader.Read())
{
// Access by index
int productId = reader.GetInt32(0);
// Access by name
string productName = reader["ProductName"].ToString();
// Handle null values
decimal? unitPrice = reader.IsDBNull(2) ? null : reader.GetDecimal(2);
Console.WriteLine($"{productId}: {productName} - ${unitPrice}");
}
}
else
{
Console.WriteLine("No data found.");
}
}
}
}The connection provides the link; the command executes the query—direct flow from source to your app.
DataAdapter fills a DataSet for offline work (filter/sort/edit) and can write changes back to the database.
using System.Data;
using System.Data.SqlClient;
public void UseDataAdapter()
{
string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;";
string query = "SELECT * FROM Products";
DataSet dataSet = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create DataAdapter
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
// Fill DataSet (connection opens and closes automatically)
adapter.Fill(dataSet, "Products");
Console.WriteLine($"Loaded {dataSet.Tables["Products"].Rows.Count} products");
}
// Work with data offline
DataTable productsTable = dataSet.Tables["Products"];
foreach (DataRow row in productsTable.Rows)
{
Console.WriteLine($"{row["ProductName"]}: ${row["UnitPrice"]}");
}
}public void UpdateWithDataAdapter()
{
string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;";
DataSet dataSet = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Load data
SqlDataAdapter adapter = new SqlDataAdapter(
"SELECT ProductID, ProductName, UnitPrice FROM Products",
connection);
adapter.Fill(dataSet, "Products");
// Modify data
DataTable table = dataSet.Tables["Products"];
table.Rows[0]["UnitPrice"] = 35.99;
// Add new row
DataRow newRow = table.NewRow();
newRow["ProductName"] = "New Product";
newRow["UnitPrice"] = 19.99;
table.Rows.Add(newRow);
// Delete a row
table.Rows[5].Delete();
// Generate update commands automatically
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
// Update database
int updatedRows = adapter.Update(dataSet, "Products");
Console.WriteLine($"{updatedRows} rows updated in database");
}
}
DataSet can host multiple tables and relationships, enabling complex in-memory operations across related data.
using System.Data;
public void WorkWithDataSet()
{
DataSet dataSet = new DataSet("CompanyData");
// Create Categories table
DataTable categoriesTable = new DataTable("Categories");
categoriesTable.Columns.Add("CategoryID", typeof(int));
categoriesTable.Columns.Add("CategoryName", typeof(string));
categoriesTable.PrimaryKey = new DataColumn[] { categoriesTable.Columns["CategoryID"] };
// Create Products table
DataTable productsTable = new DataTable("Products");
productsTable.Columns.Add("ProductID", typeof(int));
productsTable.Columns.Add("ProductName", typeof(string));
productsTable.Columns.Add("CategoryID", typeof(int));
productsTable.Columns.Add("UnitPrice", typeof(decimal));
// Add tables to DataSet
dataSet.Tables.Add(categoriesTable);
dataSet.Tables.Add(productsTable);
// Create relationship
DataRelation relation = new DataRelation(
"FK_Products_Categories",
categoriesTable.Columns["CategoryID"],
productsTable.Columns["CategoryID"]);
dataSet.Relations.Add(relation);
// Add data
categoriesTable.Rows.Add(1, "Electronics");
categoriesTable.Rows.Add(2, "Clothing");
productsTable.Rows.Add(1, "Laptop", 1, 999.99);
productsTable.Rows.Add(2, "Mouse", 1, 29.99);
productsTable.Rows.Add(3, "T-Shirt", 2, 19.99);
// Navigate relationships
foreach (DataRow categoryRow in categoriesTable.Rows)
{
Console.WriteLine($"Category: {categoryRow["CategoryName"]}");
DataRow[] childProducts = categoryRow.GetChildRows(relation);
foreach (DataRow productRow in childProducts)
{
Console.WriteLine($" - {productRow["ProductName"]}: ${productRow["UnitPrice"]}");
}
}
}
DataView exposes different perspectives of a DataTable (filter/sort) without touching the database again.
using System.Data;
public void UseDataView()
{
// Assume we have a filled DataSet
DataSet dataSet = GetProductData();
DataTable productsTable = dataSet.Tables["Products"];
// Create DataView
DataView view = new DataView(productsTable);
// Filter data
view.RowFilter = "UnitPrice > 20 AND UnitPrice < 100";
// Sort data
view.Sort = "ProductName ASC";
// Display filtered and sorted results
Console.WriteLine("Products between $20 and $100:");
foreach (DataRowView rowView in view)
{
Console.WriteLine($"{rowView["ProductName"]}: ${rowView["UnitPrice"]}");
}
// Find specific item
view.Sort = "ProductName"; // Must sort before Find
int index = view.Find("Widget");
if (index != -1)
{
Console.WriteLine($"Found Widget at index {index}");
}
}public void AdvancedDataView()
{
DataTable table = new DataTable("Products");
table.Columns.Add("ProductName", typeof(string));
table.Columns.Add("Category", typeof(string));
table.Columns.Add("Price", typeof(decimal));
// Add sample data
table.Rows.Add("Laptop", "Electronics", 999.99);
table.Rows.Add("Mouse", "Electronics", 29.99);
table.Rows.Add("Keyboard", "Electronics", 79.99);
table.Rows.Add("Shirt", "Clothing", 39.99);
// Create multiple views of same data
DataView electronicsView = new DataView(table);
electronicsView.RowFilter = "Category = 'Electronics'";
electronicsView.Sort = "Price DESC";
DataView affordableView = new DataView(table);
affordableView.RowFilter = "Price < 50";
Console.WriteLine("Electronics (by price):");
foreach (DataRowView row in electronicsView)
{
Console.WriteLine($"{row["ProductName"]}: ${row["Price"]}");
}
Console.WriteLine("\nAffordable items:");
foreach (DataRowView row in affordableView)
{
Console.WriteLine($"{row["ProductName"]}: ${row["Price"]}");
}
}Use DataReader for fast, read-only display scenarios; choose DataAdapter + DataSet for editable, related, or offline datasets.
public class ADONETExample
{
private string connectionString = "Server=localhost;Database=MyDB;Integrated Security=true;";
// Fast read-only display
public void DisplayProducts()
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(
"SELECT ProductName, UnitPrice FROM Products ORDER BY ProductName",
conn);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"{reader["ProductName"]}: ${reader["UnitPrice"]}");
}
}
}
}
// Complex data manipulation
public void ManageProducts()
{
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter(
"SELECT * FROM Products", conn);
adapter.Fill(ds, "Products");
// Work with data offline
DataTable table = ds.Tables["Products"];
// Modify existing
table.Rows[0]["UnitPrice"] = 49.99;
// Add new
DataRow newRow = table.NewRow();
newRow["ProductName"] = "New Item";
newRow["UnitPrice"] = 29.99;
table.Rows.Add(newRow);
// Update database
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(ds, "Products");
}
}
}The ADO.NET object model lets you balance speed (DataReader) and flexibility (DataAdapter/DataSet) according to your needs.
DataReader streams forward-only, read-only data with minimal memory. DataAdapter fills a DataSet for offline edits, filtering, sorting, and later updates back to the database.
Use a DataSet for multiple related tables and relationships. Prefer a DataTable for single-table tasks where simplicity and lower memory use matter.
DataView enables filtered and sorted projections of a DataTable entirely in memory, avoiding extra database queries and supporting multiple concurrent views.