ADO.NET: Reading and Writing SchoolDb from C# – Data Access | Lesson 57

Published / updated7 Oct 2026

Introduction

Until now, SchoolApp has worked with data in memory and in CSV files. The real data lives in SchoolDb, the database you built in Module 3. This lesson connects the two for the first time.

ADO.NET is the part of .NET that talks to databases. It is the lowest level: you open a connection, send SQL, and read the answer row by row. Every other tool in this module, Dapper and EF Core, is built on top of it.

By the end of this lesson, you will be able to:

Create a data project and connect it to SchoolDb with a connection string.
Run SQL with ExecuteScalar, ExecuteReader, and ExecuteNonQuery.
Copy rows from a SqlDataReader into C# objects, including NULL values.
Call stored procedures, with input and output parameters.
Stop SQL injection with parameters, and handle errors from SQL Server.

Why this matters

Module 9 builds the same features three ways: ADO.NET, Dapper, and EF Core. Seeing the manual way first shows you what the other two tools do for you, and what they cost.

ADO.NET is also where many real problems start: forgotten parameters let attackers read every row, and connections that are never closed bring a busy server to a stop. Interviewers ask about both, at every level, because both still happen in real projects.

The concept

Example: the building blocks

Think of ADO.NET as a phone call to the school office. You dial the number (open a connection), ask a question (run a command), listen to the answer one line at a time (read with a data reader), and hang up (dispose the connection).

Class	Role in the call	Used for
`SqlConnection`	The phone line	Connects to one database on one server
`SqlCommand`	The question	Holds SQL text or a procedure name
`SqlParameter`	A value in the question	Sends values safely, separate from the SQL
`SqlDataReader`	Listening to the answer	Reads result rows one at a time, forward only
`SqlTransaction`	"Don't write it down yet"	Groups changes so all are kept or none are
`SqlException`	"Sorry, I can't do that"	An error sent back by SQL Server

All of them come from the Microsoft.Data.SqlClient NuGet package. The older System.Data.SqlClient is no longer developed, so new code should not use it.

Example: three ways to run a command

A command runs in one of three ways, depending on what you expect back:

Method	Returns	SMS example
`ExecuteScalarAsync`	One value: the first column of the first row	`SELECT COUNT(*)` of active students
`ExecuteReaderAsync`	A reader over many rows	All students named Singh
`ExecuteNonQueryAsync`	The number of rows changed	`UPDATE` a student's address

Each has an Async version. Use it: the database is I/O, and Lesson 53 showed why waiting should not hold a thread.

Example: the connection pool

Opening a real connection to SQL Server is slow: the network handshake, the sign-in, and the setup all take time. So ADO.NET keeps a connection pool, a set of open connections it reuses.

When your code disposes a connection, it is not really closed; it goes back to the pool. The next OpenAsync with the same connection string gets it back almost instantly. That is why the right habit is to open late, close early, and never keep a connection in a field.

Apply it to the SMS

Step 1: a project for data access

Database code gets its own project, SchoolApp.Data. It references SchoolApp.Core, so it can create Student objects, but Core never references Data. Run these commands from the repository root:

Terminal
dotnet new classlib -n SchoolApp.Data -o src/SchoolApp.Data -f net10.0
dotnet add src/SchoolApp.Data reference src/SchoolApp.Core
dotnet add src/SchoolApp.Data package Microsoft.Data.SqlClient --version 6.1.7
dotnet add src/SchoolApp.Console reference src/SchoolApp.Data
dotnet sln SchoolApp.slnx add src/SchoolApp.Data/SchoolApp.Data.csproj --solution-folder src

Delete the Class1.cs file the template creates. The project file now looks like this:

src/SchoolApp.Data/SchoolApp.Data.csproj
<Project Sdk="Microsoft.NET.Sdk">

  <!-- Lesson 57: the data layer. It knows about SchoolApp.Core; Core never knows about it. -->
  <ItemGroup>
    <ProjectReference Include="..\SchoolApp.Core\SchoolApp.Core.csproj" />
  </ItemGroup>

  <!-- Lesson 57: the SQL Server driver for .NET. 6.1.x is the version EF Core 10 uses (Lesson 59). -->
  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="6.1.7" />
  </ItemGroup>

  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

