Dapper - Execute
Description
Execute is an extension method which can be called from any object of type IDbConnection. It can execute a command one or multiple times and return the number of affected rows. This method is usually used to execute:
Parameters
The following table shows different parameter of an Execute method.
| Name | Description |
|---|---|
| sql | The command text to execute. |
| param | The command parameters (default = null). |
| transaction | The transaction to use (default = null). |
| commandTimeout | The command timeout (default = null) |
| commandType | The command type (default = null) |
Example - Execute Stored Procedure
Single
Execute the Stored Procedure a single time.
string sql = "Invoice_Insert";
using (var connection = My.ConnectionFactory())
{
var affectedRows = connection.Execute(sql,
new ,
commandType: CommandType.StoredProcedure);
My.Result.Show(affectedRows);
}
Many
Execute the Stored Procedure multiple times. Once for every object in the array list.
string sql = "Invoice_Insert";
using (var connection = My.ConnectionFactory())
{
var affectedRows = connection.Execute(sql,
new[]
{
new ,
new ,
new
},
commandType: CommandType.StoredProcedure
);
My.Result.Show(affectedRows);
}
Example - Execute INSERT
Single
Execute the INSERT Statement a single time.
string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
var affectedRows = connection.Execute(sql, new );
Console.WriteLine(affectedRows);
var customer = connection.Query<Customer>("Select * FROM CUSTOMERS WHERE CustomerName = 'Mark'").ToList();
FiddleHelper.WriteTable(customer);
}
Many
Execute the INSERT Statement multiple times. Once for every object in the array list.
string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
connection.Open();
var affectedRows = connection.Execute(sql,
new[]
{
new {CustomerName = "John"},
new {CustomerName = "Andy"},
new {CustomerName = "Allan"}
}
);
Console.WriteLine(affectedRows);
Example - Execute UPDATE
Single
Execute the UPDATE Statement a single time.
string sql = "UPDATE Categories SET Description = @Description WHERE CategoryID = @CategoryID;";
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
var affectedRows = connection.Execute(sql,new {CategoryID = 1, Description = "Soft drinks, coffees, teas, beers, mixed drinks, and ales"});
Console.WriteLine(affectedRows);
}
Many
Execute the UPDATE Statement multiple times. Once for every object in the array list.
string sql = "UPDATE Categories SET Description = @Description WHERE CategoryID = @CategoryID;";
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
var affectedRows = connection.Execute(sql,
new[]
{
new {CategoryID = 1, Description = "Soft drinks, coffees, teas, beers, mixed drinks, and ales"},
new {CategoryID = 4, Description = "Cheeses and butters etc."}
}
);
Console.WriteLine(affectedRows);
Example - Execute DELETE
Single
Execute the DELETE Statement a single time.
string sql = "DELETE FROM Customers WHERE CustomerID = @CustomerID";
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
var affectedRows = connection.Execute(sql, new {CustomerID = 1});
Console.WriteLine(affectedRows);
}
Many
Execute the DELETE Statement multiple times. Once for every object in the array list.
string sql = "DELETE FROM OrderDetails WHERE OrderDetailID = @OrderDetailID";
using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
var affectedRows = connection.Execute(sql,
new[]
{
new {OrderDetailID = 1},
new {OrderDetailID = 2},
new {OrderDetailID = 3}
}
);
Console.WriteLine(affectedRows);