C# ADO.NET Basics

Introduction

ADO.NET (ActiveX Data Objects .NET) is a set of .NET libraries designed specifically for data access and manipulation. It serves as the bridge between your C# applications and databases, allowing you to connect to, query, and manage data from various database systems like SQL Server, Oracle, MySQL, and more.

If you're building applications that need to interact with databases, understanding ADO.NET is essential. It provides a consistent way to work with data regardless of the data source, making your code more maintainable and flexible.

Why Use ADO.NET?

Database Independence: Write code that works with multiple database systems
Performance: Direct control over database operations for optimal performance
Flexibility: Work with connected or disconnected data models
Integration: Seamless integration with other .NET technologies

ADO.NET Architecture

ADO.NET consists of several components:

Connection: Establishes a connection to a data source
Command: Executes SQL queries or stored procedures
DataReader: Provides fast, forward-only access to data
DataAdapter: Bridges between DataSet (disconnected data) and the database
DataSet: In-memory cache of data that works offline

Let's explore each of these components with practical examples.

Setting Up Your Environment

Before we begin, make sure you have:

Visual Studio installed
A database to connect to (we'll use SQL Server in our examples)
The appropriate NuGet packages (System.Data.SqlClient for SQL Server)

Add the necessary namespaces to your C# file:

csharp
using System;
using System.Data;
using System.Data.SqlClient;

Database Connection

The first step in working with ADO.NET is establishing a connection to your database.

Connection String

A connection string contains the information needed to connect to a database:

csharp
string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

For a local SQL Server Express instance:

csharp
string connectionString = "Server=localhost\\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;";

Creating a Connection

csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        connection.Open();
        Console.WriteLine("Connection opened successfully!");
        
        // Database operations go here
        
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error: {ex.Message}");
    }
    finally
    {
        // Connection is automatically closed when the using block ends
    }
}

Executing SQL Commands

Once connected, you can execute SQL commands against the database.

Simple Query Execution

csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
    string query = "SELECT COUNT(*) FROM Customers";
    
    SqlCommand command = new SqlCommand(query, connection);
    
    connection.Open();
    
    // ExecuteScalar is used for single value results
    int customerCount = (int)command.ExecuteScalar();
    
    Console.WriteLine($"Total customers: {customerCount}");
}

Output:

Total customers: 91

Inserting Data

csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
    string insertQuery = "INSERT INTO Customers (CustomerID, CompanyName) VALUES (@CustomerID, @CompanyName)";
    
    SqlCommand command = new SqlCommand(insertQuery, connection);
    
    // Add parameters to prevent SQL injection
    command.Parameters.AddWithValue("@CustomerID", "ABCDE");
    command.Parameters.AddWithValue("@CompanyName", "New Company Ltd.");
    
    connection.Open();
    
    int rowsAffected = command.ExecuteNonQuery();
    
    Console.WriteLine($"{rowsAffected} row(s) inserted");
}

Output:

1 row(s) inserted

Using SqlDataReader

The SqlDataReader provides a fast, forward-only stream of data from the database.

csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
    string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers";
    
    SqlCommand command = new SqlCommand(query, connection);
    
    connection.Open();
    
    using (SqlDataReader reader = command.ExecuteReader())
    {
        Console.WriteLine("Customer List:");
        Console.WriteLine("--------------------------------------------------");
        
        while (reader.Read())
        {
            string customerId = reader["CustomerID"].ToString();
            string companyName = reader["CompanyName"].ToString();
            string contactName = reader["ContactName"].ToString();
            
            Console.WriteLine($"{customerId} | {companyName} | {contactName}");
        }
    }
}

Output:

Customer List:
--------------------------------------------------
ALFKI | Alfreds Futterkiste | Maria Anders
ANATR | Ana Trujillo Emparedados y helados | Ana Trujillo
ANTON | Antonio Moreno Taquería | Antonio Moreno
...

Working with Parameters

Parameters help prevent SQL injection and improve performance.

csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
    string query = "SELECT * FROM Customers WHERE Country = @Country";
    
    SqlCommand command = new SqlCommand(query, connection);
    command.Parameters.AddWithValue("@Country", "Germany");
    
    connection.Open();
    
    using (SqlDataReader reader = command.ExecuteReader())
    {
        Console.WriteLine("Customers from Germany:");
        Console.WriteLine("--------------------------------------------------");
        
        while (reader.Read())
        {
            Console.WriteLine($"{reader["CustomerID"]} - {reader["CompanyName"]}");
        }
    }
}

Working with DataSet and DataAdapter

While SqlDataReader provides a connected, forward-only access to data, DataSet offers a disconnected approach.

csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
    string query = "SELECT * FROM Customers";
    
    // Create the adapter that will fill the dataset
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    
    // Create and fill the DataSet
    DataSet dataSet = new DataSet();
    adapter.Fill(dataSet, "Customers");
    
    // Access the Customers table from the DataSet
    DataTable customerTable = dataSet.Tables["Customers"];
    
    Console.WriteLine($"Retrieved {customerTable.Rows.Count} customers");
    
    // Loop through the first 5 rows
    for (int i = 0; i < Math.Min(5, customerTable.Rows.Count); i++)
    {
        DataRow row = customerTable.Rows[i];
        Console.WriteLine($"{row["CustomerID"]} - {row["CompanyName"]} ({row["Country"]})");
    }
}

Output:

Retrieved 91 customers
ALFKI - Alfreds Futterkiste (Germany)
ANATR - Ana Trujillo Emparedados y helados (Mexico)
ANTON - Antonio Moreno Taquería (Mexico)
AROUT - Around the Horn (UK)
BERGS - Berglunds snabbköp (Sweden)

Transactions

Transactions ensure that a group of database operations are executed as a single unit.

csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    // Begin a transaction
    SqlTransaction transaction = connection.BeginTransaction();
    
    try
    {
        // Create the command and assign the transaction
        SqlCommand command = connection.CreateCommand();
        command.Transaction = transaction;
        
        // First command
        command.CommandText = "UPDATE Products SET UnitsInStock = UnitsInStock - 1 WHERE ProductID = 1";
        command.ExecuteNonQuery();
        
        // Second command
        command.CommandText = "INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES (10248, 1, 1)";
        command.ExecuteNonQuery();
        
        // If we get here, commit the transaction
        transaction.Commit();
        Console.WriteLine("Transaction committed successfully.");
    }
    catch (Exception ex)
    {
        // If there's an error, roll back the transaction
        transaction.Rollback();
        Console.WriteLine($"Transaction rolled back: {ex.Message}");
    }
}

Stored Procedures

Stored procedures are precompiled SQL statements stored in the database.

csharp
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // Create a command for the stored procedure
    SqlCommand command = new SqlCommand("GetCustomersByCountry", connection);
    command.CommandType = CommandType.StoredProcedure;
    
    // Add parameters
    command.Parameters.AddWithValue("@CountryName", "France");
    
    connection.Open();
    
    using (SqlDataReader reader = command.ExecuteReader())
    {
        Console.WriteLine("Customers from France:");
        
        while (reader.Read())
        {
            Console.WriteLine($"{reader["CustomerID"]} - {reader["CompanyName"]}");
        }
    }
}

Real-World Application: Customer Management System

Let's build a simple customer management system that demonstrates ADO.NET in a practical context:

csharp
class CustomerManager
{
    private readonly string _connectionString;
    