</Project>

Version 6.1.7 is chosen on purpose. EF Core 10, which arrives in Lesson 59, uses the 6.1 versions, so all three tools in this module share one driver.

Step 2: the connection string

A connection string tells ADO.NET where the database is and how to sign in. The SMS uses Server=localhost;Database=SchoolDb;Trusted_Connection=True;TrustServerCertificate=True;: the local server, the SchoolDb database, and your Windows account.

One class creates every connection, so the string lives in exactly one place:

src/SchoolApp.Data/SqlConnectionFactory.cs
using Microsoft.Data.SqlClient;

namespace SchoolApp.Data;

// Lesson 57: the one place that knows how to reach SchoolDb.
// Every repository asks this class for a new connection instead of building its own.
public class SqlConnectionFactory
{
    // The connection string from the SMS reference: the local default instance, Windows sign-in.
    public const string DefaultConnectionString =
        "Server=localhost;Database=SchoolDb;Trusted_Connection=True;TrustServerCertificate=True;";

    public SqlConnectionFactory(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public string ConnectionString { get; }

    // Uses the SCHOOLDB_CONNECTION environment variable when it is set (for example, for LocalDB),
    // so the connection string can change without changing code.
    public static SqlConnectionFactory FromEnvironment()
    {
        string? fromEnvironment = Environment.GetEnvironmentVariable("SCHOOLDB_CONNECTION");
        return new SqlConnectionFactory(fromEnvironment ?? DefaultConnectionString);
    }

    // A new, closed connection. The caller opens it and disposes it (with using).
    public SqlConnection Create() => new SqlConnection(ConnectionString);
}

If you use LocalDB instead of a full SQL Server, set the SCHOOLDB_CONNECTION environment variable before running. The code stays the same:

Terminal
$env:SCHOOLDB_CONNECTION = "Server=(localdb)\MSSQLLocalDB;Database=SchoolDb;Trusted_Connection=True;TrustServerCertificate=True;"

Never put passwords in source code. Windows sign-in needs none; Module 12 shows where real secrets go.

Step 3: the repository

The repository holds four operations: count active students, find one student, search by last name, and get a class list from a stored procedure.

Show src/SchoolApp.Data/AdoNet/AdoNetStudentRepository.cs

src/SchoolApp.Data/AdoNet/AdoNetStudentRepository.cs
using System.Data;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;

namespace SchoolApp.Data.AdoNet;

// Lesson 57: plain ADO.NET. Every step is written by hand: open a connection, build a command,
// add parameters, run it, and copy each column of each row into a C# object.
public class AdoNetStudentRepository
{
    private const string SelectStudent = """
        SELECT Id, AdmissionNumber, FirstName, LastName, DateOfBirth, Gender,
               AdmissionDate, Address, Status, CreatedAt, CreatedBy, UpdatedAt, UpdatedBy
        FROM dbo.Students
        """;

    private readonly SqlConnectionFactory _connections;

    public AdoNetStudentRepository(SqlConnectionFactory connections)
    {
        _connections = connections;
    }

    // ExecuteScalar: returns the first column of the first row. Made for COUNT(*).
    public async Task<int> CountActiveAsync(CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);

        await using SqlCommand command = new SqlCommand(
            "SELECT COUNT(*) FROM dbo.Students WHERE Status = 'Active' AND IsDeleted = 0;", connection);

        object? count = await command.ExecuteScalarAsync(cancellationToken);
        return (int)count!;
    }

    // ExecuteReader: rows, read one at a time. @AdmissionNumber is a parameter: its value travels
    // to SQL Server separately from the SQL text, so it can never change what the query does.
    public async Task<Student?> GetByAdmissionNumberAsync(string admissionNumber, CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);

        await using SqlCommand command = new SqlCommand(
            SelectStudent + " WHERE AdmissionNumber = @AdmissionNumber AND IsDeleted = 0;", connection);
        command.Parameters.Add("@AdmissionNumber", SqlDbType.VarChar, 15).Value = admissionNumber;

