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

By

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.

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(quot;Product 1: {chai?.ProductName}, {chai?.UnitPrice:C}");

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

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

productDao.Delete(newId);
Console.WriteLine(quot;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?
        };
    }
}
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.

Do it: turn a missing price into NULL and back

The ProductDao's Add and Update send a product's price to the database as a parameter, and MapRowToProduct reads it back. A product can have no price yet: in C# its UnitPrice is null, and in the database it's NULL. Those two aren't the same thing, so the DAO converts each way. Write the two conversions as small methods of their own.

The two methods each get one value:

  • price: a product's UnitPrice, a decimal?: a price such as 5.99m, or null when there's no price.
  • column: what a reader gives for the UnitPrice column, an object: a decimal such as 18.00m, or DBNull.Value when the column is NULL.

For example, Hot Chocolate is added at $5.99, and a new tea is added with no price yet:

Call Returns Why
ToParameterValue(5.99m) 5.99m a price goes as it is
ToParameterValue(null) DBNull.Value a parameter needs DBNull.Value for NULL, not null
FromColumn(5.99m) 5.99m a price comes back as it is
FromColumn(DBNull.Value) null a NULL column becomes null

Your job: write the two methods.

  • object ToParameterValue(decimal? price) returns the price when there is one, and DBNull.Value when price is null.
  • decimal? FromColumn(object column) returns the price when column holds a decimal, and null when it holds DBNull.Value.
  • A price of 0 is a price, not a missing one: it goes as 0m and comes back as 0m.

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

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

Cheat sheets