Dapper Parameters — Safe Parameterization and Dynamic SQL

SQL injection: user input inserted directly into SQL string
  Bad:  $"SELECT * FROM patients WHERE mrn = '{mrnInput}'"
  If mrnInput = "' OR '1'='1", all patients are returned.
  If mrnInput = "'; DROP TABLE patients; --", data is lost.

Dapper parameters:
  Good: "SELECT * FROM patients WHERE mrn = @Mrn", new { Mrn = mrnInput }
  Dapper wraps the value in a SqlParameter — input is never interpreted as SQL.
  Always use parameterized queries. Never concatenate user input.

// Simplest approach: anonymous object with named properties
var patients = await connection.QueryAsync<PatientDto>(
    "SELECT id AS Id, mrn AS Mrn FROM patients WHERE ward_id = @WardId AND is_deleted = @IsDeleted",
    new { WardId = wardId, IsDeleted = false });

// Multiple parameters
var prescription = await connection.QueryFirstOrDefaultAsync<PrescriptionDto>(
    @"SELECT id AS Id, medication AS Medication
      FROM prescriptions
      WHERE patient_id = @PatientId
        AND id         = @PrescriptionId
        AND is_active  = 1",
    new { PatientId = patientId, PrescriptionId = prescriptionId });

// Use DynamicParameters when you need to:
//   - Add parameters conditionally
//   - Map output parameters
//   - Control DbType explicitly

var parameters = new DynamicParameters();
parameters.Add("@PatientId", patientId, DbType.Guid);
parameters.Add("@FromDate",  fromDate,  DbType.DateTime2);
parameters.Add("@ToDate",    toDate,    DbType.DateTime2);

if (includeCancelled.HasValue)
    parameters.Add("@IncludeCancelled", includeCancelled.Value, DbType.Boolean);

var results = await connection.QueryAsync<PrescriptionDto>(sql, parameters);

// Dapper expands IEnumerable parameters for IN clauses automatically
var patientIds = new[] { id1, id2, id3 };

var prescriptions = await connection.QueryAsync<PrescriptionDto>(
    @"SELECT id AS Id, patient_id AS PatientId, medication AS Medication
      FROM prescriptions
      WHERE patient_id IN @PatientIds AND is_active = 1",
    new { PatientIds = patientIds });

// Dapper generates: WHERE patient_id IN (@PatientIds1, @PatientIds2, @PatientIds3)
// The list expansion is handled safely — each value becomes a separate parameter.

// For large lists (100+ items), use a table-valued parameter instead for SQL Server:
// Large IN clauses can cause query plan issues with many parameters.

// SQL Server: create a TVP type in the database
// CREATE TYPE dbo.GuidList AS TABLE (Id UNIQUEIDENTIFIER);

// C# usage with Dapper
var patientIds = new DataTable();
patientIds.Columns.Add("Id", typeof(Guid));
foreach (var id in largeIdList)
    patientIds.Rows.Add(id);

var results = await connection.QueryAsync<PatientDto>(
    @"SELECT p.id AS Id, p.mrn AS Mrn
      FROM patients p
      INNER JOIN @PatientIds ids ON ids.Id = p.id",
    new { PatientIds = patientIds.AsTableValuedParameter("dbo.GuidList") });

// Build SQL dynamically with a StringBuilder — still parameterized
public async Task<IReadOnlyList<PatientDto>> SearchAsync(
    PatientSearchQuery query, CancellationToken ct)
{
    var sql        = new StringBuilder(@"
        SELECT id AS Id, mrn AS Mrn, first_name AS FirstName, last_name AS LastName
        FROM patients
        WHERE is_deleted = 0");

    var parameters = new DynamicParameters();

    if (!string.IsNullOrWhiteSpace(query.Mrn))
    {
        sql.Append(" AND mrn = @Mrn");
        parameters.Add("@Mrn", query.Mrn);
    }

    if (!string.IsNullOrWhiteSpace(query.LastName))
    {
        sql.Append(" AND last_name LIKE @LastName");
        parameters.Add("@LastName", $"%{query.LastName}%");
    }

    if (query.WardId.HasValue)
    {
        sql.Append(" AND ward_id = @WardId");
        parameters.Add("@WardId", query.WardId.Value);
    }

    if (query.AdmittedAfter.HasValue)
    {
        sql.Append(" AND admitted_at >= @AdmittedAfter");
        parameters.Add("@AdmittedAfter", query.AdmittedAfter.Value);
    }

    sql.Append(" ORDER BY last_name, first_name OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY");

    using var connection = _connectionFactory.CreateConnection();
    var result = await connection.QueryAsync<PatientDto>(sql.ToString(), parameters);
    return result.AsList();
}

// Dapper handles null values — pass null for nullable columns
await connection.ExecuteAsync(
    "UPDATE patients SET discharge_date = @DischargeDate WHERE id = @Id",
    new { DischargeDate = (DateTime?)null, Id = patientId });
// Generates: SET discharge_date = NULL

// With DynamicParameters: explicit null handling
var parameters = new DynamicParameters();
parameters.Add("@DischargeDate", dischargeDate.HasValue ? dischargeDate.Value : (object)DBNull.Value, DbType.DateTime2);
parameters.Add("@Id", patientId, DbType.Guid);

// Stored procedure with output parameter
var parameters = new DynamicParameters();
parameters.Add("@PatientId",      patientId,  DbType.Guid,   ParameterDirection.Input);
parameters.Add("@MedicationCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

await connection.ExecuteAsync(
    "EXEC sp_GetPatientMedicationCount @PatientId, @MedicationCount OUTPUT",
    parameters);

var count = parameters.Get<int>("@MedicationCount");