        await using SqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);
        if (await reader.ReadAsync(cancellationToken))
        {
            return MapStudent(reader);
        }
        return null;  // no row: no such student
    }

    public async Task<List<Student>> SearchByLastNameAsync(string lastName, CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);

        await using SqlCommand command = new SqlCommand(
            SelectStudent + " WHERE LastName = @LastName AND IsDeleted = 0 ORDER BY Id;", connection);
        command.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastName;

        List<Student> students = new List<Student>();
        await using SqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            students.Add(MapStudent(reader));
        }
        return students;
    }

    // A stored procedure: CommandType.StoredProcedure, and the command text is only its name.
    public async Task<List<ClassListEntryDto>> GetClassListAsync(int classId, string sectionName, CancellationToken cancellationToken = default)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync(cancellationToken);

        await using SqlCommand command = new SqlCommand("dbo.usp_GetClassList", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@ClassId", SqlDbType.Int).Value = classId;
        command.Parameters.Add("@SectionName", SqlDbType.VarChar, 2).Value = sectionName;

        List<ClassListEntryDto> entries = new List<ClassListEntryDto>();
        await using SqlDataReader reader = await command.ExecuteReaderAsync(cancellationToken);
        while (await reader.ReadAsync(cancellationToken))
        {
            entries.Add(new ClassListEntryDto(
                reader.GetInt32(reader.GetOrdinal("EnrollmentId")),
                reader.GetInt32(reader.GetOrdinal("StudentId")),
                reader.GetString(reader.GetOrdinal("AdmissionNumber")),
                reader.GetString(reader.GetOrdinal("FullName")),
                reader.GetInt16(reader.GetOrdinal("RollNumber"))));  // SMALLINT is short in C#
        }
        return entries;
    }

    // Copies one row into a Student. Columns are found by name, so their order in the SELECT does not matter.
    private static Student MapStudent(SqlDataReader reader)
    {
        Student student = new Student(
            reader.GetString(reader.GetOrdinal("AdmissionNumber")),
            reader.GetString(reader.GetOrdinal("FirstName")),
            reader.GetString(reader.GetOrdinal("LastName")),
            reader.GetFieldValue<DateOnly>(reader.GetOrdinal("DateOfBirth")),
            (Gender)reader.GetString(reader.GetOrdinal("Gender"))[0]);  // "M" becomes Gender.Male

        student.Id = reader.GetInt32(reader.GetOrdinal("Id"));
        student.AdmissionDate = reader.GetFieldValue<DateOnly>(reader.GetOrdinal("AdmissionDate"));
        student.Status = Enum.Parse<StudentStatus>(reader.GetString(reader.GetOrdinal("Status")));
        student.CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt"));

        // A NULL column arrives as DBNull, so nullable columns are checked first.
        int address = reader.GetOrdinal("Address");
        student.Address = reader.IsDBNull(address) ? null : reader.GetString(address);
        int createdBy = reader.GetOrdinal("CreatedBy");
        student.CreatedBy = reader.IsDBNull(createdBy) ? null : reader.GetInt32(createdBy);
        int updatedAt = reader.GetOrdinal("UpdatedAt");
        student.UpdatedAt = reader.IsDBNull(updatedAt) ? null : reader.GetDateTime(updatedAt);
        int updatedBy = reader.GetOrdinal("UpdatedBy");
        student.UpdatedBy = reader.IsDBNull(updatedBy) ? null : reader.GetInt32(updatedBy);

        return student;
    }
}

Every method follows the same pattern: create a connection, open it, build a command, run it. The await using lines dispose the reader, the command, and the connection when the method ends, even if an exception is thrown. Disposing returns the connection to the pool.

Example: copying a row into an object

MapStudent shows the real work of ADO.NET. For every column, you find its position with GetOrdinal, read it with the right method, and convert it to the C# type:

SQL column	Read with	C# property
`Id INT`	`GetInt32`	`int Id`
`DateOfBirth DATE`	`GetFieldValue<DateOnly>`	`DateOnly DateOfBirth`
`Gender CHAR(1)`	`GetString(...)[0]`, then cast	`Gender Gender` (`'M'` is `Male`)
`Status VARCHAR(12)`	`GetString`, then `Enum.Parse`	`StudentStatus Status`
`Address NVARCHAR(200) NULL`	`IsDBNull` first, then `GetString`	`string? Address`

