dapper | sql-server | orm

Dapper - Execute Multiple Stored Procedures

Dapper provides extension methods to execute multiple queries within the same command. The same can be used to execute multiple stored procedures.

Abhith Rajan
Abhith RajanSeptember 05, 2019 · 2 min read · Last Updated:

Dapper is my preferred ORM for performance constraint applications. It is one of the best solution in case of a database first approach as well as when there is already a database in place. Dapper works with SQL Server as well as so many other DB’s and I have tried Oracle DB apart from SQL Server.

Dapper have so many features/extension methods, the documentation of these features can be accessed here.

👉 Dapper Dapper Tutorial | Dapper Tutorial and Documentation .

To execute multiple stored procedures or multiple SQL statements, we can use the QueryMultipleAsync extension method. See the sample code below,

1using Dapper;
2...
3
4public async Task<SomeClass> GetAsync(Guid someId, Guid anotherId)
5{
6 using (var connection = new SqlConnection(_settings.ConnectionString()))
7 {
8 connection.Open();
9
10 const string sql = @"exec uspTableOneAction @someId;
11 exec uspTableTwoAnotherAction @anotherId;";
12
13 using (var multi = await connection.QueryMultipleAsync(sql, new { someId, anotherId }))
14 {
15 var firstTableItem = multi.Read<TableOneItem>().First();
16 var secondTableItem = multi.Read<TableTwoItem>().FirstOrDefault();
17 ...
18 }
19
20 ...
21 }
22}

In the above code, my stored procedure are,

  • uspTableOneAction
  • uspTableTwoAnotherAction

The naming convention which we follows for stored procedures are,

usp{TableName}{Action}

eg: uspUserInsert, uspUserUpdate

And in the above example, first stored procedure expects a parameter @someId. And the second stored procedure expects @anotherId. We passed these args by

1new { someId, anotherId }

The same can be written as,

1new { someId = someId, anotherId = anotherId }

Why we omitted the right hand side part in the example is because of the arguments name matching the parameter.

Just in case if both stored procedures expecting same parameter, say @someId. Then our args will look like,

1new { someId }

That’s it. You have the result of the two queries.

Additional Resources

This page is open source. Noticed a typo? Or something unclear?
Improve this page on GitHub


Abhith Rajan

Written byAbhith Rajan
Abhith Rajan is a software engineer by day and a full-stack developer by night. He's coding for almost a decade now. He codes 🧑‍💻, write ✍️, learn 📖 and advocate 👍.
Connect

Is this page helpful?

Related SnippetsView All

Related ArticlesView All

Related VideosView All

Using SQL Data Sync for Bidirectional Data Replication in SQL Server & Azure SQL DBs | Data Exposed

Build fast, scalable data system on Azure SQL Database Hyperscale | Clearent

SQL Server 2017 Graph Database

Related Tools & ServicesView All

sqlfum.pt

sequel fumpt

sqlfmt is an online SQL formatter. It is pronounced sequel fumpt. Its purpose is to beautifully format SQL statements.
SmarterASP.NET

SmarterASP.net - Unlimited ASP.NET Web Hosting

ASP.NET Hosting by SmarterASP.net. Unlimited ASP.NET Hosting Plans Starting at $2.95 a month.