.NET Development

ADO.NET: The Foundation of Data Access in .NET

ActiveX Data Objects for .NET (ADO.NET) is a set of classes in the .NET Framework that exposes data access services to the .NET programmer. It provides a consistent mechanism for accessing data from various data sources, whether relational or non-relational. ADO.NET is designed to work with data sources as resources, including XML, and needs to be able to manage data that is independent of the data source.

Key benefits include:

• Disconnected Data Access: Enables applications to process data independently of the data source, improving scalability and performance.
• Provider Model: Supports multiple data sources through a unified interface.
• XML Integration: Seamlessly works with XML data.

Core Components of ADO.NET

ADO.NET is built around a set of core objects that facilitate data interaction. The primary components are divided into two main categories: Connection-Oriented and Disconnected.

Data Providers

ADO.NET uses a provider model, meaning specific classes exist for different data sources. You'll typically import the namespace corresponding to your data source (e.g., System.Data.SqlClient for SQL Server, System.Data.OleDb for OLE DB providers).

Common Providers:

• SqlClient: For SQL Server.
• OleDb: For OLE DB compliant data sources (e.g., Access, Excel).
• Odbc: For ODBC compliant data sources.
• OracleClient: For Oracle databases (less common now, often replaced by third-party or OLE DB/ODBC).

Executing Commands

Executing SQL commands is a fundamental operation. You'll typically create a Connection, then a Command object, associate them, and then execute the command.

Executing Non-Query Commands (INSERT, UPDATE, DELETE)

Use the ExecuteNonQuery() method for commands that don't return a result set, such as INSERT, UPDATE, or DELETE statements. It returns the number of rows affected.

using System.Data.SqlClient;

// ...

string connectionString = "Your_Connection_String_Here";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string query = "UPDATE Products SET Price = Price * 1.1 WHERE Category = 'Electronics';";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        int rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine($"{rowsAffected} rows updated.");
    }
}
        

Retrieving Data

There are two primary ways to retrieve data: using a DataReader for forward-only, read-only access, or using a DataAdapter to populate a DataSet or DataTable.

1. Using SqlDataReader (Efficient for forward-only access)

The DataReader is excellent for performance when you just need to read through a result set sequentially.

using System.Data.SqlClient;

// ...

string connectionString = "Your_Connection_String_Here";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string query = "SELECT ProductID, ProductName, Price FROM Products WHERE Price > 100;";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                int productId = reader.GetInt32(0); // GetInt32(reader.GetOrdinal("ProductID")) is safer
                string productName = reader.GetString(1);
                decimal price = reader.GetDecimal(2);
                Console.WriteLine($"ID: {productId}, Name: {productName}, Price: {price:C}");
            }
        }
    }
}
        

2. Using SqlDataAdapter and DataSet/DataTable (For disconnected data manipulation)

The DataAdapter and DataSet are powerful for scenarios where you need to fetch data, modify it on the client, and then send the changes back to the database.

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

// ...

string connectionString = "Your_Connection_String_Here";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers;";
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    DataSet dataSet = new DataSet();

    adapter.Fill(dataSet, "Customers"); // Fills the DataSet with a DataTable named "Customers"

    DataTable customersTable = dataSet.Tables["Customers"];
    
    // You can now manipulate customersTable here
    foreach (DataRow row in customersTable.Rows)
    {
        Console.WriteLine($"{row["CustomerID"]} - {row["CompanyName"]}");
    }

    // To save changes back (requires more setup with InsertCommand, UpdateCommand, DeleteCommand)
    // adapter.Update(dataSet, "Customers");
}
        

Transactions

Ensuring data integrity is crucial. ADO.NET provides robust support for database transactions.

A transaction allows you to group a series of operations into a single logical unit of work. If any operation fails, the entire transaction can be rolled back, leaving the database in its original state.

using System.Data.SqlClient;

// ...

string connectionString = "Your_Connection_String_Here";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction transaction = null;

    try
    {
        transaction = connection.BeginTransaction();

        // Command 1
        string query1 = "UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;";
        using (SqlCommand command1 = new SqlCommand(query1, connection, transaction))
        {
            command1.ExecuteNonQuery();
        }

        // Command 2
        string query2 = "UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;";
        using (SqlCommand command2 = new SqlCommand(query2, connection, transaction))
        {
            command2.ExecuteNonQuery();
        }

        // If all commands succeed, commit the transaction
        transaction.Commit();
        Console.WriteLine("Transaction committed successfully.");
    }
    catch (Exception ex)
    {
        // If any command fails, roll back the transaction
        if (transaction != null)
        {
            transaction.Rollback();
            Console.WriteLine($"Transaction rolled back: {ex.Message}");
        }
    }
}
        

Performance Considerations

Optimizing ADO.NET usage is key to building performant applications.

• Use SqlDataReader: When you only need to read data sequentially, the DataReader is significantly more efficient than a DataSet.
• Minimize DataSet Usage: DataSets are powerful but memory-intensive. Only use them when you require disconnected data manipulation or complex caching.
• Parameterize Queries: Always use parameterized queries to prevent SQL injection vulnerabilities and to improve query plan caching.
• Close Connections Promptly: Use using statements to ensure connections and commands are disposed of properly, releasing resources back to the pool.
• Connection Pooling: ADO.NET uses connection pooling by default. This reuses existing database connections instead of opening new ones for every request, greatly improving performance.
• Select Only Necessary Columns: Avoid `SELECT *`. Specify only the columns you need to reduce data transfer and processing overhead.