A NULL column does not arrive as null. It arrives as DBNull.Value, and calling GetString on it throws. That is why every nullable column is checked with IsDBNull first.

This is about 25 lines for one table. The SMS has 22 tables. Lesson 58 shows how Dapper writes this code for you.

Step 4: the program

Show src/SchoolApp.Console/Program.cs

src/SchoolApp.Console/Program.cs
// Lesson 57: ADO.NET. The console program talks to the real SchoolDb for the first time.
// Set SCHOOLDB_CONNECTION to use another server, such as LocalDB (see SqlConnectionFactory).
using System.Data;
using System.Diagnostics;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Data;
using SchoolApp.Data.AdoNet;

SqlConnectionFactory factory = SqlConnectionFactory.FromEnvironment();
AdoNetStudentRepository students = new AdoNetStudentRepository(factory);

// 1. Opening connections: the first is slow, the rest come from the connection pool.
Stopwatch timer = Stopwatch.StartNew();
await using (SqlConnection first = factory.Create())
{
    await first.OpenAsync();
    Console.WriteLine($"Connected to {first.Database} in {timer.ElapsedMilliseconds} ms");
}
timer.Restart();
for (int i = 0; i < 100; i++)
{
    await using SqlConnection connection = factory.Create();
    await connection.OpenAsync();
}
Console.WriteLine($"100 more opens: {timer.ElapsedMilliseconds} ms in total (the pool reuses connections)");

// 2. ExecuteScalar: one value.
Console.WriteLine($"Active students: {await students.CountActiveAsync()}");

// 3. ExecuteReader: one row into a Student object.
Student? kabir = await students.GetByAdmissionNumberAsync("ADM-2017-0003");
Console.WriteLine($"Found: {kabir?.Describe()}, born {kabir?.DateOfBirth:yyyy-MM-dd}, {kabir?.Gender}, {kabir?.Status}");
Console.WriteLine($"Missing: {(await students.GetByAdmissionNumberAsync("ADM-1999-0001")) is null}");

// 4. SQL injection. What a user typed into a search box:
string typed = "x' OR '1'='1";

// NEVER build SQL by joining text: the typed quote ends the string, and OR '1'='1' matches every row.
string unsafeSql = "SELECT COUNT(*) FROM dbo.Students WHERE LastName = '" + typed + "';";
await using (SqlConnection connection = factory.Create())
{
    await connection.OpenAsync();
    await using SqlCommand command = new SqlCommand(unsafeSql, connection);
    Console.WriteLine($"Unsafe SQL: {unsafeSql}");
    Console.WriteLine($"Unsafe search found: {await command.ExecuteScalarAsync()} students");
}

// With a parameter, the same text is only a value: no student has that last name.
Console.WriteLine($"Parameter search found: {(await students.SearchByLastNameAsync(typed)).Count} students");
Console.WriteLine($"Students named Singh: {(await students.SearchByLastNameAsync("Singh")).Count}");

// 5. A stored procedure that returns rows.
foreach (ClassListEntryDto entry in await students.GetClassListAsync(10, "A"))
{
    Console.WriteLine($"  10-A roll {entry.RollNumber}: {entry.FullName} ({entry.AdmissionNumber})");
}

// 6. Writing: ExecuteNonQuery and a procedure with an OUTPUT parameter, inside a transaction
//    that is rolled back at the end, so running the demo again finds SchoolDb unchanged.
await using (SqlConnection connection = factory.Create())
{
    await connection.OpenAsync();
    await using SqlTransaction transaction = connection.BeginTransaction();

    await using SqlCommand update = new SqlCommand(
        "UPDATE dbo.Students SET Address = @Address, UpdatedAt = SYSUTCDATETIME() WHERE AdmissionNumber = @AdmissionNumber;",
        connection, transaction);
    update.Parameters.Add("@Address", SqlDbType.NVarChar, 200).Value = "12 Lake View Road";
    update.Parameters.Add("@AdmissionNumber", SqlDbType.VarChar, 15).Value = "ADM-2017-0003";
    int changed = await update.ExecuteNonQueryAsync();
    Console.WriteLine($"UPDATE changed {changed} row");

    await using SqlCommand pay = new SqlCommand("dbo.usp_RecordFeePayment", connection, transaction);
    pay.CommandType = CommandType.StoredProcedure;
    pay.Parameters.Add("@FeeInvoiceId", SqlDbType.Int).Value = 3;                   // Kabir's invoice
    pay.Parameters.Add("@Amount", SqlDbType.Decimal).Value = 5000m;
    pay.Parameters.Add("@PaymentMode", SqlDbType.VarChar, 12).Value = PaymentMode.Online.ToString();
    pay.Parameters.Add("@TransactionReference", SqlDbType.VarChar, 50).Value = DBNull.Value;  // SQL NULL
    SqlParameter receipt = pay.Parameters.Add("@ReceiptNumber", SqlDbType.VarChar, 20);
    receipt.Direction = ParameterDirection.Output;                                   // filled by the procedure

    await pay.ExecuteNonQueryAsync();
    Console.WriteLine($"Payment recorded, receipt {receipt.Value}");

    await transaction.RollbackAsync();
    Console.WriteLine("Rolled back: the address and the payment were not kept");
}

