ASP.NET Core Tutorials

Introduction to ADO.NET in ASP.NET Core

ADO.NET is a foundational set of .NET classes that provide access to data sources such as SQL Server and XML. It is a technology that allows developers to write code that interacts with databases. In ASP.NET Core, you can leverage ADO.NET for direct data access, offering a low-level and efficient way to manage database operations.

While Entity Framework Core (EF Core) provides a higher-level abstraction for data access, understanding ADO.NET is crucial for scenarios requiring fine-grained control over SQL queries, performance optimization, or working with specific database features not fully abstracted by ORMs.

Key ADO.NET Components

The core components of ADO.NET that you'll commonly use include:

• SqlConnection: Represents a connection to a SQL Server database.
• SqlCommand: Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.
• SqlDataReader: Provides a way of reading a forward-only stream of rows from a SQL Server data source.
• SqlDataAdapter: Represents a set of commands and a connection that are used to fill a DataSet and write or append data from a DataSet back to the data source. (Often used with DataSet, less common in modern API-centric ASP.NET Core development compared to SqlDataReader).
• SqlParameter: Represents a parameter to a SqlCommand and optionally its mapping to/from the data set. Crucial for preventing SQL injection.

Connecting to a Database

Establishing a connection is the first step. You'll need a connection string that specifies the server, database name, and authentication details. It's best practice to store connection strings in your application's configuration, typically in appsettings.json.

Example appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=your_server_name;Database=your_database_name;User Id=your_user;Password=your_password;"
  }
}

In your ASP.NET Core application, you can inject IConfiguration to retrieve this string.

public class MyService
{
    private readonly IConfiguration _configuration;

    public MyService(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    // ... rest of your service
}

using Microsoft.Data.SqlClient; // For .NET Core, use Microsoft.Data.SqlClient

// ... inside a method

string connectionString = _configuration.GetConnectionString("DefaultConnection");
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // Open the connection
    connection.Open();
    // ... perform database operations
} // Connection is automatically closed and disposed here

Executing Commands

Once connected, you can execute SQL commands using SqlCommand.

Executing a Query with SqlDataReader

This is common for retrieving data.

public List<string> GetProductNames()
{
    var productNames = new List<string>();
    string connectionString = _configuration.GetConnectionString("DefaultConnection");

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        string sql = "SELECT Name FROM Products WHERE IsActive = 1"; // Example query

        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    // Safely get string value, handling DBNull
                    string productName = reader["Name"] != DBNull.Value ? reader["Name"].ToString() : string.Empty;
                    productNames.Add(productName);
                }
            }
        }
    }
    return productNames;
}

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

Use ExecuteNonQuery() for commands that don't return a result set.

public int AddProduct(string name, decimal price)
{
    int rowsAffected = 0;
    string connectionString = _configuration.GetConnectionString("DefaultConnection");

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        string sql = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)";

        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            // Add parameters to prevent SQL injection
            command.Parameters.AddWithValue("@Name", name);
            command.Parameters.AddWithValue("@Price", price);

            rowsAffected = command.ExecuteNonQuery();
        }
    }
    return rowsAffected;
}

Using Parameters to Prevent SQL Injection

It is absolutely critical to use parameters when building SQL queries that include user-supplied input. Never directly concatenate user input into SQL strings.

The example above for AddProduct demonstrates using SqlParameter (via AddWithValue, though explicit type specification is often preferred for clarity and performance).

// More explicit parameter adding:
command.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 100).Value = name;
command.Parameters.Add("@Price", System.Data.SqlDbType.Decimal).Value = price;

Executing Stored Procedures

You can also execute stored procedures.

public string GetProductPrice(int productId)
{
    string price = null;
    string connectionString = _configuration.GetConnectionString("DefaultConnection");

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        string storedProcedureName = "GetProductPriceById"; // Assume this SP exists

        using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
        {
            command.CommandType = System.Data.CommandType.StoredProcedure; // Indicate it's an SP

            command.Parameters.AddWithValue("@ProductId", productId);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    price = reader["Price"].ToString();
                }
            }
        }
    }
    return price;
}

Advantages of ADO.NET

• Performance: Direct SQL execution can be highly performant when optimized.
• Control: Provides fine-grained control over SQL statements and data retrieval.
• Flexibility: Works with any ADO.NET compliant data provider, not just SQL Server.
• Learning Foundation: Understanding ADO.NET is beneficial even when using ORMs like EF Core.

When to Use ADO.NET vs. EF Core

• Use ADO.NET when:
  • You need maximum control over SQL queries.
  • You are optimizing for very specific performance scenarios.
  • You are working with legacy systems or require access to database-specific features not abstracted by ORMs.
  • You are building data access layers for microservices where a full ORM might be overkill.
• Use EF Core when:
  • Rapid development is a priority.
  • You prefer an object-oriented approach to data access (LINQ).
  • Database abstraction is important for portability.
  • You want features like change tracking, migrations, and lazy loading.