Dapper Execute Method – Execute SQL and Stored Procedure

Dapper Execute method is used for exceuting an SQL query or Stored Procedure. It returns number of rows affected in the execute operation. This method is use for Inserting, Updating & Deleting records in the database.

The Exceute method can take upto 4 parameters, the first parameter “sql” specifies the sql statement or stored procedure name, it is required, while the other 3 parameters are optional.

Learn Dapper from the complete series”

1. Dapper Execute Method – Execute SQL and Stored Procedure
2. Dapper Query Method – Execute SQL and Stored Procedure
3. Dapper One-to-One Relationship
4. Dapper One-to-Many Relationship
5. How to Seed Database with Dapper
6. How to perform CRUD Operations in Dapper

This is how the syntax of Execute method looks:

var sql = "Insert into Student(Name, Age) values (@StuName, @StuAge)";

using (var connection = new SqlConnection(connectionString))
{
    var rowsAffected = connection.Execute(sql, new { StuName = "Ram", StuAge = 10 });
}

The parameters of Execute method are given below.

Let us now see examples where we will use Execute method in different scenarios.

Execute SQL Query

In the below example we are executing an Insert SQL Query. This will add a single record to the database table called Inventory.

string myCS = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=DapperStart;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

var sql = @"Insert into Inventory(Name, Price, Quantity) values (@Name, @Price, @Quantity)";

using (var connection = new SqlConnection(myCS))
{
    var rowsAffected = connection.Execute(sql, new { Name = "Shirts", Price = 19, Quantity = 20 });
}

In the above code variable myCS contains the database connection string while the sql variable contains the SQL parameterized insert query. The 3 parameterized parameters in the above SQL query are @Name, @Price, @Quantity.

Next, we have created an instance of SqlConnection class of the System.Data.SqlClient namespace and then used Dapper Execute method to execute the insert query.

We have passed “sql” varaible as the first parameter. And for the second parameter we have passed the values for the 3 columns of Inventory table in parameterized manner. The ‘Name’ column is given value “Shirts”, ‘Price’ column is given value “19” and the ‘Quantity’ column is given value “20”.

The Inventory table is:

