Home / ASP.NET Core / Data Access / ADO.NET

ADO.NET Data Access in ASP.NET Core

This document provides a comprehensive guide to using ADO.NET for data access within ASP.NET Core applications. While Entity Framework Core is often the preferred choice for modern .NET development, ADO.NET remains a powerful and efficient option, especially when fine-grained control over SQL execution or performance optimization is critical.

Introduction to ADO.NET

ADO.NET (ActiveX Data Objects for .NET) is a set of classes that expose data access services to the .NET Framework. It provides a managed set of classes for interacting with data sources. The core components of ADO.NET include:

Connection: Establishes a connection to the data source.
Command: Represents a SQL statement or stored procedure to be executed against the data source.
DataReader: Provides a forward-only, read-only stream of data from the data source. Highly efficient for retrieving large datasets.
DataAdapter: Bridges the gap between a DataSet and a data source, used to fill a DataSet and detect/resolve concurrency issues.
DataSet: An in-memory representation of data, consisting of tables, columns, and rows. Useful for disconnected data scenarios.

Connecting to a Data Source

To interact with a database, you first need to establish a connection. The specific connection string and provider will depend on your database system (e.g., SQL Server, PostgreSQL, MySQL).

C# Example: Establishing a SQL Server Connection

                using System.Data.SqlClient;

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

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Connection is now open and ready for commands
    Console.WriteLine("Connection opened successfully!");
}
            

It's crucial to manage connection strings securely, typically by storing them in the application's configuration (e.g., appsettings.json) and accessing them via dependency injection.

Configuring Connection Strings

In ASP.NET Core, connection strings are usually configured in appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"
  }
}

And accessed in your C# code:

using Microsoft.Extensions.Configuration;

public class Startup
{
    public IConfiguration Configuration { get; }

    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public void ConfigureServices(IServiceCollection services)
    {
        // Registering the connection string as a singleton service
        services.AddSingleton(new SqlConnection(Configuration.GetConnectionString("DefaultConnection")));
    }

    // ... other configuration methods
}

Executing SQL Commands

Once a connection is established, you can execute SQL commands using the SqlCommand class.

Using SqlDataReader for Efficient Reads

The SqlDataReader is ideal for retrieving data row by row without loading the entire dataset into memory. This is highly beneficial for performance, especially with large tables.

C# Example: Reading Data with SqlDataReader

                using System.Data.SqlClient;

// Assuming 'connection' is an open SqlConnection
string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";

using (SqlCommand command = new SqlCommand(sql, connection))
{
    command.Parameters.AddWithValue("@City", "London"); // Using parameterized query to prevent SQL injection

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            int customerId = reader.GetInt32(0); // GetInt32(columnIndex)
            string companyName = reader.GetString(1); // GetString(columnIndex)

            Console.WriteLine($"ID: {customerId}, Name: {companyName}");
        }
    }
}
            

Tip: Always use parameterized queries (e.g., command.Parameters.AddWithValue()) to prevent SQL injection vulnerabilities.

Executing Non-Query Commands

For operations like INSERT, UPDATE, DELETE, or executing stored procedures that don't return a result set, use ExecuteNonQuery().

C# Example: Inserting Data with ExecuteNonQuery

                using System.Data.SqlClient;

// Assuming 'connection' is an open SqlConnection
string sql = "INSERT INTO Products (ProductName, UnitPrice) VALUES (@Name, @Price)";

using (SqlCommand command = new SqlCommand(sql, connection))
{
    command.Parameters.AddWithValue("@Name", "New Gadget");
    command.Parameters.AddWithValue("@Price", 99.99);

    int rowsAffected = command.ExecuteNonQuery();
    Console.WriteLine($"{rowsAffected} row(s) inserted.");
}
            

Working with DataSets (Disconnected Scenario)

DataSet objects are useful when you need to work with data offline or when you need to perform operations like filtering, sorting, and merging on the client side before committing changes back to the database.

C# Example: Using SqlDataAdapter and DataSet

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

// Assuming 'connection' is an open SqlConnection
string sql = "SELECT OrderID, OrderDate FROM Orders";

using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connection))
{
    DataSet ds = new DataSet();
    adapter.Fill(ds, "Orders"); // Fills the DataSet with data from the "Orders" table

    // Accessing data from the DataSet
    foreach (DataRow row in ds.Tables["Orders"].Rows)
    {
        Console.WriteLine($"Order ID: {row["OrderID"]}, Date: {row["OrderDate"]}");
    }
}
            

Important Note: While DataSet provides flexibility, it can consume more memory than DataReader. Use it judiciously, especially in web applications where scalability is key.

Common Challenges and Best Practices

Connection Pooling: ADO.NET providers typically implement connection pooling by default, which significantly improves performance by reusing established database connections.
Error Handling: Implement robust error handling using try-catch blocks to gracefully manage database exceptions.
Resource Management: Always use using statements for SqlConnection, SqlCommand, and SqlDataReader to ensure resources are properly disposed of.
Performance Tuning: Analyze your SQL queries and execution plans. Optimize queries for performance and consider using stored procedures where appropriate.
Asynchronous Operations: For ASP.NET Core, leverage asynchronous methods (e.g., OpenAsync(), ExecuteReaderAsync()) to keep your application responsive and improve scalability.

Asynchronous ADO.NET Operations

In modern ASP.NET Core applications, asynchronous programming is essential for performance and responsiveness. ADO.NET provides asynchronous counterparts for most of its operations.

C# Example: Asynchronous Data Reading

                using System.Data.SqlClient;
using System.Threading.Tasks;

public async Task ReadDataAsync(SqlConnection connection)
{
    string sql = "SELECT ProductID, ProductName FROM Products";

    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        await connection.OpenAsync(); // Asynchronously open the connection
        using (SqlDataReader reader = await command.ExecuteReaderAsync()) // Asynchronously execute the command
        {
            while (await reader.ReadAsync()) // Asynchronously read rows
            {
                Console.WriteLine($"Product: {reader["ProductName"]}");
            }
        }
    }
}
            

Conclusion

ADO.NET offers a low-level, high-performance way to interact with databases in ASP.NET Core. By understanding its core components and following best practices for security, resource management, and asynchronous programming, you can effectively integrate ADO.NET into your data access strategy.

For more advanced scenarios or when building complex data models, consider exploring Entity Framework Core, which provides a higher level of abstraction.