Data Access with ADO.NET

ADO.NET is the foundational data-access technology in .NET for talking to databases such as SQL Server, PostgreSQL, MySQL, and SQLite. It gives you fine-grained control through a few core objects: a connection (the link to the database), a command (the SQL you want to run), and a data reader (a fast, forward-only way to read results). ADO.NET works on modern, cross-platform .NET. Higher-level tools like Entity Framework Core build on top of these same ideas, so understanding ADO.NET helps you understand the whole stack.

The core objects

• DbConnection — opens a session with the database.
• DbCommand — holds a SQL statement and its parameters.
• DbDataReader — streams rows back, one at a time.

Each database has a provider (e.g. Microsoft.Data.SqlClient for SQL Server, Npgsql for PostgreSQL), but the pattern is the same.

Reading data safely

The example below uses using blocks so connections and commands are always closed, even if an error occurs:

using Microsoft.Data.SqlClient; // SQL Server provider package

string connectionString = "Server=localhost;Database=School;Trusted_Connection=True;";

// 'using' guarantees the connection is closed and disposed at the end of the block.
using var connection = new SqlConnection(connectionString);
connection.Open();

// NEVER build SQL by joining strings with user input. Use parameters (see below).
string sql = "SELECT Name, Marks FROM Students WHERE Marks >= @minMarks";
using var command = new SqlCommand(sql, connection);

// A parameter: the value is sent separately, preventing SQL injection.
command.Parameters.AddWithValue("@minMarks", 40);

using var reader = command.ExecuteReader();
while (reader.Read()) // advance to the next row; returns false at the end
{
    string name = reader.GetString(0);  // column 0 = Name
    int marks = reader.GetInt32(1);     // column 1 = Marks
    Console.WriteLine($"{name}: {marks}");
}

ExecuteReader is for queries that return rows. Each call to reader.Read() moves to the next row.

Parameterized queries — your defence against SQL injection

The single most important habit in data access: never concatenate user input into SQL. Use parameters so the database treats input as data, not code:

// WRONG — vulnerable to SQL injection:
// string sql = "SELECT * FROM Users WHERE Name = '" + userInput + "'";

// RIGHT — the value is bound as a parameter:
string sql = "SELECT * FROM Users WHERE Name = @name";
using var cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@name", userInput); // safe, even if userInput contains quotes

A malicious input like ' OR '1'='1 is harmless when passed as a parameter.

Inserting, updating, and deleting

For statements that change data (and return no rows), use ExecuteNonQuery, which reports how many rows were affected:

string insert = "INSERT INTO Students (Name, Marks) VALUES (@name, @marks)";
using var cmd = new SqlCommand(insert, connection);
cmd.Parameters.AddWithValue("@name", "Meera");
cmd.Parameters.AddWithValue("@marks", 88);

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

To read a single value (like a count), use ExecuteScalar:

using var cmd = new SqlCommand("SELECT COUNT(*) FROM Students", connection);
int count = (int)cmd.ExecuteScalar()!;
Console.WriteLine($"Total students: {count}");

ADO.NET vs Entity Framework Core

ADO.NET is low-level: you write SQL by hand and map columns yourself, which gives maximum control and performance. Entity Framework Core (EF Core) is an Object-Relational Mapper that lets you work with C# objects instead of raw SQL. Many real apps use EF Core for everyday work and drop to ADO.NET for performance-critical queries. Learning ADO.NET first makes EF Core easier to understand.

Common mistakes

• Concatenating user input into SQL. This causes SQL injection. Always use parameters.
• Forgetting to close connections. Use using blocks; connections are scarce resources and leaks cause failures under load.
• Reading columns by the wrong index or type. GetInt32(0) on a text column throws. Match the index and type to your SELECT list.
• Opening a connection too early and holding it open. Open late, close early; keep the open window short.
• Storing connection strings in code. Keep them in configuration, not hard-coded, and never commit secrets.

FAQ

Do I still need ADO.NET if EF Core exists? Knowing it is valuable: EF Core is built on the same concepts, and you'll occasionally need raw SQL for performance.

Which provider package do I install? It depends on your database — Microsoft.Data.SqlClient for SQL Server, Npgsql for PostgreSQL, MySqlConnector for MySQL, Microsoft.Data.Sqlite for SQLite.

Keep learning

• Hub: Learn .NET
• Previous: Exception Handling in C#
• Related: Introduction to ASP.NET Core

Connect apps to real databases with a mentor in Jalgaon — join the waitlist for the Infoplanet .NET course at /courses/dotnet.