# Example: a Product DAO

A complete ProductDao for Northwind, with methods to get, search, add, update and delete products, all with parameters and one mapping method.

Journeyman · C# · T-SQL · Updated November 3, 2026

## After this lesson you can

- Write a DAO with methods that query and change a table using parameters

## Key takeaways

- A DAO gets its connection string through its constructor, so whoever creates it decides which database it uses.
- Each DAO method opens its own connection, uses a parameter for every value, even the id, and closes the connection when it's done.
- A parameter needs `DBNull.Value`, not `null`: write `(object?)product.UnitPrice ?? DBNull.Value`. Reading, `as decimal?` turns a `NULL` into `null`.
- `Add` runs the `INSERT` and `SELECT CAST(SCOPE_IDENTITY() AS int)` as one command, and `ExecuteScalar` returns the new id.
- Keep the mapping in one private method, such as `MapRowToProduct`, that every query uses.

Here's a complete DAO for Northwind's `Products` table. Read it method by method: each one is the connect, run, read pattern from this chapter, with its own SQL.

```csharp
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
ProductDao productDao = new ProductDao(connectionString);

Product? chai = productDao.GetById(1);
Console.WriteLine($"Product 1: {chai?.ProductName}, {chai?.UnitPrice:C}");

Console.WriteLine("Sauces:");
foreach (Product sauce in productDao.Search("Sauce"))
{
    Console.WriteLine($"  {sauce.ProductName}");
}

int newId = productDao.Add(new Product { ProductName = "Hot Chocolate", CategoryId = 1, UnitPrice = 5.99m });
Console.WriteLine($"Added Hot Chocolate: {newId > 77}");

productDao.Delete(newId);
Console.WriteLine($"Deleted it: {productDao.GetById(newId) is null}");

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public int? CategoryId { get; set; }
    public decimal? UnitPrice { get; set; }
    public short? UnitsInStock { get; set; }
}

public class ProductDao
{
    private readonly string connectionString;

    public ProductDao(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public Product? GetById(int productId)
    {
        string sql = """
            SELECT ProductId, ProductName, CategoryId, UnitPrice, UnitsInStock
            FROM Products
            WHERE ProductId = @ProductId;
            """;

        using SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        using SqlCommand command = new SqlCommand(sql, connection);
        command.Parameters.AddWithValue("@ProductId", productId);

        using SqlDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
            return MapRowToProduct(reader);
        }
        return null;
    }

    public List<Product> Search(string text)
    {
        string sql = """
            SELECT ProductId, ProductName, CategoryId, UnitPrice, UnitsInStock
            FROM Products
            WHERE ProductName LIKE '%' + @Text + '%'
            ORDER BY ProductName;
            """;

        List<Product> products = new List<Product>();

        using SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        using SqlCommand command = new SqlCommand(sql, connection);
        command.Parameters.AddWithValue("@Text", text);

        using SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            products.Add(MapRowToProduct(reader));
        }
        return products;
    }

    public int Add(Product product)
    {
        string sql = """
            INSERT INTO Products (ProductName, CategoryId, UnitPrice, UnitsInStock)
            VALUES (@ProductName, @CategoryId, @UnitPrice, @UnitsInStock);
            SELECT CAST(SCOPE_IDENTITY() AS int);
            """;

        using SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        using SqlCommand command = new SqlCommand(sql, connection);
        command.Parameters.AddWithValue("@ProductName", product.ProductName);
        command.Parameters.AddWithValue("@CategoryId", (object?)product.CategoryId ?? DBNull.Value);
        command.Parameters.AddWithValue("@UnitPrice", (object?)product.UnitPrice ?? DBNull.Value);
        command.Parameters.AddWithValue("@UnitsInStock", (object?)product.UnitsInStock ?? DBNull.Value);

        return (int)command.ExecuteScalar();
    }

    public void Update(Product product)
    {
        string sql = """
            UPDATE Products
            SET ProductName = @ProductName
                , UnitPrice = @UnitPrice
                , UnitsInStock = @UnitsInStock
            WHERE ProductId = @ProductId;
            """;

        using SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        using SqlCommand command = new SqlCommand(sql, connection);
        command.Parameters.AddWithValue("@ProductId", product.ProductId);
        command.Parameters.AddWithValue("@ProductName", product.ProductName);
        command.Parameters.AddWithValue("@UnitPrice", (object?)product.UnitPrice ?? DBNull.Value);
        command.Parameters.AddWithValue("@UnitsInStock", (object?)product.UnitsInStock ?? DBNull.Value);
        command.ExecuteNonQuery();
    }

    public void Delete(int productId)
    {
        using SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        using SqlCommand command = new SqlCommand("DELETE FROM Products WHERE ProductId = @ProductId;", connection);
        command.Parameters.AddWithValue("@ProductId", productId);
        command.ExecuteNonQuery();
    }

    private static Product MapRowToProduct(SqlDataReader reader)
    {
        return new Product
        {
            ProductId = (int)reader["ProductId"],
            ProductName = (string)reader["ProductName"],
            CategoryId = reader["CategoryId"] as int?,
            UnitPrice = reader["UnitPrice"] as decimal?,
            UnitsInStock = reader["UnitsInStock"] as short?
        };
    }
}
```

```text
Product 1: Chai, $18.00
Sauces:
  Louisiana Fiery Hot Pepper Sauce
  Northwoods Cranberry Sauce
Added Hot Chocolate: True
Deleted it: True
```

## Things to notice

- **The connection string comes in through the constructor.** The DAO doesn't decide which database to use; whoever creates it does. That makes the same DAO work against your development database and, later, a test database.
- **Each method opens its own connection, and closes it.** Connections are borrowed briefly, never kept.
- **Every value is a parameter.** Even the id: a habit that protects you everywhere.
- **`NULL` both ways.** Reading, `reader["UnitPrice"] as decimal?` turns a `NULL` into `null`. Writing, `(object?)product.UnitPrice ?? DBNull.Value` turns `null` into SQL's `NULL`: a parameter needs `DBNull.Value`, not C#'s `null`.
- **`Add` returns the new id.** The `INSERT` and `SELECT CAST(SCOPE_IDENTITY() AS int)` run as one command, and `ExecuteScalar` returns the id.
- **One mapping method.** `MapRowToProduct` is private: only the DAO needs it.

In a project, `Product` goes in `Models/Product.cs` and `ProductDao` in `Data/ProductDao.cs`, and `Program.cs` holds only the first lines.

## Check yourself

1 question to try on the lesson page.

## So what

That's a whole table's data access, in one class, safe from SQL injection, with the rest of the program working only in `Product` objects. You'll write three of these in the exercise.

## Key terms

- [Data Access Object (DAO)](https://www.ask-codey.com/glossary/data-access-object-dao)
- [CRUD](https://www.ask-codey.com/glossary/crud)
- [DBNull](https://www.ask-codey.com/glossary/dbnull)

## Cheat sheets

- [Example: a Product DAO](https://www.ask-codey.com/cheat-sheets/mapping-data-to-models-in-csharp-example-a-product-dao-cheat-sheet)

Source: https://www.ask-codey.com/lessons/mapping-data-to-models-in-csharp-example-a-product-dao