CREATE TABLE [dbo].[Inventory] (
    [Id]       INT          IDENTITY (1, 1) NOT NULL,
    [Name]     VARCHAR (50) NOT NULL,
    [Price]    MONEY        NOT NULL,
    [Quantity] INT          NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Also note that the parameters are passed in C# Anonymous type.

new { Name = "Shirts", Price = 19, Quantity = 20 }

The Execute method will return “1” since only 1 rows are inserted to the database table.

The below image shows our inventory record inserted in the database.

Execute Multiple Times

We can execute SQL statement multiple times by passing parameters in an array of anonymous types. The below code will execute SQL insert Query 2 times since we have used passed 2 anonymous types by using new[]{// 2 anonymous types} operator.

string myCS = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=DapperStart;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

var sql = @"Insert into Inventory(Name, Price, Quantity) values (@Name, @Price, @Quantity)";

using (var connection = new SqlConnection(myCS))
{
    var rowsAffected = connection.Execute(sql,
        new[]
        {
            new { Name = "Shirts", Price = 19, Quantity = 20  },
            new { Name = "Pants", Price = 25, Quantity = 30  }
        });
}

Dapper Parameters – Anonymous & Dynamic

We can add parameters to Dapper Execute method (or any other Dapper method) in 2 main ways –

1. Anonymous Type Parameters
2. Dynamic Parameters

Anonymous Type Parameters are defined by C# new keyword without explicitly defining their type. Example:

// Single execution of query with anonymous parameters
var rowsAffected = connection.Execute(sql, new { Name = "Shirts", Price = 19, Quantity = 20 });

// Multiple execution of query with anonymous parameters
var rowsAffected = connection.Execute(sql,
    new[]
    {
        new { Name = "Shirts", Price = 19, Quantity = 20  },
        new { Name = "Pants", Price = 25, Quantity = 30  }
    });

Dynamic parameters are added with DynamicParameters class. We use them when:

• We want to pass a variable number of arguments to a SQL statement.
• We want to dynamically build a query.
• We don’t know at compile time what kind of parameters will be used in a query.

In the below example we are inserting a single record and passing parameters in Dynamic manner.

var sql = @"Insert into Inventory(Name, Price, Quantity) values (@Name, @Price, @Quantity)";

using (var connection = new SqlConnection(myCS))
{
    DynamicParameters parameter = new DynamicParameters();
    parameter.Add("@Name", "Shirts", DbType.String, ParameterDirection.Input);
    parameter.Add("@Price", 19, DbType.Double, ParameterDirection.Input);
    parameter.Add("@Quantity", 20, DbType.Int32, ParameterDirection.Input);

    var rowsAffected = connection.Execute(sql, parameter);
}

In the same way we can insert multiple records by passing dynamic parameters to Execute method as shown below.

var sql = @"Insert into Inventory(Name, Price, Quantity) values (@Name, @Price, @Quantity)";

using (var connection = new SqlConnection(myCS))
{
    var parameterList = new List<DynamicParameters>();

    DynamicParameters parameter = new DynamicParameters();
    parameter.Add("@Name", "Shirts", DbType.String, ParameterDirection.Input);
    parameter.Add("@Price", 19, DbType.Double, ParameterDirection.Input);
    parameter.Add("@Quantity", 20, DbType.Int32, ParameterDirection.Input);

    parameterList.Add(parameter);

    parameter.Add("@Name", "Pants", DbType.String, ParameterDirection.Input);
    parameter.Add("@Price", 25, DbType.Double, ParameterDirection.Input);
    parameter.Add("@Quantity", 30, DbType.Int32, ParameterDirection.Input);

    parameterList.Add(parameter);

    var rowsAffected = connection.Execute(sql, parameterList);
}

All the dyanamic parameters are added to List<DynamicParameters> type variable and then passed to the Dapper Execute method.

Update Records

Dapper Execute method can be used to Update and Delete records in the database. In the below code we are updating the Inventory record whose Id is 1. Note that there are 4 total parameters in this SQL query. Three for the 3 Inventory table columns (@Name, @Price, @Quantity) that are to be updated. The Forth parameter (@Id) represents the Id value of the record.

var sql = @"Update Inventory SET Name = @Name, Price = @Price, Quantity = @Quantity Where Id = @Id";

//  myCS contains connection string
using (var connection = new SqlConnection(myCS))
{
    var rowsAffected = connection.Execute(sql, new { Name = "Pants", Price = 18, Quantity = 25 , Id = 1});
}

In the Execute method we have passed the 4 parameters in anonymous type – new { Name = "Pants", Price = 18, Quantity = 25 , Id = 1 }.

We can also update multiple records with the Execute method as shown by the below code. Here we are updating the records with ids 1 and 2.

var sql = @"Update Inventory SET Name = @Name, Price = @Price, Quantity = @Quantity Where Id = @Id";

//  myCS contains connection string
using (var connection = new SqlConnection(myCS))
{
    var rowsAffected = connection.Execute(sql,
        new[]
        {
            new { Name = "Pants", Price = 18, Quantity = 25, Id = 1 },
            new { Name = "Skirts", Price = 26, Quantity = 35, Id = 2 }
        });
}

Delete Records

Next, see how to delete records from the database. The deletion code is given below.

var sql = @"Delete From Inventory Where Id = @Id";

//  myCS contains connection string
using (var connection = new SqlConnection(myCS))
{
    var rowsAffected = connection.Execute(sql, new { Id = 1});
}

The above code will delete inventory record whose Id is 1. Here we passed new { Id = 1 } to specify value for the @Id parameter.

Similarly, we can delele multiple records also. See the below code:

var sql = @"Delete From Inventory Where Id = @Id";

//  myCS contains connection string
using (var connection = new SqlConnection(myCS))
{
    var rowsAffected = connection.Execute(sql,
        new[]
        {
            new { Id = 1 },
            new { Id = 2 }
        });
}

The SQL IN Command is used to pass multiple values in the Where clause. For example we can delete multiple values using “IN” command.

In the below code we are using IN command in the SQL delete statement to delete 3 records. Notice we have passed list of ids to the execute method.

var sql = @"Delete From Inventory Where Id IN @Ids";

using (var connection = new SqlConnection(myCS))
{
    var ids = new[] { 1, 2, 3 };
    var rowsAffected = connection.Execute(sql, new { Ids = ids });
}

Execute Stored Procedure

Dapper Execute method can be used to execute stored procedure. Here we need to set the 3rd parameter (commandType) to commandType: CommandType.StoredProcedure.

We have a stored procedure named CreateInventory which takes 3 parameters – @Name, @Price & @Quantity. It then inserts a new record to the Inventory table.

The stored procedure is given below:

CREATE PROCEDURE [dbo].[CreateInventory]
	@Name Varchar(50),
	@Price Money,
	@Quantity int
AS
	Insert into Inventory(Name, Price, Quantity) values(@Name, @Price, @Quantity)

The Stored Procedure can be executed single or multiple times with Dapper Execute method.

Execute Stored Procedure Single Time

The dapper code to execute this stored procedure only one time is given below:

using (var connection = new SqlConnection(myCS))
{
    var rowsAffected = connection.Execute("CreateInventory", new { Name = "Skirts", Price = 29, Quantity = 100 }, commandType: CommandType.StoredProcedure);
}

In the above code we are calling the stored procedure name “CreateInventory” and passing the 3 parameters – Name, Price & Quantity. We have also set commandType: CommandType.StoredProcedure. The stored procedure will execute and we will receive number of rows affected.

Execute Stored Procedure Multiple Times

We can also Execute the Stored Procedure multiple times i.e. once for every object in the array object. In the below code we are executing the stored procedure three times so 3 records will be inserted to the database.

using (var connection = new SqlConnection(myCS))
{
    var rowsAffected = connection.Execute("CreateInventory",
        new[]
        {
            new { Name = "Skirts", Price = 29, Quantity = 100 },
            new { Name = "Coats", Price = 69, Quantity = 200 },
            new { Name = "Caps", Price = 9, Quantity = 400 }
        },
    commandType: CommandType.StoredProcedure);
}

Execute Scalar

The ExecuteScalar Dapper method returns a scalar value which is the first column of the first row in the Result Set. It is very similar to Execute method and has same parameters like Execute method.

We can use ExecuteScalar method with SQL aggregate functions like count(*), SUM(), etc. In the below example we are getting the count of the number of records in the Inventory table.

var sql1 = "SELECT Count(*) FROM Inventory";

using (var connection = new SqlConnection(myCS))
{
    var count = connection.ExecuteScalar<int>(sql1);
}

Next, see the below code where we are getting the SUM of all the Price in the table.

var sql1 = "SELECT SUM(Price) FROM Inventory";
using (var connection = new SqlConnection(myCS))
{
    var sum = connection.ExecuteScalar<int>(sql);
}

<p>In the final example we retrive the name of the first record.</p>

var sql = "SELECT Name FROM Inventory";

using (var connection = new SqlConnection(myCS))
{
    var name = connection.ExecuteScalar<string>(sql);
}

Execute Reader

Dapper ExecuteReader method executes a SQL query or stored procedure and returns a result in the form of dynamic list. We can use this method to show the records in an HTML table on the view or fill a DataTable object. The ExecuteReader method’s parameters are same like Execute method.

The below code use ExecuteReader() method to retrive all the Inventory records and then fill a DataTable object with them.

using (var connection = new SqlConnection(myCS))
{
    var reader = connection.ExecuteReader("SELECT * FROM Inventory");

    DataTable table = new DataTable();
    table.Load(reader);
}

In the next code we execute a stored procedure called SelectInventory which takes a Price parameter and returns those records whose prices matches to this parameter’s value provided from C# code.

CREATE PROCEDURE [dbo].[SelectInventory]
	@Price Money
AS
	Select * from Inventory Where Price=@Price

<p>The Dapper code is:</p>

using (var connection = new SqlConnection(myCS))
{
    var reader = connection.ExecuteReader("SelectInventory @Price=@price", new { price = 90 });

    DataTable table = new DataTable();
    table.Load(reader);
}

So we get those inventory records whose price is 90.

Async Methods

Dapper has asynchronous versions of every methods that executes asynchronous queries i.e. ExecuteAsync, ExecuteScalarAsync and ExecuteReaderAsync.

The below example uses ExecuteAsync which is the asynchronous version of Execute method.

public async Task<IActionResult> InsertSingleA()
{
    // connection string from appsettings.json
    string myCS = cs.Value.DefaultConnection;

    var sql = @"Insert into Inventory(Name, Price, Quantity) values (@Name, @Price, @Quantity)";

    using (var connection = new SqlConnection(myCS))
    {
        var customers = await connection.ExecuteAsync(sql, new { Name = "Shoes", Price = 99, Quantity = 200 });
    }

    return View();
}

Download source codes:

Download

Here we learnt Dapper Execute method and it’s related ExecuteScalar and ExecuteReader methods. We took a lot of example (for both SQL query and Stored Procedure) that will help you to understand how to use these methods under every condition.