Dapper Stored Procedures

1. Stored Procedure: SQL logic pre‑written inside the database, invoked directly from your application.
2. Core point for calling stored procedures in Dapper: set CommandType.StoredProcedure.
3. Pass parameters via anonymous classes or entity objects; input parameters of the stored procedure will be mapped automatically.
4. Sample scenario: create a MySQL stored procedure to delete student records by ID.

Create MySQL Stored Procedure

-- Create stored procedure for deleting student records
CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_Student_PROC`(IN `id` INT)
BEGIN
    DELETE FROM student WHERE Id = id;
ENDCode language: JavaScript (javascript)

• IN id int: Input parameter, accepts the primary‑key ID for deletion
• BEGIN ~ END: Main body of the stored procedure

DROP PROCEDURE IF EXISTS Delete_Student_PROC;

Call the Stored Procedure

using System.Data;
using MySql.Data.MySqlClient;
using Dapper;
using System.Configuration;

static void Main(string[] args)
{
    var connStr = ConfigurationManager.ConnectionStrings["StudentConnection"].ConnectionString;
    using (IDbConnection db = new MySqlConnection(connStr))
    {
        // Parameter notes:
        // 1st argument: stored procedure name
        // 2nd argument: input parameters
        // 3rd argument: explicitly set command type to stored‑procedure
        int affectedRows = db.Execute(
            "Delete_Student_PROC",
            new { id = 2 },
            commandType: CommandType.StoredProcedure
        );

        Console.WriteLine($"Affected rows: {affectedRows}");
    }
    Console.ReadKey();
}Code language: JavaScript (javascript)

Stored Procedure Returning Result Sets

Use .Query<T>() when your stored procedure returns query data

List<Student> list = db.Query<Student>(
    "GetAllStudent_PROC",
    null,
    commandType: CommandType.StoredProcedure
).ToList();Code language: PHP (php)

• Ordinary SQL: commandType: CommandType.Text (default value, can be omitted)
• Stored procedure: commandType: CommandType.StoredProcedure (must be specified explicitly)

Dapper Stored Procedures