4. Stored procedures and output parameters

await using var cmd = new SqlCommand("dbo.usp_PlaceOrder", conn) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = customerId;
cmd.Parameters.Add("@Total", SqlDbType.Decimal).Value = total;
var outId = cmd.Parameters.Add("@OrderId", SqlDbType.Int);
outId.Direction = ParameterDirection.Output;
var ret = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
ret.Direction = ParameterDirection.ReturnValue;

await cmd.ExecuteNonQueryAsync();
int orderId = (int)outId.Value;
int status = (int)ret.Value;

var table = new DataTable();
table.Columns.Add("ProductId", typeof(int));
table.Columns.Add("Qty", typeof(int));
foreach (var l in lines) table.Rows.Add(l.ProductId, l.Qty);

var p = cmd.Parameters.AddWithValue("@Lines", table);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = "dbo.OrderLineType";      // CREATE TYPE dbo.OrderLineType AS TABLE (ProductId INT, Qty INT)