Call a stored procedure with multiple result sets from Dapper .NET
C# is an elegant and type-safe object-oriented language that enables developers to build a variety of secure and robust applications that run on the .NET Framework. You can use C# to create Windows client applications, XML Web services, distributed components, client-server applications, database applications, and much, much more. Visual C# provides an advanced code editor, convenient user interface designers, integrated debugger, and many other tools to make it easier to develop applications based on the C# language and the .NET Framework.
Dapper .NET provides a multiple result st support with the following method:
QueryMultiple
You can use it with both stored procedures and select statements, for instance:
1
2 string sql = "SELECT * FROM Customers WHERE CustomerId = @CustomerId; SELECT * FROM Messages WHERE CustomerId = @CustomerId;";
3
4 using (var connection = SqlServer.ConnectionFactory())
5 {
3
4 using (var connection = SqlServer.ConnectionFactory())
5 {
6 connection.Open();
7
8 using (var multResultSet = connection.QueryMultiple(sql, new {CustomerId = 1}))
9 {
10 var customer = multi.Read().First();
11 var messages = multi.Read().ToList();
12 }
13 }
Stored procedure example:
1 CREATE PROCEDURE [dbo].[CustomerSearch]
2 @CustomerID as int
3 AS
4 BEGIN
5 SELECT * FROM Customers WHERE CustomerId = @CustomerId;
6 SELECT * FROM Messages WHERE CustomerID = @CustomerId;
7 END;
8
9 using (var connection = SqlServer.ConnectionFactory())
10 {
11 connection.Open();
12
13 using (var multResultSet = connection.QueryMultiple("CustomerSearch", new {CustomerId = 1}))
14 {
15 var customer = multi.Read().First();
16 var messages = multi.Read().ToList();
17 }
18 }