.NET ADO.NET Introduction
What is ADO.NET?
ADO.NET is a set of classes in the .NET Framework that provides data access services to relational database systems. It's a core component of the .NET Framework, designed specifically for data-centric software applications that need to connect to, retrieve, manipulate, and update data from various data sources.
ADO.NET stands for ActiveX Data Objects for .NET, though this historical expansion is rarely used nowadays. It evolved from the older ADO (ActiveX Data Objects) technology but was completely redesigned for the .NET Framework with improvements in performance, scalability, and programmer productivity.
Why Use ADO.NET?
Before diving into the technical aspects, let's understand why ADO.NET is important:
- Standardized Data Access: Provides a consistent way to access different database systems
- Disconnected Data Architecture: Allows efficient use of database connections
- Integration with Other .NET Technologies: Works seamlessly with other parts of the .NET ecosystem
- Type Safety: Leverages C#'s strong typing for more robust code
- Scalability: Designed to handle enterprise-level data access needs
ADO.NET Architecture
ADO.NET is built around a disconnected architecture that consists of several key components:
Key Components of ADO.NET
- Connection: Establishes a connection to a specific data source
- Command: Executes a command against the data source
- DataReader: Reads a forward-only, read-only stream of data from a data source
- DataAdapter: Bridges between the data source and DataSet
- DataSet: An in-memory cache of data retrieved from a data source
- DataTable: Represents a single table of data in memory
- DataRelation: Represents a relationship between two DataTable objects
Connection Objects
The first step in accessing a database is establishing a connection. The most commonly used connection objects are:
SqlConnection- For Microsoft SQL ServerOleDbConnection- For OLE DB data sourcesOdbcConnection- For ODBC data sourcesMySqlConnection- For MySQL (requires MySQL connector)
Here's how to establish a connection to a SQL Server database:
csharp
// Add necessary using directives
using System.Data;
using System.Data.SqlClient;
// Create a connection string
string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password";
// Create and open the connection
SqlConnection connection = new SqlConnection(connectionString);
try
{
connection.Open();
Console.WriteLine("Connection opened successfully!");
// Database operations would go here
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
finally
{
// Always close the connection when done
if (connection.State == ConnectionState.Open)
connection.Open();
}
Command Objects
Command objects execute commands against a data source. The most common command objects are:
SqlCommand- For SQL ServerOleDbCommand- For OLE DB data sourcesOdbcCommand- For ODBC data sources
Here's an example of executing a simple SQL command:
csharp
using System.Data.SqlClient;
string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create a command
SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE Country = @Country", connection);
// Add parameters (always use parameterized queries to prevent SQL injection)
command.Parameters.AddWithValue("@Country", "USA");
try
{
connection.Open();
// Execute the command and process results
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string name = reader["CustomerName"].ToString();
string city = reader["City"].ToString();
Console.WriteLine($"Customer: {name}, City: {city}");
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
}
DataReader
A DataReader provides a high-performance way to retrieve data from a database. It's forward-only and read-only, making it ideal for situations where you need to process results sequentially:
csharp
using System.Data.SqlClient;
string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE CategoryID = @CategoryID";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@CategoryID", 1);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
// Check if we have any rows
if (reader.HasRows)
{
Console.WriteLine("Product List:");
Console.WriteLine("ID\tName\t\tPrice");
Console.WriteLine("---------------------------");
// Read each row
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
decimal price = reader.GetDecimal(2);
Console.WriteLine($"{id}\t{name}\t${price}");
}
}
else
{
Console.WriteLine("No products found in this category.");
}
}
}
/* Sample Output:
Product List:
ID Name Price
---------------------------
1 Chai $18.00
2 Chang $19.00
3 Aniseed Syrup $10.00
*/
DataAdapter and DataSet
The DataAdapter acts as a bridge between a DataSet and a data source. It uses Command objects to execute SQL commands to retrieve data and fill a DataSet with that data. This approach is useful when you need to work with disconnected data:
csharp
using System.Data;
using System.Data.SqlClient;
string connectionString = "Data Source=ServerName;Initial Catalog=NorthwindDB;Integrated Security=True";
// Create the connection and data adapter
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "SELECT CustomerID, CompanyName, ContactName, Country FROM Customers";
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
// Create and fill the DataSet
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Customers");
// Work with the data in the DataSet
DataTable customerTable = dataSet.Tables["Customers"];
Console.WriteLine($"Retrieved {customerTable.Rows.Count} customers");
// Loop through the data
foreach (DataRow row in customerTable.Rows)
{
Console.WriteLine($"Customer: {row["CompanyName"]}, Contact: {row["ContactName"]}, Country: {row["Country"]}");
}
// Making changes to disconnected data
DataRow newRow = customerTable.NewRow();
newRow["CustomerID"] = "NEWID";
newRow["CompanyName"] = "New Company Inc.";
newRow["ContactName"] = "John Smith";
newRow["Country"] = "Canada";
customerTable.Rows.Add(newRow);
// To save changes back to the database, you would typically:
// 1. Create command builders or define insert/update/delete commands
// 2. Call the Update method of the data adapter
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
// adapter.Update(dataSet, "Customers"); // Uncomment to actually save changes
}
Transactions in ADO.NET
Transactions allow you to group multiple operations into a single unit of work that either succeeds completely or fails completely:
csharp
using System.Data.SqlClient;
string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Start a transaction
SqlTransaction transaction = connection.BeginTransaction();
try
{
// Create commands for the transaction
SqlCommand command1 = new SqlCommand("UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1", connection, transaction);
SqlCommand command2 = new SqlCommand("UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2", connection, transaction);
// Execute commands
int rowsAffected1 = command1.ExecuteNonQuery();
int rowsAffected2 = command2.ExecuteNonQuery();
// Check if both operations succeeded
if (rowsAffected1 > 0 && rowsAffected2 > 0)
{
// Commit the transaction
transaction.Commit();
Console.WriteLine("Transaction committed successfully.");
}
else
{
// Roll back if any operation failed
transaction.Rollback();
Console.WriteLine("Transaction rolled back due to missing rows.");
}
}
catch (Exception ex)
{
// Roll back on error
transaction.Rollback();
Console.WriteLine("Transaction rolled back due to error: " + ex.Message);
}
}
Best Practices for ADO.NET Development
- Always use parameterized queries to prevent SQL injection attacks
- Close connections explicitly or use the
usingstatement to ensure proper disposal - Use connection pooling for better performance
- Implement proper error handling with try-catch blocks
- Use transactions when performing multiple related operations
- Avoid storing sensitive information directly in connection strings
- Choose the right object model (Connected vs. Disconnected) based on your needs
Common Patterns
Connected Pattern (Using DataReader):
- More memory efficient
- Ideal for displaying read-only data
- Good for large datasets that you process sequentially
Disconnected Pattern (Using DataSet):
- Better for operations that require offline work
- Useful when you need to cache data
- Good for situations where you need to manipulate data before saving it back
Real-World Example: Building a Customer Management System
Let's create a simple customer management system that demonstrates various ADO.NET features:
csharp
using System;
using System.Data;
using System.Data.SqlClient;
namespace CustomerManagementSystem
{
public class CustomerRepository
{
private readonly string _connectionString;
public CustomerRepository(string connectionString)
{
_connectionString = connectionString;
}
public DataTable GetAllCustomers()
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection);
DataTable customers = new DataTable();
adapter.Fill(customers);
return customers;
}
}
public Customer GetCustomerById(string customerId)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE CustomerID = @CustomerID", connection);
command.Parameters.AddWithValue("@CustomerID", customerId);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
return new Customer
{
CustomerID = reader["CustomerID"].ToString(),
CompanyName = reader["CompanyName"].ToString(),
ContactName = reader["ContactName"].ToString(),
ContactTitle = reader["ContactTitle"].ToString(),
Country = reader["Country"].ToString()
};
}
return null;
}
}
}
public bool AddCustomer(Customer customer)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
string sql = @"INSERT INTO Customers (CustomerID, CompanyName, ContactName, ContactTitle, Country)
VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Country)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@CustomerID", customer.CustomerID);
command.Parameters.AddWithValue("@CompanyName", customer.CompanyName);
command.Parameters.AddWithValue("@ContactName", customer.ContactName);
command.Parameters.AddWithValue("@ContactTitle", customer.ContactTitle);
command.Parameters.AddWithValue("@Country", customer.Country);
connection.Open();
int result = command.ExecuteNonQuery();
return result > 0;
}
}
public bool UpdateCustomer(Customer customer)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
string sql = @"UPDATE Customers SET
CompanyName = @CompanyName,
ContactName = @ContactName,
ContactTitle = @ContactTitle,
Country = @Country
WHERE CustomerID = @CustomerID";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@CustomerID", customer.CustomerID);
command.Parameters.AddWithValue("@CompanyName", customer.CompanyName);
command.Parameters.AddWithValue("@ContactName", customer.ContactName);
command.Parameters.AddWithValue("@ContactTitle", customer.ContactTitle);
command.Parameters.AddWithValue("@Country", customer.Country);
connection.Open();
int result = command.ExecuteNonQuery();
return result > 0;
}
}
public bool DeleteCustomer(string customerId)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);
command.Parameters.AddWithValue("@CustomerID", customerId);
connection.Open();
int result = command.ExecuteNonQuery();
return result > 0;
}
}
}
public class Customer
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }
public string Country { get; set; }
}
class Program
{
static void Main(string[] args)
{
string connectionString = "Data Source=ServerName;Initial Catalog=NorthwindDB;Integrated Security=True";
CustomerRepository repository = new CustomerRepository(connectionString);
// Add a new customer
Customer newCustomer = new Customer
{
CustomerID = "DEMO1",
CompanyName = "Demo Company",
ContactName = "John Demo",
ContactTitle = "Owner",
Country = "USA"
};
if (repository.AddCustomer(newCustomer))
{
Console.WriteLine("Customer added successfully!");
// Retrieve the customer
Customer retrievedCustomer = repository.GetCustomerById("DEMO1");
Console.WriteLine($"Retrieved: {retrievedCustomer.CompanyName} from {retrievedCustomer.Country}");
// Update the customer
retrievedCustomer.Country = "Canada";
if (repository.UpdateCustomer(retrievedCustomer))
{
Console.WriteLine("Customer updated successfully!");
}
// Delete the customer
if (repository.DeleteCustomer("DEMO1"))
{
Console.WriteLine("Customer deleted successfully!");
}
}
// Get all customers
DataTable allCustomers = repository.GetAllCustomers();
Console.WriteLine($"Total customers: {allCustomers.Rows.Count}");
}
}
}
Summary
In this introduction to ADO.NET, we've covered:
- What ADO.NET is and how it fits into the .NET Framework
- The key components of ADO.NET architecture
- How to establish database connections
- Creating and executing commands
- Reading data with DataReader
- Working with disconnected data using DataAdapter and DataSet
- Transaction management
- Best practices for ADO.NET development
- A real-world example of a customer management system
ADO.NET provides a flexible and powerful way to interact with databases in .NET applications. While newer technologies like Entity Framework provide higher-level abstractions, understanding ADO.NET gives you a solid foundation in data access principles and more control over database interactions.
Additional Resources
Exercises
-
Create a console application that connects to a SQL Server database and retrieves a list of products.
-
Modify the application to allow adding, updating, and deleting products.
-
Implement a transaction that transfers money between two accounts, ensuring that both operations succeed or fail together.
-
Create a data access layer using ADO.NET that accesses a database of your choice and performs CRUD operations.
-
Build a simple Windows Forms or WPF application that uses a DataGridView or DataGrid to display and edit data from a database using ADO.NET.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)