// 7. Errors from SQL Server arrive as SqlException. Number is the THROW number from the procedure.
await using (SqlConnection connection = factory.Create())
{
    await connection.OpenAsync();
    await using SqlCommand pay = new SqlCommand("dbo.usp_RecordFeePayment", connection);
    pay.CommandType = CommandType.StoredProcedure;
    pay.Parameters.Add("@FeeInvoiceId", SqlDbType.Int).Value = 3;
    pay.Parameters.Add("@Amount", SqlDbType.Decimal).Value = 50000m;               // more than the 45,500 due
    pay.Parameters.Add("@PaymentMode", SqlDbType.VarChar, 12).Value = PaymentMode.Cash.ToString();
    pay.Parameters.Add("@ReceiptNumber", SqlDbType.VarChar, 20).Direction = ParameterDirection.Output;

    try
    {
        await pay.ExecuteNonQueryAsync();
    }
    catch (SqlException ex) when (ex.Number is >= 50010 and <= 50013)
    {
        Console.WriteLine($"Refused by SQL Server: error {ex.Number}, {ex.Message}");
    }
}

Output
Connected to SchoolDb in 472 ms
100 more opens: 3 ms in total (the pool reuses connections)
Active students: 10012
Found: Kabir Singh (student ADM-2017-0003), born 2011-01-30, Male, Active
Missing: True
Unsafe SQL: SELECT COUNT(*) FROM dbo.Students WHERE LastName = 'x' OR '1'='1';
Unsafe search found: 10012 students
Parameter search found: 0 students
Students named Singh: 668
  10-A roll 1: Aarav Sharma (ADM-2017-0001)
  10-A roll 2: Diya Patel (ADM-2017-0002)
  10-A roll 3: Kabir Singh (ADM-2017-0003)
  10-A roll 4: Meera Iyer (ADM-2017-0004)
UPDATE changed 1 row
Payment recorded, receipt RCP-2026-000005
Rolled back: the address and the payment were not kept
Refused by SQL Server: error 50013, Amount is more than the balance due.

The first connection took 472 ms; the next 100 took 3 ms together. That difference is the connection pool. Your timings and receipt number will differ.

Example: SQL injection

Step 4 of the program pretends a user typed x' OR '1'='1 into a search box. The unsafe code joins that text into the SQL. The user's quote closes the string, and OR '1'='1' is true for every row, so the "search" returns all 10,012 students.

This is SQL injection: user input changing what a query does. With more typing, an attacker could read password hashes or delete tables. It is one of the most common security holes in the world, and it is completely preventable.

The fix is a parameter. SearchByLastNameAsync sends @LastName as a separate value, so SQL Server treats the whole text as one last name. No student is called x' OR '1'='1, so it finds 0.

Example: parameters with exact types

The repository adds parameters with an explicit type and size: Parameters.Add("@AdmissionNumber", SqlDbType.VarChar, 15). The shortcut AddWithValue guesses the type from the C# value, and a C# string becomes NVARCHAR.

AdmissionNumber is a VARCHAR column. Comparing it with an NVARCHAR value makes SQL Server convert the column, and that can turn an index seek into a scan, the problem from Lesson 26.

On SchoolDb, finding Kabir with a VARCHAR value read 2 pages. The same search with an NVARCHAR value scanned the whole index: 37 pages.

