ADO.NET - Manual Database Access

Level: Beginner

ℹ️ Where This Fits

ADO.NET is the low-level database access technology in .NET. It helps you understand how C# talks to SQL Server before you use higher-level tools like EF Core or Dapper.

ℹ️ What You'll Learn

• What ADO.NET is
• How it compares with EF Core and Dapper
• What SqlConnection, SqlCommand, SqlDataReader, and SqlParameter do
• How to read data manually
• Why parameterized queries prevent SQL injection
• When ADO.NET is useful in real projects

What is ADO.NET?

ADO.NET is a set of .NET classes for talking to databases manually.

With ADO.NET, you write SQL and use C# classes to:

• open a database connection
• run a command
• read rows
• pass parameters safely
• call stored procedures
• handle database errors

It is closer to the database than EF Core.

ADO.NET vs EF Core vs Dapper

Keep these three together in your learning path:

Technology	Meaning	Best Use
ADO.NET	Manual database access	Learn internals, full control, legacy systems
EF Core	Object-based ORM	Normal CRUD, relationships, migrations, business apps
Dapper	SQL-first lightweight ORM	Reports, stored procedures, performance-focused reads

Short version:

ADO.NET = manual database access
EF Core = ORM, object-based database access
Dapper = lightweight ORM, SQL-first database access

Core ADO.NET Classes

Class	Purpose
SqlConnection	Opens connection to SQL Server
SqlCommand	Executes SQL query or stored procedure
SqlDataReader	Reads rows one by one
SqlParameter	Passes values safely to SQL
SqlDataAdapter	Fills DataTable/DataSet, common in older apps

Namespace:

using Microsoft.Data.SqlClient;

Many older examples use:

using System.Data.SqlClient;

For modern .NET projects, Microsoft.Data.SqlClient is commonly preferred.

Basic Read Example

using Microsoft.Data.SqlClient;

var connectionString =
    "Server=localhost;Database=SchoolDb;Trusted_Connection=True;TrustServerCertificate=True";

using var connection = new SqlConnection(connectionString);

var command = new SqlCommand(
    "SELECT Id, Name, ClassNumber FROM Students",
    connection);

connection.Open();

using var reader = command.ExecuteReader();

while (reader.Read())
{
    var id = reader.GetInt32(0);
    var name = reader.GetString(1);
    var classNumber = reader.GetInt32(2);

    Console.WriteLine($"{id} - {name} - Class {classNumber}");
}

The Flow

Create connection string
Create SqlConnection
Create SqlCommand
Open connection
Execute command
Read results
Close/dispose connection

The using statement helps close resources safely.

Parameterized Query

Never build SQL by joining user input into a string.

Bad:

var sql = "SELECT * FROM Students WHERE Name = '" + name + "'";

Good:

var sql = "SELECT Id, Name FROM Students WHERE Name = @Name";

using var command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@Name", name);

Parameters protect against SQL injection.

SQL Injection Example

If user input is directly joined into SQL, a malicious value can change the query meaning.

' OR 1=1 --

Parameterized queries treat input as data, not SQL code.

That is why SqlParameter is important.

Insert Example

var sql = @"
INSERT INTO Students (Name, ClassNumber, ParentEmail)
VALUES (@Name, @ClassNumber, @ParentEmail)";

using var connection = new SqlConnection(connectionString);
using var command = new SqlCommand(sql, connection);

command.Parameters.AddWithValue("@Name", request.Name);
command.Parameters.AddWithValue("@ClassNumber", request.ClassNumber);
command.Parameters.AddWithValue("@ParentEmail", request.ParentEmail);

connection.Open();

var rowsAffected = command.ExecuteNonQuery();

ExecuteNonQuery() is used for insert, update, and delete commands.

Scalar Example

Use ExecuteScalar() when you expect one value.

var sql = "SELECT COUNT(*) FROM Students";

using var connection = new SqlConnection(connectionString);
using var command = new SqlCommand(sql, connection);

connection.Open();

var count = (int)command.ExecuteScalar();

Stored Procedure Example

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

using var connection = new SqlConnection(connectionString);
using var command = new SqlCommand("dbo.GetStudentById", connection);

command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@StudentId", studentId);

connection.Open();

using var reader = command.ExecuteReader();

ADO.NET works very well with stored procedures.

Where ADO.NET is Useful

ADO.NET is useful when:

• maintaining older .NET applications
• learning database fundamentals
• writing very controlled SQL code
• calling stored procedures manually
• debugging what ORM tools do internally

For new business applications, many teams prefer EF Core or Dapper for productivity.

ADO.NET in ASP.NET Core

In ASP.NET Core, do not put ADO.NET code directly inside controllers.

Better structure:

Controller -> Service -> Repository -> ADO.NET

Example:

public class StudentRepository
{
    private readonly string _connectionString;

    public StudentRepository(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("SchoolDb")
            ?? throw new InvalidOperationException("Connection string missing.");
    }
}

Register repository:

builder.Services.AddScoped<StudentRepository>();

Common Mistakes

Mistake	Better Approach
Not closing connections	Use using
Joining user input into SQL	Use parameters
Putting SQL code in controllers	Move it to repository/service
Selecting * always	Select needed columns
Ignoring exceptions	Log and handle failures
Learning only ADO.NET forever	Move to EF Core/Dapper after basics

Practice Task

Create a simple ADO.NET student reader:

1. Create a connection string.
2. Open SqlConnection.
3. Create SqlCommand.
4. Use a parameter @StudentId.
5. Read one student using SqlDataReader.
6. Print name and class number.

Quick Recap

Question	Answer
ADO.NET style?	Manual database access
Opens SQL connection?	SqlConnection
Executes query?	SqlCommand
Reads rows?	SqlDataReader
Prevents SQL injection?	SqlParameter
Higher-level ORM?	EF Core
SQL-first lightweight ORM?	Dapper

🎯 Interview Favourite

Q: What is ADO.NET and when would you use it?

Good Answer: "ADO.NET is the low-level database access API in .NET. It uses classes such as SqlConnection, SqlCommand, SqlDataReader, and SqlParameter to connect to SQL Server, execute SQL, read rows, and pass values safely. It gives full control but requires more manual code than EF Core or Dapper. ADO.NET is useful for understanding database internals, maintaining legacy systems, and calling SQL or stored procedures directly."

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on ADO.NET. Try these prompts:

• "Explain ADO.NET vs EF Core vs Dapper."
• "Show me an ADO.NET SqlDataReader example."
• "Why do SqlParameter values prevent SQL injection?"
• "How should ADO.NET code be organized in ASP.NET Core?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.