Dapper .NET Guide – Execute Method

In this post, you’ll learn about the Execute Method and how to use it with various examples and use cases.

Dapper provides an Execute extension method for running a query or stored procedure. The number of rows affected by the query/stored procedure is returned as an int by this method.

Any object of type IDbConnection can invoke the Execute method.

Parameters

• The Execute method accepts a string SQL query and an optional object parameter.
• The object parameter can be used to pass SQL query parameters.
• The transaction, command timeout, and command type parameters are optional for the Execute method.

How to Execute a Stored Procedure in Dapper?

To run a stored procedure, use the Execute extension method. You should also set the commandType parameter to commandType:CommandType.StoredProcedure.

var sql = "spEmployeeUpdate";

using (var connection = new SqlConnection(connectionString))
{
    var rowsAffected = connection.Execute(sql, new { id = 1, FirstName = "Test", LastName = "LastName Test" }, commandType: CommandType.StoredProcedure);
}

In the above example, we are calling a stored procedure called “spEmployeeUpdate” and passing three parameters. The first parameter is an id, the second is a FirstName, and the third is a LastName.

The Execute method will return the number of rows that were updated.

INSERT Single Record using Execute in Dapper

You can insert data into the database using the Dapper Execute method. It accepts a SQL statement as well as an optional parameters object. The SQL statement will be executed against the database, and the number of rows affected will be returned.

When inserting a single record, you can directly pass the INSERT statement to the Execute method. It also supports parameterized INSERT statements, as shown in the example below.

string sql = "INSERT INTO Employee (FirstName) Values (@EmployeeName);";

using (var connection = new SqlConnection(<ConnectionString>))
{
    var affectedRows = connection.Execute(sql, new {CustomerName = "Senthil"});

    Console.WriteLine(affectedRows);

}

INSERT Multiple Record using Execute in Dapper

If you need to insert multiple records into the database, use the Execute method in conjunction with a SQL statement that contains a parameterized insert statement. The INSERT statement will be run for each record in the parameters object.

string sql = "INSERT INTO Employee (FirstName) Values (@FirstName);";

using (var connection = new SqlConnection(<Connection String>))
{
    connection.Open();

    var affectedRows = connection.Execute(sql,
    new[]
    {
    new {FirstName = "Senthil"},
    new { FirstName = "Kumar"},
    new { FirstName = "Ashwini"}
    }
);

Console.WriteLine(affectedRows);

UPDATE Record using Execute in Dapper

To execute a UPDATE statement with Dapper, you must supply the object to be updated as well as the SQL query. For instance, if we had a Employee class with the properties FirstName and LastName, we could update a record in the following way:

var employee = new Employee { FirstName = "Senhtil", LastName = "Kumar" }; 

using (var connection = new SqlConnection(connectionString))
{
    connection.Execute("UPDATE Employee SET FirstName = @FirstName, LastName = @LastName WHERE Id = 101", employee);
}

In this example, named parameters are used in our SQL query. Dapper will automatically map the Employee class’s properties to the query parameters.

If you are not using named parameters, you must pass in an anonymous object with the properties listed in the query in the same order.

As an example:

using (var connection = new SqlConnection(connectionString))
{
    connection.Execute("UPDATE Employee SET FirstName = @FirstName, LastName = @LastName WHERE Id = 1", new { FirstName = "Senthil", LastName = "Kumar" });
}

DELETE Record using Execute in Dapper

You can pass the DELETE statement directly to the Execute method when you need to delete a record. It also enables the use of parameterized DELETE statements, as shown in the example below.

string sql = "DELETE FROM Employee WHERE EmployeeId = @EmployeeID";

using (var connection = new SqlConnection(<ConnectionString>))
{            
    var affectedRows = connection.Execute(sql, new {EmployeeID = 1});

    Console.WriteLine(affectedRows);
}