Seeding and Importing Data

After this lesson you can

• Write a seed script that can safely run more than once
• Import rows from a CSV file with C#, checking each row and reporting the ones skipped

Key takeaways

• A seed script must be idempotent: check with IF NOT EXISTS before each INSERT, so running it again changes nothing.
• Seed only what every copy of the database needs, and keep test data for your laptop in a separate script.
• An import checks every row, and reports each bad one with its line number, so someone can fix the file.
• Add the good rows inside one transaction, so either they're all added or none are.
• Splitting on commas works for simple files; when values can have commas inside them, use a CSV library such as CsvHelper.

Every database you create starts empty. Before an app is useful, data has to get in. Two jobs come up again and again:

Seed scripts

A seed script is SQL that fills in data. Keep it in the repository, next to the scripts that create the tables, so every developer, test database and new server gets the same data.

The most important rule: a seed script must be safe to run again. Someone will run it twice, on purpose or by accident, and a plain INSERT would then add every row a second time, or stop with an error at the first UNIQUE column. So each insert first checks whether the row is there:

IF NOT EXISTS (SELECT 1 FROM Users WHERE UserName = 'admin')
    INSERT INTO Users (UserName, PasswordHash, Role)
    VALUES ('admin', 'AQAAAAIAAYagAAAAE...', 'Admin');

IF NOT EXISTS (SELECT 1 FROM Categories WHERE CategoryName = 'Snacks')
    INSERT INTO Categories (CategoryName, Description)
    VALUES ('Snacks', 'Chips, nuts and trail mix');

Run it once, and the rows are added. Run it again, and nothing changes. A script like that is called idempotent: running it many times has the same effect as running it once.

• The admin's password is stored as a hash, never as the password. Make the hash once with PasswordHasher, from Hashing and Encryption, and paste it in.
• Seed only what every copy needs. Test data for your laptop goes in a separate script, so it never reaches the live database.

(With EF Core migrations, which this course doesn't use, HasData in the DbContext plays the same part.)

Importing a CSV file

A CSV file, comma-separated values, is the most common way data arrives: every spreadsheet can save one. A supplier sends new products, in new-products.csv:

Name,CategoryId,Price
Trail Mix,3,4.50
Sea Salt Crackers,5,3.25
Chai,1,18.00
Mango Chutney,2,twelve
,3,2.00
Smoked Almonds,42,6.75

A file from outside always has problems. Here: Chai is already in Northwind, twelve isn't a number, one row has no name, and there's no category 42. An import must check every row, add the good ones, and report the bad ones, so someone can fix the file. It must never add half a row, or stop halfway and leave the database with some of the file in it.

A console app with the Dapper and Microsoft.Data.SqlClient packages:

using System.Globalization;
using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

var existingNames = (await connection.QueryAsync<string>("SELECT ProductName FROM Products;"))
    .ToHashSet(StringComparer.OrdinalIgnoreCase);
var categoryIds = (await connection.QueryAsync<int>("SELECT CategoryID FROM Categories;")).ToHashSet();

var newProducts = new List<NewProduct>();
var problems = new List<string>();
int lineNumber = 1;

foreach (string line in File.ReadLines("new-products.csv").Skip(1))
{
    lineNumber++;
    string[] fields = line.Split(',');
    if (fields.Length != 3)
    {
        problems.Add(quot;Line {lineNumber}: expected 3 values, found {fields.Length}.");
        continue;
    }

    string name = fields[0].Trim();
    if (name == "")
    {
        problems.Add(quot;Line {lineNumber}: the name is missing.");
    }
    else if (existingNames.Contains(name))
    {
        problems.Add(quot;Line {lineNumber}: {name} is already a product.");
    }
    else if (!int.TryParse(fields[1], out int categoryId) || !categoryIds.Contains(categoryId))
    {
        problems.Add(quot;Line {lineNumber}: there's no category {fields[1]}.");
    }
    else if (!decimal.TryParse(fields[2], CultureInfo.InvariantCulture, out decimal price) || price < 0)
    {
        problems.Add(quot;Line {lineNumber}: the price {fields[2]} isn't a number.");
    }
    else
    {
        newProducts.Add(new NewProduct(name, categoryId, price));
        existingNames.Add(name);
    }
}

using SqlTransaction transaction = connection.BeginTransaction();
await connection.ExecuteAsync(
    "INSERT INTO Products (ProductName, CategoryID, UnitPrice) VALUES (@Name, @CategoryId, @Price);",
    newProducts, transaction);
transaction.Commit();

Console.WriteLine(quot;Added {newProducts.Count} products.");
foreach (string problem in problems)
{
    Console.WriteLine(problem);
}

public record NewProduct(string Name, int CategoryId, decimal Price);

Added 2 products.
Line 4: Chai is already a product.
Line 5: the price twelve isn't a number.
Line 6: the name is missing.
Line 7: there's no category 42.

• File.ReadLines(...).Skip(1) reads the file a line at a time, skipping the header line.
• Every check has its own message, with the line number, so whoever fixes the file knows exactly where to look. Line numbers count the header as line 1, as a spreadsheet would.
• CultureInfo.InvariantCulture reads 4.50 with a dot, whatever language the computer is set to. In some countries, 4,50 is how prices are written, and the import would read the wrong number.
• existingNames.Add(name) catches the same name twice within the file, too.
• ExecuteAsync(sql, newProducts, transaction): given a list, Dapper runs the INSERT once for each item. Inside a transaction, either they're all added, or, if one fails, none are, as in Transactions.

Run it a second time, and it adds nothing: Trail Mix and Sea Salt Crackers are now already a product. Checking for what's already there makes an import safe to repeat, like a seed script.

Splitting on commas is fine for simple files like this one. Real CSV files can have commas inside a value, written in quotes, such as "Crackers, salted". For those, use a CSV library, such as CsvHelper, rather than Split.

For a one-off load that needs no checks, SSMS can do it without code: right-click the database, then Tasks → Import Flat File, and follow the wizard.

Check yourself

2 questions to try on the lesson page.

So what

Seed scripts give every copy of the database what it needs, and are written to run again safely. Imports trust nothing: check every row, add the good ones in one transaction, and report the rest with line numbers, so the data that gets in is data you meant to let in.

Key terms

• Seed Script
• Idempotent
• CSV File

Cheat sheets

• Seeding and Importing Data