On a table with millions of rows, that difference is seconds. Matching the column's type avoids it.

Example: output parameters and a transaction

Step 6 calls usp_RecordFeePayment from Lesson 22. @ReceiptNumber has Direction = ParameterDirection.Output, so the procedure fills it, and C# reads receipt.Value after the call.

Both writes run inside a SqlTransaction, and RollbackAsync undoes them at the end. That keeps the demo safe to run again. Lesson 64 covers transactions in code properly.

Notice the receipt number still went up. A sequence (Lesson 22) never gives a number back, even after a rollback, so receipt numbers can have gaps. That is normal and expected.

Example: errors from SQL Server

When the procedure runs THROW 50013, ADO.NET raises a SqlException whose Number is 50013. The filter when (ex.Number is >= 50010 and <= 50013) catches only the business errors you expect.

Any other error, such as a lost network connection, is not caught here. It should reach code that can log it, which comes in Module 12.

Common mistakes

Joining user input into SQL text. It allows SQL injection. Fix: always use parameters, for every value, every time.
Not disposing connections. The pool runs out and new requests wait, then fail with a timeout. Fix: await using on every connection, command, and reader.
Reading a NULL column with GetString. It throws SqlNullValueException. Fix: check IsDBNull first for nullable columns.
Using AddWithValue for VARCHAR columns. The NVARCHAR guess can slow queries down. Fix: Parameters.Add with SqlDbType and size.
Keeping one connection open for the whole program. It blocks pooling and breaks when the network blips. Fix: open late, close early.

In real projects

From 16 years of .NET projects

I once reviewed a school portal where the student search built SQL by joining text. A parent noticed that typing a quote caused an error page, and reported it as a bug.

It was more than a bug. With the right text, anyone could read every student's record. Fixing it took an afternoon: 40 queries changed to use parameters.

Finding it took two years. Since then, any SQL built from user input fails code review, with no exceptions for "internal" screens.

Best practices

Use parameters for every value, with an explicit SqlDbType and size.
Wrap every connection, command, and reader in await using.
Keep the connection string in one place, and never put passwords in code.
Use the Async methods and pass a CancellationToken.
Catch SqlException only for error numbers you can handle.

Recap

ADO.NET is the low-level way .NET talks to databases; Dapper and EF Core build on it.
A connection, a command, and a reader are the core; the pool makes opening connections cheap.
ExecuteScalar returns one value, ExecuteReader rows, and ExecuteNonQuery a count of changes.
NULL arrives as DBNull; check IsDBNull before reading nullable columns.
Parameters stop SQL injection; joining user text into SQL never does.
Procedure errors arrive as SqlException with the THROW number.

Homework

Library module task: Write a single-file app that reads the library tables with ADO.NET. Count the available copies of Wings of Fire, search titles containing "Days" with a LIKE parameter, and list books not yet returned with the fine as on 2026-08-01 (14-day loan, 2 per late day).

A correct result looks like:

Output
Wings of Fire: 2 copies available
Search 'Days': Malgudi Days by R. K. Narayan
Not returned: The Jungle Book, Advika Sharma, due 2026-07-20, 12 days late on 2026-08-01, fine 24

Show the solution

library-assignment/057-library-adonet.cs
// Library homework, Lesson 57: read the library tables in SchoolDb with plain ADO.NET.
// Run with:  dotnet run library-assignment/057-library-adonet.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
using System.Data;
using Microsoft.Data.SqlClient;
using SchoolApp.Data;

SqlConnectionFactory factory = SqlConnectionFactory.FromEnvironment();
await using SqlConnection connection = factory.Create();
await connection.OpenAsync();

// 1. ExecuteScalar with a parameter: available copies of one title.
await using (SqlCommand count = new SqlCommand("""
    SELECT COUNT(*)
    FROM dbo.BookCopies AS c
    JOIN dbo.Books AS b ON b.Id = c.BookId
    WHERE b.Title = @Title AND c.Status = 'Available';
    """, connection))
{
    count.Parameters.Add("@Title", SqlDbType.NVarChar, 200).Value = "Wings of Fire";
    Console.WriteLine($"Wings of Fire: {await count.ExecuteScalarAsync()} copies available");
}

