After lesson M3.2 there are two ways to the same rows: the Windows program with its DataSet and the new layer with Entity Framework Core. Soon a web API and a Blazor page will join, and for weeks or months Ms. Aydin works in the old program and a pilot customer in the new one. Both read, both write, and there is one database. Whoever does not regulate that deliberately learns the rules from the errors.
This lesson regulates it: with a protective layer in the new code, with clear owners for each piece of data, with a view as a seam and with a method for changing the schema without breaking either program. The data architecture behind it, that is, when to share databases and when not to, is in K4, module 4.13. Here we deal with the transition.
What goes wrong when two programs share a database
The failure patterns can be listed in advance. Each one exists in the Order Book or arises in the coming weeks:
| Failure pattern | Example in the Order Book | Remedy |
|---|---|---|
| Both change the same row | Ms. Aydin changes the appointment in the old program, the pilot customer the note in the new one | Lock in both programs (lesson M3.2) |
| The new program writes something the old one cannot read | Status “Invoiced” without an invoice number: The old program's export reads (string)order["InvoiceNumber"] and fails with an exception |
Protective layer when writing |
| A schema change breaks the other program | a new required column without a default value: every INSERT of the old program fails |
Expand and Contract |
| A rule exists in only one place | the trigger that moves the maintenance plan forward is never called by the new code and replaced by nobody | Determine owners, test |
| The words of the old system seep into the new one | status as a string, Appointment as a nullable date |
Translation in one place |
| Nobody knows who may do what | everybody writes everywhere because it is technically possible | Database permissions, document |
The two middle rows are the most dangerous: They break quietly, and nobody thinks of them as long as nothing goes wrong.
How quietly, a probe shows. The old program writes orders with an INSERT that names the columns from its SELECT list. If you add a
column to the table, it depends on the kind of column. The probe runs in the test database, in a transaction that is rolled back:
$insert = "INSERT dbo.Orders (CustomerId, MaintenanceId, Title, Kind, Status, Appointment, Hours, Material, InvoiceNumber) VALUES (1, NULL, N'Test', N'Heating', N'New', NULL, 0, 0, NULL);"
sqlcmd -S '(localdb)\MSSQLLocalDB' -d OrderBookTest -E -W -Q "BEGIN TRAN; ALTER TABLE dbo.Orders ADD Priority int NULL; $insert SELECT N'nullable: INSERT ok' AS Result; ROLLBACK;"
sqlcmd -S '(localdb)\MSSQLLocalDB' -d OrderBookTest -E -W -Q "BEGIN TRAN; ALTER TABLE dbo.Orders ADD Priority int NOT NULL CONSTRAINT DF_Priority DEFAULT (0); $insert SELECT N'required with default: INSERT ok' AS Result; ROLLBACK;"
sqlcmd -S '(localdb)\MSSQLLocalDB' -d OrderBookTest -E -W -Q "BEGIN TRAN; ALTER TABLE dbo.Orders ADD Priority int NOT NULL CONSTRAINT DF_Priority DEFAULT (0); ALTER TABLE dbo.Orders DROP CONSTRAINT DF_Priority; $insert ROLLBACK;"
Result
------
nullable: INSERT ok
Result
------
required with default: INSERT ok
Msg 515, Level 16, State 2, Server ..., Line 1
Cannot insert the value NULL into column 'Priority', table 'OrderBookTest.dbo.Orders'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(The third line has a required column without a default value, as a new program likes to create it: You create it with a default value so that the table can be changed, and remove it afterwards. The old program does not know the column and cannot fill it. The wording of the message depends on the language of the SQL Server installation.)
The probe yields the rule of thumb of the whole lesson: A change for which the old program has to change nothing is backward compatible. Anything else breaks it.