NET 和具有多个结果集的存储过程

有什么方法可以将 Dapper.NET 用于返回多个结果集的存储进程吗？

在我的例子中，第一个结果集是一行，只有一列; 如果它是 0，那么调用就成功了，第二个结果集将包含实际的数据行/列。(如果它是非零的，就会出现错误，并且不会提供第二个结果集)

有机会用 Dapper.NET 处理这个吗？到目前为止，我只得到了一个单一的 0-但没有更多。

更新: OK，它工作得很好——只要结果集2是一个单独的实体:

Dapper.SqlMapper.GridReader reader =
_conn.QueryMultiple("sprocname", dynParams,
commandType: CommandType.StoredProcedure);


int status = reader.Read<int>().FirstOrDefault();
MyEntityType resultObj = reader.Read<MyEntityType>().FirstOrDefault();

现在，我有 又一个的要求。

Dapper 为第二个结果集提供的多映射(将从 SQL Server 返回的一行分解为两个独立的实体)似乎还不受支持(至少似乎不存在能够处理多映射的 .Read<T>过载)。

如何将该行拆分为两个实体？

87589 次浏览
小开
最佳答案

Have you tried the QueryMultiple method? It says it should:

Execute a command that returns multiple result sets, and access each in turn

You'll need to add this using statement to enable QueryMultiple .

using Dapper; /* to add extended method QueryMultiple public static GridReader QueryMultiple(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null); */
小开

QueryMultiple supports the ability to deal with multiple result sets. The only design restriction we added was totally disabling buffering for the grid reader. This means the whole API is streaming.

In the simplest case you can use: 

var grid = connection.QueryMultiple("select 1 select 2");
grid.Read<int>().First().IsEqualTo(1);
grid.Read<int>().First().IsEqualTo(2);

In the slightly more sophisticated case you can do crazy stuff like this: 

var p = new DynamicParameters();
p.Add("a", 11);
p.Add("r", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);


connection.Execute(@"create proc #spEcho
@a int
as
begin


select @a Id, 'ping' Name, 1 Id, 'pong1' Name
select @a Id, 'ping' Name, 2 Id, 'pong2' Name
return @a
end");


var grid = connection.QueryMultiple("#spEcho", p,
commandType: CommandType.StoredProcedure);


var result1 = grid.Read<dynamic, dynamic, Tuple<dynamic, dynamic>>(
(a, b) => Tuple.Create((object)a, (object)b)).ToList();
var result2 = grid.Read<dynamic, dynamic, Tuple<dynamic, dynamic>>(
(a, b) => Tuple.Create((object)a, (object)b)).ToList();


((int)(result1[0].Item1.Id)).IsEqualTo(11);
((int)(result1[0].Item2.Id)).IsEqualTo(1);


((int)(result2[0].Item1.Id)).IsEqualTo(11);
((int)(result2[0].Item2.Id)).IsEqualTo(2);


p.Get<int>("r").IsEqualTo(11);

You'll need to add this using statement to enable QueryMultiple .

using Dapper; /* to add extended method QueryMultiple public static GridReader QueryMultiple(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null); */
小开

Multiple result set.

var reader = conn.QueryMultiple("ProductSearch", param: new { CategoryID = 1 }, commandType: CommandType.StoredProcedure);
var ProductListOne = reader.Read<ProuductTbl>().ToList();
var ProductListTwo = reader.Read<ProuductTbl>().ToList();

You'll need to add this using statement to enable QueryMultiple .

using Dapper; /* to add extended method QueryMultiple public static GridReader QueryMultiple(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null); */

Stored procedure:

CREATE PROCEDURE [dbo].[ProductSearch]
@CategoryID as int
AS
BEGIN
SELECT * FROM ProductTbl
SELECT * FROM ProductTbl
END

提交答案