Model Review
Write a model class with one property for each column, choosing C# types that match the SQL types, and nullable types for columns that allow NULL.
Journeyman · C# · T-SQL · Updated
After this lesson you can
- Write a model class whose properties match a table's columns
Key takeaways
- A model has an auto-property for each column, with the column's name and a C# type that matches its SQL type, such as
decimalformoney. - A column that allows
NULLgets a?on its type:string?forDescription,decimal?forUnitPrice. - A text column that never allows
NULLis a plainstring, started at""so it's nevernull. - Models usually live in a
Modelsfolder, one class per file, with a namespace that matches the folder, such asNorthwindReport.Models.
A model is an ordinary class, of the kind you wrote in Encapsulation and Classes, with an auto-property for each column. Here's the Categories table, as SSMS shows it under Columns:
| Column | SQL type | Allows NULL |
|---|---|---|
| CategoryId | int (identity) |
no |
| CategoryName | nvarchar(15) |
no |
| Description | ntext |
yes |
| Picture | image |
yes |
And its model, leaving out the picture, which this program doesn't need:
public class Category
{
public int CategoryId { get; set; }
public string CategoryName { get; set; } = "";
public string? Description { get; set; }
}
Choosing each property's type
Match each column's SQL type with its C# type, as in the table in ADO.NET Objects: int for int, string for nvarchar and ntext, decimal for money, bool for bit, DateTime for datetime.
Then ask whether the column allows NULL:
- No: use the plain type.
CategoryNamealways has a value, so it's astring, started at""so it's nevernull. - Yes: add a
?.Descriptionmight be missing, so it's astring?. For value types such asintanddecimal, the?makes a nullable type:decimal?can hold a price, ornullfor no price.
Here is the Products table's model. Look at which properties are nullable:
public class Product
{
public int ProductId { get; set; }
public string ProductName { get; set; } = "";
public int? SupplierId { get; set; }
public int? CategoryId { get; set; }
public string? QuantityPerUnit { get; set; }
public decimal? UnitPrice { get; set; }
public short? UnitsInStock { get; set; }
public short? UnitsOnOrder { get; set; }
public short? ReorderLevel { get; set; }
public bool Discontinued { get; set; }
}
ProductId, ProductName and Discontinued are required in the table, so their properties are plain. Every other column allows NULL, so its property can hold null too. UnitsInStock is a smallint in SQL Server, a small whole number, which is a short in C#.
Where models live
In a project, models usually get a folder of their own, Models, with one class per file: Models/Category.cs, Models/Product.cs. Each file starts with a namespace that matches its folder:
namespace NorthwindReport.Models;
public class Category
{
public int CategoryId { get; set; }
public string CategoryName { get; set; } = "";
public string? Description { get; set; }
}
Other files use the models with using NorthwindReport.Models;.
Do it: write the Employee model
The staff page needs Northwind's employees as C# objects, so it needs a model for the Employees table. Here are the columns it uses, as SSMS shows them under Columns:
| Column | SQL type | Allows NULL |
|---|---|---|
| EmployeeId | int (identity) |
no |
| LastName | nvarchar(20) |
no |
| FirstName | nvarchar(10) |
no |
| Title | nvarchar(30) |
yes |
| BirthDate | datetime |
yes |
| ReportsTo | int |
yes |
For example, here is Andrew Fuller's row. He runs sales and reports to no one, so his ReportsTo is NULL:
| EmployeeId | LastName | FirstName | Title | BirthDate | ReportsTo |
|---|---|---|---|---|---|
| 2 | Fuller | Andrew | Vice President, Sales | 1952-02-19 | NULL |
So the model's ReportsTo must be able to hold null: it's an int?, not an int.
Your job: fill in the class Employee with one auto-property ({ get; set; }) for each column.
- Give each property the column's name, spelled the same way.
- Give each property the C# type that matches the column's SQL type.
- A column that allows
NULLgets a?on its type. - A text column that doesn't allow
NULLis a plainstring, started at"".
Practiced live on the lesson page: write the C# right there and check it.
Check yourself
1 question to try on the lesson page.
So what
A good model is a faithful copy of a row's shape: the same names, matching types, and a ? wherever the database allows a value to be missing.