// 2. A LIKE search: the % signs go into the parameter value, never into the SQL text.
await using (SqlCommand search = new SqlCommand(
    "SELECT Title, Author FROM dbo.Books WHERE Title LIKE @Pattern AND IsDeleted = 0 ORDER BY Title;", connection))
{
    search.Parameters.Add("@Pattern", SqlDbType.NVarChar, 202).Value = "%Days%";
    await using SqlDataReader reader = await search.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"Search 'Days': {reader.GetString(0)} by {reader.GetString(1)}");
    }
}

// 3. Books not yet returned, with the fine as on a given date (14-day loan, 2 per late day).
DateOnly asOf = new DateOnly(2026, 8, 1);
await using (SqlCommand open = new SqlCommand("""
    SELECT b.Title, s.FirstName + ' ' + s.LastName AS StudentName, i.DueOn
    FROM dbo.BookIssues AS i
    JOIN dbo.BookCopies AS c ON c.Id = i.BookCopyId
    JOIN dbo.Books AS b ON b.Id = c.BookId
    JOIN dbo.Students AS s ON s.Id = i.StudentId
    WHERE i.ReturnedOn IS NULL
    ORDER BY i.DueOn;
    """, connection))
{
    await using SqlDataReader reader = await open.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        DateOnly dueOn = reader.GetFieldValue<DateOnly>(reader.GetOrdinal("DueOn"));
        int daysLate = Math.Max(0, asOf.DayNumber - dueOn.DayNumber);
        Console.WriteLine($"Not returned: {reader.GetString(0)}, {reader.GetString(1)}, due {dueOn:yyyy-MM-dd}, " +
                          $"{daysLate} days late on {asOf:yyyy-MM-dd}, fine {daysLate * 2}");
    }
}

The #:project line lets a single-file app use SqlConnectionFactory, so the homework connects the same way as SchoolApp. For LIKE, the % signs go into the parameter value, never into the SQL.

Quiz

Which method fits SELECT COUNT(*) FROM dbo.Students?
1. ExecuteNonQueryAsync
2. ExecuteScalarAsync
3. ExecuteReaderAsync
4. OpenAsync
Show answer
Answer: B. ExecuteScalarAsync ExecuteScalar returns the first column of the first row: exactly one value.
What does ExecuteNonQueryAsync return for an UPDATE?
1. The updated rows
2. The number of rows changed
3. Nothing
4. The new Id
Show answer
Answer: B. The number of rows changed It returned 1 when Kabir's address was changed.
How does a NULL column arrive in a SqlDataReader?
1. As null
2. As an empty string
3. As DBNull.Value
4. As 0
Show answer
Answer: C. As DBNull.Value Check IsDBNull before calling GetString or GetInt32 on a nullable column.
What stops SQL injection?
1. Removing quotes from the input
2. Parameters
3. Using a stored procedure name in the text
4. A longer timeout
Show answer
Answer: B. Parameters A parameter is sent as a value, so it can never change the SQL.
Why is the second OpenAsync so much faster than the first?
1. The data is cached
2. The connection comes back from the pool
3. SQL Server skips sign-in for 10 minutes
4. The second one is not async
Show answer
Answer: B. The connection comes back from the pool Disposing returns the connection to the pool; opening takes it back.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: Data Access interview questions.

Fresher (0-2 yrs)

F What is ADO.NET, and what are its main classes for SQL Server? concept

ADO.NET is the low-level part of .NET for talking to databases. For SQL Server, the main classes come from Microsoft.Data.SqlClient: SqlConnection (the connection), SqlCommand (SQL text or a procedure name), SqlParameter (a value), SqlDataReader (rows, forward only), SqlTransaction, and SqlException. Dapper and EF Core are built on top of it.

See the example · Permalink

F What is the difference between ExecuteScalar, ExecuteReader, and ExecuteNonQuery? concept

ExecuteScalar returns one value, the first column of the first row, such as a COUNT(*). ExecuteReader returns a reader over many rows. ExecuteNonQuery runs INSERT, UPDATE, or DELETE and returns the number of rows changed. Each has an Async version that should be preferred.

See the example · Permalink

F What is SQL injection, and how do you prevent it in C#? concept