    public CustomerManager(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    public List<Customer> GetAllCustomers()
    {
        List<Customer> customers = new List<Customer>();
        
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            string query = "SELECT CustomerID, CompanyName, ContactName, Country FROM Customers";
            SqlCommand command = new SqlCommand(query, connection);
            
            connection.Open();
            
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Customer customer = new Customer
                    {
                        Id = reader["CustomerID"].ToString(),
                        CompanyName = reader["CompanyName"].ToString(),
                        ContactName = reader["ContactName"].ToString(),
                        Country = reader["Country"].ToString()
                    };
                    
                    customers.Add(customer);
                }
            }
        }
        
        return customers;
    }
    
    public bool AddCustomer(Customer customer)
    {
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            string query = @"INSERT INTO Customers 
                           (CustomerID, CompanyName, ContactName, Country) 
                           VALUES (@ID, @CompanyName, @ContactName, @Country)";
            
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@ID", customer.Id);
            command.Parameters.AddWithValue("@CompanyName", customer.CompanyName);
            command.Parameters.AddWithValue("@ContactName", customer.ContactName);
            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 query = @"UPDATE Customers 
                           SET CompanyName = @CompanyName, 
                               ContactName = @ContactName,
                               Country = @Country
                           WHERE CustomerID = @ID";
            
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@ID", customer.Id);
            command.Parameters.AddWithValue("@CompanyName", customer.CompanyName);
            command.Parameters.AddWithValue("@ContactName", customer.ContactName);
            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))
        {
            string query = "DELETE FROM Customers WHERE CustomerID = @ID";
            
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@ID", customerId);
            
            connection.Open();
            
            int result = command.ExecuteNonQuery();
            return result > 0;
        }
    }
}

class Customer
{
    public string Id { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string Country { get; set; }
}

Example usage:

csharp
static void Main(string[] args)
{
    string connectionString = "Server=localhost\\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;";
    CustomerManager manager = new CustomerManager(connectionString);
    
    // Get all customers
    List<Customer> customers = manager.GetAllCustomers();
    Console.WriteLine($"Total customers: {customers.Count}");
    
    // Add a new customer
    Customer newCustomer = new Customer
    {
        Id = "CODER",
        CompanyName = "Code Academy",
        ContactName = "John Developer",
        Country = "USA"
    };
    
    bool added = manager.AddCustomer(newCustomer);
    Console.WriteLine($"Customer added: {added}");
    
    // Update the customer
    newCustomer.Country = "Canada";
    bool updated = manager.UpdateCustomer(newCustomer);
    Console.WriteLine($"Customer updated: {updated}");
    
    // Delete the customer
    bool deleted = manager.DeleteCustomer("CODER");
    Console.WriteLine($"Customer deleted: {deleted}");
}

Best Practices

Always use parameters to prevent SQL injection attacks
Dispose of connections properly using the using statement
Keep connections open for as short a time as possible
Use stored procedures for complex database operations
Implement error handling for database operations
Consider connection pooling for high-traffic applications
Avoid storing sensitive information in connection strings in code

Summary

ADO.NET provides a powerful set of tools for accessing and manipulating data in C# applications. We've covered:

Establishing database connections
Executing SQL commands
Reading data with DataReader
Working with parameters to prevent SQL injection
Using DataSet for disconnected operations
Implementing transactions
Calling stored procedures
Building a practical customer management system

With these fundamentals, you can start building data-driven applications that interact with various database systems.

Additional Resources

Exercises

Create an application that connects to a database and displays a list of products in a console application.
Modify the customer management system to include validation before adding or updating customers.
Implement a transaction that handles order processing (creating an order and updating inventory).
Build a data access layer that uses stored procedures instead of direct SQL queries.
Create a small application that demonstrates the differences between connected and disconnected data access.

Happy coding!

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Why Use ADO.NET?​

ADO.NET Architecture​

Setting Up Your Environment​

Database Connection​

Connection String​

Creating a Connection​

Executing SQL Commands​

Simple Query Execution​

Inserting Data​

Using SqlDataReader​

Working with Parameters​

Working with DataSet and DataAdapter​

Transactions​

Stored Procedures​

Real-World Application: Customer Management System​

Best Practices​

Summary​

Additional Resources​

Exercises​

Introduction

Why Use ADO.NET?

ADO.NET Architecture

Setting Up Your Environment

Database Connection

Connection String

Creating a Connection

Executing SQL Commands

Simple Query Execution

Inserting Data

Using SqlDataReader

Working with Parameters

Working with DataSet and DataAdapter

Transactions

Stored Procedures

Real-World Application: Customer Management System

Best Practices

Summary

Additional Resources

Exercises