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

By

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 decimal for money.
  • A column that allows NULL gets a ? on its type: string? for Description, decimal? for UnitPrice.
  • A text column that never allows NULL is a plain string, started at "" so it's never null.
  • Models usually live in a Models folder, one class per file, with a namespace that matches the folder, such as NorthwindReport.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. CategoryName always has a value, so it's a string, started at "" so it's never null.
  • Yes: add a ?. Description might be missing, so it's a string?. For value types such as int and decimal, the ? makes a nullable type: decimal? can hold a price, or null for 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 NULL gets a ? on its type.
  • A text column that doesn't allow NULL is a plain string, 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.

Key terms

Cheat sheets