File Upload and Download: CSV Import and Export – Web API | Extra 9
Introduction
In admission week, the school office has a spreadsheet of new students. POST /api/students (Lesson 73) adds
them one at a time, which means 120 forms for 120 students.
This lesson adds two endpoints. One takes a CSV file and adds every student in it; the other downloads every student as a CSV file. Both use the same format, so the office can download, correct, and upload again.
By the end of this lesson, you will be able to:
- Receive an uploaded file safely with
IFormFile, and decide what to check. - Check every row with the same rules as the API, and report problems by row number.
- Save a whole file, or none of it, with a staging table,
SqlBulkCopy, andMERGE. - Stream a large download row by row, as a real file the browser saves.
- Check a picture by its first bytes, not by its name.
Why this matters
Every business system moves data in bulk: new admissions, price lists, bank statements, exam results. The people who do it work in spreadsheets, so CSV files are everywhere.
Uploads are also one of the riskiest doors into a server. A file can be huge, it can lie about its type, and its name is chosen by someone you do not know.
A careless import is risky in another way: half a file saved, and nobody knows which half. This lesson builds an import that is safe for the server and clear for the office.
The concept
Think of the admissions desk. A parent hands over a pile of forms. The clerk checks every form, and if any box is wrong, the whole pile goes back with each problem circled.
Nothing is half-done, and the parent knows exactly what to fix. That is the import we want: all or nothing, with every problem named.
Example: what a CSV file is
CSV means "comma-separated values". It is plain text: one line per row, commas between values, and the first line names the columns. Any spreadsheet program can save one.
AdmissionNumber,FirstName,LastName,DateOfBirth,Gender,AdmissionDate,Address
ADM-2026-20001,Ishaan,Verma,2020-05-14,Male,2026-06-01,"14, Lake View Road"
ADM-2026-20002,Myra,Kulkarni,2020-09-02,Female,2026-06-01,
ADM-2026-20003,Reyansh,Gupta,2020-02-14,Male,2026-06-01,
ADM-2026-20004,Aadhya,Nair,2020-11-20,Female,2026-06-01,
ADM-2017-0002,Diya,Patel,2011-08-23,Female,2017-06-01,"7, Temple Street"
A value that contains a comma, such as 14, Lake View Road, goes inside quotes. A quote inside a value is
written twice: "Flat 2, ""Sea Breeze""".
Example: how a file reaches the API
A browser sends a file in a multipart form: the request body is split into parts, and each part has a name.
ASP.NET Core gives the file part to the action as an IFormFile.
curl -H "Authorization: Bearer <token>" -F "file=@3-new-admissions.csv" http://localhost:5080/api/students/import
-F "file=@..." builds that form, with one part named file. An HTML page does the same with
<input type="file" name="file">.
Example: what an upload can lie about
| Part of the upload | Who decides it | Trust it? |
|---|---|---|
File name (new.csv) | The user's computer | No: never use it to save a file |
Content type (text/csv) | The user's computer | No |
| Size | The bytes that arrive | Yes, and limit it |
| The content | The bytes that arrive | Only after checking it |
So the API checks the size, glances at the extension only to catch the wrong file chosen by mistake, and then checks the content itself: the header row and every value.
Example: all or nothing
The import has two kinds of checks, in this order:
- Each row on its own: dates, gender, names, the admission number format. This needs no database.
- The rows against SchoolDb: does an admission number already belong to a different student?
Only when both pass does anything get saved, in one transaction. One mistake anywhere means nothing is saved.
Apply it to the SMS
Step 1: the rules, reused
Each row must pass the same rules as POST /api/students: the admission number pattern, required names,
lengths, and "at least 3 years old" (Lesson 73). So each row becomes a CreateStudentRequest.
Then a surprise. .NET's own Validator.TryValidateObject said a request with admission number BAD, an empty
first name, and gender Z was valid. We tested it twice.
The reason: CreateStudentRequest is a record, and its rules sit on the record's parameters. ASP.NET Core
reads them there, but Validator reads only properties. Moving the rules to the properties broke the API instead:
InvalidOperationException: Record type 'SchoolApp.Core.Dtos.CreateStudentRequest' has validation metadata defined
on property 'FirstName' that will be ignored. 'FirstName' is a parameter in the record primary constructor and
validation metadata must be associated with the constructor parameter.
So the rules stay where ASP.NET Core wants them, and a small class reads them from the parameters:
using System.ComponentModel.DataAnnotations;
using System.Reflection;
namespace SchoolApp.Core.Common;
// Extra 9: checks a request record's rules outside ASP.NET Core, for example for every row of a CSV file.
// CreateStudentRequest puts its attributes on the record's parameters, because ASP.NET Core insists on that for
// records. .NET's own Validator reads only properties, so it would miss every one of them. This class reads the
// parameters' rules, then runs Validate (IValidatableObject), in the same order as ASP.NET Core.
public static class RecordValidator
{
public static List<string> Validate(object record)
{
List<string> errors = [];
Type type = record.GetType();
// The record's primary constructor is its public one with the most parameters.
ConstructorInfo constructor = type.GetConstructors().OrderByDescending(c => c.GetParameters().Length).First();
foreach (ParameterInfo parameter in constructor.GetParameters())
{
object? value = type.GetProperty(parameter.Name!)!.GetValue(record);
ValidationContext context = new ValidationContext(record) { MemberName = parameter.Name, DisplayName = parameter.Name! };
foreach (ValidationAttribute rule in parameter.GetCustomAttributes<ValidationAttribute>())
{
ValidationResult? result = rule.GetValidationResult(value, context);
if (result is not null)
{
errors.Add(result.ErrorMessage!);
}
}
}
// Like ASP.NET Core: the rules that compare fields run only when every single field is valid.
if (errors.Count == 0 && record is IValidatableObject validatable)
{
errors.AddRange(validatable.Validate(new ValidationContext(record)).Select(result => result.ErrorMessage!));
}
return errors;
}
}
Step 2: reading the file
StudentCsv reads the text with TextFieldParser, which comes with .NET and understands quoted values. It checks
the header, then every row, and collects problems under the row number a spreadsheet shows.
Show src/SchoolApp.Core/Imports/StudentCsv.cs
using System.Globalization;
using Microsoft.VisualBasic.FileIO;
using SchoolApp.Core.Common;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Enums;
namespace SchoolApp.Core.Imports;
// Extra 9: the one CSV format for students, used both ways. The export writes it and the import reads it, so the
// office can download the list, correct it in a spreadsheet, and upload it again.
public static class StudentCsv
{
public static readonly string[] Columns =
["AdmissionNumber", "FirstName", "LastName", "DateOfBirth", "Gender", "AdmissionDate", "Address"];
public const string DateFormat = "yyyy-MM-dd"; // never the computer's own date format
public const int MaxRows = 20_000;
public static StudentCsvContent Read(TextReader reader)
{
List<StudentImportRow> rows = [];
Dictionary<string, string[]> errors = [];
HashSet<string> admissionNumbers = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
// TextFieldParser comes with .NET (it began in Visual Basic, hence the namespace). It understands quoted
// values, so an address such as "12, Park Road" stays one value although it contains a comma.
using TextFieldParser parser = new TextFieldParser(reader)
{
TextFieldType = FieldType.Delimited,
HasFieldsEnclosedInQuotes = true,
TrimWhiteSpace = true
};
parser.SetDelimiters(",");
string[]? header = parser.EndOfData ? null : parser.ReadFields();
if (header is null || !header.SequenceEqual(Columns, StringComparer.OrdinalIgnoreCase))
{
errors["Row 1"] = [$"The first row must be the column names: {string.Join(",", Columns)}."];
return new StudentCsvContent(rows, errors);
}
int rowNumber = 1;
while (!parser.EndOfData)
{
rowNumber++;
if (rowNumber - 1 > MaxRows)
{
errors["File"] = [$"A file may have at most {MaxRows:N0} students."];
break;
}
string[] fields;
try
{
fields = parser.ReadFields() ?? [];
}
catch (MalformedLineException)
{
errors[$"Row {rowNumber}"] = ["This row could not be read: check its quotes."];
continue;
}
List<string> problems = ReadStudent(fields, out CreateStudentRequest? student);
if (student is not null && !admissionNumbers.Add(student.AdmissionNumber))
{
problems.Add($"{student.AdmissionNumber} appears more than once in the file.");
}
if (problems.Count > 0)
{
errors[$"Row {rowNumber}"] = [.. problems];
}
else
{
rows.Add(new StudentImportRow(rowNumber, student!));
}
}
if (rowNumber == 1)
{
errors["File"] = ["The file has the column names but no students."];
}
return new StudentCsvContent(rows, errors);
}
// One row of text into a CreateStudentRequest, checked with the same rules as POST /api/students.
private static List<string> ReadStudent(string[] fields, out CreateStudentRequest? student)
{
student = null;
if (fields.Length != Columns.Length)
{
return [$"Expected {Columns.Length} values, found {fields.Length}."];
}
List<string> problems = [];
DateOnly dateOfBirth = ReadDate(fields[3], "DateOfBirth", problems);
Gender gender = ReadGender(fields[4], problems);
DateOnly admissionDate = ReadDate(fields[5], "AdmissionDate", problems);
if (problems.Count > 0)
{
return problems;
}
CreateStudentRequest request = new CreateStudentRequest(fields[0], fields[1], fields[2], dateOfBirth, gender,
admissionDate, fields[6].Length == 0 ? null : fields[6]);
problems.AddRange(RecordValidator.Validate(request));
if (problems.Count == 0)
{
student = request;
}
return problems;
}
private static DateOnly ReadDate(string text, string column, List<string> problems)
{
if (DateOnly.TryParseExact(text, DateFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out DateOnly date))
{
return date;
}
problems.Add($"{column} '{text}' is not a date like 2015-06-30.");
return default;
}
// The names Male, Female, Other, as in the API's JSON. Not numbers, and not the database's M, F, O codes.
private static Gender ReadGender(string text, List<string> problems)
{
string? name = Enum.GetNames<Gender>().FirstOrDefault(n => n.Equals(text, StringComparison.OrdinalIgnoreCase));
if (name is not null)
{
return Enum.Parse<Gender>(name);
}
problems.Add($"Gender '{text}' must be Male, Female, or Other.");
return default;
}
// The header, then one line per student, written as they arrive: the whole list is never in memory at once.
public static async Task WriteAsync(TextWriter writer, IAsyncEnumerable<CreateStudentRequest> students,
CancellationToken cancellationToken = default)
{
await writer.WriteLineAsync(string.Join(",", Columns));
await foreach (CreateStudentRequest s in students.WithCancellation(cancellationToken))
{
await writer.WriteLineAsync(string.Join(",",
Escape(s.AdmissionNumber), Escape(s.FirstName), Escape(s.LastName),
s.DateOfBirth.ToString(DateFormat, CultureInfo.InvariantCulture), s.Gender.ToString(),
s.AdmissionDate.ToString(DateFormat, CultureInfo.InvariantCulture), Escape(s.Address ?? "")));
}
}
// A value with a comma, a quote, or a line break goes inside quotes, and each quote inside is doubled:
// 12, "Lake View" Road becomes "12, ""Lake View"" Road"
public static string Escape(string value) =>
value.IndexOfAny([',', '"', '\r', '\n']) >= 0 ? $"\"{value.Replace("\"", "\"\"")}\"" : value;
}
Dates must be written yyyy-MM-dd. A date like 14/02/2020 means 14 February in one country and nonsense in
another, so the file never depends on the computer's settings.
Step 3: saving every row or none
The repository follows SQL Extra 2: load the file into a staging table, check it, then one MERGE. Three new
tools appear:
#StudentImport, a temporary table that belongs to this connection only.SqlBulkCopy, which sends all rows in one stream instead of oneINSERTper row.OUTPUT $action, which tells the code whether each row was inserted or updated.
Show src/SchoolApp.Data/Dapper/DapperStudentBulkRepository.cs
using System.Data;
using System.Runtime.CompilerServices;
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Imports;
using SchoolApp.Core.Interfaces;
namespace SchoolApp.Data.Dapper;
// Extra 9: the CSV import and export. The import is SQL Extra 2's pattern: load the file into a staging table,
// check it, then one MERGE. All of it runs in one transaction, so SchoolDb gets every row or none.
public class DapperStudentBulkRepository : IStudentBulkRepository
{
private readonly SqlConnectionFactory _connections;
public DapperStudentBulkRepository(SqlConnectionFactory connections)
{
_connections = connections;
}
public async Task<StudentImportResult> ImportAsync(IReadOnlyList<StudentImportRow> rows, CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
await connection.OpenAsync(cancellationToken);
await using SqlTransaction transaction = (SqlTransaction)await connection.BeginTransactionAsync(cancellationToken);
// 1. A staging table that belongs to this connection only (#), and disappears when it closes.
await connection.ExecuteAsync("""
CREATE TABLE #StudentImport (
RowNumber INT NOT NULL PRIMARY KEY,
AdmissionNumber VARCHAR(15) NOT NULL,
FirstName NVARCHAR(100) NOT NULL,
LastName NVARCHAR(100) NOT NULL,
DateOfBirth DATE NOT NULL,
Gender CHAR(1) NOT NULL,
AdmissionDate DATE NOT NULL,
Address NVARCHAR(400) NULL
);
""", transaction: transaction);
// 2. SqlBulkCopy sends all rows in one stream, instead of one INSERT statement per row.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
bulkCopy.DestinationTableName = "#StudentImport";
await bulkCopy.WriteToServerAsync(ToTable(rows), cancellationToken);
}
// 3. Rows that clash with a student SchoolDb already has. An import may correct names and addresses, but a
// different birth date or gender means the admission number belongs to someone else.
List<ImportClash> clashes = (await connection.QueryAsync<ImportClash>("""
SELECT s.RowNumber,
CASE WHEN t.IsDeleted = 1
THEN CONCAT(s.AdmissionNumber, ' belongs to a removed student.')
ELSE CONCAT(s.AdmissionNumber, ' already belongs to a student with a different date of birth, gender, or admission date.')
END AS Problem
FROM #StudentImport AS s
JOIN dbo.Students AS t ON t.AdmissionNumber = s.AdmissionNumber
WHERE t.IsDeleted = 1 OR t.DateOfBirth <> s.DateOfBirth OR t.Gender <> s.Gender OR t.AdmissionDate <> s.AdmissionDate
ORDER BY s.RowNumber;
""", transaction: transaction)).ToList();
if (clashes.Count > 0)
{
await transaction.RollbackAsync(cancellationToken);
return StudentImportResult.Failed(clashes.ToDictionary(c => $"Row {c.RowNumber}", c => new[] { c.Problem }));
}
// 4. One MERGE: new admission numbers are inserted, changed names or addresses are updated, and identical
// rows are left alone. OUTPUT $action says which happened to each row. HOLDLOCK: see SQL Extra 2.
List<string> actions = (await connection.QueryAsync<string>("""
MERGE dbo.Students WITH (HOLDLOCK) AS t
USING #StudentImport AS s
ON t.AdmissionNumber = s.AdmissionNumber
WHEN MATCHED AND (t.FirstName <> s.FirstName OR t.LastName <> s.LastName
OR ISNULL(t.Address, N'') <> ISNULL(s.Address, N'')) THEN
UPDATE SET FirstName = s.FirstName, LastName = s.LastName, Address = s.Address,
UpdatedAt = SYSUTCDATETIME(), UpdatedBy = CAST(SESSION_CONTEXT(N'UserId') AS INT)
WHEN NOT MATCHED BY TARGET THEN
INSERT (AdmissionNumber, FirstName, LastName, DateOfBirth, Gender, AdmissionDate, Address)
VALUES (s.AdmissionNumber, s.FirstName, s.LastName, s.DateOfBirth, s.Gender, s.AdmissionDate, s.Address)
OUTPUT $action;
""", transaction: transaction)).ToList();
await transaction.CommitAsync(cancellationToken);
int added = actions.Count(action => action == "INSERT");
int updated = actions.Count(action => action == "UPDATE");
return StudentImportResult.Done(new StudentImportSummary(rows.Count, added, updated, rows.Count - added - updated));
}
// Unbuffered: Dapper hands over each row as SQL Server sends it, so 10,000 students never sit in memory together.
public async IAsyncEnumerable<CreateStudentRequest> ReadAllAsync([EnumeratorCancellation] CancellationToken cancellationToken = default)
{
await using SqlConnection connection = _connections.Create();
await connection.OpenAsync(cancellationToken);
IAsyncEnumerable<StudentRow> rows = connection.QueryUnbufferedAsync<StudentRow>("""
SELECT AdmissionNumber, FirstName, LastName, DateOfBirth, Gender, AdmissionDate, Address
FROM dbo.Students
WHERE IsDeleted = 0
ORDER BY AdmissionNumber;
""");
await foreach (StudentRow row in rows.WithCancellation(cancellationToken))
{
yield return new CreateStudentRequest(row.AdmissionNumber, row.FirstName, row.LastName, row.DateOfBirth,
(Gender)row.Gender[0], row.AdmissionDate, row.Address);
}
}
// SqlBulkCopy reads a DataTable whose columns are in the staging table's order.
private static DataTable ToTable(IReadOnlyList<StudentImportRow> rows)
{
DataTable table = new DataTable();
table.Columns.Add("RowNumber", typeof(int));
table.Columns.Add("AdmissionNumber", typeof(string));
table.Columns.Add("FirstName", typeof(string));
table.Columns.Add("LastName", typeof(string));
table.Columns.Add("DateOfBirth", typeof(DateTime));
table.Columns.Add("Gender", typeof(string));
table.Columns.Add("AdmissionDate", typeof(DateTime));
table.Columns.Add("Address", typeof(string));
foreach (StudentImportRow row in rows)
{
CreateStudentRequest s = row.Student;
table.Rows.Add(row.RowNumber, s.AdmissionNumber, s.FirstName, s.LastName, s.DateOfBirth.ToDateTime(TimeOnly.MinValue),
((char)s.Gender).ToString(), s.AdmissionDate.ToDateTime(TimeOnly.MinValue), (object?)s.Address ?? DBNull.Value);
}
return table;
}
private sealed record ImportClash(int RowNumber, string Problem);
}
An import may correct a name or an address. A different date of birth, gender, or admission date means the admission number belongs to someone else, so that row is refused and the transaction is rolled back.
Step 4: the endpoints
The controller checks what needs no reading, turns the upload into a TextReader, and answers with
ApiResponse (Lesson 80). The download is different: a file is not JSON.
using System.Text;
using Asp.Versioning;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using SchoolApp.Api.Logging;
using SchoolApp.Api.Models;
using SchoolApp.Api.Security;
using SchoolApp.Core.Imports;
using SchoolApp.Core.Services;
namespace SchoolApp.Api.Controllers;
// Extra 9: students in and out as CSV files. Both directions use one format (StudentCsv in Core), so a downloaded
// file can be corrected in a spreadsheet and uploaded again.
[ApiController]
[ApiVersion("1.0")]
[Route("api/students")]
[Route("api/v{version:apiVersion}/students")]
[Authorize(Policy = Policies.ManageSchool)] // the same people who may admit a student one at a time
public class StudentFilesController : ControllerBase
{
// 4 MB: about 50,000 students in this format; a class list is a few KB. A bigger upload is a mistake or an attack.
public const long MaxFileBytes = 4 * 1024 * 1024;
private readonly StudentFileService _files;
private readonly TimeProvider _time;
private readonly ILogger<StudentFilesController> _logger;
public StudentFilesController(StudentFileService files, TimeProvider time, ILogger<StudentFilesController> logger)
{
_files = files;
_time = time;
_logger = logger;
}
// POST /api/students/import, a form with one file field named "file".
/// <summary>Adds new students, and corrects names and addresses, from a CSV file. Every row is saved, or none.</summary>
/// <param name="file">A .csv file whose first row is the column names (the same format as the export).</param>
/// <param name="cancellationToken">Cancelled when the client gives up.</param>
/// <response code="200">How many rows were added, updated, and unchanged.</response>
/// <response code="400">The file was refused; errors lists the problems by row. Nothing was saved.</response>
[HttpPost("import")]
[Consumes("multipart/form-data")]
// The whole request (file plus form) may be a little larger than the file. Beyond this, Kestrel stops reading,
// and ASP.NET Core answers 400 "Request body too large" before the action runs.
[RequestSizeLimit(MaxFileBytes + 64 * 1024)]
[Produces("application/json")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status400BadRequest)]
public async Task<ActionResult<ApiResponse<StudentImportSummary>>> Import(IFormFile? file, CancellationToken cancellationToken)
{
string? problem = CheckFile(file);
if (problem is not null)
{
return BadRequest(ApiResponse.Fail(problem));
}
// Read straight from the upload: the file is never saved to disk, so no file name is ever used.
// StreamReader recognises a UTF-8 byte order mark, which spreadsheet programs often write.
using StreamReader reader = new StreamReader(file!.OpenReadStream());
StudentImportResult result = await _files.ImportAsync(reader, cancellationToken);
if (!result.Succeeded)
{
return BadRequest(ApiResponse.Fail(
$"The file has problems in {result.Errors.Count} place(s). Nothing was imported.",
errors: result.Errors.ToDictionary(entry => entry.Key, entry => entry.Value)));
}
StudentImportSummary summary = result.Summary!;
SchoolLog.StudentsImported(_logger, summary.Added, summary.Updated, summary.Unchanged);
return Ok(ApiResponse.Ok(summary, $"{summary.Added} added, {summary.Updated} updated, {summary.Unchanged} unchanged."));
}
// GET /api/students/export
/// <summary>Downloads every student as a CSV file, in the format the import reads.</summary>
/// <param name="cancellationToken">Cancelled when the client gives up.</param>
/// <response code="200">The CSV file, sent row by row as it is read.</response>
[HttpGet("export")]
[Produces("text/csv")]
[ProducesResponseType(StatusCodes.Status200OK)]
public IResult Export(CancellationToken cancellationToken)
{
string fileName = $"students-{_time.GetLocalNow():yyyy-MM-dd}.csv";
// Results.Stream writes the answer while the rows arrive. A file is not JSON, so there is no ApiResponse
// around it; Content-Disposition (from the file name) tells the browser to save it as a download.
return Results.Stream(async body =>
{
// UTF-8 with a byte order mark, so spreadsheet programs show every name correctly.
await using StreamWriter writer = new StreamWriter(body, new UTF8Encoding(encoderShouldEmitUTF8Identifier: true));
await _files.ExportAsync(writer, cancellationToken);
}, "text/csv; charset=utf-8", fileName);
}
// The checks that need no reading. The file name and content type come from the user's computer and prove
// nothing about what is inside; the extension only catches the wrong file chosen by mistake. The real check
// is the content: the header row and every value (StudentCsv).
private static string? CheckFile(IFormFile? file)
{
if (file is null || file.Length == 0)
{
return "Choose a CSV file to upload, in a form field named \"file\".";
}
if (file.Length > MaxFileBytes)
{
return $"The file is larger than {MaxFileBytes / 1024 / 1024} MB.";
}
if (!string.Equals(Path.GetExtension(file.FileName), ".csv", StringComparison.OrdinalIgnoreCase))
{
return "Only .csv files can be imported. In a spreadsheet program, use Save As and choose CSV UTF-8.";
}
return null;
}
}
Notice that the upload is never saved to disk. It is read straight from the request, so its name is never used for anything.
Example: a file with mistakes
We uploaded 1-new-admissions-with-mistakes.csv as the principal (shortened: traceId left out):
{"status":"error","message":"The file has problems in 4 place(s). Nothing was imported.",
"errors":{"Row 3":["The LastName field is required."],
"Row 4":["DateOfBirth '14/02/2020' is not a date like 2015-06-30."],
"Row 5":["Gender 'F' must be Male, Female, or Other."],
"Row 6":["ADM-2026-20001 appears more than once in the file."]}}
Four problems, four rows, one answer. The office does not have to fix one mistake, upload, and meet the next.
Example: a clash with the database
After fixing those rows, the file still had row 7: Kabir Singh's admission number with another birth date.
{"status":"error","message":"The file has problems in 1 place(s). Nothing was imported.",
"errors":{"Row 7":["ADM-2017-0003 already belongs to a student with a different date of birth, gender, or admission date."]}}
Rows 2 to 6 were fine, but none of them was saved: the transaction was rolled back.
Example: success, and success again
Without row 7, the file went through. Uploading the same file a second time changed nothing:
{"status":"success","message":"4 added, 1 updated, 0 unchanged.","data":{"rows":5,"added":4,"updated":1,"unchanged":0}}
{"status":"success","message":"0 added, 0 updated, 5 unchanged.","data":{"rows":5,"added":0,"updated":0,"unchanged":5}}
The "1 updated" is Diya Patel, whose address the file added. The audit columns from Lesson 88 recorded who did it:
AdmissionNumber FirstName LastName Address CreatedBy UpdatedAt UpdatedBy
ADM-2017-0002 Diya Patel 7, Temple Street NULL 2026-10-12 14:37:23 2
ADM-2026-20001 Ishaan Verma 14, Lake View Road 2 NULL NULL
User 2 is the principal. We then removed the four test students and restored Diya's row.
Example: the wrong file, and a file too big
notes.txt 400 Only .csv files can be imported. In a spreadsheet program, use Save As and choose CSV UTF-8.
5 MB file 400 Failed to read the request form. Request body too large. The max request body size is 4259840 bytes.
export, as accounts 403 (only the admin and the principal may import or export, as for POST /api/students)
The big file never reached the action. Kestrel stopped reading at the limit, and ASP.NET Core answered 400 for us.
Example: the download
curl -D - -o students.csv -H "Authorization: Bearer <token>" http://localhost:5080/api/students/export
HTTP/1.1 200 OK
Content-Type: text/csv; charset=utf-8
Transfer-Encoding: chunked
Content-Disposition: attachment; filename=students-2026-10-12.csv; filename*=UTF-8''students-2026-10-12.csv
AdmissionNumber,FirstName,LastName,DateOfBirth,Gender,AdmissionDate,Address
ADM-2017-0001,Aarav,Sharma,2011-04-12,Male,2017-06-01,
ADM-2017-0002,Diya,Patel,2011-08-23,Female,2017-06-01,
ADM-2017-0003,Kabir,Singh,2011-01-30,Male,2017-06-01,
All 10,012 students arrived in 0.13 seconds: 569 KB, with no Content-Length. Transfer-Encoding: chunked means
the API sent the file in pieces while Dapper read the rows, so the list was never in memory all at once.
Content-Disposition: attachment tells a browser to save the answer as a file with that name. The file starts with
three bytes, EF BB BF: the UTF-8 byte order mark, which spreadsheet programs need to show every name correctly.
Example: download, upload, nothing changes
The strongest test of a shared format: upload the downloaded file, unchanged.
{"status":"success","message":"0 added, 0 updated, 10012 unchanged.","data":{"rows":10012,"added":0,"updated":0,"unchanged":10012}}
Every one of 10,012 rows was read, checked, bulk-copied, and matched. Nothing was changed.
Step 5: tests
Fifteen new tests: the CSV rules without a database, the repository against SchoolDb, and the endpoints through the whole API. The route list from Lesson 96 failed first, as it should: the four new routes had to be approved.
Show tests/SchoolApp.Tests/Unit/StudentCsvTests.cs
using System.ComponentModel.DataAnnotations;
using SchoolApp.Core.Common;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Imports;
namespace SchoolApp.Tests.Unit;
// Extra 9: the CSV format, read and written without a database or a web server.
public class StudentCsvTests
{
private const string Header = "AdmissionNumber,FirstName,LastName,DateOfBirth,Gender,AdmissionDate,Address";
private static StudentCsvContent Read(params string[] lines) => StudentCsv.Read(new StringReader(string.Join("\n", lines)));
[Fact]
public void AValidRow_IsRead_AndAQuotedCommaStaysInTheAddress()
{
StudentCsvContent content = Read(Header, "ADM-2026-20001,Ishaan,Verma,2020-05-14,Male,2026-06-01,\"14, Lake View Road\"");
Assert.Empty(content.Errors);
StudentImportRow row = Assert.Single(content.Rows);
Assert.Equal(2, row.RowNumber); // the header is row 1, as in a spreadsheet
Assert.Equal(new CreateStudentRequest("ADM-2026-20001", "Ishaan", "Verma", new DateOnly(2020, 5, 14), Gender.Male,
new DateOnly(2026, 6, 1), "14, Lake View Road"), row.Student);
}
[Fact]
public void EveryProblem_IsReported_WithItsRow()
{
StudentCsvContent content = Read(Header,
"ADM-2026-20001,Ishaan,Verma,2020-05-14,Male,2026-06-01,",
"ADM-2026-20002,Myra,,2020-09-02,Female,2026-06-01,",
"ADM-2026-20003,Reyansh,Gupta,14/02/2020,Male,2026-06-01,",
"ADM-2026-20004,Aadhya,Nair,2020-11-20,F,2026-06-01,",
"ADM-2026-20001,Ishaan,Verma,2020-05-14,Male,2026-06-01,",
"ADM-26-1,Kiara,Shah,2024-01-01,Female,2026-06-01,");
Assert.Equal(["Row 3", "Row 4", "Row 5", "Row 6", "Row 7"], content.Errors.Keys.Order());
Assert.Equal("The LastName field is required.", Assert.Single(content.Errors["Row 3"]));
Assert.Equal("Gender 'F' must be Male, Female, or Other.", Assert.Single(content.Errors["Row 5"]));
Assert.Equal("ADM-2026-20001 appears more than once in the file.", Assert.Single(content.Errors["Row 6"]));
Assert.Equal("AdmissionNumber must look like ADM-2026-00001.", Assert.Single(content.Errors["Row 7"]));
}
[Fact]
public void TheRuleThatComparesTwoFields_RunsToo()
{
StudentCsvContent content = Read(Header, "ADM-2026-20005,Kiara,Shah,2024-01-01,Female,2026-06-01,");
Assert.Equal("A student must be at least 3 years old on the admission date.", Assert.Single(content.Errors["Row 2"]));
}
[Theory]
[InlineData("Admission,First,Last,Birth,Gender,Admitted,Address")]
[InlineData("AdmissionNumber,FirstName,LastName")]
[InlineData("")]
public void AWrongHeader_StopsTheWholeFile(string header)
{
StudentCsvContent content = Read(header, "ADM-2026-20001,Ishaan,Verma,2020-05-14,Male,2026-06-01,");
Assert.Empty(content.Rows);
Assert.StartsWith("The first row must be the column names", Assert.Single(content.Errors["Row 1"]));
}
[Fact]
public async Task WhatTheExportWrites_TheImportReadsBack()
{
CreateStudentRequest tricky = new CreateStudentRequest("ADM-2026-20006", "Anaya", "D'Souza", new DateOnly(2019, 3, 8),
Gender.Female, new DateOnly(2026, 6, 1), "Flat 2, \"Sea Breeze\"");
StringWriter writer = new StringWriter();
await StudentCsv.WriteAsync(writer, new[] { tricky }.ToAsyncEnumerable());
StudentCsvContent content = StudentCsv.Read(new StringReader(writer.ToString()));
Assert.Contains("\"Flat 2, \"\"Sea Breeze\"\"\"", writer.ToString()); // quoted, and the quotes doubled
Assert.Equal(tricky, Assert.Single(content.Rows).Student);
}
[Fact]
public void DotNetsValidator_MissesTheRecordsRules_RecordValidatorDoesNot()
{
CreateStudentRequest wrong = new CreateStudentRequest("BAD", "", "Verma", new DateOnly(2020, 5, 14), (Gender)'Z',
new DateOnly(2026, 6, 1), null);
bool validatorSaysValid = Validator.TryValidateObject(wrong, new ValidationContext(wrong), [], validateAllProperties: true);
List<string> errors = RecordValidator.Validate(wrong);
Assert.True(validatorSaysValid); // the rules sit on the record's parameters, where Validator never looks
Assert.Equal(3, errors.Count); // admission number, first name, gender
}
}
One test failed for a reason worth knowing. Our cleanup deleted every student LIKE 'ADM-2099-%', and so did
the cleanup in EnrollmentTests. The two classes run at the same time, so each one deleted the other's students.
The fix: our tests use their own numbers, ADM-2098-09xx, and delete them by exact number.
Passed! - Failed: 0, Passed: 209, Skipped: 0, Total: 209, Duration: 11 s - SchoolApp.Tests.dll (net10.0)
Passed! - Failed: 0, Passed: 209, Skipped: 0, Total: 209, Duration: 10 s - SchoolApp.Tests.dll (net10.0)
Common mistakes
- Saving an upload under its own name. A name like
../../web.configcan overwrite server files. Fix: never use it; read the file in memory, or save it under a name the server makes. - Trusting the extension or content type. Both come from the user's computer. Fix: check the content.
- No size limit. One huge upload fills memory or disk. Fix:
RequestSizeLimitand a check in the code. - Saving the good rows and skipping the bad. Nobody knows what was saved. Fix: all or nothing, with every problem listed by row.
- One
INSERTper row. Slow and chatty for thousands of rows. Fix:SqlBulkCopyinto a staging table. - Building the whole download in memory. Large lists use large memory. Fix: stream rows as they are read.
- Dates in the computer's format.
03/04/2020means two different days. Fix:yyyy-MM-dd, always.
In real projects
A school's fee export opened fine for the accounts team. Then a parent's name with a leading = appeared in the
file, and the spreadsheet program treated the cell as a formula.
Nothing harmful happened that day, but it showed a real risk called CSV injection: a value that starts with
=, +, -, or @ can run as a formula when the file is opened.
The team now prefixes such values with an apostrophe in every export, and checks that imports never accept them in names. The SMS export does not do this yet; it is worth adding before real parents' data goes into it.
Best practices
- Limit the request size, and check the file size again in code.
- Never use the uploaded file name for storage; check the content, not the claimed type.
- Use one CSV format for import and export, with fixed column names and
yyyy-MM-dddates. - Check every row with the same rules as the single-record endpoint, and report problems by row.
- Save through a staging table and one transaction: all rows or none, and harmless to repeat.
- Stream large downloads, with
Content-Dispositionand UTF-8 with a byte order mark.
Recap
POST /api/students/importtakes a CSV file in a form field namedfile;GET /api/students/exportdownloads every student in the same format.- .NET's
Validatormisses rules on a record's parameters, and ASP.NET Core refuses them on properties, soRecordValidatorreads them from the parameters. - Rows are checked alone, then against SchoolDb; any problem means nothing is saved.
- A staging table,
SqlBulkCopy, andMERGEwithOUTPUT $actionimported 10,012 rows in 0.75 seconds. - The export streamed 10,012 rows as a chunked download, and uploading it back changed nothing. 209 tests pass.
Homework
Library module task: Let librarians upload a cover picture for each book, and let the catalogue show it.
POST /api/books/{isbn}/covertakes a form field namedcover: a PNG or JPEG of at most 200 KB.- Check the file's first bytes. PNG files start with
89 50 4E 47 0D 0A 1A 0A, JPEG files withFF D8 FF. - Accept only a 13-digit ISBN, and save the file as
{isbn}.pngor{isbn}.jpgin a folder that is not public. GET /api/books/{isbn}/coverreturns the picture with the right content type, or 404.
A correct result looks like:
real PNG 200 {"message":"Cover saved for 9780143039655.","bytes":69}
text renamed .png 400 {"error":"Only PNG and JPEG pictures are accepted."}
300 KB 400 Failed to read the request form. Request body too large. The max request body size is 221184 bytes.
ISBN "../../win" 400 {"error":"An ISBN is 13 digits."}
download 200 image/png, 69 bytes, the same bytes as uploaded
another ISBN 404 {"error":"No cover for 9788173711466."}
Show the solution
// Library homework, Extra 9: upload and download a cover picture for a book.
// POST /api/books/{isbn}/cover a form with one file field, "cover": a PNG or JPEG of at most 200 KB
// GET /api/books/{isbn}/cover the picture, with the right content type
// The checks look at the file's first bytes, not its name or content type, which the user's computer chooses.
// The picture is saved under a name the server makes from the ISBN, in a folder outside any public web folder.
// Run with: dotnet run library-assignment/e9-library-book-covers.cs
#:sdk Microsoft.NET.Sdk.Web
#:property PublishAot=false
using System.Text.RegularExpressions;
using Microsoft.AspNetCore.Mvc;
WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
WebApplication app = builder.Build();
app.MapControllers();
app.Run("http://localhost:5109");
[ApiController]
[Route("api/books/{isbn}/cover")]
public partial class CoversController : ControllerBase
{
private const long MaxBytes = 200 * 1024;
// A setting, so the folder can live anywhere; by default, a folder in the computer's temp folder.
private readonly string _folder;
public CoversController(IConfiguration configuration)
{
_folder = configuration["CoverFolder"] ?? Path.Combine(Path.GetTempPath(), "library-covers");
Directory.CreateDirectory(_folder);
}
[HttpPost]
[RequestSizeLimit(MaxBytes + 16 * 1024)]
public async Task<IActionResult> Upload(string isbn, IFormFile? cover)
{
if (!IsbnPattern().IsMatch(isbn))
{
return BadRequest(new { error = "An ISBN is 13 digits." }); // so it is safe to use in a file name
}
if (cover is null || cover.Length == 0 || cover.Length > MaxBytes)
{
return BadRequest(new { error = "Send a picture of at most 200 KB in the form field \"cover\"." });
}
// The first bytes of a file say what it really is. A text file renamed to cover.png fails here.
byte[] start = new byte[8];
await using Stream upload = cover.OpenReadStream();
int read = await upload.ReadAtLeastAsync(start, start.Length, throwOnEndOfStream: false);
string? extension = KindOf(start.AsSpan(0, read));
if (extension is null)
{
return BadRequest(new { error = "Only PNG and JPEG pictures are accepted." });
}
// Replace any earlier cover, then save under a name the server chose.
foreach (string old in Directory.GetFiles(_folder, $"{isbn}.*"))
{
System.IO.File.Delete(old);
}
string path = Path.Combine(_folder, $"{isbn}{extension}");
await using (FileStream file = System.IO.File.Create(path))
{
await file.WriteAsync(start.AsMemory(0, read));
await upload.CopyToAsync(file);
}
return Ok(new { message = $"Cover saved for {isbn}.", bytes = cover.Length });
}
[HttpGet]
public IActionResult Download(string isbn)
{
string? path = IsbnPattern().IsMatch(isbn) ? Directory.GetFiles(_folder, $"{isbn}.*").FirstOrDefault() : null;
if (path is null)
{
return NotFound(new { error = $"No cover for {isbn}." });
}
string contentType = path.EndsWith(".png") ? "image/png" : "image/jpeg";
return PhysicalFile(path, contentType);
}
// PNG files start with 89 50 4E 47 0D 0A 1A 0A; JPEG files start with FF D8 FF.
private static string? KindOf(ReadOnlySpan<byte> start) =>
start.StartsWith((byte[])[0x89, 0x50, 0x4E, 0x47, 0x0D, 0x0A, 0x1A, 0x0A]) ? ".png"
: start.StartsWith((byte[])[0xFF, 0xD8, 0xFF]) ? ".jpg"
: null;
[GeneratedRegex(@"^\d{13}$")]
private static partial Regex IsbnPattern();
}
The ISBN check is what makes the file name safe: thirteen digits can never contain .. or a slash. The picture is
served by the controller, not from a public folder, so the API can also check who is asking.
Quiz
Which part of an upload can you trust without checking?
Show answer
Answer: C. The number of bytes that arrive Name, extension, and content type are chosen by the user's computer; the size is what actually arrives.
A file has 500 good rows and 1 bad row. What does the SMS import do?
Show answer
Answer: B. Saves nothing and reports the bad row's number All or nothing: the office fixes one row and uploads again.
Why did Validator.TryValidateObject say an invalid CreateStudentRequest was valid?
Show answer
Answer: B. The rules are on the record's parameters, and Validator reads only properties ASP.NET Core requires the rules on the parameters for records, so the SMS reads them there with RecordValidator.
What does Transfer-Encoding: chunked tell you about the export?
Show answer
Answer: B. The API sent the file in pieces while reading rows, without knowing the total size first Streaming: 10,012 rows were never in memory all at once.
Why upload the exported file back in a test?
Show answer
Answer: B. If the format really is shared, nothing should change: 0 added, 0 updated 10,012 unchanged proved that export and import agree on every value.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: Web API interview questions.
Fresher (0-2 yrs)
F How does an ASP.NET Core controller receive an uploaded file? coding
The client sends a multipart form, and the file part arrives as an IFormFile parameter whose name matches
the form field. IFormFile gives the length, the claimed file name and content type, and OpenReadStream()
to read the bytes.
F Which parts of an uploaded file can you trust? concept
Only what actually arrives: its size, and its content after you check it. The file name, extension, and content type are chosen by the user's computer, so never use the name to save the file, and check the content itself, for example the header row of a CSV or the first bytes of a picture.
F How do you make the browser download an API's answer as a file? coding
Return the bytes with the right content type and a Content-Disposition: attachment; filename=... header.
In ASP.NET Core, File(...), PhysicalFile(...), or Results.Stream(..., contentType, fileDownloadName) set
it from the file name you pass.
Mid (3-5 yrs)
M How do you limit the size of uploads, and what does the client get when a file is too big? coding
[RequestSizeLimit] on the action (or Kestrel's MaxRequestBodySize) stops reading beyond the limit, and form
options limit multipart bodies. In an MVC action, the failed form read becomes a 400 "Request body too large"
before the action runs. Check IFormFile.Length in code as well, since a test server may not enforce the limit.
M Why might Validator.TryValidateObject miss the rules of a request record? concept
For a positional record, ASP.NET Core requires validation attributes on the constructor parameters, and
throws if they are on the properties. Validator reads only properties, so it does not see them. Outside MVC
you must read the parameters' attributes yourself, as the SMS's RecordValidator does, or validate differently.
M How do you stream a large download instead of building it in memory? coding
Read rows one at a time (for example Dapper's QueryUnbufferedAsync as an IAsyncEnumerable) and write each
to the response stream as it arrives, for example inside Results.Stream. The response goes out chunked,
memory stays flat, and the client starts receiving data at once.
Senior (6-9 yrs)
S Design a CSV import of thousands of rows that must be safe to repeat and must never save half a file. design
Check the file (size, header), then every row with the same rules as the single-record endpoint, collecting errors by row. Bulk-copy valid rows into a staging table, check them against existing data, and apply one MERGE in a single transaction; any problem rolls everything back and returns every error. Matching on a natural key makes a repeated upload change nothing. Return counts of added, updated, and unchanged rows.
How to answer: Cover the two stages of checks, error reporting by row, staging plus bulk copy, one transaction, the natural key for idempotency, and the summary.
S What security risks do file uploads and CSV exports bring, and how do you handle them? concept
Uploads: huge files (limit size), path traversal through file names (never use them; generate names), lying
types (check content and signatures), malware (scan files that are stored or shared), and public folders
(store outside them and serve through an endpoint that checks permissions). Exports: CSV injection, where a
value starting with =, +, -, or @ runs as a formula; neutralise such values. Both need authorization.
How to answer: Split upload and export risks, and give a defence for each.
Architect / Lead (10+ yrs)
A Users upload large files (videos, scans) to your platform. How would you design it? design
Do not stream large files through the API servers. The API authorises the upload and returns a short-lived signed URL to object storage; the client uploads directly, in resumable chunks. A storage event triggers background processing: virus scan, type check by content, thumbnails or conversion, then the metadata row is marked ready. Downloads use signed URLs or a CDN. Set size and type limits, quotas per tenant, retention rules, and encryption at rest.
How to answer: Cover direct-to-storage uploads with signed URLs, resumable chunks, asynchronous processing and scanning, metadata, downloads through a CDN, limits and quotas, and data protection.
Resources
- Code for this lesson: git tag
extra-9-csv-files(code repository coming soon) - Files:
src/SchoolApp.Api/Controllers/StudentFilesController.cs,src/SchoolApp.Core/Imports/StudentCsv.cs,src/SchoolApp.Core/Common/RecordValidator.cs,src/SchoolApp.Data/Dapper/DapperStudentBulkRepository.cs,tests/SchoolApp.Tests/Unit/StudentCsvTests.cs,tests/SchoolApp.Tests/Api/StudentFilesTests.cs,extras/e9-csv-files/1-new-admissions-with-mistakes.csv,library-assignment/e9-library-book-covers.cs - Video: coming soon
What's next
The office can now move students in and out in bulk. The next extra returns to Entity Framework Core from Module 9 and shows how a team changes a database safely with migrations. Until it is published, continue with the capstone: Lesson 97: From one school to 1,000
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.