Parameters & Mapping

Level: Beginner

ℹ️ Where This Fits

Dapper parameters ensure SQL injection prevention and clean code. Understanding parameter binding and DynamicParameters is essential for safe queries.

ℹ️ What You'll Learn

• Anonymous objects: new { className = "10-A" } for parameter binding
• Parameter matching: Property name → @parameter in SQL
• DynamicParameters: Advanced parameter builder
• DbString: Special handling for string parameters
• Parameter direction: Input (default), Output
• Type safety: Dapper infers types from values
• Performance: Parameters cached by Dapper
• School Management: Safe student queries, batch fee updates
• Common mistakes: String concatenation (SQL injection!), wrong parameter names

Basic Parameter Binding

// Anonymous object parameters
var students = conn.Query<Student>(
    "SELECT Id, Name, ClassName FROM Students WHERE ClassName = @className AND Status = @status",
    new { className = "10-A", status = "Active" }
).ToList();

// Multiple parameters
var fees = conn.Query<FeeAccount>(
    @"SELECT Id, TotalFees, PaidAmount 
      FROM FeeAccount 
      WHERE StudentId = @studentId 
      AND Status = @status 
      AND DueDate < @dueDate",
    new 
    { 
        studentId = 101, 
        status = "Pending", 
        dueDate = DateTime.Now 
    }
).ToList();

DynamicParameters for Complex Scenarios

var parameters = new DynamicParameters();
parameters.Add("@id", 101);
parameters.Add("@name", "Updated Name");
parameters.Add("@newId", dbType: DbType.Int32, direction: ParameterDirection.Output);

conn.Execute(
    "UPDATE Students SET Name = @name WHERE Id = @id; SELECT SCOPE_IDENTITY() AS NewId;",
    parameters
);

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

DbString for String Length Control

var parameters = new DynamicParameters();
parameters.Add("@name", new DbString { Value = "Ravi Kumar", Length = 100 });
parameters.Add("@className", new DbString { Value = "10-A", Length = 10 });

conn.Execute(
    "INSERT INTO Students (Name, ClassName) VALUES (@name, @className)",
    parameters
);

Output Parameters from Stored Procedure

var parameters = new DynamicParameters();
parameters.Add("@name", "Priya Sharma");
parameters.Add("@className", "10-A");
parameters.Add("@newId", dbType: DbType.Int32, direction: ParameterDirection.Output);

conn.Execute(
    "sp_CreateStudent",
    parameters,
    commandType: CommandType.StoredProcedure
);

var studentId = parameters.Get<int>("@newId");

Automatic Property Mapping

Student class properties map to columns automatically:

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string ClassName { get; set; }
}

// Dapper automatically maps:
// Id column → student.Id
// Name column → student.Name
// ClassName column → student.ClassName
var students = conn.Query<Student>(
    "SELECT Id, Name, ClassName FROM Students"
).ToList();

Column Name Mismatch - Custom Mapping

// If column names don't match properties, use aliases or custom mapping
var students = conn.Query<Student>(
    @"SELECT 
        StudentId as Id,
        StudentName as Name,
        ClassNumber as ClassName
      FROM Students"
).ToList();

School Management Example - Safe Batch Update

public async Task UpdateFeeStatusAsync(int[] studentIds, string newStatus)
{
    var sql = @"
        UPDATE FeeAccount 
        SET Status = @status, UpdatedAt = GETDATE()
        WHERE StudentId IN (SELECT value FROM STRING_SPLIT(@studentIds, ','))";
    
    var parameters = new DynamicParameters();
    parameters.Add("@studentIds", string.Join(",", studentIds));
    parameters.Add("@status", newStatus);
    
    await _connection.ExecuteAsync(sql, parameters);
}

Best Practices

✓ Always use parameters (never string concatenation) ✓ Use anonymous objects for simple cases ✓ Use DynamicParameters for complex scenarios ✓ Match SQL parameter names to property names

💡 Parameter Name Matching

If your SQL has @className, your anonymous object must have:

new { className = "10-A" }  // Property named 'className' (case-insensitive)

Dapper matches by name.

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Parameters & Mapping. Try these prompts:

• "What's the difference between anonymous objects and DynamicParameters?"
• "How do you pass output parameters in Dapper?"
• "Why should you always use parameters?"
• "How does Dapper map columns to class properties?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> 04. Stored Procedures & Multiple Results