Database-backed .NET applications depend on both a correct schema and correctly parameterized application queries. This tutorial links foreign keys, stored procedures, and prepared SQL commands.
Design the schema first
A foreign key from Orders to Customers prevents an order from referencing a nonexistent customer, provided the constraint is enabled and the relationship is correctly modelled.
A stored procedure with output
CREATE OR ALTER PROCEDURE dbo.CountCustomerOrders
@CustomerID int,
@OrderCount int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @OrderCount = COUNT(*)
FROM dbo.Orders
WHERE CustomerID = @CustomerID;
END;Call queries with parameters
Use Microsoft.Data.SqlClient and a SqlCommand; choose CommandType.StoredProcedure when invoking a procedure by name, and set the Direction property on output parameters. Prefer explicitly typed SqlParameter objects to ambiguous inferred types.
Transaction boundaries
When a business operation changes several tables, group the dependent writes into a transaction and commit only when each step succeeds. Catch errors, roll back, and avoid holding a transaction open while awaiting external services.
Exercise
Add Products and OrderLines with primary/foreign keys; create a procedure to return items from one order, and call it with a parameter from a small .NET console application.
AI, sources and reuse: This is an AI-generated rewrite informed by the original Open Education post (#69886), with additional explanations and examples. The new article text may be copied, adapted, and republished with or without a reference to this site. Third-party resources and trademarks keep their own rights. AI can make mistakes. Verify version support, code correctness, and security practices against Microsoft documentation, and test with nonproduction data first.
