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.

Example
string sql = "Invoice_Insert";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    var affectedRows = connection.Execute(sql,
        new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"},
        commandType: CommandType.StoredProcedure);

    My.Result.Show(affectedRows);
}

Stored Procedure Single

Many

Execute the Stored Procedure multiple times. Once for every object in the array list.

Example
string sql = "Invoice_Insert";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    var affectedRows = connection.Execute(sql,
        new[]
        {
            new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_1"},
            new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_2"},
            new {Kind = InvoiceKind.StoreInvoice, Code = "Many_Insert_3"}
        },
        commandType: CommandType.StoredProcedure
    );

    My.Result.Show(affectedRows);
}

Example - Execute INSERT

Single

Execute the INSERT Statement a single time.

Example
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 {CustomerName = "Mark"});
}

Try it online

Many

Execute the INSERT Statement multiple times. Once for every object in the array list.

Example
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"}
 		}
	);	
}

Try it online

Example - Execute UPDATE

Single

Execute the UPDATE Statement a single time.

Example
string sql = "UPDATE Categories SET Description = @Description WHERE CategoryID = @CategoryID;";

using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
	connection.Open();
	
	var affectedRows = connection.Execute(sql,new {CategoryID = 1, Description = "Soft drinks, coffees, teas, beers, mixed drinks, and ales"});

	Console.WriteLine(affectedRows);
}

Try it online

Many

Execute the UPDATE Statement multiple times. Once for every object in the array list.

Example
string sql = "UPDATE Categories SET Description = @Description WHERE CategoryID = @CategoryID;";

using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
	connection.Open();
	
	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);
	
}

Try it online

Example - Execute DELETE

Single

Execute the DELETE Statement a single time.

Example
string sql = "DELETE FROM Customers WHERE CustomerID = @CustomerID";

using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
	connection.Open();
	
	var affectedRows = connection.Execute(sql, new {CustomerID = 1});

	Console.WriteLine(affectedRows);
}

Try it online

Many

Execute the DELETE Statement multiple times. Once for every object in the array list.

Example
string sql = "DELETE FROM OrderDetails WHERE OrderDetailID = @OrderDetailID";

using (var connection = new SqlCeConnection("Data Source=SqlCe_W3Schools.sdf"))
{
	connection.Open();
	
	var affectedRows = connection.Execute(sql, 
		new[]
 		{
     		new {OrderDetailID = 1},
     		new {OrderDetailID = 2},
     		new {OrderDetailID = 3}
 		}
	);

	Console.WriteLine(affectedRows);
}

Try it online