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
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, notnull: write(object?)product.UnitPrice ?? DBNull.Value. Reading,as decimal?turns aNULLintonull. Addruns theINSERTandSELECT CAST(SCOPE_IDENTITY() AS int)as one command, andExecuteScalarreturns 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.
NULLboth ways. Reading,reader["UnitPrice"] as decimal?turns aNULLintonull. Writing,(object?)product.UnitPrice ?? DBNull.Valueturnsnullinto SQL'sNULL: a parameter needsDBNull.Value, not C#'snull.Addreturns the new id. TheINSERTandSELECT CAST(SCOPE_IDENTITY() AS int)run as one command, andExecuteScalarreturns the id.- One mapping method.
MapRowToProductis 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'sUnitPrice, adecimal?: a price such as5.99m, ornullwhen there's no price.column: what a reader gives for theUnitPricecolumn, anobject: adecimalsuch as18.00m, orDBNull.Valuewhen the column isNULL.
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, andDBNull.Valuewhenpriceisnull.decimal? FromColumn(object column)returns the price whencolumnholds adecimal, andnullwhen it holdsDBNull.Value.- A price of
0is a price, not a missing one: it goes as0mand comes back as0m.
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.