Example: a Product DAO

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.

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)
• CRUD
• DBNull

Cheat sheets

• Example: a Product DAO