In the last lesson you read data. Now you change it: create new rows, change existing ones, delete old ones. Along the way you learn two things that set a database apart from a JSON file. First, every column has a fixed data type. Second, the database can enforce rules by itself: an email address exists only once, an appointment belongs to a contact that exists, a duration is never zero minutes. It rejects every change that breaks such a rule, no matter which program it comes from.

At the end you write, for the first time, a C# program that talks to SQL Server, and in doing so you make the mistake that attackers love most. Then you fix it and prove with a test that it doesn’t come back.

Work in the practice database practice from lesson 2.4.2 again. Create it anew first, so that your numbers match those in the lesson. In the folder k2-practice/sql-queries:

PowerShell
dotnet run check.cs -- create

INSERT: creating rows

You create a new row with INSERT:

SQL
INSERT INTO dbo.Contacts (Id, Name, Email, CompanyId, CreatedOn)
VALUES (11, N'Karl Concept', N'karl@example.org', 5, '2025-03-03');

(1 row affected)
  • The column list in parentheses after the table name says which value goes into which column. Always write it out. Without it, the order of the columns in the table counts, and an INSERT that is right today puts the values in the wrong places or fails after a new column is added.
  • Columns that you leave out get their default value. If a column has none, that is NULL, and if NULL isn’t allowed, the command fails.
  • Several rows you write separated by commas after one VALUES, as in practice.sql. They are created together: if one fails, none is created.

In practice, the script assigns the numbers itself. Usually you leave that to the server, with IDENTITY as with the notes in lesson 2.4.1. Your program then knows the number only after the INSERT and asks for it right away with OUTPUT. Try it on a temporary table: its name begins with #, it lives in tempdb, and it disappears when the connection ends. Run the four statements together:

SQL
CREATE TABLE #Tags
(
    Id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name nvarchar(50) NOT NULL
);

INSERT INTO #Tags (Name) OUTPUT inserted.Id VALUES (N'customer');
INSERT INTO #Tags (Name) OUTPUT inserted.Id VALUES (N'supplier');

DROP TABLE #Tags;

Id
1

Id
2

OUTPUT inserted.Id returns the values of the new row as a result, as if you had written a SELECT afterward. EF Core does exactly that when it saves a row with IDENTITY; in lesson 2.4.7 you see it in the generated SQL.