SQL injection is user input changing what a query does, because the input was joined into the SQL text. Typing x' OR '1'='1 into an unsafe student search returned all 10,012 students. Prevent it with parameters: the value travels separately from the SQL, so it is only ever a value.

See the example · Permalink

Mid (3-5 yrs)

M What is connection pooling, and how should code use connections because of it? concept

ADO.NET keeps a pool of open connections per connection string. Disposing a connection returns it to the pool, and the next open takes it back almost instantly: 100 opens took 3 ms after a first open of 472 ms. So open as late as possible, dispose as early as possible, and never keep a connection in a field.

See the example · Permalink

M How do you read a nullable column with a SqlDataReader? coding

A NULL column arrives as DBNull.Value, not null, and calling GetString or GetInt32 on it throws. Check reader.IsDBNull(ordinal) first, then read the value, for example Address = reader.IsDBNull(i) ? null : reader.GetString(i).

See the example · Permalink

M How do you call a stored procedure with an output parameter from ADO.NET? coding

Set CommandType = CommandType.StoredProcedure and use the procedure name as the command text. Add input parameters normally, and add the output parameter with Direction = ParameterDirection.Output and a size. After ExecuteNonQueryAsync, read its Value, as with @ReceiptNumber from usp_RecordFeePayment.

See the example · Permalink

Senior (6-9 yrs)

S Why can AddWithValue slow a query down, and what do you use instead? concept

AddWithValue guesses the SQL type from the C# value, and a string becomes NVARCHAR. Against a VARCHAR column, SQL Server converts the column and can scan instead of seek: finding a student by admission number read 37 pages instead of 2. Use Parameters.Add with an explicit SqlDbType and size that match the column.

How to answer: Explain the type guess, the implicit conversion on the column side, and its effect on index use. Give the fix, and mention that plan-cache bloat from varying string lengths is a second reason.

See the example · Permalink

S A school portal becomes slow at 9 a.m., then fails with 'Timeout expired... obtaining a connection from the pool'. What do you check? scenario

The pool is exhausted: connections are being opened and not returned. Look for connections, readers, or commands that are not disposed, especially on exception paths, and for connections held during slow work. Fix with await using everywhere, short-lived connections, and async calls; only then consider raising Max Pool Size.

How to answer: Name the cause (pool exhaustion), how to confirm it (counters or sp_who for sleeping connections from the app), the code fixes, and why raising the pool size first only hides the leak.

See the example · Permalink

Architect / Lead (10+ yrs)

A When would you still choose plain ADO.NET over Dapper or EF Core in a new system? design

When you need full control or maximum throughput with no mapping layer: bulk loading with SqlBulkCopy, streaming huge result sets, table-valued parameters, or a small hot path measured as too slow. For ordinary CRUD, the hand-written mapping costs more in time and bugs than it saves, so most teams use EF Core or Dapper and drop to ADO.NET only where measured.

How to answer: Show you know the trade-off: control and speed against code volume and mistakes. Give concrete cases, and say how you would keep such code isolated behind a repository.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-57-ado-net (code repository coming soon)
Files: src/SchoolApp.Data/SqlConnectionFactory.cs, src/SchoolApp.Data/AdoNet/AdoNetStudentRepository.cs, src/SchoolApp.Console/Program.cs, library-assignment/057-library-adonet.cs
Video: coming soon

What's next

ADO.NET needed about 25 lines to copy one student row into an object. Next: Dapper, which does the same work in one line, with the same SQL and the same parameters. Lesson 58: Dapper

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: the building blocks​

Example: three ways to run a command​

Example: the connection pool​

Apply it to the SMS​

Step 1: a project for data access​

Step 2: the connection string​

Step 3: the repository​

Example: copying a row into an object​

Step 4: the program​

Example: SQL injection​

Example: parameters with exact types​

Example: output parameters and a transaction​

Example: errors from SQL Server​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: the building blocks

Example: three ways to run a command

Example: the connection pool

Apply it to the SMS

Step 1: a project for data access

Step 2: the connection string

Step 3: the repository

Example: copying a row into an object

Step 4: the program

Example: SQL injection

Example: parameters with exact types

Example: output parameters and a transaction

Example: errors from SQL Server

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next